PL/SQL变长数组

来源：互联网发布：ubuntu root密码设置编辑：程序博客网时间：2024/06/05 18:44

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 [NOTNULL];                                   -->element_type用于指定元素的数据类型
       varray_nameTYPE_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 ) OFdept.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(   'Namevarray_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(   'Namevarray_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 ISVARRAY( 2 ) OF VARCHAR2( 40 );-->创建变长数组类型
2 /

Type created.

scott@CNMMBO> CREATE TABLEtb_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 select6666,'Robinson',varray_phone('13423456789','075520123650') fromdual;
1 row created.

scott@CNMMBO> insert into tb_emp select7777,'Jackson',varray_phone('13423456789','075520123650') fromdual;
1 row created.

scott@CNMMBO> commit;
Commit complete.

scott@CNMMBO> col phone format a25
scott@CNMMBO> select * fromtb_emp;  -->查看插入的记录

    EMPNOENAME     PHONE
---------- ---------- -------------------------
     6666 Robinson  VARRAY_PHONE('13423456789
                     ', '075520123650')

     7777 Jackson   VARRAY_PHONE('13423456789
                     ', '075520123650')

-->插入变长数组包含3个元素的记录，此时提示超出了变长数组范围
scott@CNMMBO> insert into tb_emp select8888,'Johnson',varray_phone('13423456789','075520123650','010123')
2 from dual;
insert into tb_emp select8888,'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 3cascade;

Type altered.

-->再次插入数据成功
scott@CNMMBO> insert into tb_emp select8888,'Johnson',varray_phone('13423456789','075520123650','010123')
2 from dual;

1 row created.

-->插入变长数组元素超出预定义字符串长度时收到错误提示
scott@CNMMBO> insert into tb_emp select8888,'Johnson',varray_phone('13423456789000000000000000000000000000000000000')
2 from dual;
insert into tb_emp select8888,'Johnson',varray_phone('13423456789000000000000000000000000000000000000')
                                                     *
ERROR at line 1:
ORA-22814: attribute or element value is larger than specified intype

-->修改变长数组元素的数据长度到varchar2(60),且使用cascade，即及联修改到表tb_emp
scott@CNMMBO> alter type varray_phone modify elementtype varchar2(60) cascade;

Type altered.

-->再次插入数据成功
scott@CNMMBO> insert into tb_emp select8888,'Johnson',varray_phone('13423456789000000000000000000000000000000000000')
2 from dual;

1 row created.

-->查看变长数组的定义
scott@CNMMBO> selectparent_table_name,parent_table_column,type_name from user_varrays;

PARENT_TABLE_NAPARENT_TABLE_COLUMN           TYPE_NAME
--------------- ------------------------------------------------------------
TB_EMP         PHONE                         VARRAY_PHONE

0 0