--一.PL/SQL复合数据类型--(一).PL/SQL记录--1.定义PL/SQL记录--(1).定义PL/SQL记录--GrammarTYPE type_name IS RECORD( field_declaration1[, field_declaration2]...);idetifier type_name;--Demo1DECLARE TYPE emp_record_type IS RECORD( name emp.ename%TYPE, salary emp.sal%TYPE, dno emp.deptno%TYPE ); emp_record emp_record_type;...--(2).使用%ROWTYPE属性定义记录变量--Grammeridentifier table_name%ROWTYPE;identifier view_name%ROWTYPE;--Demo1dept_record dept%ROWTYPE;emp_record emp%ROWTYPE;--2.使用PL/SQL记录--Demo1:SELECT INTO 中用PL/SQL记录SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 TYPE emp_record_type IS RECORD( 3 name emp.ename%TYPE, 4 salary emp.sal%TYPE, 5 dno emp.deptno%TYPE 6 ); 7 emp_record emp_record_type; 8 BEGIN 9 SELECT ename,sal,deptno INTO emp_record 10 FROM emp 11 WHERE empno=&no; 12 dbms_output.put_line(emp_record.name); 13 END; 14 /输入 no 的值: 7788原值 11: WHERE empno=&no;新值 11: WHERE empno=7788;SCOTTPL/SQL 过程已成功完成。--Demo2:SELECT INTO 中用PL/SQL记录成员变量记录SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 TYPE emp_record_type IS RECORD( 3 name emp.ename%TYPE, 4 salary emp.sal%TYPE, 5 dno emp.deptno%TYPE 6 ); 7 emp_record emp_record_type; 8 BEGIN 9 SELECT ename,sal INTO emp_record.name,emp_record.salary 10 FROM emp 11 WHERE empno=&no; 12 dbms_output.put_line(emp_record.name); 13 END; 14 /输入 no 的值: 7369原值 11: WHERE empno=&no;新值 11: WHERE empno=7369;SMITHPL/SQL 过程已成功完成。--Demo3:INSERT 中用PL/SQL记录SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 dept_record dept%ROWTYPE; 3 BEGIN 4 dept_record.deptno:=50; 5 dept_record.dname:='ADMINISTRATOR'; 6 dept_record.loc:='BEIJING'; 7 INSERT INTO dept VALUES dept_record; 8 dbms_output.put_line('新增'||SQL%ROWCOUNT||'行'); 9 END; 10 /新增1行PL/SQL 过程已成功完成。--Demo4:INSERT VALUES 中用PL/SQL记录成员SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 dept_record dept%ROWTYPE; 3 BEGIN 4 dept_record.deptno:=60; 5 dept_record.dname:='SALES'; 6 dept_record.loc:='BEIJING'; 7 INSERT INTO dept(deptno,dname) 8 VALUES (dept_record.deptno,dept_record.dname); 9 dbms_output.put_line('新增'||SQL%ROWCOUNT||'行'); 10 END; 11 /新增1行PL/SQL 过程已成功完成。--Demo5:UPDATE SET 中用PL/SQL记录变量SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 dept_record dept%ROWTYPE; 3 BEGIN 4 dept_record.deptno:=30; 5 dept_record.dname:='SALES'; 6 dept_record.loc:='SHANGHAI'; 7 UPDATE dept SET ROW=dept_record 8 WHERE deptno=&deptno; 9 dbms_output.put_line('修改'||SQL%ROWCOUNT||'行'); 10 END; 11 /输入 deptno 的值: 30原值 8: WHERE deptno=&deptno;新值 8: WHERE deptno=30;修改1行PL/SQL 过程已成功完成。--Demo6:UPDATE SET 中用PL/SQL记录变量成员SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 dept_record dept%ROWTYPE; 3 BEGIN 4 dept_record.loc:='GUANGZHOU'; 5 UPDATE dept SET loc=dept_record.loc 6 WHERE deptno=&deptno; 7 dbms_output.put_line('修改'||SQL%ROWCOUNT||'行'); 8 END; 9 /输入 deptno 的值: 10原值 6: WHERE deptno=&deptno;新值 6: WHERE deptno=10;修改1行PL/SQL 过程已成功完成。--Demo7:DELETE 中用PL/SQL记录成员(只能用成员)SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 dept_record dept%ROWTYPE; 3 BEGIN 4 dept_record.deptno:=50; 5 DELETE FROM dept WHERE deptno=dept_record.deptno; 6 dbms_output.put_line('删除'||SQL%ROWCOUNT||'行'); 7 END; 8 /删除1行PL/SQL 过程已成功完成。--(二).PL/SQL集合--单行单列用标量变量--单行多列用PL/SQL记录--多行单列用PL/SQL集合--集合类型包括索引表,嵌套表,变长数组--1.索引表,也称为PL/SQL表--特性:索引表下标可以为负数;索引表的元素个数没有限制;索引表只能作为PL/SQL复合数据类型使用;索引表不能作为表列类型使用--Grammer:key_type可以使用数据类型BINARY_INTEGER,PLS_INTEGER,VARCHAR2TYPE type_name IS TABLE OF element_type[NOT NULL] INDEX BY key_type;identifier type_name;--Demo1SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 TYPE ename_table_type IS TABLE OF emp.ename%TYPE 3 INDEX BY BINARY_INTEGER; 4 ename_table ename_table_type; 5 BEGIN 6 SELECT ename INTO ename_table(-1) 7 FROM emp 8 WHERE empno=&no; 9 dbms_output.put_line('雇员名：'||ename_table(-1)); 10 END; 11 /输入 no 的值: 7788原值 8: WHERE empno=&no;新值 8: WHERE empno=7788;雇员名：SCOTTPL/SQL 过程已成功完成。--Demo2SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 TYPE area_table_type IS TABLE OF NUMBER 3 NOT NULL INDEX BY VARCHAR(10); 4 area_table area_table_type; 5 BEGIN 6 area_table('北京'):=1; 7 area_table('上海'):=2; 8 area_table('广州'):=3; 9 dbms_output.put_line('第一个元素：'||area_table.first); 10 dbms_output.put_line('第二个元素：'||area_table.last); 11 END; 12 /第一个元素：北京第二个元素：上海PL/SQL 过程已成功完成。--2.嵌套表--特性:下标从1开始;元素个数没有限制;数组元素值可以稀疏;可以作为表列的数据类型使用--GrammerTYPE type_name IS TABLE OF element_type;identifier type_name;--Demo1:SELECT INTOSQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 TYPE ename_table_type IS TABLE OF emp.ename%TYPE; 3 ename_table ename_table_type; 4 BEGIN 5 --构造函数初始化嵌套表变量后才能在PL/SQL中引用 6 ename_table:=ename_table_type('A','A','A'); 7 SELECT ename INTO ename_table(2) 8 FROM emp 9 WHERE empno=&no; 10 dbms_output.put_line('雇员名：'||ename_table(2)); 11 END; 12 /输入 no 的值: 7788原值 9: WHERE empno=&no;新值 9: WHERE empno=7788;雇员名：SCOTTPL/SQL 过程已成功完成。--Demo2:表列中使用嵌套表SQL> CREATE TYPE phone_type IS TABLE OF VARCHAR(20); 2 /类型已创建。SQL> CREATE TABLE employes( 2 id NUMBER(4), 3 name VARCHAR2(10), 4 sal NUMBER(6,2), 5 phone phone_type 6 ) 7 --使用嵌套表作为表列的数据类型时，必须要为嵌套表指定专门的存储表 8 NESTED TABLE phone STORE AS phone_table;表已创建。--Demo3:在PL/SQL块中为嵌套表列插入数据SQL> --当定义嵌套表类型时，Oracle自动为该类型生成对应的构造方法。SQL> --当为嵌套表插入数据时，需要使用嵌套表的构造方法。SQL> BEGIN 2 INSERT INTO employes VALUES(2,'SMITH',1000, 3 phone_type('020-81181817','13922390000')); 4 dbms_output.put_line('插入'||SQL%ROWCOUNT||'条'); 5 END; 6 /插入1条PL/SQL 过程已成功完成。--Demo4:在PL/SQL块中检索嵌套表列的数据SQL> DECLARE 2 phone_table phone_type; 3 BEGIN 4 SELECT phone INTO phone_table 5 FROM employes 6 WHERE id=1; 7 FOR i IN 1..phone_table.COUNT LOOP 8 dbms_output.put_line('电话号码：'||phone_table(i)); 9 END LOOP; 10 END; 11 /电话号码：020-81181818电话号码：13922390110PL/SQL 过程已成功完成。--Demo5:在PL/SQL中更新嵌套表列的数据SQL> DECLARE 2 phone_table phone_type:=phone_type('020-817191111','13922222222', 3 '030-81111111','13911111111'); 4 BEGIN 5 UPDATE employes SET phone=phone_table 6 WHERE id=1; 7 dbms_output.put_line('更新'||SQL%ROWCOUNT||'条'); 8 END; 9 /更新1条PL/SQL 过程已成功完成。--3.变长数组VARRAY--特性:下标从1开始;可以作为表列的类型使用;有限定义长度数组--GrammerTYPE type_name IS VARRAY(size_limit) OF element_type [NOT NULL];identifier type_name;--Demo1:PL/SQL块中使用VARRAYSQL> DECLARE 2 TYPE ename_varray_type IS VARRAY(20) OF emp.ename%TYPE; 3 --PL/SQL块中引用VARRAY类型变量时，必须使用构造方法初始化。 4 ename_varray ename_varray_type:=ename_varray_type('mary'); 5 BEGIN 6 SELECT ename INTO ename_varray(1) FROM emp 7 WHERE empno=&eno; 8 dbms_output.put_line('雇员名：'||ename_varray(1)); 9 END; 10 /输入 eno 的值: 7788原值 7: WHERE empno=&eno;新值 7: WHERE empno=7788;PL/SQL 过程已成功完成。--Demo2:表列中使用VARRAYSQL> DROP TABLE employes;表已删除。SQL> DROP TYPE phone_type;类型已删除。SQL>SQL> CREATE TYPE phone_type IS VARRAY(20) OF VARCHAR2(20); 2 /类型已创建。SQL> CREATE TABLE employes( 2 id NUMBER(4), 3 name VARCHAR2(10), 4 sal NUMBER(6,2), 5 phone phone_type 6 );表已创建。--4.PL/SQL记录表--特性：处理多行多列数据;结合了PL/SQL记录与PL/SQL集合的优点;--demo1:PL/SQL块中使用PL/SQL记录表SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 TYPE emp_table_type IS TABLE OF emp%ROWTYPE 3 INDEX BY BINARY_INTEGER; 4 emp_table emp_table_type; 5 BEGIN 6 SELECT * INTO emp_table(1) FROM emp 7 WHERE empno=&eno; 8 dbms_output.put_line('雇员名：'||emp_table(1).ename); 9 dbms_output.put_line('雇员工资：'||emp_table(1).sal); 10 END; 11 /输入 eno 的值: 7788原值 7: WHERE empno=&eno;新值 7: WHERE empno=7788;雇员名：SCOTT雇员工资：1200PL/SQL 过程已成功完成。--5.多级集合--demo1:在PL/SQL块中使用多级VARRAYSQL> DECLARE 2 --定义一维VARRAY 3 TYPE al_varray_type IS VARRAY(10) OF INT; 4 --定义二维VARRAY 5 TYPE nal_varray_type IS VARRAY(10) OF al_varray_type; 6 --初始化二维集合变量 7 nvl nal_varray_type:=nal_varray_type( 8 al_varray_type(1,2,3), 9 al_varray_type(4,5,6), 10 al_varray_type(7,8,9) 11 ); 12 BEGIN 13 dbms_output.put_line('显示二维数组所有元素'); 14 FOR i IN 1..nvl.COUNT LOOP 15 FOR j IN 1..nvl(i).COUNT LOOP 16 dbms_output.put_line('nvl('||i||','||j||')=' 17 ||nvl(i)(j)); 18 END LOOP; 19 END LOOP; 20 END; 21 /显示二维数组所有元素nvl(1,1)=1nvl(1,2)=2nvl(1,3)=3nvl(2,1)=4nvl(2,2)=5nvl(2,3)=6nvl(3,1)=7nvl(3,2)=8nvl(3,3)=9PL/SQL 过程已成功完成。--demo2:PL/SQL块中使用多级嵌套表SQL> DECLARE 2 --定义一维嵌套表 3 TYPE al_table_type IS TABLE OF INT; 4 --定义二维嵌套表 5 TYPE nal_table_type IS TABLE OF al_table_type; 6 --初始化二维集合变量 7 nvl nal_table_type:=nal_table_type( 8 al_table_type(1,2,3), 9 al_table_type(4,5,6), 10 al_table_type(7,8,9) 11 ); 12 BEGIN 13 dbms_output.put_line('显示二维数组所有元素'); 14 FOR i IN 1..nvl.COUNT LOOP 15 FOR j IN 1..nvl(i).COUNT LOOP 16 dbms_output.put_line('nvl('||i||','||j||')=' 17 ||nvl(i)(j)); 18 END LOOP; 19 END LOOP; 20 END; 21 /显示二维数组所有元素nvl(1,1)=1nvl(1,2)=2nvl(1,3)=3nvl(2,1)=4nvl(2,2)=5nvl(2,3)=6nvl(3,1)=7nvl(3,2)=8nvl(3,3)=9PL/SQL 过程已成功完成。--demo3:PL/SQL中使用多级索引表SQL> DECLARE 2 --定义一维索引表 3 TYPE al_table_type IS TABLE OF INT 4 INDEX BY BINARY_INTEGER; 5 --定义二维索引表 6 TYPE nal_table_type IS TABLE OF al_table_type 7 INDEX BY BINARY_INTEGER; 8 nvl nal_table_type; 9 BEGIN 10 nvl(1)(1):=1; 11 nvl(1)(2):=2; 12 nvl(1)(3):=3; 13 nvl(2)(1):=4; 14 nvl(2)(2):=5; 15 nvl(2)(3):=6; 16 nvl(3)(1):=7; 17 nvl(3)(2):=8; 18 nvl(3)(3):=9; 19 dbms_output.put_line('显示二维数组所有元素'); 20 FOR i IN 1..nvl.COUNT LOOP 21 FOR j IN 1..nvl(i).COUNT LOOP 22 dbms_output.put_line('nvl('||i||','||j||')=' 23 ||nvl(i)(j)); 24 END LOOP; 25 END LOOP; 26 END; 27 /显示二维数组所有元素nvl(1,1)=1nvl(1,2)=2nvl(1,3)=3nvl(2,1)=4nvl(2,2)=5nvl(2,3)=6nvl(3,1)=7nvl(3,2)=8nvl(3,3)=9PL/SQL 过程已成功完成。--(三).集合方法--集合方法是Oracle提供的用于操作集合变量的内置函数或过程--函数:EXISTS,COUNT,LIMIT,FIRST,NEXT,PRIOR,NEXT--过程:EXTEND,TRIM,DELETE--特性:集合方法只使用于PL/SQL,不适用于SQL语句中调用;EXTEND,TRIM只适用于嵌套表和VARRAY;--集合方法调用语法collection_name.method_name[(parameters)]--1.EXISTS:确定集合元素是否存在,TRUE/FALSESQL> DECLARE 2 TYPE ename_table_type IS TABLE OF emp.ename%TYPE; 3 ename_table ename_table_type; 4 BEGIN 5 IF ename_table.EXISTS(1) THEN 6 ename_table(1):='SCOTT'; 7 ELSE 8 dbms_output.put_line('嵌套表变量在PL/SQL块中使用时必须初始化'); 9 END IF; 10 END; 11 /嵌套表变量在PL/SQL块中使用时必须初始化PL/SQL 过程已成功完成。--2.COUNT:集合元素个数SQL> DECLARE 2 TYPE ename_table_type IS TABLE OF emp.ename%TYPE 3 INDEX BY BINARY_INTEGER; 4 ename_table ename_table_type; 5 TYPE sal_table_type IS TABLE OF emp.sal%TYPE 6 INDEX BY BINARY_INTEGER; 7 sal_table ename_table_type; 8 BEGIN 9 ename_table(-9):='SCOTT'; 10 ename_table(0):='SMITH'; 11 ename_table(9):='MARY'; 12 dbms_output.put_line('集合元素总个数：'||ename_table.COUNT); 13 dbms_output.put_line('集合元素总个数：'||sal_table.COUNT); 14 END; 15 /集合元素总个数：3集合元素总个数：0PL/SQL 过程已成功完成。--3.LIMIT:返回集合元素的最大个数,嵌套表和索引表的元素没有个数限制,返回NULLSQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 TYPE ename_varray_type IS VARRAY(20) OF emp.ename%TYPE; 3 ename_varray ename_varray_type:=ename_varray_type('mary'); 4 TYPE ename_table_type IS TABLE OF emp.ename%TYPE; 5 ename_table ename_table_type:=ename_table_type('mary'); 6 BEGIN 7 dbms_output.put_line('ename_varray集合元素的最大个数：'||ename_varray.LIMIT); 8 dbms_output.put_line('ename_table集合元素的最大个数：'||ename_table.LIMIT); 9 END; 10 /ename_varray集合元素的最大个数：20ename_table集合元素的最大个数：PL/SQL 过程已成功完成。--4.FIRST和LAST返回集合变量第一个和最后一个的下标SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 TYPE ename_varray_type IS VARRAY(20) OF emp.ename%TYPE; 3 ename_varray ename_varray_type:=ename_varray_type('mary','mary','mary','mary'); 4 TYPE ename_table_type IS TABLE OF emp.ename%TYPE; 5 ename_table ename_table_type:=ename_table_type('mary','mary','mary','mary'); 6 TYPE ename_table1_type IS TABLE OF emp.ename%TYPE 7 INDEX BY BINARY_INTEGER; 8 ename_table1 ename_table1_type; 9 BEGIN 10 ename_table(1):='SCOTT'; 11 ename_table(2):='TIGER'; 12 ename_table(3):='MARY'; 13 ename_table(4):='BLAKE'; 14 dbms_output.put_line('第一个元素：'||ename_table.FIRST); 15 dbms_output.put_line('最后一个元素：'||ename_table.LAST); 16 ename_varray(1):='SCOTT'; 17 ename_varray(2):='SMITH'; 18 ename_varray(3):='MARY'; 19 ename_varray(4):='BLAKE'; 20 dbms_output.put_line('第一个元素：'||ename_varray.FIRST); 21 dbms_output.put_line('最后一个元素：'||ename_varray.LAST); 22 ename_table1(-5):='SCOTT'; 23 ename_table1(1):='SMITH'; 24 ename_table1(5):='MARY'; 25 ename_table1(10):='BLAKE'; 26 dbms_output.put_line('第一个元素：'||ename_table1.FIRST); 27 dbms_output.put_line('最后一个元素：'||ename_table1.LAST); 28 END; 29 /第一个元素：1最后一个元素：4第一个元素：1最后一个元素：4第一个元素：-5最后一个元素：10PL/SQL 过程已成功完成。--5,PRIOR和NEXT:返回集合元素的前(后)一个元素的下标SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 TYPE ename_table1_type IS TABLE OF emp.ename%TYPE 3 INDEX BY BINARY_INTEGER; 4 ename_table1 ename_table1_type; 5 BEGIN 6 ename_table1(-5):='SCOTT'; 7 ename_table1(1):='SMITH'; 8 ename_table1(5):='MARY'; 9 ename_table1(10):='BLAKE'; 10 dbms_output.put_line('元素5的前一个元素下标：'||ename_table1.PRIOR(5)); 11 dbms_output.put_line('元素5的后一个元素下标：'||ename_table1.NEXT(5)); 12 dbms_output.put_line('元素-5的后一个元素下标：'||ename_table1.PRIOR(-5)); 13 dbms_output.put_line('元素10的后一个元素下标：'||ename_table1.NEXT(10)); 14 END; 15 /元素5的前一个元素下标：1元素5的后一个元素下标：10元素-5的后一个元素下标：元素10的后一个元素下标：PL/SQL 过程已成功完成。--6,EXTEND:改变原有集合的尺寸,只适用于嵌套表和VARRAY--EXTEND:为集合添加一个NULL元素;EXTEND(n):为集合添加n个NULL元素;EXTEND(n,i):为集合添加n个与第i个元素的值相同的元素;SQL> DECLARE 2 TYPE ename_table_type IS TABLE OF emp.ename%TYPE; 3 ename_table ename_table_type; 4 TYPE ename_table_type1 IS TABLE OF emp.ename%TYPE; 5 ename_table1 ename_table_type1; 6 TYPE ename_table_type2 IS TABLE OF emp.ename%TYPE; 7 ename_table2 ename_table_type2; 8 BEGIN 9 ename_table:=ename_table_type('MARY'); 10 ename_table1:=ename_table_type1('MARY'); 11 ename_table2:=ename_table_type2('MARY'); 12 ename_table.EXTEND(5,1); 13 ename_table1.EXTEND(5); 14 ename_table2.EXTEND(); 15 dbms_output.put_line('ename_table的元素个数为：'||ename_table.COUNT); 16 dbms_output.put_line('ename_table1的元素个数为：'||ename_table1.COUNT); 17 dbms_output.put_line('ename_table2的元素个数为：'||ename_table2.COUNT); 18 END; 19 /ename_table的元素个数为：6ename_table1的元素个数为：6ename_table2的元素个数为：2PL/SQL 过程已成功完成。--7:TRIM与DELETE--TRIM:删除尾部元素,适用于嵌套表和和VARRAY--DELETE:删除集合中的元素,适用于嵌套表和索引表--TRIM:删除尾部元素;--TRIM(n):删除尾部n个元素;--DELETE:删除集合中所有元素;--DELETE(n):删除集合中第n个元素;--DELETE(m,n):删除集合中第m到n个元素;SQL> DECLARE 2 TYPE ename_table_type IS TABLE OF emp.ename%TYPE; 3 ename_table ename_table_type; 4 TYPE ename_table_type1 IS TABLE OF emp.ename%TYPE 5 INDEX BY BINARY_INTEGER; 6 ename_table1 ename_table_type1; 7 TYPE ename_varray_type IS VARRAY(20) OF emp.ename%TYPE; 8 ename_varray ename_varray_type; 9 BEGIN 10 ename_table:=ename_table_type('SCOTT','TIGER','NIOR','SMITH','MARY'); 11 ename_table1(-5):='SS'; 12 ename_table1(0):='TT'; 13 ename_table1(4):='WW'; 14 ename_table1(10):='GG'; 15 ename_varray:=ename_varray_type('张三','李四','王五','赵六'); 16 17 dbms_output.put_line('-------ename_table:'||ename_table.COUNT); 18 ename_table.TRIM(); 19 dbms_output.put_line('ename_table-TRIM:'||ename_table.COUNT); 20 ename_table.TRIM(2); 21 dbms_output.put_line('ename_table-TRIM(2):'||ename_table.COUNT); 22 23 dbms_output.put_line('-----ename_table1:'||ename_table1.COUNT); 24 ename_table1.DELETE(4); 25 dbms_output.put_line('ename_table1-DELETE(4):'||ename_table1.COUNT); 26 ename_table1.DELETE(0,10); 27 dbms_output.put_line('ename_table1-DELETE(0,10):'||ename_table1.COUNT); 28 29 dbms_output.put_line('-----ename_varray:'||ename_varray.COUNT); 30 ename_varray.TRIM(); 31 dbms_output.put_line('ename_varray-TRIM:'||ename_varray.COUNT); 32 ename_varray.TRIM(2); 33 dbms_output.put_line('ename_varray-TRIM(2):'||ename_varray.COUNT); 34 END; 35 /-------ename_table:5ename_table-TRIM:4ename_table-TRIM(2):2-----ename_table1:4ename_table1-DELETE(4):3ename_table1-DELETE(0,10):1-----ename_varray:4ename_varray-TRIM:3ename_varray-TRIM(2):1PL/SQL 过程已成功完成。--(四),集合赋值--嵌套表和VARRAY--1.将一个集合赋值给另一个集合--要求集合的类型必须相同SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 TYPE name_varray_type IS VARRAY(4) OF VARCHAR2(40); 3 name_varray1 name_varray_type:=name_varray_type('SCOTT','SMITH'); 4 name_varray2 name_varray_type; 5 BEGIN 6 FOR i IN 1..name_varray1.COUNT LOOP 7 dbms_output.put_line('name_varray1_ini:'||name_varray1(i)); 8 END LOOP; 9 name_varray2:=name_varray1; 10 FOR i IN 1..name_varray2.COUNT LOOP 11 dbms_output.put_line('name_varray2_ini:'||name_varray2(i)); 12 END LOOP; 13 END; 14 /name_varray1_ini:SCOTTname_varray1_ini:SMITHname_varray2_ini:SCOTTname_varray2_ini:SMITHPL/SQL 过程已成功完成。--2.给集合赋NULLSQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 TYPE name_varray_type IS VARRAY(4) OF VARCHAR2(40); 3 name_varray1 name_varray_type:=name_varray_type('SCOTT','SMITH'); 4 name_varray2 name_varray_type; 5 BEGIN 6 FOR i IN 1..name_varray1.COUNT LOOP 7 dbms_output.put_line('name_varray1_ini:'||name_varray1(i)); 8 END LOOP; 9 name_varray1:=name_varray2; 10 IF name_varray1 IS NULL THEN 11 dbms_output.put_line('name_varray1的现有元素个数为：0'); 12 END IF; 13 END; 14 /name_varray1_ini:SCOTTname_varray1_ini:SMITHname_varray1的现有元素个数为：0PL/SQL 过程已成功完成。--3.集合操作符给嵌套表赋值--SET,MULTISET UNION,MULTISET UNION DISTINCT,MULTISET INTERSECT,MULTISET EXCEPT;--SET去重SQL> DECLARE 2 TYPE temp_table_type IS TABLE OF NUMBER; 3 temp_table temp_table_type:=temp_table_type(1,2,2,3,4,5,6,6); 4 BEGIN 5 temp_table:=SET(temp_table); 6 FOR i IN 1..temp_table.COUNT LOOP 7 dbms_output.put_line('temp_table_values:'||temp_table(i)); 8 END LOOP; 9 END; 10 /temp_table_values:1temp_table_values:2temp_table_values:3temp_table_values:4temp_table_values:5temp_table_values:6PL/SQL 过程已成功完成。--MULTISET UNION 取并集SQL> DECLARE 2 TYPE temp_table_type IS TABLE OF NUMBER; 3 temp1_table temp_table_type:=temp_table_type(5,6,7,8); 4 temp2_table temp_table_type:=temp_table_type(1,2,3,4); 5 BEGIN 6 temp1_table:=temp1_table MULTISET UNION temp2_table; 7 FOR i IN 1..temp1_table.COUNT LOOP 8 dbms_output.put_line('temp1_table_values:'||temp1_table(i)); 9 END LOOP; 10 END; 11 /temp1_table_values:5temp1_table_values:6temp1_table_values:7temp1_table_values:8temp1_table_values:1temp1_table_values:2temp1_table_values:3temp1_table_values:4PL/SQL 过程已成功完成。--MULTISET UNION DISTINCT取非交集部分数据SQL> DECLARE 2 TYPE temp_table_type IS TABLE OF NUMBER; 3 temp1_table temp_table_type:=temp_table_type(5,5,7,8); 4 temp2_table temp_table_type:=temp_table_type(1,2,2,3); 5 BEGIN 6 temp1_table:=temp1_table MULTISET UNION DISTINCT temp2_table; 7 FOR i IN 1..temp1_table.COUNT LOOP 8 dbms_output.put_line('temp1_table_values:'||temp1_table(i)); 9 END LOOP; 10 END; 11 /temp1_table_values:5temp1_table_values:7temp1_table_values:8temp1_table_values:1temp1_table_values:2temp1_table_values:3PL/SQL 过程已成功完成。--MULTISET INTERSECTSQL> DECLARE 2 TYPE temp_table_type IS TABLE OF NUMBER; 3 temp1_table temp_table_type:=temp_table_type(5,6,7,8); 4 temp2_table temp_table_type:=temp_table_type(2,3,4,5); 5 BEGIN 6 temp1_table:=temp1_table MULTISET INTERSECT temp2_table; 7 FOR i IN 1..temp1_table.COUNT LOOP 8 dbms_output.put_line('temp1_table_values:'||temp1_table(i)); 9 END LOOP; 10 END; 11 /temp1_table_values:5PL/SQL 过程已成功完成。--MULTISET EXCEPT取左边非交集部分SQL> DECLARE 2 TYPE temp_table_type IS TABLE OF NUMBER; 3 temp1_table temp_table_type:=temp_table_type(5,6,7,8); 4 temp2_table temp_table_type:=temp_table_type(2,3,4,5); 5 BEGIN 6 temp1_table:=temp1_table MULTISET EXCEPT temp2_table; 7 FOR i IN 1..temp1_table.COUNT LOOP 8 dbms_output.put_line('temp1_table_values:'||temp1_table(i)); 9 END LOOP; 10 END; 11 /temp1_table_values:6temp1_table_values:7temp1_table_values:8PL/SQL 过程已成功完成。--(五),集合比较--IS NULL,IS EMPTY,=,CARDINALITY(),MEMBER OF,IS A SET,SUBMULTISET OF;--CARDINALITY:返回元素个数SQL> DECLARE 2 TYPE temp_table_type IS TABLE OF NUMBER; 3 temp1_table temp_table_type:=temp_table_type(5,6,7,8); 4 BEGIN 5 dbms_output.put_line('temp1_table_CountValues:'||CARDINALITY(temp1_table)); 6 END; 7 /temp1_table_CountValues:4PL/SQL 过程已成功完成。--MEMBER OF:判断元素是否在另一个元素中SQL> DECLARE 2 TYPE temp_table_type IS TABLE OF NUMBER; 3 temp1_table temp_table_type:=temp_table_type(5,6,7,8); 4 temp2 NUMBER:=5; 5 BEGIN 6 IF temp2 MEMBER OF temp1_table THEN 7 dbms_output.put_line('temp2:'||temp2); 8 END IF; 9 END; 10 /temp2:5PL/SQL 过程已成功完成。--SUBMULTISET OF:判断一个嵌套表是否为另一个嵌套表的子集SQL> DECLARE 2 TYPE temp_table_type IS TABLE OF NUMBER; 3 temp1_table temp_table_type:=temp_table_type(5,6,7,8); 4 temp2_table temp_table_type:=temp_table_type(5,6); 5 temp3_table temp_table_type:=temp_table_type(7,9); 6 BEGIN 7 IF temp2_table SUBMULTISET OF temp1_table THEN 8 FOR i IN 1..temp2_table.COUNT LOOP 9 dbms_output.put_line('temp2_table:'||temp2_table(i)); 10 END LOOP; 11 END IF; 12 IF temp3_table SUBMULTISET OF temp1_table THEN 13 FOR i IN 1..temp3_table.COUNT LOOP 14 dbms_output.put_line('temp3_table:'||temp3_table(i)); 15 END LOOP; 16 END IF; 17 END; 18 /temp2_table:5temp2_table:6PL/SQL 过程已成功完成。--IS A SET：判断一个集合中是否有重复的值SQL> DECLARE 2 TYPE temp_table_type IS TABLE OF NUMBER; 3 temp1_table temp_table_type:=temp_table_type(5,5,6,7,8); 4 temp2_table temp_table_type:=temp_table_type(5,6,7,8); 5 BEGIN 6 IF temp1_table IS A SET THEN 7 FOR i IN 1..temp1_table.COUNT LOOP 8 dbms_output.put_line('temp1_table:'||temp1_table(i)); 9 END LOOP; 10 END IF; 11 IF temp2_table IS A SET THEN 12 FOR i IN 1..temp2_table.COUNT LOOP 13 dbms_output.put_line('temp2_table:'||temp2_table(i)); 14 END LOOP; 15 END IF; 16 END; 17 /temp2_table:5temp2_table:6temp2_table:7temp2_table:8PL/SQL 过程已成功完成。--(五),批量绑定--1.使用批量绑定与不使用批量绑定耗时DEMO对比SQL> CREATE TABLE demo 2 ( 3 id NUMBER(6) PRIMARY KEY, 4 name VARCHAR2(10) 5 );表已创建。SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 TYPE id_table_type IS TABLE OF NUMBER(6) 3 INDEX BY BINARY_INTEGER; 4 TYPE name_table_type IS TABLE OF VARCHAR2(10) 5 INDEX BY BINARY_INTEGER; 6 id_table id_table_type; 7 name_table name_table_type; 8 start_time NUMBER(10); 9 end_time NUMBER(10); 10 BEGIN 11 FOR i IN 1..50000 LOOP 12 id_table(i):=i; 13 name_table(i):='Name_'||to_char(i); 14 END LOOP; 15 start_time:=dbms_utility.get_time; 16 FOR i IN 1..id_table.COUNT LOOP 17 INSERT INTO demo VALUES(id_table(i),name_table(i)); 18 END LOOP; 19 end_time:=dbms_utility.get_time; 20 dbms_output.put_line('总时间(秒)：'||to_char((end_time-start_time)/100)); 21 END; 22 /总时间(秒)：3.72PL/SQL 过程已成功完成。SQL> DROP TABLE demo;表已删除。SQL> CREATE TABLE demo 2 ( 3 id NUMBER(6) PRIMARY KEY, 4 name VARCHAR2(10) 5 );表已创建。SQL> SET SERVEROUTPUT ON;SQL> DECLARE 2 TYPE id_table_type IS TABLE OF NUMBER(6) 3 INDEX BY BINARY_INTEGER; 4 TYPE name_table_type IS TABLE OF VARCHAR2(10) 5 INDEX BY BINARY_INTEGER; 6 id_table id_table_type; 7 name_table name_table_type; 8 start_time NUMBER(10); 9 end_time NUMBER(10); 10 BEGIN 11 FOR i IN 1..50000 LOOP 12 id_table(i):=i; 13 name_table(i):='Name_'||to_char(i); 14 END LOOP; 15 start_time:=dbms_utility.get_time; 16 FORALL i IN 1..id_table.COUNT 17 INSERT INTO demo VALUES(id_table(i),name_table(i)); 18 end_time:=dbms_utility.get_time; 19 dbms_output.put_line('总时间(秒)：'||to_char((end_time-start_time)/100)); 20 END; 21 /总时间(秒)：.33PL/SQL 过程已成功完成。--2.使用FORALL的几种用法--语法1FORALL index IN lower_bound..upper_bound sql_statement;--语法2FORALL index IN INDICES OF collection [BETWEEN lower_bound.AND.upper_bound] sql_statement;--语法3FORALL index IN VALUES OF index_collection sql_statement;--INSERT,UPDATE,DELETE中均可以使用批量绑定--FORALL i IN 8..10--FORALL i IN INDICES OF id_table:使用批量绑定跳过NULL集合元素--FORALL i IN VALUES OF id_table:使用批量绑定访问指定值得集合元素--sql%bulk_rowcount(2):返回第二个元素作用的行数--3.BULK COLLECT--用于取得批量数据,只能在SLECT INTO,FETCH INTO,DML返回子句--DEMO1SQL> DECLARE 2 TYPE id_table_type IS TABLE OF NUMBER(6) 3 INDEX BY BINARY_INTEGER; 4 TYPE name_table_type IS TABLE OF VARCHAR2(10) 5 INDEX BY BINARY_INTEGER; 6 id_table id_table_type; 7 name_table name_table_type; 8 BEGIN 9 SELECT * BULK COLLECT INTO id_table,name_table FROM demo; 10 FOR i IN 1..id_table.COUNT LOOP 11 IF i<10 THEN 12 dbms_output.put_line('id_table:'||id_table(i)); 13 dbms_output.put_line('name_table:'||name_table(i)); 14 END IF; 15 END LOOP; 16 END; 17 /id_table:819name_table:Name_819id_table:820name_table:Name_820id_table:821name_table:Name_821id_table:822name_table:Name_822id_table:823name_table:Name_823id_table:824name_table:Name_824id_table:825name_table:Name_825id_table:826name_table:Name_826id_table:827name_table:Name_827PL/SQL 过程已成功完成。--DEMO2SQL> DECLARE 2 TYPE ename_table_type IS TABLE OF emp.ename%TYPE; 3 ename_table ename_table_type; 4 BEGIN 5 DELETE FROM emp WHERE deptno=&no 6 RETURNING ename BULK COLLECT INTO ename_table; 7 dbms_output.put('雇员名：'); 8 FOR i IN 1..ename_table.COUNT LOOP 9 dbms_output.put(ename_table(i)||' '); 10 END LOOP; 11 dbms_output.new_line; 12 END; 13 /输入 no 的值: 10原值 5: DELETE FROM emp WHERE deptno=&no新值 5: DELETE FROM emp WHERE deptno=10雇员名：CLARK KING MILLERPL/SQL 过程已成功完成。