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
四、更多参考
dbms_xplan之display_cursor函数的使用
时间： 2024-09-29 06:09:26