PL/SQL变长数组

PL/SQL变长数组

转自：http://blog.csdn.net/leshami/article/details/7387647

PL/SQL变长数组是PL/SQL集合数据类型中的一种，其使用方法与PL/SQL嵌套表大同小异，唯一的区别则是变长数组的元素的最大个数是有限制的。
也即是说变长数组的下标固定下限等于1，上限可以扩展。下面给出具体的描述及其使用方法。


一、变长数组语法
              TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF  -->type_name 用于指定varray类型名,size_limit 定义varray元素的最大个数
              element_type [NOT NULL];                                                                      -->element_type用于指定元素的数据类型  
              varray_name TYPE_NAME;                                                                          -->varray_name 用于定义varray变量

二、变长数组特性
                  变长数组主要的特性即是元素的最大个数是有限制
                  变长数组下标固定为1,上限可以扩展
                  与嵌套表类似,在变长数组声明时自动设置为NULL值.所谓的空值指的是集合本身是空，不是针对它所拥有的元素
                  故在元素引用前需要对其进行初始化
           
           
三、变长数组示例

--1、声明变长数组，并输出其结果   

scott@CNMMBO> DECLARE  
  2        CURSOR name_cur IS  
  3              SELECT dname  
  4              FROM    dept  
  5              WHERE  deptno < 40;  
  6     
  7        TYPE name_type IS VARRAY( 10 ) OF dept.dname%TYPE;  -->声明一个包含10个元素的变长数组，且数据类型为dept.dname类型   
  8     
  9        varray_dname_tab    name_type := name_type( );            -->初始化变长数组   
  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              varray_dname_tab.EXTEND;                                                  -->使用extend进行扩展     
  18              varray_dname_tab( v_counter ) := name_rec.dname;  -->按下标输出变长数组的所有元素   
  19              DBMS_OUTPUT.put_line(    'Dname ('  
  20                                                        || v_counter  
  21                                                        || ') is :'  
  22                                                        || varray_dname_tab( v_counter ) );  
  23        END LOOP;  
  24  END;  
  25  /  
Dname (1) is :ACCOUNTING  
Dname (2) is :RESEARCH  
Dname (3) is :SALES           
 
 
--2、对变长数组直接赋初值，且使用count遍历并输出所有元素       
         
scott@CNMMBO> DECLARE  
  2        TYPE name_type IS VARRAY( 2 ) OF VARCHAR2( 10 );  
  3     
  4        varray_name_tab    name_type := name_type( 'Robinson', 'Jackson' ); -->此处对varray_name_tab初始化并赋初值   
  5  BEGIN  
  6        FOR i IN 1 .. varray_name_tab.COUNT                              -->使用count来遍历并输出变长数组的所有元素   
  7        LOOP  
  8              DBMS_OUTPUT.put_line(    'Name varray_name_tab( '  
  9                                                        || i  
  10                                                        || ' ) is : '  
  11                                                        || varray_name_tab( i ) );  
  12        END LOOP;  
  13  END;  
  14  /  
Name varray_name_tab( 1 ) is : Robinson  
Name varray_name_tab( 2 ) is : Jackson  
 
PL/SQL procedure successfully completed.  
 
--3、超出变长数组大小的情形   

scott@CNMMBO> DECLARE  
  2        TYPE name_type IS VARRAY( 2 ) OF VARCHAR2( 10 );  
  3     
  4        varray_name_tab    name_type := name_type( 'Robinson', 'Jackson' );  
  5  BEGIN  
  6        FOR i IN 1 .. varray_name_tab.COUNT  
  7        LOOP  
  8              DBMS_OUTPUT.put_line(    'Name varray_name_tab( '  
  9                                                        || i  
  10                                                        || ' ) is : '  
  11                                                        || varray_name_tab( i ) );  
  12        END LOOP;  
  13     
  14        varray_name_tab.EXTEND;  
  15        varray_name_tab( 3 ) := 'Johnson';  
  16        DBMS_OUTPUT.put_line( 'Name varray_name_tab (3) is '  
  17                                                  || varray_name_tab( 3 ) );  
  18  END;  
  19  /  
Name varray_name_tab( 1 ) is : Robinson  
Name varray_name_tab( 2 ) is : Jackson  
DECLARE  
*  
ERROR at line 1:  
ORA-06532: Subscript outside of limit  
ORA-06512: at line 14  
 
 
--4、存储变长数组到数据库及修改变长数组   

scott@CNMMBO> CREATE OR REPLACE TYPE varray_phone IS VARRAY( 2 ) OF VARCHAR2( 40 ); -->创建变长数组类型   
  2  /  
 
Type created.  
 
scott@CNMMBO> CREATE TABLE tb_emp              -->创建表tb_emp且其中一列使用到了变长数组   
  2  (  
  3        empno    NUMBER( 4 )  
  4    , ename    VARCHAR2( 10 )  
  5    , phone    varray_phone                      -->列phone使用到了变长数组   
  6  )  
  7  ;  
 
Table created.  
 
--插入新记录到变长数组   
scott@CNMMBO> insert into tb_emp select 6666,'Robinson',varray_phone('13423456789','075520123650') from dual;  
1 row created.  
 
scott@CNMMBO> insert into tb_emp select 7777,'Jackson',varray_phone('13423456789','075520123650') from dual;  
1 row created.  
 
scott@CNMMBO> commit;  
Commit complete.  
 
scott@CNMMBO> col phone format a25  
scott@CNMMBO> select * from tb_emp;    -->查看插入的记录   
 
        EMPNO ENAME          PHONE  
---------- ---------- -------------------------   
          6666 Robinson    VARRAY_PHONE('13423456789  
                                          ', '075520123650')  
 
          7777 Jackson      VARRAY_PHONE('13423456789  
                                          ', '075520123650')  
 
-->插入变长数组包含3个元素的记录，此时提示超出了变长数组范围   
scott@CNMMBO> insert into tb_emp select 8888,'Johnson',varray_phone('13423456789','075520123650','010123')                 
  2  from dual;  
insert into tb_emp select 8888,'Johnson',varray_phone('13423456789','075520123650','010123')  
                                                                                *  
ERROR at line 1:  
ORA-22909: exceeded maximum VARRAY limit  
 
-->修改变长数组长度范围限制到3，且使用cascade，即及联修改到表tb_emp   
scott@CNMMBO> alter type varray_phone modify limit 3 cascade;     
 
Type altered.  
 
-->再次插入数据成功   
scott@CNMMBO> insert into tb_emp select 8888,'Johnson',varray_phone('13423456789','075520123650','010123')  
  2  from dual;  
 
1 row created.  
 
-->插入变长数组元素超出预定义字符串长度时收到错误提示   
scott@CNMMBO> insert into tb_emp select 8888,'Johnson',varray_phone('134234567890000000000000 00000000000000000000000')     
  2  from dual;  
insert into tb_emp select 8888,'Johnson',varray_phone('134234567890000000000000 00000000000000000000000')  
                                                                                                          *  
ERROR at line 1:  
ORA-22814: attribute or element value is larger than specified in type  
 
-->修改变长数组元素的数据长度到varchar2(60),且使用cascade，即及联修改到表tb_emp   
scott@CNMMBO> alter type varray_phone modify element type varchar2(60) cascade;  
 
Type altered.  
 
-->再次插入数据成功   
scott@CNMMBO> insert into tb_emp select 8888,'Johnson',varray_phone('134234567890000000000000 00000000000000000000000')   
  2  from dual;  
 
1 row created.  
 
-->查看变长数组的定义   
scott@CNMMBO> select parent_table_name,parent_table_column,type_name from user_varrays;  
 
PARENT_TABLE_NA PARENT_TABLE_COLUMN                      TYPE_NAME  
--------------- ------------------------------ ------------------------------   
TB_EMP                  PHONE                                                  VARRAY_PHONE