PL/SQL变长数组

3121321321

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('13423456789000000000000000000000000000000000000')   
      2  from dual;  
    insert into tb_emp select 8888,'Johnson',varray_phone('13423456789000000000000000000000000000000000000')  
                                                          *  
    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('13423456789000000000000000000000000000000000000')   
      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