《精通Oracle10gPLSQL编程》8、使用复合数据类型

1.PL/SQL记录

PL/SQL记录（Record）类似于高级语言中的结构，有益于处理单行数据。

(1)自定义PL/SQL记录

declare
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
dno emp.deptno%type
);
emp_record emp_record_type;
begin
select ename,sal,deptno into emp_record from emp where empno = &no;
end;

 

(2)使用%rowtype属性定义记录变量

identifier table_name%rowtype;
identifier view_name%rowtype;

 

2.PL/SQL集合

类似于高级语言数组的一种复合数据类型，包括索引表（PL/SQL表）、嵌套表（Nested Table）、变长数组（VARRAY）。

索引表：

用于处理PL/SQL数组的数据类型，索引表元素个数没有限制，并且下标可以为负值。

declare
type ename_table_type is table of emp.ename%type index by binary_integer;
ename_table ename_table_type;
begin
select ename into ename_table(-1) from emp where empno = &no;
dbms_output.put_line(ename_table(-1));
end;

嵌套表：

用于处理PL/SQL数组的数据类型。元素个数无限制，下标从1开始。

declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type:=ename_table_type('a','a');
begin
ename_table:=ename_table_type('a','a','a');
select ename into ename_table(2) from emp where empno = &no;
dbms_output.put_line(ename_table(2));
end;

变长数组：

处理PL/SQL数组的数据类型，也可作为表列的数据类型使用。元素个数有限制，下标从1开始。

declare
type ename_table_type is varray(20) of emp.ename%type;
ename_table ename_table_type:=ename_table_type('a');
begin
select ename into ename_table(1) from emp where empno=&no;
dbms_output.put_line(ename_table(1));
end;

PL/SQL记录表

PL/SQL变量用于处理单行单列数据，PL/SQL记录用于处理单行多列数据，PL/SQL集合用于处理多行单列数据，PL/SQL记录表用于处理多行多列数据。

declare
type emp_table_type is table of emp%rowtype index by binary_integer;
emp_table emp_table_type;
begin
select * into emp_table(1) from emp where empno=&no;
dbms_output.put_line(emp_table(1).ename || '---' || emp_table(1).sal);
end;

多级集合

指嵌套了集合类型的集合类型。类似于多维数组。

declare
--一维数组
type a1_varray_type is varray(10) of int;
--二维数组
type na1_varray_type is varray(10) of a1_varray_type;
--初始化二维集合
nv1 na1_varray_type:=nal_varray_type(
a1_varray_type(1,1,1),
a1_varray_type(2,2,2),
a1_varray_type(3,3)
);
begin
for i in 1.. nv1.count loop
for j in 1..nv1(i).count loop
dbms_output.put_line(nv1(i)(j));
end loop;
end loop;
end;

集合方法：

用于操作集合变量的内置函数或过程，EXISTS,COUNT,LIMIT,FIRST,NEXT

collection_name.method_name(parameters)

集合赋值：

INSERT/UPDATE/FETCH/SELECT; SET/MULTISET UNION/MULTISET INTERSECT/MULTISET EXCEPT等。

比较集合：

CARDINALITY/SUBMULTISET OF/MEMBER OF/IS A SET / IS EMPTY;

3.批量绑定（速度更快）

批量绑定是使用RULKCOLLECT（取得批量数据，只能用于SELECT / FETCH / DML返回子句）和FORALL（执行批量的DML操作）语句来完成的。

declare
type id_table_type is table of number(6) index by binary_integer;
type name_table_type is table of varchar2(10) index by binary_integer;
id_table id_table_type;
name_table name_table_type;
start_time number(10);
end_time number(10);
begin
for i in 1..5000 loop
id_table(i):=i;
name_talbe(i):='name'||to_char(i);
end loop;
start_time:=dbms_utility.get_time;
forall i in 1..id_table.count
insert into demo values(id_table(i), name_table(i));
end_time:=dbms_utility.get_time;
dbms_output.put_line(end_time - start_time);
end;

SQL%BULK_ROWCOUNT为FORALL语句提供，用于取得在执行批量绑定操作时第i个元素所作用的行数。

 

BULK COLLECT子句：一次将SELECT语句的多行结果检索到集合变量中。

declare
type emp_table_type is table of emp%rowtype index by binary_integer;
emp_table emp_table_type;
begin
select * bulk collect into emp_table from emp where deptno=&no;
for i in 1..emp_table.count loop
dbms_output.put_line(emp_table(i).ename);
end loop;
end;

declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
delete from emp where deptno = &no returning ename bulk collect into ename_table;
for i in 1..ename_table.count loop
dbms_output.put_line(ename_table(i));
dbms_output.new_line;
end loop;
end;