PL/SQL 联合数组与嵌套表

3121321321

通常情况下，在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、语法         
        TYPE type_name IS TABLE OF element_type [NOT NULL]  -->element_type为联合数组元数据指定数据类型(先使用TYPE声明表结构)  
        INDEX BY key_type;                                  -->元素下标的数据类型  
        table_name TYPE_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 OF dept.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(   'Dname is('  
     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 is JOHN                                     -->注意此处输出的是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 data found                              -->当下标超范围时，将收到ORA-01403错误  
    ORA-06512: at line 10  

二、嵌套表


    1、嵌套表的特点：  
        元素下表从1开始，个数没有限制.即元素个数可以动态增长  
        嵌套表的数组元素值可以是稀疏的，即可以使得中间的某个元素没有赋值  
        嵌套表的语法与联合数组类似，不同的是仅仅是少了index by子句     
        嵌套表必需先初始化，然后才能引用其中的元素。如果初始化为空值，则后续需要使用extend来扩展其大小   
        嵌套表初始化时为密集的，但允许有间隙，即允许使用内置过程delete从嵌套表中删除元素  
        嵌套表类型可以作为表列的数据类型来使用  
      
    2、语法  
            TYPE type_name IS TABLE OF element_type [NOT NULL];   -->element_type为索引表元数据指定数据类型(先使用TYPE声明表结构)   
            table_name TYPE_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 uninitialized collection.  
     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 beyond count  
     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 IS TABLE OF VARCHAR2( 100 );  
      2  /  
      
    Type created.  
      
    scott@CNMMBO> CREATE TABLE tb_tmp       -->创建表tb_tmp  
      2  (  
      3     empno   NUMBER( 4 )  
      4   , ename   VARCHAR2( 10 )  
      5   , mail    mail_type               -->列mail的类型为mail_type  
      6  )  
      7  NESTED TABLE mail                 -->注意此处需要指定嵌套表的存储方式  
      8     STORE AS mail_tab;  
      
    Table created.  
      
    --为嵌套表插入数据  
    scott@CNMMBO> insert into tb_tmp select 8888,'Jack','Jack@yahoo.com,Jack@163.com' from dual;  -->传统方式插入失败  
    insert into tb_tmp select 8888,'Jack','Jack@yahoo.com,Jack@163.com' from dual  
                                          *  
    ERROR at line 1:  
    ORA-00932: inconsistent datatypes: expected - got CHAR  
      
    scott@CNMMBO> insert into tb_tmp select 8888,'Jack',q'['Jack@yahoo.com','Jack@163.com']' from dual;  -->分割字符串方式插入失败  
    insert into tb_tmp select 8888,'Jack',q'['Jack@yahoo.com','Jack@163.com']' from dual  
                                          *  
    ERROR at line 1:  
    ORA-00932: inconsistent datatypes: expected - got CHAR  
      
    scott@CNMMBO> insert into tb_tmp select 8888,'Jack',mail_type('Jack@yahoo.com','Jack@163.com') from dual; -->插入时指定嵌套表类型   
      
    1 row created.  
      
    scott@CNMMBO> commit;  
      
    Commit complete.  
      
    --查看存在嵌套表数据类型的表中的记录  
    scott@CNMMBO> col mail format a30  
    scott@CNMMBO> select * from tb_tmp;                    -->查询数据时带有嵌套表的类型  
      
         EMPNO ENAME      MAIL  
    ---------- ---------- ------------------------------  
          8888 Jack       MAIL_TYPE('Jack@yahoo.com', 'J  
                          ack@163.com')  
      
    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 Jack@yahoo.com  
    Jack mail address is Jack@163.com  
      
    PL/SQL procedure successfully completed.  
      
    --更新表中含有嵌套表类型中的值  
    scott@CNMMBO> DECLARE  
      2     mail_tab   mail_type := mail_type( 'Jackson@yahoo.com', 'Jackson@163.com' );  
      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;  
      
         EMPNO ENAME      MAIL  
    ---------- ---------- ------------------------------  
          8888 Jack       MAIL_TYPE('Jackson@yahoo.com',  
                           'Jackson@163.com')  
      
    --删除表中存在嵌套表类型的记录  
    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                        
    --> Author: Robinson Cheng  
    --> Blog: http://blog.iyunv.com/robinson_0612  

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