1第11章 PL/SQL高级特性2主要内容集合批绑定动态SQL利用PL/SQL实现分页查询311.1 集合集合概述索引表 嵌套表 可变
数组 集合类型在数据库中的应用 4概述复合类型分为：记录类型：多个不同类型分量的集合，类似于高级语言中的结构体类型。集合类型：多个
相同类型分量的集合，类似于高级语言中的数组类型。集合类型分为索引表（Index-By Table）嵌套表（Nested Table
）可变数组（Varray）应用先定义集合类型，然后利用集合类型定义集合类型变量 511.1.1 索引表索引表概念索引表的定义 索
引表中元素的赋值和引用 索引表中元素的添加、修改 索引表中元素的删除 集合类型的属性和方法 6（1）索引表概念概念索引表类型是Or
acle中最早引入的集合类型，是相同类型数据的集合，类似于高级语言中的数组，但与数组有很大的不同，其索引值没有固定的上下限，即元素
个数是不受限制的，而且其索引值可以是无序的。7（2）索引表的定义索引表类型的定义语法为TYPE index_table IS TA
BLE OF element_type INDEX BY BINARY_INTERGER| PLS_INTEGER| V
ARCHAR2(n);参数说明element_type：可以是基本数据类型、用户自定义类型，也可以通过%TYPE或%ROWTYPE
获取的类型，但不能为BOOLEAN，NCHAR，NVARCHAR，NCLOB，REF CURSOR类型；INDEX BY：指定索引
值的类型，可以是BINARY_INTERGER，PLS_INTEGER或VARCHAR2类型。 8定义了索引表类型后，就可以利用该
类型定义索引表变量。TYPE nametab IS TABLE OF VARCHAR2(25) INDEX BY BINARY_
INTEGER;TYPE deptnotab IS TABLE OF NUMBER(2) INDEX BY VARCHAR2(1
0);v_names nametab;v_deptnos deptnotab;9（3）索引表中元素的赋值和引用引用索引表变量元素
或为元素赋值形式为index_table_name(index) index_table_name(index)：=new_val
ue; 注意：元素的索引值可以是无序的；当为不存在的元素赋值时，会自动创建该元素；当引用不存在的元素时，会导致NO_DATA_FO
UND异常。10DECLARE TYPE indextable IS TABLE OF NUMBER INDE
X BY BINARY_INTEGER; v_numbers indextable; BEGIN FOR i IN 1.
.5 LOOP v_numbers(i) := i10; END LOOP; FOR i IN 1..5
LOOP DBMS_OUTPUT.PUT_LINE(''v_numbers(''||i||''):''||v_numbers(i
)); END LOOP; BEGIN DBMS_OUTPUT.PUT_LINE(''v_numbers(6):
'' || v_numbers(6)); EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(''No data found reading
v_Numbers(6)!''); END
; END; 11该程序的运行结果为v_numbers(1):10v_numbers(2):20v_numbers(3):30v_
numbers(4):40v_numbers(5):50No data found reading v_Numbers(6)! 1
2（4）索引表中元素的添加、修改 为索引表添加元素的方法就是为一个原来不存在元素赋值，方法为index_table_name(in
dex):=value; 修改索引表元素的方法就是为一个存在元素重新赋值，其方法为index_table_name(index)
:=new_value; 13（5）索引表中元素的删除 删除索引表中的元素用DELETE方法进行DELETE：删除整个索引表。DE
LETE(m)：删除索引值为n的元素。DELETE(m,n)：删除索引值从m到n的所有元素。注意如果要删除的元素不存在，DELET
E也不会引起错误，而仅仅是跳过那个元素。 14DECLARE TYPE indextable IS TABLE OF NUMBE
R INDEX BY BINARY_INTEGER; v_numbers indextable;BEG
IN FOR i IN 1..5 LOOP v_numbers(i) := i10; END LOOP; v_n
umbers(5):=100; v_numbers.delete(1); v_numbers.dele
te(2,4); v_numbers.delete(6); FOR i IN 1..5 LOOP IF v
_numbers.exists(i) THEN DBMS_OUTPUT.PUT_LINE(v_numbers(i));
END IF; END LOOP;END; 程序的运行结果为 10015（6）集合类型的属性和方法COUNT：返回集合
中元素的个数。FIRST：返回集合中元素的最小索引值。LAST：返回集合中元素的最大索引值；LIMIT：返回集合中允许元素的最大个
数。由于嵌套表和索引表元素个数没有限制，所以返回值为NULL。NEXT(n)：返回集合中比当前索引值n大的下一个有效索引值。PRI
OR(n)：返回集合中比当前索引值n小的上一个有效索引值。EXISTS(n)：判断索引值为n的元素是否存在，如果存在则返回TRUE
，否则返回FALSE。 16DELETE，DELETE(n)，DELETE(m,n)：删除索引表或嵌套表中的所有元素、索引值为n
的元素、索引值从m到n之间的所有元素。TRIM，TRIM(n)：删除嵌套表或可变数组的最后一个元素或尾部的n个元素。EXTEND，
EXTEND(n)，EXTEND(n,i)：在嵌套表或可变数组的尾部添加一个元素（元素值为NULL）、n个元素（元素值都为NULL
）、n个元素（元素值为索引值为i的元素值）17将所有员工的员工号、员工名保存到一个索引表中，然后输出索引表中的元素个数、最小索引值
、最大索引值，以及删除部分元素后的剩余元素个数，并输出剩余元素。 18DECLARECURSOR all_emps IS SELE
CT employee_id,first_name,last_name FROM employeesORDER BY employ
ee_id;TYPE t_emp IS RECORD (empno employees.employee_id%TYPE, fna
me employees.first_name%TYPE,lname employees.last_name%TYPE);TYPE
emp_table IS TABLE OF t_emp INDEX BY BINARY_INTEGER;emps emp_tab
le;emps_index BINARY_INTEGER :=0;BEGIN FOR emp IN all_emps LOOP
emps_index:=emps_index+1; emps(emps_index):=emp; END LOOP;
DBMS_OUTPUT.PUT_LINE(''There are ''||emps.COUNT||''
employees.''); DBMS_OU
TPUT.PUT_LINE(''The min index values is: ''||
emps.FIRST); DBMS_OUTPUT.P
UT_LINE(''The max index values is: ''||
emps.LAST); emps.delete(2); em
ps.delete(6,10); DBMS_OUTPUT.PUT_LINE(''At last,there are ''||emps
.COUNT||'' el
ements.''); FOR i IN emps.FIRST..emps.LAST loop IF emps.exists
(i) THEN DBMS_OUTPUT.PUT_LINE(emps(i).empno||'' ''|| e
mps(i).fname||'' ''||emps(i). lname); END IF; END LOOP;END;1920
11.1.2 嵌套表嵌套表的概念嵌套表的定义 嵌套表初始化 嵌套表中元素的引用、修改 嵌套表中元素的添加 嵌套表中元素的删除 2
1（1）嵌套表的概念嵌套表类型与索引表类型相似，都是相同类型数据的集合，但与索引表不同的是，嵌套表必须用有序的索引值创建（索引值从
1开始，没有固定的上限），需要使用构造器进行初始化，并且嵌套表可以保存在数据库中。22（2）嵌套表的定义 嵌套表定义的语法为TYP
E nested_table IS TABLE OF element_type [NOT NULL]; 参数说明elemen
t_type：可以是基本数据类型、用户自定义类型，也可以通过%TYPE或%ROWTYPE获取的数据类型，但不能为BOOLEAN，N
CHAR，NVARCHAR，NCLOB，REF CURSOR类型。定义完嵌套表类型后，可以使用该类型定义嵌套表变量。TYPE ne
stedtable IS TABLE OF NUMBER;v_nestedexample nestedtable;23（3）嵌套表
初始化 创建一个嵌套表后，如果没有初始化，它的值为NULL。如果对一个还没有初始化的嵌套表引用或赋值，会激发COLLECTION_
IS_NULL异常。嵌套表的初始化是通过构造器实现的。嵌套表构造器是一个与嵌套表类型同名的函数，可以有不定数目的参数，每个参数都应
该与嵌套表中的元素类型相兼容。参数形成从索引值1开始有序的嵌套表元素。 24DECLARE TYPE nametab IS TA
BLE OF VARCHAR2(50); v_tab1 nametab; v_tab2 nametab :=nametab()
; v_tab3 nametab:=nametab(null); v_tab4 nametab:=nametab(''TOM'',
''JOAN'',''SMITH'');BEGIN IF v_tab1 IS NULL THEN DBMS_OUTPUT.PUT_
LINE(''v_tab1 is NULL''); ELSE DBMS_OUTPUT.PUT_LINE(''v_tab1 is
not NULL''); END IF; IF v_tab2 IS NULL THEN DBMS_OUTPUT.PUT_L
INE(''v_tab2 is NULL''); ELSE DBMS_OUTPUT.PUT_LINE(''v_tab2 is n
ot NULL''); END IF; 25IF v_tab3 IS NULL THEN DBMS_OUTPUT.PUT_L
INE(''v_tab3 is NULL''); ELSE DBMS_OUTPUT.PUT_LINE(''v_tab3 is n
ot NULL''); END IF; IF v_tab3(1) IS NULL THEN DBMS_OUTPUT.PUT
_LINE(''v_tab3(1) is NULL''); ELSE DBMS_OUTPUT.PUT_LINE(''v_tab3
(1) is not NULL''); END IF; FOR i IN v_tab4.FIRST..v_tab4.LAST L
OOP DBMS_OUTPUT.PUT_LINE(v_tab4(i)); END LOOP;END; 26该程序的运行结果
为v_tab1 is NULLv_tab2 is not NULLv_tab3 is not NULLv_tab3(1) is N
ULLTOMJOANSMITH 27从该程序可以看出：没有初始化的嵌套表为空（NULL）；不带参数的构造器会创建一个没有元素的嵌套
表，该嵌套表不为空（NULL）；带NULL参数的构造器会创建一个非空的嵌套表，该嵌套表的一个元素值为NULL；构造器中参数的个数和
参数值决定了新建的嵌套表中元素的个数以及相应的元素值。28（4）嵌套表中元素的引用、修改 对嵌套表中元素引用和修改的方法为nest
ed_table_name(index)nested_table_name(index):=new_value;注意对不存在的嵌套
表元素的引用或修改，将导致SUBSCRIPT_BEYOND_COUNT异常。 29（5）嵌套表中元素的添加 嵌套表元素的添加是通过
EXTEND方法实现EXTEND：在当前嵌套表的末尾添加一个元素，元素值为NULL；EXTEND(n)：在当前嵌套表的末尾添加n个
元素，元素值都为NULL；EXTEND(n,i)：在当前嵌套表的末尾添加n个元素，元素值为索引值为i的元素值。30将所有员工信息保
存到一个嵌套表变量中，并显示员工号、员工姓名、工资与部门号。DECLARE CURSOR all_emps IS SELECT
FROM employees; TYPE t_empnested IS TABLE OF employees%ROWTYPE
; v_emp t_empnested :=t_empnested(); indexvalue BINARY_INTEGE
R :=1;BEGIN FOR emp IN all_emps LOOP v_emp.extend; v
_emp(indexvalue):=emp; indexvalue:=indexvalue+1; END LOOP;FO
R i IN 1..v_emp.COUNT LOOPDBMS_OUTPUT.PUT_LINE(v_emp(i).employee_
id||'' ''|| v_emp(i).first_name||'' ''||v_emp(i).last_name); END LOO
P;END;31（6）嵌套表中元素的删除嵌套表元素的删除使用DELETE或TRIM方法。DELETE方法删除嵌套表中的元素与DEL
ETE方法删除索引表的元素形式相同。 使用TRIM方法删除嵌套表中元素有两种形式：TRIM：删除嵌套表末尾的一个元素，并回收该元素
的存储空间TRIM(n)：从嵌套表尾删除n个元素，并回收这些元素的存储空间。注意使用DELETE方法删除元素后，元素的存储空间并没
有被回收，因此，还可以通过赋值重新引用该元素；使用TRIM方法删除元素后，空间被回收，不可以再引用该元素。 32将所有员工信息保存
到一个嵌套表变量中。删除索引值为2，5～10，以及最后2个元素，然后给索引值为2的元素重新赋值，最后输出嵌套表中元素的个数以及嵌套
表中员工的员工号、员工姓名和部门号。33DECLARE CURSOR all_emps IS SELECT FROM emp
loyees; TYPE t_emp IS TABLE OF employees%ROWTYPE; v_emp t_emp:=
t_emp(); indexvalue BINARY_INTEGER:=1;BEGIN FOR emp IN all_emps
LOOP v_emp.extend; v_emp(indexvalue):=emp; indexvalue:=
indexvalue+1; END LOOP; FOR i IN v_emp.FIRST..v_emp.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v_emp(i).employee_id||'' ''|| v_emp(i).
first_ name||'' ''||v_emp(i).last_name|| '' ''|| v_emp(i).salary
||'' ''||v_emp(i).department_id); END LOOP;34 v_emp.delete(2); v
_emp.delete(5,10); v_emp.trim(3); DBMS_OUTPUT.PUT_LINE(''Now the
re are ''|| v_emp.COUNT||
'' elements''); v_emp(2).employee_id:=1234; v_emp(2).first_name:=
''san''; v_emp(2).last_name:=''zhang''; v_emp(2).department_id:=10;
FOR i IN v_emp.FIRST..v_emp.LAST LOOP IF v_emp.exists(i) THE
N DBMS_OUTPUT.PUT_LINE(v_emp(i).employee_id||'' ''|| v_e
mp(i).first_ name||'' ''||v_emp(i).last_name||'' ''|| v_emp(i).sal
ary||'' ''||v_emp(i).department_ id); END IF; END LOOP;END;3511
.1.3 可变数组可变数组概念可变数组定义 可变数组初始化 可变数组元素的引用、修改、添加与删除 36（1）可变数组概念可变数组
类型与嵌套表类型非常相似，不同点在于：可变数组有固定的上限不能删除可变数组中中间的元素 37（2）可变数组定义 可变数组类型定义的
基本语法为TYPE varray_name IS VARRAY|VARYING ARRAY(maximum_size) OF
element_type [NOT NULL];参数说明maximum_size：指定可变数组中元素的最大数量elemen
t_type：可以是基本数据类型、用户自定义类型，也可以通过%TYPE或%ROWTYPE获取的数据类型，但不能为BOOLEAN，N
CHAR，NVARCHAR，NCLOB，REF CURSOR类型。38定义完可变数组类型后，就可以使用该类型定义可变数组变量。TY
PE varraytype IS VARRARY(5) OF CHAR(10);v_varrayname exampleTab;3
9（3）可变数组初始化 可变数组需要使用构造器进行初始化。注意可变数组初始化时需要注意，构造器中参数的个数不能超过可变数据类型定义
时允许的最大元素数量。 40DECLARE TYPE varray_numbers IS VARRAY(5) OF NUMBE
R; v_numbers varray_numbers:=varray_numbers(1,2,3); BEGIN DBM
S_OUTPUT.PUT_LINE(v_numbers.COUNT); FOR i IN 1..v_numbers.COUNT
LOOP DBMS_OUTPUT.PUT_LINE(v_numbers(i)); END LOOP; END; 41
（4）可变数组元素的引用、修改、添加与删除引用或修改没有初始化的可变数组，将会导致COLLECTION_IS_NULL异常；引用或
修改可变数组中不存在的元素，将会导致SUBSCRIPT_BEYOND_COUNT异常。可变数组元素的添加通过EXTEND方法实现可
变数组元素的删除通过TRIM方法实现。 42把大于本部门平均工资的员工信息写入一个可变数组变量中，并显示可变数组中的内容。然后，删
除最后的两个元素，显示剩余元素；添加一个元素，以索引值为2的元素值进行填充，并显示最后的所有元素。43DECLARE CURSO
R c_emp IS SELECT FROM employees e WHERE salary> (SELECT avg(sa
lary) FROM employees WHERE department_id=e.department_id); TYPE
t_empvarray IS VARRAY(200) OF
employees%ROWTYPE; v_emparray t_empvarray :=t_empvarray(); in
dexvalue BINARY_INTEGER :=1;BEGIN FOR v_emp IN c_emp LOOP v_e
mparray.extend; v_emparray(indexvalue):=v_emp; indexvalue:=
indexvalue+1; END LOOP;44FOR i IN 1..v_emparray.LAST LOOPDBMS_OU
TPUT.PUT_LINE(v_emparray(i).employee_id||'' ''||v_emparray(i). firs
t_name||'' ''||v_emparray(i).last_name||'' ''||v_emparray(i).salary||
'' ''||v_emparray(i). department_id); END LOOP; v_emparray.trim(2
); v_emparray.extend(1,2); DBMS_OUTPUT.PUT_LINE(''Now,there are
''|| v_emparray.COUNT||'' element''); F
OR i IN 1..v_emparray.LAST LOOP DBMS_OUTPUT.PUT_LINE(v_emparray(
i).employee_id||'' ''|| v_emparray(i). first_name||'' ''||v_emparray
(i).last_name||'' ''||v_emparray(i).salary||'' ''||v_emparray(i). dep
artment_id); END LOOP;END;45概述嵌套表类型在数据库中的应用 可变数组类型在数据库中的应用 11.1.
4 集合类型在数据库中的应用46（1）概述如果集合类型在PL/SQL程序中定义，则该集合类型是局部的数据类型，只能在当前PL/S
QL程序内部使用，其他PL/SQL程序是无法使用的。如果集合类型（嵌套表类型、可变数组类型）是在数据库中定义的，那么该集合类型是全
局的，可以在数据库内部以及各种PL/SQL程序中使用。为了在数据库中使用集合类型，必须先在数据库中创建集合类型，然后把该集合类型作
为表中列的类型。47（2）嵌套表类型在数据库中的应用 嵌套表类型的创建在数据库中创建全局嵌套表类型的语法为：CREATE OR R
EPLACE TYPE nested_table IS|AS TABLE OF element_type;例如：CREATE
OR REPLACE TYPE cno_list IS TABLE OF NUMBER;48创建包含嵌套表列的表 创建了嵌套表
类型后，就可以在创建表时使用该类型定义列了。例如：CREATE TABLE sc( sno NUMBER PRIMARY
KEY, sname CHAR(10), cno cno_list) NESTED TABLE cno S
TORE AS col_nested;49注意：嵌套表类型必须是在数据库中定义的；必须用NESTED TABLE ...STORE
AS子句指明存储表的名称；存储表是系统产生的，用于存储嵌套表中的实际数据；在数据库表的嵌套表列产生一个指向存储表的指针；不可以直
接对存储表进行访问。50对表中的嵌套表列进行操作创建了包含嵌套表列的表后，就可以对嵌套表列进行数据的插入、修改、查询等操作。操作时
通常把该嵌套表列作为一个整体进行，可以使用构造器或嵌套表变量实现。 51DECLARE v_cno1 cno_list:=cno
_list(10,20,30); v_cno2 cno_list;BEGIN INSERT INTO sc VALUES(1,''
TOM'',v_cno1); INSERT INTO sc VALUES(2,''JOAN'',cno_list(10,30,5
0)); SELECT cno INTO v_cno1 FROM sc WHERE sno=1; FOR i IN 1
..v_cno1.COUNT LOOP DBMS_OUTPUT.PUT_LINE(v_cno1(i)); END LOOP;
v_cno1(2):=40; UPDATE sc SET cno=v_cno1 WHERE sno=1; UPDATE
sc SET cno=cno_list(10,20,50) WHERE sno=2;END;52（3）可变数组类型在数据库中的应
用可变数组类型的创建在数据库中创建全局可变数组类型的语法为CREATE OR REPLACE TYPE varray_name
IS|AS VARRAY|VARYING ARRAY (maximum_size) OF element_type;例如：
CREATE OR REPLACE TYPE studentlist AS VARRAY(10) OF NUMBER(4);
53创建包含可变数组列的表 创建了可变数组类型后，就可以在创建表时使用该类型定义列了。例如：CREATE TABLE room_s
tudent( id NUMBER(2) PRIMARY KEY, max_in NUMBER(2), c
urrent_in NUMBER(2), student studentlist);54对表中的可变数组列进行操作创建了包
含可变数组列的表后，就可以对可变数组列进行数据的插入、修改、查询等操作了。操作时通常把该可变数组表列作为一个整体进行，可以使用构造
器或可变数组变量实现。 55DECLARE v_stud1 studentlist; v_stud2 studentlist:=s
tudentlist(1001,1002,1003);BEGIN INSERT INTO room_student VALUES
(1,5,2,studentlist(1005,1006)); INSERT INTO room_student VALUES(2
,5,3,v_stud2); SELECT student INTO v_stud1 FROM room_student WHE
RE id=1; v_stud1.extend; v_stud1(v_stud1.LAST):=1007; UPDATE room
_student SET student=v_stud1 WHERE id=1;END; 5611.2 批 绑 定批绑定概述
批DML操作 批查询 批绑定属性 5711.2.1 批绑定概述PL/SQL中的SQL语句是由PL/SQL引擎发送到SQL引擎中执
行的，SQL引擎将执行的结果返回给PL/SQL引擎。这种在PL/SQL引擎与SQL引擎之间传送SQL语句的过程称为上下文切换。所谓
批绑定是指，将集合中的元素一次性地从PL/SQL引擎传递给SQL引擎，从而只需要在PL/SQL引擎与SQL引擎之间进行一次上下文切
换，从而大大提高了系统的性能。 5811.2.2 批DML操作FORALL语句 使用批绑定实现INSERT操作使用批绑定实现UP
DATE操作 使用批绑定实现DELETE操作 59（1）FORALL语句 使用FORALL语句执行批量DML操作。FORALL语句
的语法： FORALL index IN lower_bound..upper_bond sql_statement;注意：
FORALL语句中每次使用集合中一个完整的元素，不能是元素的分量。 60CREATE TABLE bulk_test( sno
NUMBER(6) PRIMARY KEY, sname VARCHAR2(15));61（2）使用批绑定实现INSERT操
作DECLARE TYPE SNO_TABLE_TYPE IS TABLE OF NUMBER(10)
INDEX BY BINARY_INTEGER; TYPE SNAME_TABLE_TYPE IS TABLE OF VA
RCHAR2(30) INDEX BY BINARY_INTEGER; v_sno SNO_TABL
E_TYPE; v_sname SNAME_TABLE_TYPE;BEGIN FOR i IN 1..5000 LOOP
v_sno(i):=i; v_sname(i):=''Name''||TO_CHAR(i); END LOOP; DELE
TE FROM BULK_TEST; FORALL i IN v_sno.FIRST..v_sno.LAST INSER
T INTO bulk_test VALUES(v_sno(i),v_sname(i)); COMMIT;END;62（3）使
用批绑定实现UPDATE操作 DECLARE TYPE SNO_TABLE_TYPE IS TABLE OF NUMBER(10
) INDEX BY BINARY_INTEGER; TYPE SNAME_TABLE_
TYPE IS TABLE OF VARCHAR2(30) INDEX BY BINAR
Y_INTEGER; v_sno SNO_TABLE_TYPE; v_sname SNAME_TABLE_TYPE;BEGIN
FOR i IN 1..1000 LOOP v_sno(i):=i; v_sname(i):=''ModifyNam
e''||TO_CHAR(i); END LOOP; FORALL i IN v_sno.FIRST..v_sno.LAST
UPDATE bulk_test SET SNAME=v_sname(i) WHERE sno=v_sno(i);
COMMIT;END;63（4）使用批绑定实现DELETE操作 DECLARE TYPE SNO_TABLE_TYPE IS
TABLE OF NUMBER(10) INDEX BY BINARY_INTEGE
R; v_sno SNO_TABLE_TYPE;BEGIN FOR i IN 1..1000 LOOP v_sno(i)
:=i; END LOOP; FORALL i IN v_sno.FIRST..v_sno.LAST DELETE
FROM bulk_test WHERE sno=v_sno(i); COMMIT;END; 64（5）FORALL语句中的异常
处理在通常情况下，当使用FORALL执行批DML操作过程出现错误时，引发错误的那条SQL语句以自动回滚的方式终止，同时批绑定操作停
止。前面已经完成的SQL语句并不进行回滚，因此可以通过在异常处理中执行COMMIT命令，提交产生错误的SQL语句之前已经成功执行的
SQL语句。 65DECLARE TYPE SNO_TABLE_TYPE IS TABLE OF NUMBER(10)
INDEX BY BINARY_INTEGER; TYPE SNAME_TABLE_TYPE IS
TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
v_sno SNO_TABLE_TYPE; v_sname SNAME_TABLE_TYPE; BEGIN FOR
i IN 1..50 LOOP v_sno(i):=i; v_sname(i):=''Name''||TO_CHAR(
i); END LOOP; v_sname(4):=''The name is too long''; DELETE
FROM BULK_TEST; FORALL i IN v_sno.FIRST..v_sno.LAST INSERT
INTO bulk_test VALUES(v_sno(i),v_sname(i)); 66EXCEPTION WHEN
OTHERS THEN COMMIT; END;SELECT FROM BULK_TEST;SNO SNAM
E--------------1 Name12 Name23 Name3结果分析：bulk_t
est表中sname列的数据类型为CHAR(10)，而v_sname(4)的字符串长度大于10，因此插入操作失败，导致异常，但此时
并不回滚之前已经成功执行的插入操作，程序流程转移到异常处理部分，执行COMMIT操作，因此只完成前3条记录的插入。如果没有异常处理
部分，则由于程序没有正常结束而整体回滚，所有的操作都将被取消。6711.2.3 批查询批查询概述BULK COLLECT在SEL
ECT…INTO语句中的应用BULK COLLECT在FETCH…INTO语句中的应用 BULK COLLECT在RETURNIN
G …INTO语句中的应用 68（1）批查询概述批查询是指利用BULK COLLECT语句一次返回多条记录信息，并存放到集合类型的
变量中。可以在SELECT…INTO，FETCH…INTO， RETURNING…INTO语句中使用批查询。69（2）BULK C
OLLECT 在SELECT…INTO语句中的应用基本语法：SELECT column_name BULK COLLECT
INTO collect_name; 70利用批绑定查询50部门员工信息，并输出。DECLARE TYPE t_emp IS T
ABLE OF employees%ROWTYPE; v_emp t_emp;BEGIN SELECT BULK COLL
ECT INTO v_emp FROM employees WHERE department_id=50; FOR i IN
v_emp.FIRST..v_emp.LAST LOOP DBMS_OUTPUT.PUT_LINE(v_emp(i).em
ployee_id||'' ''|| v_emp(i).first_name||'' ''||v_emp(i).last_nam
e ||'' ''|| v_emp(i).salary); END LOOP;END;71（3）BULK COLLECT在 F
ETCH…INTO语句中的应用 基本语法：FETCH cursor_name BULK COLLECT INTO collect_
name; 72利用批绑定查询比本部门平均工资高的员工信息。DECLARE TYPE t_emp IS TABLE OF emp
loyees%ROWTYPE; v_emp t_emp; CURSOR c_emp IS SELECT FROM empl
oyees e WHERE salary>(SELECT avg(salary) FROM employees WH
ERE department_id=e.department_id);BEGIN OPEN c_emp; FE
TCH c_emp BULK COLLECT INTO v_emp; FOR i IN v_emp.FIRST..v
_emp.LAST LOOP DBMS_OUTPUT.PUT_LINE(v_emp(i).employee_id||''
''|| v_emp(i).first_name||'' ''||v_emp(i).last_name||'' ''||
v_emp(i).salary); END LOOP;END;73（4）BULK COLLECT在RETURNING …INT
O语句中的应用基本语法RETURNI NG column_name BULK COLLECT INTO collect_name;
注意不能在FORALL语句中使用SELECT…BULK COLLECT INTO，但可以在FORALL语句的RETURNING…I
NTO语句中使用BULK COLLECT子句。 74利用批绑定修改50部门员工工资，并输出修改后的员工信息DECLARETYPE
t_rec IS RECORD(empno employees.employee_id%TYPE,fname employees.
first_name%TYPE,lname employees.last_name%TYPE,salary employees.s
alary%TYPE); TYPE t_emp IS TABLE OF t_rec; v_emp t_emp;BEGINUPD
ATE employees SET salary=salary+100 WHERE department_id=50 RETURN
ING employee_id,first_name,last_name,salary BULK COLLECT INTO v_
emp; FOR i IN v_emp.first..v_emp.last LOOP DBMS_OUTPUT.PUT_LI
NE(v_emp(i).empno||'' ''|| v_emp(i).fname||'' ''||emp(i). lname||
'' ''||v_emp(i).salary); END LOOP;END;7511.2.4 批绑定属性%FOUND：当前SQL语
句操作是否对数据库有影响%NOTFOUND：当前SQL语句操作是否对数据库有影响%ROWCOUNT：批绑定操作全部完成后所影响的行
数%BULK_EXCEPTIONS：保存批绑定运行过程中的错误信息%BULK_ROWCOUNT：具有索引表语义（注意不是索引表，不
能通过COUNT属性统计元素个数），它的索引值为i的元素值%BULK_ROWCOUNT(i)表示批绑定中第i个SQL语句处理的行数
。 76利用批绑定属性输出批绑定操作中每个SQL语句所影响的行数以及整个批绑定过程所影响的行数。 77DECLARE TYPE
t_number IS TABLE OF NUMBER; deptnums t_number;BEGIN SELECT dep
artment_id BULK COLLECT INTO deptnums FROM departments; FORALL
i IN 1..deptnums.COUNT INSERT INTO bulk_test SELECT emp
loyee_id, department_id FROM employees WHERE department_id
=deptnums(i); FOR i IN 1..deptnums.COUNT LOOP DBMS_OUTPUT.PUT_
LINE(''Department ''||deptnums(i)|| '': inserted ''||SQL%BULK_ROW
COUNT(i)||'' records''); END LOOP; DBMS_OUTPUT.PUT_LINE(''Total re
cords inserted :'' || SQL%ROWCOUNT);END;11.3 动态SQL 动态SQL概述动态
非查询语句及单行查询语句动态多行查询语句7811.3.1 动态SQL概述动态SQL语句是指在运行PL/SQL程序时动态创建和运行
的SQL语句，在运行时动态分析并执行动态SQL语句实现技术：DBMS_SQL包本地动态SQL语句本地动态SQL语句运行模式：非查询
语句（DML、DDL、ALTER SESSION、事务控制、匿名PL/SQL块）查询语句79CREATE OR REPLACE P
ROCEDURE sql_test(p_empno employees.employee_id%TYPE)ASv_sal empl
oyees.salary%TYPE;BEGIN SELECT salary INTO v_sal FROM employees
WHERE employee_id=p_empno; DBMS_OUTPUT.PUT_LINE(v_sal);END;80
CREATE OR REPLACE PROCEDURE dyn_sql_test( p_col VARCHAR2,p_value
VARCHAR2)AS v_sal employees.salary%TYPE;BEGIN SELECT salary INT
O v_sal FROM employees WHERE p_col=p_value; DBMS_OUTPUT.PUT_L
INE(v_sal);END;81分别调用这两个存储过程：SQL>EXEC sql_test(150)PL/SQL 过程已成功完成
。SQL>EXEC dyn_sql_test(''EMPLOYEE_ID'',''150'')BEGIN dyn_sql_test(''EM
PLOYEE_ID'',''150''); END;第 1 行出现错误:ORA-01403: 未找到数据ORA-06512: 在 "H
R.DYN_SQL_TEST", line 6ORA-06512: 在 line 18211.3.2 动态非查询语句及单行查询语句
EXECUTE IMMEDIATE ''SQL statement''[INTO (var1,var2,…|record)][USIN
G[IN|OUT|IN OUT ]bindvar1,bindvar2, …][RETURNING|RETURN INTO outv
ar1,outvar2,…];83INTO子句用于将查询的返回值赋给PL/SQL变量，该变量的模式是OUT。USING子句将SQL
语句中的绑定变量与PL/SQL变量或参数相关联。如果动态SQL语句中有RETURNING子句，则该变量可以是IN模式给绑定变量传值
，也可以是OUT模式接受值。否则，只能是IN模式。RETURNING中的变量是OUT模式，接受动态SQL语句中RETURNING子
句操作返回值84（1）使用EXECUTE IMMEDIATE实现DDL操作85DECLARE i NUMBER; v_str
VARCHAR2(100);BEGIN SELECT count() INTO i FROM user_tables
WHERE table_name =''USERINFO''; IF i=0 THEN v_str:=''CREATE TA
BLE USERINFO( id NUMBER,name CHAR(10))''; EXECUT
E IMMEDIATE v_str; END IF;END;CREATE OR REPLACE PROCEDURE create_
table( p_tablename VARCHAR2, p_col1 VARCHAR2, p_col1_type VARCHAR
2, p_col2 VARCHAR2, p_col2_type VARCHAR2, p_col3 VARCHAR2, p_col3
_type VARCHAR2)ASv_creation VARCHAR2(100);BEGIN v_creation:=''CRE
ATE TABLE ''||p_tablename||''(''|| p_col1 ||'' ''||p_col1_type||'' p
rimary key,''||p_col2||'' ''|| p_col2_type||'',''||p_col3||'' ''||p_co
l3_type||'')''; EXECUTE IMMEDIATE v_creation; END;86BEG
INcreate_table(''test1'',''sno'',''NUMBER'',''sname'',''CHAR(10)'',''sage'',''
NUMBER'');create_table(''test2'',''id'',''NUMBER'',''name'',''CHAR(20)'',''gr
ade'',''NUMBER'');END;87（2）使用EXECUTE IMMEDIATE实现DCL操作88CREATE OR REP
LACE PROCEDURE grant_priv(p_priv VARCHAR2,p_username VARCHAR2)ASs
ql_str varchar2(100);BEGIN sql_str:=''GRANT ''||p_priv ||'' TO ''||
p_username; EXECUTE IMMEDIATE sql_str;END;（3）使用EXECUTE IMMEDIATE
实现DML操作89（1）SQL语句中不包含绑定变量CREATE OR REPLACE PROCEDURE dyn_updatesa
l(p_empno employees.employee_id%TYPE,p_sal NUMBER)AS v_str VARCH
AR2(100);BEGIN v_str:=''UPDATE employees SET salary=''||p_sal||
'' WHERE employee_id=''||p_empno; EXECUTE IMMEDIATE v_str;E
ND;（2）SQL语句中包含绑定变量如果动态DML语句中包含绑定变量，则需要使用USING子句为绑定变量传值。 例如，创建一个存储
过程，根据参数修改某个员工的信息。CREATE OR REPLACE PROCEDURE dyn_dml_test(p_col V
ARCHAR2, p_value VARCHAR2,p_empno NUMBER)IS v_str VARCHAR2(100);
BEGIN v_str:=''UPDATE emp SET ''||p_col|| ''=:ph_value WHERE
employee_id=:ph_empno''; EXECUTE IMMEDIATE v_str USING p_value,p
_empno;END;90（3）处理包含RETURNING子句的DML语句CREATE OR REPLACE PROCEDURE
dyn_return_local(p_col VARCHAR2, p_value VARCHAR2)AS v_str VARCH
AR2(200); v_fname employees.first_name%TYPE; v_lname employees.
last_name%TYPE;BEGIN v_str:=''DELETE FROM employees WHERE ''||p_co
l||'' =:ph_value RETURNING first_name,last_name
INTO :ph_fname,:ph_lname''; EXECUTE IMMEDIATE v_str USING
p_value RETURNING INTO v_fname,v_lname; DBMS_OUTPUT.P
UT_LINE(v_fname||'' ''||v_lname);END;91（4）使用EXECUTE IMMEDIATE实现单行查询
92CREATE OR REPLACE PROCEDURE query_emp (p_col VARCHAR2, p_value
VARCHAR2)IS v_fname employees.first_name%TYPE; v_lname emplo
yees.last_name%TYPE; v_empno employees.employee_id%TYPE; v_dep
tno employees.department_id%TYPE; v_sal employees.salary%TYPE
; v_str VARCHAR2(200);BEGIN v_str:=''SELECT employee_id,first
_name,last_name,salary,department_id FROM employees WHERE ''||p_co
l ||''=:ph_value''; EXECUTE IMMEDIATE v_str INTO v_empno,v_fn
ame,v_lname,v_sal,v_deptno USING p_value; DBMS_OUTPUT.PUT_LINE
(v_empno||'' ''||v_fname||'' ''|| v_lname||'' ''||v_sal||'' ''||v_dept
no);END;9311.3.3 动态多行查询语句94（1）定义游标类型TYPE t_typename IS REF CURSO
R;（2）定义游标变量c_cursor REFCURSOR;（3）构造动态查询字符串（4）将游标变量与动态查询语句结合OPEN c
_cursor FOR v_str USING ……;（5）利用循环结构、FETCH语句检索游标并进行处理。LOOP FETCH
… INTO …; EXIT WHEN c_cursor%NOTFOUND;END LOOP;（6）关闭游标变量。CLOSE
c_cursor;CREATE OR REPLACE PROCEDURE multi_query(p_col VARCHAR2,p
_condition VARCHAR2,p_value VARCHAR2)AS TYPE t_cursor IS REF CUR
SOR; v_cursor t_cursor; lv_query VARCHAR2(200); v_empno empl
oyees.employee_id%TYPE; v_fname employees.first_name%TYPE; v_l
name employees.last_name%TYPE; v_sal employees.salary%TYPE;
v_deptno employees.department_id%TYPE; BEGIN lv_query:=''SELEC
T mployee_id,first_name,last_name,salary, department_id FROM em
ployees WHERE ''||p_col||p_condition|| '':ph_value'';95 OPEN v_
cursor FOR lv_query USING p_value; LOOP FETCH v_cursor IN
TO v_empno,v_fname,v_lname,v_sal,v_deptno ; EXIT
WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno||'' ''||v_f
name||'' ''|| v_lname||'' ''||v_sal||'' ''||v_deptno); EN
D LOOP;END;969711.4 利用PL/SQL实现分页查询利用集合实现分页查询利用游标变量实现分页查询分页查询在Java
开发中的应用9811.4.1 利用集合实现分页查询如果要对特定结构的结果集进行分页查询，则可以使用集合实现。 利用分页查询实现对
员工工资名次、员工号、员工名、工资和部门号的查询。 CREATE OR REPLACE PACKAGE emppkg IS TY
PE t_record IS RECORD( rn INT, empno employees.employee_id%TY
PE, fname employees.first_name%TYPE, lname employees.last_nam
e%TYPE, sal employees.salary%TYPE, deptno employees.departmen
t_id%TYPE); TYPE emplist IS TABLE OF t_record; PROCEDURE sp_pa
ge( p_pageSize IN OUT INT, p_curPageNo IN OUT INT,
p_outCollection OUT emplist );END;99CREATE OR REPLACE PACKAGE B
ODY emppkg IS PROCEDURE sp_page( p_pageSize IN OUT IN
T, p_curPageNo IN OUT INT, p_outCollection OUT empl
ist ) AS v_startRownum NUMBER; v_endRownum NUMB
ER; v_totalRecords NUMBER; v_totalPages NUMBER;
indexValue BINARY_INTEGER :=1; BEGIN SELECT cou
nt() INTO v_totalRecords FROM employees; IF p_pageSize < 0 TH
EN p_pageSize := 10; END IF; 100 IF MOD(v_totalRec
ords,p_pageSize)=0 THEN v_totalPages:=v_totalRecords/p_pageS
ize; ELSE v_totalPages:=floor(v_totalRecords/p_pageSize)+
1; END IF; IF p_curPageNo < 1 THEN p_curPageNo := 1;
END IF; IF p_curPageNo>v_totalPages THEN p_curPageNo
:=v_totalPages; END IF; v_startRownum := (p_curPageNo-1)p_
pageSize + 1; v_endRownum := p_curPageNo p_pageSize; p_o
utCollection:=emplist();101FOR v_emp IN (SELECT FROM (
SELECT ROWNUM rn,employee_id,first_name, last_name,sa
lary,department_id FROM ( SELECT employee_id,first_name,
last_name, salary,department_id FROM employees
WHERE salary IS NOT NULL ORDER BY salary) A WHERE RO
WNUM<=v_endRownum) B WHERE rn>=v_startRownum ) LOOP
p_outCollection.extend; p_outCollection(indexValue):=v_emp;
indexValue:=indexValue+1; END LOOP; END sp_page;END emppkg;1
0210311.4.2 利用游标变量实现分页查询如果要对任意查询实现分页，则可以使用游标变量来实现。104CREATE OR R
EPLACE PACKAGE curspkg IS TYPE refCursorType IS REF CURSOR;
PROCEDURE sp_page( p_pageSize IN OUT IN
T, --每页输出的记录数 p_curPageNo IN OUT INT, --当前页码
p_sqlSelect VARCHAR2, --查询语句,含排序部分 p_
totalPages OUT INT, --返回总页数 p_totalRecords O
UT INT, --返回总记录数 p_outCursor OUT refCursor
Type --查询返回当
前页的数据 ); END; 105CREATE OR REPLACE PACKAGE BODY CURSPKG
IS PROCEDURE sp_page( p_pageSize IN OUT INT,
p_curPageNo IN OUT INT, p_sqlSelect
VARCHAR2, p_totalPages OUT INT,
p_totalRecords OUT INT,
p_outCursor OUT refCursorType) IS v_
countSql VARCHAR2(1000); v_startRownum INT; v_e
ndRownum INT; v_sql VARCHAR2(4000); BEGIN v
_countSql:= ''SELECT to_char(count())FROM(''||p_sqlSe
lect||'')''; EXECUTE IMMEDIATE v_countSql INTO p_totalRecor
ds;106 IF p_pageSize < 0 THEN p_pageSize := 10
; END IF; IF MOD(p_totalRecords,p_pageS
ize) = 0 THEN p_totalPages := p_totalRecords/p_pageSiz
e; ELSE p_totalPages := floor(p_totalRecords/p
_pageSize)+ 1; END IF; IF p_curPageNo < 1 THEN
p_curPageNo := 1; END IF; IF p_cu
rPageNo>p_totalPages THEN p_curPageNo := p_totalPages;
END IF; 107 v_startRownum := p_curPageNo p_pageSize;
v_endRownum := (p_curPageNo-1)p_pageSize + 1; v_sql := ''
SELECT FROM ( SELECT ROWNUM rn ,A. FROM ('' || p_sqlSelect |
| '') A WHERE rownum <= '' || to_char(v_startRownum) || '') B
WHERE rn >= '' || to_char(v_endRownum); OPEN p_outCursor FOR
v_sql; END sp_page; END; 108SQL>VARIABLE v_cursor REFCURSORSQL>DE
CLARE v_pageSize NUMBER :=10; v_curPageNo NUMBER:=2;
v_sql VARCHAR2(1000); v_totalPages NUMBER; v_totalR
ecordCount NUMBER; BEGIN v_sql:=''SELECT FROM employees
WHERE sal ary IS NOT NULL ORDER BY sal''; curspkg.sp_page(
v_pageSize,v_curPageNo,v_sql,v_totalPages, v_totalRecordCount,:v_cursor); DBMS_OUTPUT.PUT_LINE(''The pagesize is :''||v_pageSize); DBMS_OUTPUT.PUT_LINE( ''The current page NO. is :''||v_curPageNo); DBMS_OUTPUT.PUT_LINE( ''The totalPages is :''||v_totalPages); DBMS_OUTPUT.PUT_LINE( ''The totalRecords is :''|| v_totalRecordCount);END; 10911.4.3 分页查询在Java开发中的应用import java.sql.;import oracle.jdbc.driver.;public class Oracle{ public static void main(String[] args){ Connection conn = null; CallableStatement proc = null; ResultSet rs = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn=DriverManager.getConnection("jdbc:oracle:thin: @172.23.5.60: 1521:orcl", “hr","tiger"); String sql = "{call CURSPKG.SP_PAGE(?,?,?,?,?,?)}"; 110 proc = conn.prepareCall(sql); proc.setInt(1, 10); proc.registerOutParameter(1,OracleTypes.INTEGER); proc.setInt(2, 2)； proc.registerOutParameter(2,OracleTypes.INTEGER)； proc.setString(3, "select from emp order by sal")； proc.registerOutParameter(4, OracleTypes.INTEGER)； proc.registerOutParameter(5, OracleTypes.INTEGER)； proc.registerOutParameter(6, OracleTypes.CURSOR)； proc.execute()； int pages=((OracleCallableStatement)proc).getInt(4)； int rows=((OracleCallableStatement)proc).getInt(5)； rs=((OracleCallableStatement)proc).getCursor(6)； int pages=((OracleCallableStatement)proc).getInt(4)； int rows=((OracleCallableStatement)proc).getInt(5)； rs=((OracleCallableStatement)proc).getCursor(6)； 111while(rs.next()) {System.out.println(rs.getString(1)+"|"+rs.getString(2)+"|"+ rs.getString(3)+"|"+rs.getString(4)+"|"+rs.getString(5)+"|"+ rs.getString(6)+"|"+rs.getString(7))；} }catch(Exception e) { e.printStackTrace()；} finally{ try { rs.close()； proc.close()； conn.close()； } catch (SQLException e) { e.printStackTrace()；} } }} 112总结集合 批绑定动态SQL分页查询 |
|
