本文详细介绍了PL/SQL中联合数组与嵌套表的使用方法，包括它们的特点、语法及示例。联合数组类似于单列数据库表，适用于多行单列数据处理；嵌套表则支持作为表列数据类型，适用于单行多列数据处理。
通常情况下，在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
- --> Author: Robinson Cheng
- --> Blog: http://blog.youkuaiyun.com/robinson_0612
三、总结
1、不论是联合数组还是嵌套表，应当以集合的方式来看待与处理
2、联合数组在声明其类型时需要指定index by子句，而嵌套表则不需要
3、联合数组和嵌套表两者元素个数无限制
4、联合数组不需要初始化，而嵌套表则需要对其进行初始化。其次是嵌套表初始化赋空值的情形则后续需要使用extend方式来扩展集合尺寸的大小
5、联合数组不能作为表上列的数据类型，而嵌套表无此限制
转载地址：http://blog.youkuaiyun.com/robinson_0612/article/details/7372061
扫一扫
2321
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
