--一.PL/SQL复合数据类型 --(一).PL/SQL记录 --1.定义PL/SQL记录 --(1).定义PL/SQL记录 --Grammar TYPE type_name IS RECORD( field_declaration1[, field_declaration2]... ); idetifier type_name; --Demo1 DECLARE 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属性定义记录变量 --Grammer identifier table_name%ROWTYPE; identifier view_name%ROWTYPE; --Demo1 dept_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; SCOTT PL/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; SMITH PL/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,VARCHAR2 TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY key_type; identifier type_name; --Demo1 SQL> 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; 雇员名：SCOTT PL/SQL 过程已成功完成。 --Demo2 SQL> 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开始;元素个数没有限制;数组元素值可以稀疏;可以作为表列的数据类型使用 --Grammer TYPE type_name IS TABLE OF element_type; identifier type_name; --Demo1:SELECT INTO SQL> 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; 雇员名：SCOTT PL/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 电话号码：13922390110 PL/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开始;可以作为表列的类型使用;有限定义长度数组 --Grammer TYPE type_name IS VARRAY(size_limit) OF element_type [NOT NULL]; identifier type_name; --Demo1:PL/SQL块中使用VARRAY SQL> 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:表列中使用VARRAY SQL> 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 雇员工资：1200 PL/SQL 过程已成功完成。 --5.多级集合 --demo1:在PL/SQL块中使用多级VARRAY SQL> 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)=1 nvl(1,2)=2 nvl(1,3)=3 nvl(2,1)=4 nvl(2,2)=5 nvl(2,3)=6 nvl(3,1)=7 nvl(3,2)=8 nvl(3,3)=9 PL/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)=1 nvl(1,2)=2 nvl(1,3)=3 nvl(2,1)=4 nvl(2,2)=5 nvl(2,3)=6 nvl(3,1)=7 nvl(3,2)=8 nvl(3,3)=9 PL/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)=1 nvl(1,2)=2 nvl(1,3)=3 nvl(2,1)=4 nvl(2,2)=5 nvl(2,3)=6 nvl(3,1)=7 nvl(3,2)=8 nvl(3,3)=9 PL/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/FALSE SQL> 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 集合元素总个数：0 PL/SQL 过程已成功完成。 --3.LIMIT:返回集合元素的最大个数,嵌套表和索引表的元素没有个数限制,返回NULL 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'); 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集合元素的最大个数：20 ename_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 最后一个元素：10 PL/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的元素个数为：6 ename_table1的元素个数为：6 ename_table2的元素个数为：2 PL/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:5 ename_table-TRIM:4 ename_table-TRIM(2):2 -----ename_table1:4 ename_table1-DELETE(4):3 ename_table1-DELETE(0,10):1 -----ename_varray:4 ename_varray-TRIM:3 ename_varray-TRIM(2):1 PL/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:SCOTT name_varray1_ini:SMITH name_varray2_ini:SCOTT name_varray2_ini:SMITH PL/SQL 过程已成功完成。 --2.给集合赋NULL 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_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:SCOTT name_varray1_ini:SMITH name_varray1的现有元素个数为：0 PL/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:1 temp_table_values:2 temp_table_values:3 temp_table_values:4 temp_table_values:5 temp_table_values:6 PL/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:5 temp1_table_values:6 temp1_table_values:7 temp1_table_values:8 temp1_table_values:1 temp1_table_values:2 temp1_table_values:3 temp1_table_values:4 PL/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:5 temp1_table_values:7 temp1_table_values:8 temp1_table_values:1 temp1_table_values:2 temp1_table_values:3 PL/SQL 过程已成功完成。 --MULTISET INTERSECT 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 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:5 PL/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:6 temp1_table_values:7 temp1_table_values:8 PL/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:4 PL/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:5 PL/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:5 temp2_table:6 PL/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:5 temp2_table:6 temp2_table:7 temp2_table:8 PL/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.72 PL/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 / 总时间(秒)：.33 PL/SQL 过程已成功完成。 --2.使用FORALL的几种用法 --语法1 FORALL index IN lower_bound..upper_bound sql_statement; --语法2 FORALL index IN INDICES OF collection [BETWEEN lower_bound.AND.upper_bound] sql_statement; --语法3 FORALL 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返回子句 --DEMO1 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 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:819 name_table:Name_819 id_table:820 name_table:Name_820 id_table:821 name_table:Name_821 id_table:822 name_table:Name_822 id_table:823 name_table:Name_823 id_table:824 name_table:Name_824 id_table:825 name_table:Name_825 id_table:826 name_table:Name_826 id_table:827 name_table:Name_827 PL/SQL 过程已成功完成。 --DEMO2 SQL> 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 MILLER PL/SQL 过程已成功完成。
1674
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
