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
转自:http://blog.csdn.net/leshami/article/details/7387647
PL/SQL变长数组是PL/SQL集合数据类型中的一种,其使用方法与PL/SQL嵌套表大同小异,唯一的区别则是变长数组的元素的最大个数是有限制的。
也即是说变长数组的下标固定下限等于1,上限可以扩展。下面给出具体的描述及其使用方法。
一、变长数组语法
二、变长数组特性
三、变长数组示例
--1、声明变长数组,并输出其结果
scott@CNMMBO> DECLARE
Dname (1) is :ACCOUNTING
Dname (2) is :RESEARCH
Dname (3) is :SALES
--2、对变长数组直接赋初值,且使用count遍历并输出所有元素
scott@CNMMBO> DECLARE
Name varray_name_tab( 1 ) is : Robinson
Name varray_name_tab( 2 ) is : Jackson
PL/SQL procedure successfully completed.
--3、超出变长数组大小的情形
scott@CNMMBO> DECLARE
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 ); -->创建变长数组类型
Type created.
scott@CNMMBO> CREATE TABLE tb_emp
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;
---------- ---------- -------------------------
-->插入变长数组包含3个元素的记录,此时提示超出了变长数组范围
scott@CNMMBO> insert into tb_emp select 8888,'Johnson',varray_phone('13423456789','075520123650','010123')
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')
1 row created.
-->插入变长数组元素超出预定义字符串长度时收到错误提示
scott@CNMMBO> insert into tb_emp select 8888,'Johnson',varray_phone('134234567890000000000000
insert into tb_emp select 8888,'Johnson',varray_phone('134234567890000000000000
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
1 row created.
-->查看变长数组的定义
scott@CNMMBO> select parent_table_name,parent_table_column,type_name from user_varrays;
PARENT_TABLE_NA PARENT_TABLE_COLUMN
--------------- ------------------------------ ------------------------------
TB_EMP