PL/SQL 联合数组与嵌套表
转自:http://blog.csdn.net/leshami/article/details/7372061
通常情况下,在PL/SQL中,处理单行单列的数据可以使用标量变量,而处理单行多列的数据则使用PL/SQL记录是不错的选择。
单列多行数据则由联合数组或嵌套表来完成,其特点是类似于单列数据库表。
在Oracle 9i 之前称为PL/SQL索引表,9i 之后称之为联合数组。
嵌套表也是集合类型中的一种,下面分别介绍这两种集合数据类型的使用方法。
一、联合数组
1、联合数组的特性
类似于一张简单的SQL表,按照主键检索数据
其数据行并不是按照预定义的顺序存储。当使用变量来检索其数据时,每行数据会分配一个连续的下标且从1开始。
下标可以为负值,且下标的数据类型支持binary_integer,pls_integer,varchar2
其数据元素个数无限制
不能作为表列的数据类型使用,即只能作为PL/SQL复合数据类型使用
存放的数据类型是临时数据,故不支持insert,select into 等SQL语句,等同于SQL server中的表变量
2、语法
TYPE type_name IS TABLE OF element_type [NOT NULL]
-->element_type为联合数组元数据指定数据类型(先使用TYPE声明表结构)
INDEX BY key_type;
-->元素下标的数据类型
table_name TYPE_NAME;
-->再使用声明的TYPE类型声明实际数组名
3、示例
--使用PLS_INTEGE类型联合数组
scott@CNMMBO> DECLARE
-->定义游标
2
CURSOR name_cur IS
3
SELECT dname
4
FROM
dept
5
WHERE
deptno < 40;
6
7
TYPE name_type IS TABLE OF dept.dname%TYPE
-->定义一个联合数组类型,且其下标的数据类型为PLS_INTEGER
8
INDEX BY PLS_INTEGER;
9
10
dname_tab
name_type;
-->声明类型为name_type的联合数组dname_tab
11
v_counter
INTEGER := 0;
12
BEGIN
13
FOR name_rec IN name_cur
14
LOOP
15
v_counter
:=
16
v_counter
17
1;
18
dname_tab( v_counter ) := name_rec.dname;
-->对联合数据进行循环赋值
19
DBMS_OUTPUT.put_line(
'Dname is('
20
|| v_counter
21
|| '):'
22
|| dname_tab( v_counter ) );
-->输出联合数组中的所有元素
23
END LOOP;
24
END;
25
/
Dname is(1):ACCOUNTING
Dname is(2):RESEARCH
Dname is(3):SALES
PL/SQL procedure successfully completed.
--使用VARCHAR2类型联合数组
scott@CNMMBO> DECLARE
2
TYPE score_type IS TABLE OF NUMBER
3
INDEX BY VARCHAR2( 10 );
-->注意此处声明的联合数组的下标数据类型为varchar2
4
5
score_tab
score_type;
6
BEGIN
7
score_tab( 'SCOTT' ) := 95;
-->对不同下标分别进行赋值
8
score_tab( 'JOHN' ) := 98;
9
score_tab( 'ROBINSON' ) := 96;
10
DBMS_OUTPUT.put_line( 'First element is '
11
|| score_tab.FIRST );
-->输出联合数组score_tab中的第一个元素的下标
12
DBMS_OUTPUT.put_line( 'last element is '
13
|| score_tab.LAST );
-->输出联合数组score_tab中的最后一个元素的下标
14
DBMS_OUTPUT.put_line( 'The score of Scott is '
15
|| score_tab( 'SCOTT' ) );
-->输出下标为SCOTT的元素的值
16
END;
17
/
First element is JOHN
-->注意此处输出的是JOHN(第一个和最后一个是按字母排序得到的)
last element is SCOTT
The score of Scott is 95
PL/SQL procedure successfully completed.
--下标超范围的情况
scott@CNMMBO> DECLARE
2
TYPE score_type IS TABLE OF NUMBER
3
INDEX BY VARCHAR2( 10 );
4
5
score_tab
score_type;
6
BEGIN
7
score_tab( 'SCOTT' ) := 95;
8
score_tab( 'JOHN' ) := 98;
9
score_tab( 'ROBINSON' ) := 96;
10
DBMS_OUTPUT.put_line( 'The score of Jason is
'
11
|| score_tab( 'JASON' ) );
12
END;
13
/
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
-->当下标超范围时,将收到ORA-01403错误
ORA-06512: at line 10
二、嵌套表
1、嵌套表的特点:
元素下标从1开始,个数没有限制.即元素个数可以动态增长
嵌套表的数组元素值可以是稀疏的,即可以使得中间的某个元素没有赋值
嵌套表的语法与联合数组类似,不同的是仅仅是少了index by子句
嵌套表必需先初始化,然后才能引用其中的元素。如果初始化为空值,则后续需要使用extend来扩展其大小
嵌套表初始化时为密集的,但允许有间隙,即允许使用内置过程delete从嵌套表中删除元素
嵌套表类型可以作为表列的数据类型来使用
2、语法
TYPE type_name IS TABLE OF element_type [NOT NULL];
-->element_type为索引表元数据指定数据类型(先使用TYPE声明表结构)
table_name TYPE_NAME;
-->再使用声明的TYPE类型声明实际嵌套表
3、示例
--声明嵌套表并输出嵌套表的实际内容
scott@CNMMBO> DECLARE
2
CURSOR name_cur IS
3
SELECT dname
4
FROM
dept
5
WHERE
deptno < 40;
6
7
TYPE name_type IS TABLE OF dept.dname%TYPE;
8
9
dname_tab
name_type := name_type( );
-->需要初始化,否则将收到 ORA-06531:Reference to uninitialized collection.
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
dname_tab.EXTEND;
-->需要扩展,否则将收到 ORA-06533: Subscript beyond count
18
dname_tab( v_counter ) := name_rec.dname;
19
DBMS_OUTPUT.put_line(
'Dname ('
20
|| v_counter
21
|| ') is :'
22
|| dname_tab( v_counter ) );
23
END LOOP;
24
END;
25
/
Dname (1) is :ACCOUNTING
Dname (2) is :RESEARCH
Dname (3) is :SALES
PL/SQL procedure successfully completed.
--将嵌套表作为表列的数据类型来使用
scott@CNMMBO> CREATE OR REPLACE TYPE mail_type IS TABLE OF VARCHAR2( 100 );
2
/
Type created.
scott@CNMMBO> CREATE TABLE tb_tmp
-->创建表tb_tmp
2
(
3
empno
NUMBER( 4 )
4
, ename
VARCHAR2( 10 )
5
, mail
mail_type
-->列mail的类型为mail_type
6
)
7
NESTED TABLE mail
-->注意此处需要指定嵌套表的存储方式
8
STORE AS mail_tab;
Table created.
--为嵌套表插入数据
scott@CNMMBO> insert into tb_tmp select 8888,'Jack','Jack@yahoo.com,Jack@163.com' from dual; -->传统方式插入失败
insert into tb_tmp select 8888,'Jack','Jack@yahoo.com,Jack@163.com' from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
scott@CNMMBO> insert into tb_tmp select 8888,'Jack',q'['Jack@yahoo.com','Jack@163.com']' from dual; -->分割字符串方式插入失败
insert into tb_tmp select 8888,'Jack',q'['Jack@yahoo.com','Jack@163.com']' from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
scott@CNMMBO> insert into tb_tmp select 8888,'Jack',mail_type('Jack@yahoo.com','Jack@163.com') from dual; -->插入时指定嵌套表类型
1 row created.
scott@CNMMBO> commit;
Commit complete.
--查看存在嵌套表数据类型的表中的记录
scott@CNMMBO> col mail format a30
scott@CNMMBO> select * from tb_tmp;
-->查询数据时带有嵌套表的类型
EMPNO ENAME
MAIL
---------- ---------- ------------------------------
8888 Jack
MAIL_TYPE('Jack@yahoo.com', 'J
ack@163.com')
scott@CNMMBO> DECLARE
2
mail_tab
mail_type;
-->声明一个mail_type数据类型
3
BEGIN
4
SELECT mail
5
INTO
mail_tab
-->将数据保存到 mail_tab 变量中
6
FROM
tb_tmp
7
WHERE
empno = 8888;
8
9
FOR i IN 1 .. mail_tab.COUNT
-->轮循输出嵌套表类型中的值
10
LOOP
11
DBMS_OUTPUT.put_line( 'Jack mail address is '
12
|| mail_tab( i ) );
13
END LOOP;
14
END;
15
/
Jack mail address is Jack@yahoo.com
Jack mail address is Jack@163.com
PL/SQL procedure successfully completed.
--更新表中含有嵌套表类型中的值
scott@CNMMBO> DECLARE
2
mail_tab
mail_type := mail_type( 'Jackson@yahoo.com', 'Jackson@163.com' );
3
BEGIN
4
UPDATE tb_tmp
5
SET mail =
mail_tab;
6
7
COMMIT;
8
DBMS_OUTPUT.put_line( 'Jack mail address was updated' );
9
END;
10
/
Jack mail address was updated
PL/SQL procedure successfully completed.
scott@CNMMBO> select * from tb_tmp;
EMPNO ENAME
MAIL
---------- ---------- ------------------------------
8888 Jack
MAIL_TYPE('Jackson@yahoo.com',
'Jackson@163.com')
--删除表中存在嵌套表类型的记录
scott@CNMMBO> delete from tb_tmp where empno=8888;
1 row deleted.
scott@CNMMBO> commit;
Commit complete.
scott@CNMMBO> select * from tb_tmp;
no rows selected
三、总结
1、不论是联合数组还是嵌套表,应当以集合的方式来看待与处理
2、联合数组在声明其类型时需要指定index by子句,而嵌套表则不需要
3、联合数组和嵌套表两者元素个数无限制
4、联合数组不需要初始化,而嵌套表则需要对其进行初始化。其次是嵌套表初始化赋空值的情形则后续需要使用extend方式来扩展集合尺寸的大小
5、联合数组不能作为表上列的数据类型,而嵌套表无此限制
--> Author: Robinson Cheng
--> Blog: http://blog.csdn.net/robinson_0612
转自:http://blog.csdn.net/leshami/article/details/7372061
通常情况下,在PL/SQL中,处理单行单列的数据可以使用标量变量,而处理单行多列的数据则使用PL/SQL记录是不错的选择。
单列多行数据则由联合数组或嵌套表来完成,其特点是类似于单列数据库表。
在Oracle 9i 之前称为PL/SQL索引表,9i 之后称之为联合数组。
嵌套表也是集合类型中的一种,下面分别介绍这两种集合数据类型的使用方法。
一、联合数组
1、联合数组的特性
2、语法
3、示例
--使用PLS_INTEGE类型联合数组
scott@CNMMBO> DECLARE
Dname is(1):ACCOUNTING
Dname is(2):RESEARCH
Dname is(3):SALES
PL/SQL procedure successfully completed.
--使用VARCHAR2类型联合数组
scott@CNMMBO> DECLARE
First element is JOHN
last element is SCOTT
The score of Scott is 95
PL/SQL procedure successfully completed.
--下标超范围的情况
scott@CNMMBO> DECLARE
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 10
二、嵌套表
1、嵌套表的特点:
2、语法
3、示例
--声明嵌套表并输出嵌套表的实际内容
scott@CNMMBO> DECLARE
Dname (1) is :ACCOUNTING
Dname (2) is :RESEARCH
Dname (3) is :SALES
PL/SQL procedure successfully completed.
--将嵌套表作为表列的数据类型来使用
scott@CNMMBO> CREATE OR REPLACE TYPE mail_type IS TABLE OF VARCHAR2( 100 );
Type created.
scott@CNMMBO> CREATE TABLE tb_tmp
Table created.
--为嵌套表插入数据
scott@CNMMBO> insert into tb_tmp select 8888,'Jack','Jack@yahoo.com,Jack@163.com' from dual;
insert into tb_tmp select 8888,'Jack','Jack@yahoo.com,Jack@163.com' from dual
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
scott@CNMMBO> insert into tb_tmp select 8888,'Jack',q'['Jack@yahoo.com','Jack@163.com']' from dual;
insert into tb_tmp select 8888,'Jack',q'['Jack@yahoo.com','Jack@163.com']' from dual
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
scott@CNMMBO> insert into tb_tmp select 8888,'Jack',mail_type('Jack@yahoo.com','Jack@163.com') from dual; -->插入时指定嵌套表类型
1 row created.
scott@CNMMBO> commit;
Commit complete.
--查看存在嵌套表数据类型的表中的记录
scott@CNMMBO> col mail format a30
scott@CNMMBO> select * from tb_tmp;
---------- ---------- ------------------------------
scott@CNMMBO> DECLARE
Jack mail address is Jack@yahoo.com
Jack mail address is Jack@163.com
PL/SQL procedure successfully completed.
--更新表中含有嵌套表类型中的值
scott@CNMMBO> DECLARE
Jack mail address was updated
PL/SQL procedure successfully completed.
scott@CNMMBO> select * from tb_tmp;
---------- ---------- ------------------------------
--删除表中存在嵌套表类型的记录
scott@CNMMBO> delete from tb_tmp where empno=8888;
1 row deleted.
scott@CNMMBO> commit;
Commit complete.
scott@CNMMBO> select * from tb_tmp;
no rows selected
三、总结
1、不论是联合数组还是嵌套表,应当以集合的方式来看待与处理
2、联合数组在声明其类型时需要指定index by子句,而嵌套表则不需要
3、联合数组和嵌套表两者元素个数无限制
4、联合数组不需要初始化,而嵌套表则需要对其进行初始化。其次是嵌套表初始化赋空值的情形则后续需要使用extend方式来扩展集合尺寸的大小
5、联合数组不能作为表上列的数据类型,而嵌套表无此限制
--> Author: Robinson Cheng
--> Blog: http://blog.csdn.net/robinson_0612