PL/SQL 集合的初始化与赋值

对于集合类型，与单一的数据类型相比较而言，应该以一个整体的观念来考虑集合，即是一批类型相同的数据组合而非单一的数据。因此集
合类型集合的声明、赋值、初始化较之单一类型而言，有很大的不同。尤其是嵌套表与变长数组，在赋值之前必须先初始化。当嵌套表和变长数
组在声明时，它们都会自动地被设置成NULL值。也就是嵌套表和变长数组中集合不存在任何元素，并不是针对它所拥有的元素。可以使用系统定
义的与集合类型同名的函数来初始化集合。我们必须显式地调用构造函数为每一个变长数组和嵌套表变量进行初始化操作(对于关联数组来说，
是不需要使用构造函数进行初始化的)。

有关集合类型的描述请参考：
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

一、联合数组的赋值
联合数组的不需要初始化，直接赋值即可。(后续讲到的集合的初始化均指嵌套表与变长数组)
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为函数或过程。

三、集合的初始化与赋值引用示例
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方式来扩展。 四、总结
1、对于集合类型在为其赋值之前，需要对集合进行初始化。而联合数组不需要初始化而直接进行赋值。
2、在声明嵌套表与变长数组时，这些集合类型会被自动置为NULL，即集合不存在任何元素。而不是集合中的元素为NULL。
3、集合类型的初始化方法是是直接使用声明时的同名类型构造器来对集合进行初始化。
4、集合类型的初始化方法有多种，可以在声明时初始化，也可以在执行块初始化。
5、集合类型的赋值可以在声明块声明时赋值，也可以在执行块执行时使用extend方式扩展后再赋值。
6、集合类型的初始化过程连同赋值可以在声明集合的同时使用构造函数直接进行初始化并赋值，从而一步完成。
7、SQL语句中也需要使用构造函数来操作集合数据。
8、注意本文描述中的集合初始化后为空的理解。初始化后为空表示的是一个空(empty)集合，而未初始化时是NULL(UNKNOWN)值。
9、集合与集合之间的赋值需要声明的为同一类型的变量之间才可以赋值，否则收到错误提示。

10、注意理解复合类型之间(嵌套表和变长数组中嵌有PL/SQL记录)的变量元素间的传递以及集合方法BULK COLLECT INTO，LAST，EXTEND等。

五、更多参考

PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

启用 AUTOTRACE 功能

函数使得索引列失效

Oracle 绑定变量窥探

Oracle 自适应共享游标