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

1.PL/SQL记录

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

(1)自定义PL/SQL记录

declaretype emp_record_type is record(name emp.ename%type,salary emp.sal%type,dno emp.deptno%type);emp_record emp_record_type;beginselect 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数组的数据类型，索引表元素个数没有限制，并且下标可以为负值。

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

嵌套表：

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

declaretype ename_table_type is table of emp.ename%type;ename_table ename_table_type:=ename_table_type('a','a');beginename_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开始。

declaretype ename_table_type is varray(20) of emp.ename%type;ename_table ename_table_type:=ename_table_type('a');beginselect 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记录表用于处理多行多列数据。

declaretype emp_table_type is table of emp%rowtype index by binary_integer;emp_table emp_table_type;beginselect * 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));beginfor i in 1.. nv1.count loopfor j in 1..nv1(i).count loopdbms_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操作）语句来完成的。

declaretype 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);beginfor i in 1..5000 loopid_table(i):=i;name_talbe(i):='name'||to_char(i);end loop;start_time:=dbms_utility.get_time;forall i in 1..id_table.countinsert 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语句的多行结果检索到集合变量中。

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

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