Oracle之数组
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle之数组,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含16775字,纯文字阅读大概需要24分钟。
内容图文
![Oracle之数组](/upload/InfoBanner/zyjiaocheng/477/68f5fb477a9547c1988be2c6c64400e2.jpg)
联合数组的不需要初始化,直接赋值即可。(后续讲到的集合的初始化均指嵌套表与变长数组)
[sql] view plain copy
- DECLARE
- TYPE idx_loc_type IS TABLE OF VARCHAR2( 13 )
- INDEX BY BINARY_INTEGER;
- loc_tab idx_loc_type;
- BEGIN
- loc_tab( 1 ) := ‘NEW YORK‘; -->联合数组不需要初始化,直接赋值即可
- loc_tab( 2 ) := ‘DALLAS‘;
- DBMS_OUTPUT.put_line( ‘ loc_tab(1) value is ‘ || loc_tab( 1 ) );
- DBMS_OUTPUT.put_line( ‘ loc_tab(2) value is ‘ || loc_tab( 2 ) );
- END;
- --------------------------------------------------------------------------------------------------------
- DECLARE
- TYPE idx_loc_type IS TABLE OF VARCHAR2( 13 )
- INDEX BY BINARY_INTEGER;
- loc_tab idx_loc_type;
- v_counter INTEGER := 0;
- BEGIN
- FOR x IN ( SELECT loc FROM dept ) -->这里通过for 循环得到loc的值
- LOOP
- v_counter := v_counter + 1; -->使用一个v_counter变量来控制联合数组的下标
- loc_tab( v_counter ) := x.loc; -->将得到的loc的值赋值给联合数组中对应的一个下标位
- DBMS_OUTPUT.put_line( ‘ loc_tab(‘ || v_counter || ‘) value is ‘ || loc_tab( v_counter ) );
- END LOOP;
- END;
二、集合的初始化与赋值
1、初始化的方法集合类型主要分为三步来完成,一是声明,二是初始化,三是赋值。初始化和赋值可以在声明块中完成,也可以在执行块中完成。
collection_name collection_type:=collection_type(); -->初始化集合为空(empty)
集合的初始化主要是通过构造函数(构造函数即是声明类型是的类型名)来进行初始化,下面常用的初始化方法包括:
a、在声明块声明集合,且在声明块中使用构造函数初始化为空(empty)但非NULL,在执行块中使用extend方式后进行赋值
b、在声明块声明集合,在执行块中使用构造函数初始化为空(empty)但非NULL,在执行块中使用extend方式后赋值
c、在声明块声明集合,在执行块中使用构造函数初始化时一并赋值
d、在声明块声明集合,同时使用构造函数初始化并赋值,即三步合为一步来完成
对于初始化为空的集合(empty),后续需要使用extend方式来扩展容量,除非使用bulk collect into方式
2、集合赋值的方法
collection_name(subscript) := expression;
3、赋值时可能引起的异常
在下面几种给集合元素赋值的情况下,可能会引起多种异常。
a、如果下标索引不存在或无法转换成正确的数据类型,PL/SQL就会抛出预定义异常VALUE_ERROR。
通常,下标是一个整数。但关联数组的下标也可以是VARCHAR2类型。
b、如果所给下标索引指向了一个未初始化的元素时,PL/SQL就会抛出SUBSCRIPT_BEYOND_COUNT异常。
c、如果集合被自动初始化为空值并且程序引用了其中的一个元素,PL/SQL会抛出COLLECTION_IS_NULL异常。
4、元素的引用
collection_name(subscript)
可以把其中的表元素作为参数传递。如verify_loc(nest_loc_tab(i)),verify_loc为函数或过程。
三、集合的初始化与赋值引用示例
[sql] view plain copy
- 1、未初始化集合的情形
- DECLARE
- TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 );
- loc_tab nest_loc_type;
- BEGIN
- loc_tab( 1 ) := ‘NEW YORK‘;
- loc_tab( 2 ) := ‘DALLAS‘;
- DBMS_OUTPUT.put_line( ‘ loc_tab(1) value is ‘ || loc_tab( 1 ) );
- DBMS_OUTPUT.put_line( ‘ loc_tab(2) value is ‘ || loc_tab( 2 ) );
- END;
- DECLARE
- *
- ERROR at line 1:
- ora-06531: Reference to uninitialized collection -->收到了ora-06531错误提示,变长数组未初始化时会收到同样的错误提示
- ora-06512: at line 6
- --------------------------------------------------------------------------------------------------------
- 2、集合为NULL的判断
- DECLARE
- TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 );
- loc_tab nest_loc_type;
- BEGIN
- IF loc_tab IS NULL THEN
- DBMS_OUTPUT.put_line( ‘Before initialization, the loc_tab is null.‘ );
- -- While the collection is null, we cannot check its COUNT attribute.
- -- DBMS_OUTPUT.PUT_LINE(‘It has ‘ || loc_tab.COUNT || ‘ elements.‘);
- ELSE
- DBMS_OUTPUT.put_line( ‘Before initialization, the loc_tab is not null.‘ );
- END IF;
- loc_tab := nest_loc_type( ); --> initialize empty nest table
- IF loc_tab IS NULL THEN
- DBMS_OUTPUT.put_line( ‘After initialization, the loc_tab is null.‘ );
- ELSE
- DBMS_OUTPUT.put_line( ‘After initialization, the loc_tab is not null.‘ );
- DBMS_OUTPUT.put_line( ‘It has ‘ || loc_tab.COUNT || ‘ elements.‘ );
- END IF;
- END;
- Before initialization, the loc_tab is null.
- After initialization, the loc_tab is not null.
- It has 0 elements.
- PL/SQL procedure successfully completed.
- --------------------------------------------------------------------------------------------------------
- 3、使用空构造函数在声明时进行初始化
- -->使用该方法初始化之后,表明嵌套表或变成数组是空的,但是非NULL,在执行块再对其赋值
- -->下面对变长数组进行初始化
- DECLARE
- TYPE varry_loc_type IS VARRAY( 10 ) OF scott.dept.loc%TYPE;
- varry_loc_tab varry_loc_type := varry_loc_type( ); -->仅仅是在集合变量之后使用空构造函数
- BEGIN
- varry_loc_tab( 1 ) := ‘NEW YORK‘; -->尽管变长数组被初始化,但仍然不能直接赋值
- varry_loc_tab( 2 ) := ‘DALLAS‘; -->这是由变长数组和嵌套表特性决定需要先做extend
- DBMS_OUTPUT.put_line( ‘ varry_loc_tab(1) value is ‘ || varry_loc_tab( 1 ) );
- DBMS_OUTPUT.put_line( ‘ varry_loc_tab(2) value is ‘ || varry_loc_tab( 2 ) );
- END;
- DECLARE
- *
- ERROR at line 1:
- ora-06533: subscript beyond count
- ora-06512: at line 6
- --------------------------------------------------------------------------------------------------------
- 4、使用空构造函数在声明时进行初始化,执行块使用extend方式扩展后赋值
- DECLARE
- TYPE varry_loc_type IS VARRAY( 10 ) OF scott.dept.loc%TYPE;
- varry_loc_tab varry_loc_type := varry_loc_type( ); -->仅仅是在集合变量之后使用空构造函数
- BEGIN
- varry_loc_tab.EXTEND; -->需要使用extend方式扩展集合容量
- varry_loc_tab( 1 ) := ‘NEW YORK‘;
- varry_loc_tab.EXTEND; -->需要使用extend方式扩展集合容量
- varry_loc_tab( 2 ) := ‘DALLAS‘;
- DBMS_OUTPUT.put_line( ‘ varry_loc_tab(1) value is ‘ || varry_loc_tab( 1 ) );
- DBMS_OUTPUT.put_line( ‘ varry_loc_tab(2) value is ‘ || varry_loc_tab( 2 ) );
- END;
- --------------------------------------------------------------------------------------------------------
- 5、嵌套表的初始化,使用构造函数在执行块直接初始化并赋值
- DECLARE
- TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 );
- loc_tab nest_loc_type;
- BEGIN
- loc_tab :=
- nest_loc_type( ‘NEW YORK‘ -->使用声明时的类型nest_loc_type函数来进行初始化
- ,‘DALLAS‘
- ,‘CHICAGO‘
- ,‘BOSTON‘ );
- FOR i IN 1 .. loc_tab.COUNT
- LOOP
- DBMS_OUTPUT.put_line( ‘loc_tab(‘ || i || ‘) value is ‘ || loc_tab( i ) );
- END LOOP;
- END;
- --------------------------------------------------------------------------------------------------------
- 6、含有NOT NULL嵌套表的初始化
- DECLARE
- TYPE loc_type IS TABLE OF VARCHAR2( 13 ) NOT NULL; -->定义了NOT NULL约束条件
- loc_tab loc_type;
- BEGIN
- loc_tab :=
- loc_type( ‘NEW york‘
- ,NULL -->构造时传递了NULL值
- ,NULL
- ,‘boston‘ );
- FOR i IN 1 .. loc_tab.COUNT
- LOOP
- DBMS_OUTPUT.put_line( ‘loc_tab(‘ || i || ‘) value is ‘ || loc_tab( i ) );
- END LOOP;
- END;
- -->由于存在not null约束,初始化传递null值则收到错误提示
- ERROR at line 8:
- ora-06550: line 8, column 17:
- pls-00567: cannot pass NULL to a NOT NULL constrained formal parameter
- ora-06550: line 9, column 17:
- pls-00567: cannot pass NULL to a NOT NULL constrained formal parameter
- ora-06550: line 6, column 4:
- pl/SQL: Statement ignored
- --------------------------------------------------------------------------------------------------------
- 7、变长数组的初始化,使用构造函数直接初始化并赋值
- -->变长数组的初始化与嵌套表一样,可以使用构造函数直接初始化并赋值
- DECLARE
- TYPE varry_loc_type IS VARRAY( 10 ) OF scott.dept.loc%TYPE;
- varry_loc_tab varry_loc_type;
- BEGIN
- varry_loc_tab :=
- varry_loc_type( ‘NEW YORK‘
- ,‘DALLAS‘
- ,‘CHICAGO‘
- ,‘BOSTON‘ );
- FOR i IN varry_loc_tab.FIRST .. varry_loc_tab.LAST -->注意此处使用了集合方法中的函数first和last来控制循环步长
- LOOP
- DBMS_OUTPUT.put_line( ‘varry_loc_tab(‘ || i || ‘) value is ‘ || varry_loc_tab( i ) );
- END LOOP;
- END;
- --------------------------------------------------------------------------------------------------------
- 8、声明时初始化(构造)、并赋值
- DECLARE
- TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 ) NOT NULL;
- nest_loc_tab nest_loc_type := nest_loc_type( ‘NEW YORK‘, ‘DALLAS‘, ‘CHICAGO‘ ); -->在声明时直接初始化并赋值
- BEGIN
- FOR i IN 1 .. nest_loc_tab.COUNT -->注意此处调用了集合操作方法中的count函数
- LOOP
- DBMS_OUTPUT.put_line( ‘nest_loc_tab(‘ || i || ‘) value is ‘ || nest_loc_tab( i ) );
- END LOOP;
- END;
- --------------------------------------------------------------------------------------------------------
- 9、SQL语句中使用构造函数
- CREATE OR REPLACE TYPE mail_type IS TABLE OF VARCHAR2( 100 ); -->创建一个嵌套表类型
- CREATE TABLE tb_tmp -->创建表tb_tmp
- (
- empno NUMBER( 4 )
- ,ename VARCHAR2( 10 )
- ,mail mail_type -->列mail的类型为mail_type
- )
- NESTED TABLE mail -->注意此处需要指定嵌套表的存储方式
- STORE AS mail_tab;
- INSERT INTO tb_tmp
- SELECT 8888, ‘Jack‘, mail_type( ‘Jack@yahoo.com‘, ‘Jack@163.com‘ ) FROM dual; -->插入数据时需要使用构造函数
- 10、集合与集合之间的赋值
- -->下面的例子声明了两个变长数组last_name_type和surname_type
- DECLARE
- TYPE last_name_type IS VARRAY( 3 ) OF VARCHAR2( 64 );
- TYPE surname_type IS VARRAY( 3 ) OF VARCHAR2( 64 );
- -->下面声明了两个相同类型的变长数组并为其赋值,group1和group2使用了相同的构造函数
- group1 last_name_type := last_name_type( ‘Jones‘, ‘Wong‘, ‘Marceau‘ );
- group2 last_name_type := last_name_type( ‘Klein‘, ‘Patsos‘, ‘Singh‘ );
- -->下面的group3使用了surname_type作为类型
- group3 surname_type := surname_type( ‘Trevisi‘, ‘Macleod‘, ‘Marquez‘ );
- BEGIN
- group1 := group2; -- >group1 和group2之间可以相互赋值
- -- group3 := group2; -->raises an error PLS-00382: expression is of wrong type
- END;
- -- >group3和group2则不能赋值,因为两者为不同的数据类型
- -->尽管last_name_type与surname_type类型定义是相同的,但其实例化后,其集合变量不能互相赋值
- --------------------------------------------------------------------------------------------------------
- 11、使用NULL值集合为集合赋值
- DECLARE
- TYPE nest_loc_type IS TABLE OF VARCHAR2( 30 );
- nest_loc_tab nest_loc_type := nest_loc_type( ‘NEW YORK‘, ‘DALLAS‘, ‘CHICAGO‘ );
- empty_nest_loc_tab nest_loc_type; --> 嵌套表empty_nest_loc_tab没有初始化,此时被自动置为NULL
- BEGIN
- IF nest_loc_tab IS NOT NULL THEN
- DBMS_OUTPUT.put_line( ‘OK, at first nest_loc_tab is not null.‘ );
- END IF;
- nest_loc_tab := empty_nest_loc_tab; -->将empty_nest_loc_tab的值(NULL)嵌套表赋值给nest_loc_tab
- IF nest_loc_tab IS NULL THEN --> 此时nest_loc_tab被置为NULL,相当于没有初始化
- DBMS_OUTPUT.put_line( ‘OK, now nest_loc_tab has become null.‘ );
- END IF;
- nest_loc_tab := nest_loc_type( ‘NEW YORK‘, ‘DALLAS‘, ‘CHICAGO‘ ); -->此时如果后续需要使用该嵌套表,应重新初始化它
- END;
- 12、记录类型的变长数组的初始化、赋值与元素引用
- DECLARE
- TYPE emp_name_rec IS RECORD -->声明一个基于用户定义的记录类型
- (
- firstname employees.first_name%TYPE
- ,lastname employees.last_name%TYPE
- ,hiredate employees.hire_date%TYPE
- );
- TYPE emplist_arr IS VARRAY( 10 ) OF emp_name_rec; -->声明一个基于记录的变长数组,且最大尺寸为10
- seniorsalespeople emplist_arr; -->声明基于记录的变长数组变量
- CURSOR c1 IS -->声明游标,其列前面的记录类型相对照
- SELECT first_name, last_name, hire_date FROM employees;
- TYPE nameset IS TABLE OF c1%ROWTYPE; -->声明基于游标的记录类型
- seniorten nameset; -->声明基于游标记录类型的变量
- endcounter NUMBER := 10; -->变量endcounter计数器
- BEGIN
- seniorsalespeople := emplist_arr( ); -->初始化集合
- SELECT first_name, last_name, hire_date -->从表中提取数据,且使用了BULK COLLECT INTO方式
- BULK COLLECT INTO seniorten
- FROM employees
- WHERE job_id = ‘SA_REP‘
- ORDER BY hire_date;
- IF seniorten.LAST > 0 THEN
- IF seniorten.LAST < 10 THEN -->如果小于10,则缩小变长数组的最大尺寸
- endcounter := seniorten.LAST;
- END IF;
- FOR i IN 1 .. endcounter -->使用循环将游标类型变量中的元素逐条赋值给记录的变长数组变量seniorsalespeople并输出
- LOOP
- seniorsalespeople.EXTEND( 1 );
- seniorsalespeople( i ) := seniorten( i );
- DBMS_OUTPUT.
- put_line(seniorsalespeople(i).lastname||‘, ‘||seniorsalespeople(i).firstname||‘, ‘||seniorsalespeople(i).hiredate);
- END LOOP;
- END IF;
- END;
- 上面的这个例子是一复合的数据类型,比单一的集合类型更为复杂。我们知道集合通常是针对单列多行数据而言,而记录则是单行多列。两
- 者的综合,则此时就等同于数据库中的一张二维表。示例中首先声明用户定义的记录类型以及变长数组,接下来基于这两者声明变量。后面使用
- 基于游标的记录类型来申明变量seniorten与前面的变量seniorsalespeople相对应,seniorten变量用于存储后面的SQL语句批量提取的数据集。
- 后面使用了一个for循环来从seniorten变量取出数据并赋值为seniorsalespeople。注:在这个例子中变量seniorten存储的记录超出了变长数组
- 的最大尺寸,因此后续的被丢弃。
- 13、记录类型的嵌套表的初始化、赋值与元素引用
- DECLARE
- TYPE emp_name_rec IS RECORD
- (
- firstname employees.first_name%TYPE
- ,lastname employees.last_name%TYPE
- ,hiredate employees.hire_date%TYPE
- );
- TYPE emplist_tab IS TABLE OF emp_name_rec;
- seniorsalespeople emplist_tab;
- endcounter NUMBER := 10;
- TYPE empcurtyp IS REF CURSOR; -->声明游标变量类型
- emp_cv empcurtyp; -->声明游标变量类型的变量emp_cv
- BEGIN
- OPEN emp_cv FOR
- SELECT first_name, last_name, hire_date
- FROM employees
- WHERE job_id = ‘SA_REP‘
- ORDER BY hire_date;
- FETCH emp_cv
- BULK COLLECT INTO seniorsalespeople; -->使用BULK COLLECT INTO 方式一次将数据加载到seniorsalespeople变量
- CLOSE emp_cv;
- IF seniorsalespeople.LAST > 0 THEN
- IF seniorsalespeople.LAST < 10 THEN
- endcounter := seniorsalespeople.LAST;
- END IF;
- FOR i IN 1 .. endcounter
- LOOP
- DBMS_OUTPUT.
- put_line(seniorsalespeople(i).lastname||‘, ‘||seniorsalespeople(i).firstname||‘, ‘||seniorsalespeople(i).hiredate);
- END LOOP;
- END IF;
- END;
- -->Author : Robinson Cheng
- -->Blog : http://blog.csdn.net/robinson_0612
- 上面的这个例子稍有不同于前面的例子,使用的基于用户定义记录的嵌套表方式,且使用了游标变量类型。在fetch时直接将数据fetch 到
- 集合变量seniorsalespeople中,此时不需要使用extend方式来扩展。
四、总结
<span style="font-size: 14px; line-height: 26px; font-family: SimSun 本文系统来源:http://www.cnblogs.com/aipan/p/6516163.html内容总结
以上是互联网集市为您收集整理的Oracle之数组全部内容,希望文章能够帮你解决Oracle之数组所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。