--一.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 过程已成功完成。
浙公网安备 33010602011771号