PL/SQL 联合数组与嵌套表

PL/SQL 联合数组与嵌套表

转自：http://blog.csdn.net/leshami/article/details/7372061
 
通常情况下，在PL/SQL中，处理单行单列的数据可以使用标量变量，而处理单行多列的数据则使用PL/SQL记录是不错的选择。
单列多行数据则由联合数组或嵌套表来完成，其特点是类似于单列数据库表。
在Oracle 9i 之前称为PL/SQL索引表，9i 之后称之为联合数组。
嵌套表也是集合类型中的一种，下面分别介绍这两种集合数据类型的使用方法。


一、联合数组

1、联合数组的特性  
   类似于一张简单的SQL表，按照主键检索数据  
   其数据行并不是按照预定义的顺序存储。当使用变量来检索其数据时，每行数据会分配一个连续的下标且从1开始。 
   下标可以为负值，且下标的数据类型支持binary_integer,pls_integer,varchar2 
    其数据元素个数无限制 
   不能作为表列的数据类型使用，即只能作为PL/SQL复合数据类型使用  
   存放的数据类型是临时数据，故不支持insert,select into 等SQL语句,等同于SQL server中的表变量 
         
2、语法         
    TYPEtype_name IS TABLE OF element_type [NOT NULL] -->element_type为联合数组元数据指定数据类型(先使用TYPE声明表结构)  
    INDEX BYkey_type;                                 -->元素下标的数据类型  
    table_nameTYPE_NAME;                              -->再使用声明的TYPE类型声明实际数组名  
         
3、示例  
--使用PLS_INTEGE类型联合数组   
scott@CNMMBO>DECLARE                                  -->定义游标         
 2    CURSOR name_cur IS  
 3       SELECT dname  
 4       FROM   dept 
 5       WHERE  deptno < 40; 
  6   
 7    TYPE name_type IS TABLE OFdept.dname%TYPE     -->定义一个联合数组类型，且其下标的数据类型为PLS_INTEGER  
 8                         INDEX BY PLS_INTEGER;  
  9   
 10    dname_tab  name_type;                         -->声明类型为name_type的联合数组dname_tab  
 11    v_counter   INTEGER := 0; 
 12  BEGIN 
 13    FOR name_rec IN name_cur  
 14    LOOP  
 15       v_counter   := 
 16          v_counter  
 17          1;  
 18       dname_tab( v_counter ) :=name_rec.dname;     -->对联合数据进行循环赋值  
 19       DBMS_OUTPUT.put_line(   'Dnameis('  
 20                            || v_counter  
 21                            || '):'  
 22                            || dname_tab( v_counter ) );  -->输出联合数组中的所有元素  
 23    END LOOP;  
 24  END; 
 25  /  
Dname is(1):ACCOUNTING  
Dname is(2):RESEARCH  
Dname is(3):SALES  
 
PL/SQL procedure successfully completed.  
 
--使用VARCHAR2类型联合数组   
scott@CNMMBO> DECLARE  
 2    TYPE score_type IS TABLE OF NUMBER  
 3                          INDEX BY VARCHAR2( 10);   -->注意此处声明的联合数组的下标数据类型为varchar2  
  4   
 5    score_tab   score_type; 
  6  BEGIN 
 7    score_tab( 'SCOTT' ) :=95;                      -->对不同下标分别进行赋值  
 8    score_tab( 'JOHN' ) := 98;  
 9    score_tab( 'ROBINSON' ) := 96;  
 10    DBMS_OUTPUT.put_line( 'First element is '  
 11                         || score_tab.FIRST);       -->输出联合数组score_tab中的第一个元素的下标  
 12    DBMS_OUTPUT.put_line( 'last element is '  
 13                         || score_tab.LAST);        -->输出联合数组score_tab中的最后一个元素的下标  
 14    DBMS_OUTPUT.put_line( 'The score of Scott is ' 
 15                         || score_tab( 'SCOTT' ) );  -->输出下标为SCOTT的元素的值  
 16  END; 
 17  /  
First element isJOHN                                    -->注意此处输出的是JOHN(第一个和最后一个是按字母排序得到的)  
last element is SCOTT  
The score of Scott is 95  
 
PL/SQL procedure successfully completed.  
 
--下标超范围的情况   
scott@CNMMBO> DECLARE  
 2    TYPE score_type IS TABLE OF NUMBER  
 3                          INDEX BY VARCHAR2( 10 );  
  4   
 5    score_tab   score_type; 
  6  BEGIN 
 7    score_tab( 'SCOTT' ) := 95;  
 8    score_tab( 'JOHN' ) := 98;  
 9    score_tab( 'ROBINSON' ) := 96;  
 10    DBMS_OUTPUT.put_line( 'The score of Jason is  ' 
 11                         || score_tab( 'JASON' ) );  
 12  END; 
 13  /  
DECLARE  
*  
ERROR at line 1:  
ORA-01403: no datafound                             -->当下标超范围时，将收到ORA-01403错误  
ORA-06512: at line 10  


二、嵌套表


1、嵌套表的特点：  
   元素下标从1开始，个数没有限制.即元素个数可以动态增长  
   嵌套表的数组元素值可以是稀疏的，即可以使得中间的某个元素没有赋值  
   嵌套表的语法与联合数组类似，不同的是仅仅是少了indexby子句    
   嵌套表必需先初始化，然后才能引用其中的元素。如果初始化为空值，则后续需要使用extend来扩展其大小   
   嵌套表初始化时为密集的，但允许有间隙，即允许使用内置过程delete从嵌套表中删除元素 
   嵌套表类型可以作为表列的数据类型来使用  
 
2、语法  
       TYPE type_name IS TABLE OF element_type [NOTNULL];  -->element_type为索引表元数据指定数据类型(先使用TYPE声明表结构)   
       table_nameTYPE_NAME;                                -->再使用声明的TYPE类型声明实际嵌套表         
 
3、示例  
--声明嵌套表并输出嵌套表的实际内容            
scott@CNMMBO> DECLARE  
 2    CURSOR name_cur IS  
 3       SELECT dname  
 4       FROM   dept 
 5       WHERE  deptno < 40; 
  6   
 7    TYPE name_type IS TABLE OF dept.dname%TYPE; 
  8   
 9    dname_tab   name_type :=name_type( );  -->需要初始化,否则将收到 ORA-06531：Reference to uninitializedcollection.   
 10    v_counter   INTEGER := 0; 
 11  BEGIN 
 12    FOR name_rec IN name_cur  
 13    LOOP  
 14       v_counter   := 
 15          v_counter  
 16          1;  
 17       dname_tab.EXTEND;        -->需要扩展，否则将收到 ORA-06533: Subscript beyondcount   
 18       dname_tab( v_counter ) := name_rec.dname;  
 19       DBMS_OUTPUT.put_line(   'Dname (' 
 20                            || v_counter  
 21                            || ') is :'  
 22                            || dname_tab( v_counter ) );  
 23    END LOOP;  
 24  END; 
 25  /  
Dname (1) is :ACCOUNTING  
Dname (2) is :RESEARCH  
Dname (3) is :SALES  
 
PL/SQL procedure successfully completed.  
 
--将嵌套表作为表列的数据类型来使用   
scott@CNMMBO> CREATE OR REPLACE TYPE mail_type ISTABLE OF VARCHAR2( 100 );  
  2  /  
 
Type created.  
 
scott@CNMMBO> CREATE TABLEtb_tmp      -->创建表tb_tmp  
  2  (  
 3    empno   NUMBER( 4 ) 
  4   ,ename   VARCHAR2( 10 ) 
  5   ,mail   mail_type              -->列mail的类型为mail_type  
  6  )  
  7  NESTED TABLEmail                -->注意此处需要指定嵌套表的存储方式  
 8    STORE AS mail_tab;  
 
Table created.  
 
--为嵌套表插入数据   
scott@CNMMBO> insert into tb_tmp select8888,'Jack','[e-mail],[e-mail]' fromdual; -->传统方式插入失败  
insert into tb_tmp select 8888,'Jack','[e-mail],[e-mail]'from dual  
                                     *  
ERROR at line 1:  
ORA-00932: inconsistent datatypes: expected - got CHAR 
 
scott@CNMMBO> insert into tb_tmp select8888,'Jack',q'['[e-mail]','[e-mail]']' fromdual; -->分割字符串方式插入失败  
insert into tb_tmp select8888,'Jack',q'['[e-mail]','[e-mail]']' from dual 
                                     *  
ERROR at line 1:  
ORA-00932: inconsistent datatypes: expected - got CHAR 
 
scott@CNMMBO> insert into tb_tmp select8888,'Jack',mail_type('[e-mail]','[e-mail]') from dual;-->插入时指定嵌套表类型   
1 row created.  
 
scott@CNMMBO> commit;  
Commit complete.  
 
--查看存在嵌套表数据类型的表中的记录   
scott@CNMMBO> col mail format a30 
scott@CNMMBO> select * fromtb_tmp;                   -->查询数据时带有嵌套表的类型  
 
    EMPNOENAME     MAIL  
---------- ----------------------------------------  
     8888Jack      MAIL_TYPE('[e-mail]', 'J  
                     [e-mail]')  
 
scott@CNMMBO> DECLARE  
 2    mail_tab  mail_type;   -->声明一个mail_type数据类型  
  3  BEGIN 
 4    SELECT mail  
 5    INTO  mail_tab         -->将数据保存到 mail_tab 变量中  
 6    FROM   tb_tmp 
 7    WHERE  empno = 8888;  
  8   
 9    FOR i IN 1 .. mail_tab.COUNT  -->轮循输出嵌套表类型中的值  
 10    LOOP  
 11       DBMS_OUTPUT.put_line( 'Jack mail address is ' 
 12                            || mail_tab( i ) );  
 13    END LOOP;  
 14  END; 
 15  /  
Jack mail address is [e-mail]  
Jack mail address is [e-mail]  
 
PL/SQL procedure successfully completed.  
 
--更新表中含有嵌套表类型中的值   
scott@CNMMBO> DECLARE  
 2    mail_tab   mail_type :=mail_type( '[e-mail]', '[e-mail]' ); 
  3  BEGIN 
 4    UPDATE tb_tmp  
 5    SET mail =  mail_tab;  
  6   
 7    COMMIT;  
 8    DBMS_OUTPUT.put_line( 'Jack mail address was updated' ); 
  9  END; 
 10  /  
Jack mail address was updated  
 
PL/SQL procedure successfully completed.  
 
scott@CNMMBO> select * from tb_tmp; 
 
    EMPNOENAME     MAIL  
---------- ----------------------------------------  
     8888Jack      MAIL_TYPE('[e-mail]',  
                      '[e-mail]')  
 
--删除表中存在嵌套表类型的记录   
scott@CNMMBO> delete from tb_tmp where empno=8888; 
1 row deleted.  
 
scott@CNMMBO> commit;  
Commit complete.  
 
scott@CNMMBO> select * from tb_tmp; 
no rows selected   
 

三、总结
1、不论是联合数组还是嵌套表，应当以集合的方式来看待与处理
2、联合数组在声明其类型时需要指定index by子句，而嵌套表则不需要
3、联合数组和嵌套表两者元素个数无限制   
4、联合数组不需要初始化，而嵌套表则需要对其进行初始化。其次是嵌套表初始化赋空值的情形则后续需要使用extend方式来扩展集合尺寸的大小
5、联合数组不能作为表上列的数据类型，而嵌套表无此限制

                     
--> Author: Robinson Cheng  
--> Blog: http://blog.csdn.net/robinson_0612