一、记录
为了检索单行多列数据的处理，应该使用记录。
如果检索所有列的数据，那么应该使用%ROWTYPE属性定义记录变量；
如果只需要检索部分列数据，那么应该使用自定义类型和记录变量。
14-1:使用记录，检索部分列
DECLARE
TYPE emp_record_type IS RECORD(
name emp.ename%TYPE,
salary emp.sal%TYPE,
title emp.job%TYPE);
emp_record emp_record_type;
BEGIN
SELECT ename,sal,job INTO emp_record
FROM emp WHERE empno=&no;
dbms_output.put_line('姓名：'||emp_record.name);
dbms_output.put_line('岗位：'||emp_record.title);
dbms_output.put_line('工资：'||emp_record.salary);
END;
/
14-2:使用记录，检索所有列
DECLARE
dept_record dept%ROWTYPE;
BEGIN
dept_record.deptno:=50;
dept_record.dname:='ADMINISTRATOR';
dept_record.loc:='BEIJING';
INSERT INTO dept VALUES dept_record;
END;
/
14-3:
DECLARE
dept_record dept%ROWTYPE;
BEGIN
dept_record.deptno:=60;
dept_record.dname:='SALES';
INSERT INTO dept (deptno,dname) VALUES
(dept_record.deptno,dept_record.dname);
END;
/
14-4:所有的列都会被更新
DECLARE
dept_record dept%ROWTYPE;
BEGIN
dept_record.deptno:=30;
dept_record.dname:='SALES';
dept_record.loc:='SHANGHAI';
UPDATE dept SET ROW=dept_record WHERE deptno=30;
END;
/
14-5:
DECLARE
dept_record dept%ROWTYPE;
BEGIN
dept_record.loc:='GUANGZHOU';
UPDATE dept SET loc=dept_record.loc WHERE deptno=10;
END;
/
14-6:
DECLARE
dept_record dept%ROWTYPE;
BEGIN
dept_record.deptno:=50;
DELETE FROM dept WHERE deptno=dept_record.deptno;
END;
/
二、集合-PL/SQL表
为了处理单行单列的数据，可以使用标量变量；
为了处理单行多列的数据，可以使用记录；
为了处理多行单列的数据，应该使用集合。
14-7:BINARY_INTEGER定义下标的数据类型
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
BEGIN
SELECT ename INTO ename_table(-1) FROM emp
WHERE empno=&no;
dbms_output.put_line('姓名:'||ename_table(-1));
END;
/
14-8:使用VARCHAR2定义下标
DECLARE
TYPE area_table_type IS TABLE OF NUMBER
INDEX BY VARCHAR2(10);
area_table area_table_type;
BEGIN
area_table('Japan'):=1;
area_table('China'):=2;
area_table('America'):=3;
area_table('England'):=4;
area_table('Portugal'):=4;
dbms_output.put_line('第一个元素:'||area_table.first);
dbms_output.put_line('最后一个元素:'||area_table.last);
END;
/
三、集合-嵌套表
嵌套表下标从1开始
14-9:下面初始化了长度为3的嵌套表
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
ename_table ename_table_type;
BEGIN
ename_table:=ename_table_type('MARY','MARY','MARY'); --构造方法初始化嵌套表
SELECT ename INTO ename_table(2) FROM emp
WHERE empno=&no;
dbms_output.put_line('雇员名:'||ename_table(2));
END;
/
14-10:PL/SQL表不能作为表列的数据类型使用，但嵌套表可以作为表列的数据类型使用
CREATE OR REPLACE TYPE phone_type IS TABLE OF VARCHAR2(20);
/
CREATE TABLE person(
id NUMBER(4),name VARCHAR2(10),sal NUMBER(6,2),
phone phone_type
)NESTED TABLE phone STORE AS phone_table;
14-11:在表列中使用嵌套表列
BEGIN
INSERT INTO person VALUES(1,'SCOTT',800,
phone_type('0471-3456788','13804711111'));
END;
/
14-12:检索嵌套表列的数据
DECLARE
phone_table phone_type;
BEGIN
SELECT phone INTO phone_table
FROM person WHERE name='SCOTT';
FOR i IN 1..phone_table.COUNT LOOP
dbms_output.put_line('号码'||i||':'||phone_table(i));
END LOOP;
END;
/
14-13:更新嵌套表列的数据
DECLARE
phone_table phone_type:=phone_type('0471-3456788',
'13804711111','0471-2233066','13056278568');
BEGIN
UPDATE person SET phone=phone_table
WHERE id=1;
END;
/
四、集合-变长数组
VARRAY也可以作为表列的数据类型使用
14-14:
DECLARE
TYPE job_array_type IS VARRAY(20) OF emp.job%TYPE;
job_array job_array_type:=job_array_type('CLERK', 'CLERK');
BEGIN
SELECT job INTO job_array(1) FROM emp
WHERE lower(ename)=lower('&name');
dbms_output.put_line('岗位:'||job_array(1));
END;
/
14-15:在表列中使用VARRAY
CREATE TYPE phone_array IS VARRAY(20) OF VARCHAR2(20);
/
CREATE TABLE worker (
id NUMBER(4),name VARCHAR2(10),sal NUMBER(6,2),phone phone_array);
五、集合-PL/SQL记录表
14-16:
DECLARE
TYPE emp_table_type IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
BEGIN
SELECT * INTO emp_table(1) FROM emp
WHERE empno=&no;
dbms_output.put_line('姓名:'||emp_table(1).ename);
dbms_output.put_line('工资:'||emp_table(1).sal);
dbms_output.put_line('岗位:'||emp_table(1).job);
dbms_output.put_line('上岗日期:'||emp_table(1).hiredate);
END;
/
六、集合的方法
14-17:EXISTS用于确定集合元素是否存在
DECLARE
TYPE id_table_type IS TABLE OF NUMBER(6);
id_table id_table_type:=id_table_type(1,2,5);
no INT;
BEGIN
no:=&i;
IF id_table.EXISTS(no) THEN
dbms_output.put_line('元素值：'||id_table(no));
ELSE
dbms_output.put_line('元素未初始化');
END IF;
END;
/
14-18:count返回集合变量的元素总个数
DECLARE
TYPE id_array_type IS VARRAY(20) OF NUMBER(6);
id_array id_array_type:=id_array_type(1,null,2,5);
BEGIN
dbms_output.put_line('ID_ARRAY的元素总数：'||id_array.count);
END;
/
14-19:limit返回VARRAY变量所允许的最大元素个数
DECLARE
TYPE id_array_type IS VARRAY(20) OF NUMBER(6);
id_array id_array_type:=id_array_type(1,null,2,5);
BEGIN
dbms_output.put_line('ID_ARRAY的元素总数：'||id_array.count);
dbms_output.put_line('ID_ARRAY的最大元素个数：'||id_array.limit);
END;
/
14-20:first和last返回集合第一个和最后一个元素的下标
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
BEGIN
ename_table(-5):='SCOTT';
ename_table(1):='SMITH';
ename_table(5):='MARY';
ename_table(10):='BLAKE';
dbms_output.put_line('第一个元素的值:'
||ename_table(ename_table.first));
dbms_output.put_line('最后一个元素的值:'
||ename_table(ename_table.last));
END;
/
14-21:prior前一个元素的下标；next后一个元素的下标
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
BEGIN
ename_table(-5):='SCOTT';
ename_table(1):='SMITH';
ename_table(5):='MARY';
ename_table(10):='BLAKE';
dbms_output.put_line('元素1的前一个元素值:'||
ename_table(ename_table.prior(1)));
dbms_output.put_line('元素1的后一个元素:'||
ename_table(ename_table.next(1)));
END;
/
14-22:EXTEND为集合增加元素，该方法只适用于嵌套表和VARRAY
EXTEND：用于为集合变量添加一个null元素。
EXTEND(n)：用于为集合变量添加n个null元素。
EXTEND(n,i)：用于为集合变量添加n个元素，元素值与第i个元素相同。
DECLARE
TYPE id_table_type IS TABLE OF NUMBER(6);
id_table id_table_type:=id_table_type(1);
BEGIN
id_table.EXTEND(&no);
dbms_output.put_line('第一个元素：'||id_table(id_table.first));
dbms_output.put_line('最后一个元素：'||nvl(to_char(id_table(id_table.last)),'null'));
dbms_output.put_line('元素总个数：'||id_table.count);
END;
/
14-23:TRIM从集合变量尾部删除元素
DECLARE
TYPE id_array_type IS VARRAY(30) OF CHAR;
id_array id_array_type:=id_array_type
('A','B','C','D','E','F','G','H','I','J','K','L');
BEGIN
id_array.TRIM(&no);
dbms_output.put_line('第一个元素：'||id_array(id_array.first));
dbms_output.put_line('最后一个元素：'||id_array(id_array.last));
dbms_output.put_line('元素总数：'||id_array.count);
END;
/
14-24:delete删除集合特定元素，该方法只适用于嵌套表和PL/SQL表
DELETE：用于删除集合变量的所有元素。
DELETE(n)：用于删除集合变量的第n个元素。
DELETE(m,n)：用于删除集合变量的中m到n间的所有元素。
DECLARE
TYPE id_table_type IS TABLE OF NUMBER(6)
INDEX BY BINARY_INTEGER;
id_table id_table_type;
BEGIN
FOR i IN 1..&no LOOP
id_table(i):=i;
END LOOP;
FOR j IN 1..id_table.count LOOP
IF mod(j,2)=0 THEN
id_table.delete(j);
END IF;
END LOOP;
dbms_output.put_line('元素总数:'||id_table.COUNT);
dbms_output.put_line('第一个元素:'||id_table(id_table.first));
dbms_output.put_line('最后一个元素:'||id_table(id_table.last));
END;
/
七、批量绑定
批量绑定用于在执行SQL操作时传递所有PL/SQL集合元素的数据；
使用批量绑定，可以极大的加快批量数据的处理速度，提高性能。
BULK COLLECT子句适用于select语句、fetch语句和DML返回子句；
FORALL语句只适用于DML语句。
14-25:传统循环插入数据，总计时间(秒):5.92
DECLARE
TYPE id_table_type IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER;
TYPE name_table_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
id_table id_table_type;
name_table name_table_type;
start_time NUMBER(10);
end_time NUMBER(10);
BEGIN
FOR i IN 1..100000 LOOP
id_table(i):=i;
name_table(i):='Name'||to_char(i);
END LOOP;
start_time:=dbms_utility.get_time;
FOR i IN 1..id_table.COUNT LOOP
INSERT INTO demo VALUES(id_table(i),name_table(i));
END LOOP;
end_time:=dbms_utility.get_time;
dbms_output.put_line('总计时间(秒):'||to_char((end_time-start_time)/100));
END;
/
14-26:使用批量绑定插入数据，总计时间(秒):0.1
DECLARE
TYPE id_table_type IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER;
TYPE name_table_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
id_table id_table_type;
name_table name_table_type;
start_time NUMBER(10);
end_time NUMBER(10);
BEGIN
FOR i IN 1..100000 LOOP
id_table(i):=i;
name_table(i):='Name'||to_char(i);
END LOOP;
start_time:=dbms_utility.get_time;
FORALL i IN 1..id_table.COUNT
INSERT INTO demo VALUES(id_table(i),name_table(i));
end_time:=dbms_utility.get_time;
dbms_output.put_line('总计时间(秒):'||to_char((end_time-start_time)/100));
END;
/
BULK COLLECT子句用于将批量数据存放到PL/SQL集合。
14-27:在SELECT INTO语句中使用BULK COLLECT子句
DECLARE
TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
BEGIN
SELECT * BULK COLLECT INTO emp_table FROM emp WHERE deptno=&no;
FOR i IN 1..emp_table.COUNT LOOP
dbms_output.put_line('姓名:'||emp_table(i).ename||
'，岗位:'||emp_table(i).job||
'，工资:'||emp_table(i).sal);
END LOOP;
END;
/
14-28:在DML返回子句中使用BULK COLLECT子句
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
TYPE sal_table_type IS TABLE OF emp.sal%TYPE;
ename_table ename_table_type;
sal_table sal_table_type;
BEGIN
UPDATE emp SET sal=sal*1.1 WHERE deptno=&no
RETURNING ename,sal BULK COLLECT INTO ename_table,sal_table;
FOR i IN 1..ename_table.COUNT LOOP
dbms_output.put_line('姓名:'||ename_table(i)||
'，新工资：'||sal_table(i));
END LOOP;
END;
/
FORALL语句适用于INSERT、UPDATE和DELETE操作
14-29:批量插入
DECLARE
TYPE id_table_type IS TABLE OF demo.id%TYPE INDEX BY BINARY_INTEGER;
TYPE name_table_type IS TABLE OF demo.name%TYPE INDEX BY BINARY_INTEGER;
id_table id_table_type;
name_table name_table_type;
BEGIN
FOR i IN 1..200000 LOOP
id_table(i):=i;
name_table(i):='Name'||to_char(i);
END LOOP;
FORALL i IN 1..id_table.COUNT
INSERT INTO demo VALUES(id_table(i),name_table(i));
END;
/
14-30:批量修改
DECLARE
TYPE id_table_type IS TABLE OF demo.id%TYPE INDEX BY BINARY_INTEGER;
TYPE name_table_type IS TABLE OF demo.name%TYPE INDEX BY BINARY_INTEGER;
id_table id_table_type;
name_table name_table_type;
BEGIN
FOR i IN 1..10000 LOOP
id_table(i):=i;
name_table(i):='N'||to_char(i);
END LOOP;
FORALL i IN 1..id_table.COUNT
UPDATE demo SET name=name_table(i) WHERE id=id_table(i);
END;
/
14-31:批量删除
DECLARE
TYPE id_table_type IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER;
id_table id_table_type;
BEGIN
FOR i IN 1..300 LOOP
id_table(i):=i;
END LOOP;
FORALL i IN 100..200
DELETE FROM demo WHERE id=id_table(i);
END;
/
14-32:sql%bulk_rowcount返回特定元素所作用和行数
DECLARE
TYPE deptno_table_type IS TABLE OF NUMBER(3);
deptno_table deptno_table_type:=deptno_table_type(10,20);
BEGIN
FORALL i IN 1..deptno_table.COUNT
UPDATE emp SET sal=sal*1.1 WHERE deptno=deptno_table(i);
dbms_output.put_line('部门10被更新了'||sql%bulk_rowcount(1)||'行');
dbms_output.put_line('部门20被更新了'||sql%bulk_rowcount(2)||'行');
END;
/
14-33:INDICES OF跳过集合变量中NULL元素
DECLARE
TYPE id_table_type IS TABLE OF NUMBER(6);
id_table id_table_type;
BEGIN
id_table:=id_table_type(100,null,300,null,500);
FORALL i IN INDICES OF id_table
DELETE FROM demo WHERE id=id_table(i);
FOR i IN 1..id_table.count LOOP
IF sql%bulk_rowcount(i)<>0 THEN
dbms_output.put_line('ID='||id_table(i)||'的行被删除');
END IF;
END LOOP;
END;
/
14-34:VALUES OF从其它集合变量中取得集合下标值
DECLARE
TYPE id_table_type IS TABLE OF demo.id%TYPE;
TYPE name_table_type IS TABLE OF demo.name%TYPE;
id_table id_table_type;
name_table name_table_type;
TYPE index_pointer_type IS TABLE OF PLS_INTEGER;
index_pointer index_pointer_type;
BEGIN
SELECT * BULK COLLECT INTO id_table,name_table FROM demo;
index_pointer:=index_pointer_type(6,8,10);
FORALL i IN VALUES OF index_pointer
INSERT INTO new_demo VALUES(id_table(i),name_table(i));
END;
/
八、集合的高级特性
14-35:集合赋值
DECLARE
TYPE id_array_type IS VARRAY(40) OF NUMBER(6);
id1_array id_array_type:=id_array_type(1,2,3,4,5,6);
id2_array id_array_type:=id_array_type(0);
BEGIN
id2_array:=id1_array;
dbms_output.put_line('ID2_ARRAY的元素总数：'||id2_array.count);
dbms_output.put_line('ID2_ARRAY的第一个元素：'||id2_array(id2_array.first));
dbms_output.put_line('ID2_ARRAY的最后一个元素：'||id2_array(id2_array.last));
END;
/
14-36:SET取消嵌套表变量的重复值
DECLARE
TYPE id_table_type IS TABLE OF NUMBER(6);
id1_table id_table_type:=id_table_type(1,2,1,4,1,6);
id2_table id_table_type:=id_table_type(0);
BEGIN
id2_table:=SET(id1_table);
dbms_output.put_line('ID2_table的元素总数：'||id2_table.count);
dbms_output.put_line('ID2_table的第一个元素：'||id2_table(id2_table.first));
dbms_output.put_line('ID2_table的最后一个元素：'||id2_table(id2_table.last));
END;
/
14-37:MULTISET UNION合并两个嵌套表变量的结果
DECLARE
TYPE nt_table_type IS TABLE OF NUMBER;
nt1 nt_table_type:=nt_table_type(1,2,3,4);
nt2 nt_table_type:=nt_table_type(3,4,5);
BEGIN
nt2:=nt1 MULTISET UNION nt2;
dbms_output.put('结果:');
FOR i IN 1..nt2.count LOOP
dbms_output.put(' '||nt2(i));
END LOOP;
dbms_output.new_line;
END;
/
14-38:MULTISET UNION DISTINCT合并两个嵌套表变量的结果并去重
DECLARE
TYPE nt_table_type IS TABLE OF NUMBER;
nt1 nt_table_type:=nt_table_type(1,2,3,4);
nt2 nt_table_type:=nt_table_type(3,4,5);
BEGIN
nt2:=nt1 MULTISET UNION DISTINCT nt2;
dbms_output.put('结果:');
FOR i IN 1..nt2.count LOOP
dbms_output.put(' '||nt2(i));
END LOOP;
dbms_output.new_line;
END;
/
14-39:MULTISET INTERSECT 求交集
DECLARE
TYPE nt_table_type IS TABLE OF NUMBER;
nt1 nt_table_type:=nt_table_type(1,2,3,4);
nt2 nt_table_type:=nt_table_type(3,4,5);
BEGIN
nt2:=nt1 MULTISET INTERSECT nt2;
dbms_output.put('结果:');
FOR i IN 1..nt2.count LOOP
dbms_output.put(' '||nt2(i));
END LOOP;
dbms_output.new_line;
END;
/
14-40:MULTISET EXCEPT 求差集
DECLARE
TYPE nt_table_type IS TABLE OF NUMBER;
nt1 nt_table_type:=nt_table_type(1,2,3,4);
nt2 nt_table_type:=nt_table_type(3,4,5);
BEGIN
nt2:=nt1 MULTISET EXCEPT nt2;
dbms_output.put('结果:');
FOR i IN 1..nt2.count LOOP
dbms_output.put(' '||nt2(i));
END LOOP;
dbms_output.new_line;
END;
/
14-41:检查集合变量是否为NULL
DECLARE
TYPE id_array_type IS VARRAY(30) OF NUMBER(4);
TYPE id_table_type IS TABLE OF NUMBER(4);
id_array id_array_type:=id_array_type(1);
id_table id_table_type;
BEGIN
IF id_array IS NULL THEN
dbms_output.put_line('ID_ARRAY未初始化');
ELSE
dbms_output.put_line('ID_ARRAY元素总数：'||id_array.COUNT);
END IF;
IF id_table IS NULL THEN
dbms_output.put_line('ID_TABLE未初始化');
ELSE
dbms_output.put_line('ID_TABLE元素总数：'||id_table.COUNT);
END IF;
END;
/
14-42:比较嵌套表
DECLARE
TYPE id_table_type IS TABLE OF NUMBER(4);
Id1_table id_table_type:= id_table_type(1);
Id2_table id_table_type:= id_table_type(1,2);
BEGIN
IF id1_table=id2_table THEN
dbms_output.put_line('ID1_TABLE=ID2_TABLE');
ELSE
dbms_output.put_line('ID1_TABLE!=ID2_TABLE');
END IF;
END;
/
14-43:cardinality返回嵌套表变量的元素个数
DECLARE
TYPE nt_table_type IS TABLE OF NUMBER;
nt1 nt_table_type:=nt_table_type(1,2,3,1);
BEGIN
dbms_output.put_line('元素总数:'||cardinality(nt1));
END;
/
14-44:SUBMULTISET OF确定一个嵌套表变量是否为另一个嵌套表变量数据的子集
DECLARE
TYPE nt_table_type IS TABLE OF NUMBER;
nt1 nt_table_type:=nt_table_type(1,2,3);
nt2 nt_table_type:=nt_table_type(1,2,3,4);
BEGIN
IF nt1 SUBMULTISET OF nt2 THEN
dbms_output.put_line('nt1是nt2的子集');
END IF;
END;
/
14-45:MEMBER OF检查特定数据是否为嵌套表变量的元素
DECLARE
TYPE nt_table_type IS TABLE OF NUMBER;
nt1 nt_table_type:=nt_table_type(1,2,3,5);
v1 NUMBER:=&no;
BEGIN
IF v1 MEMBER OF nt1 THEN
dbms_output.put_line(v1||'是nt1的元素');
ELSE
dbms_output.put_line(v1||'不是nt1的元素');
END IF;
END;
/
14-46:IS A SET检测嵌套表变量是否存在具有相同数值的元素
DECLARE
TYPE nt_table_type IS TABLE OF NUMBER;
nt1 nt_table_type:=nt_table_type(1,2,3,5,&no);
BEGIN
IF nt1 IS A SET THEN
dbms_output.put_line('嵌套表变量nt1无重复值');
ELSE
dbms_output.put_line('嵌套表变量nt1存在重复值');
END IF;
END;
/
九、集合嵌套
14-47:嵌套VARRAY
DECLARE
TYPE id_array_type IS VARRAY(10) OF INT;
TYPE id_table_type IS TABLE OF id_array_type;
id_table id_table_type:=id_table_type(
id_array_type(58,100,102),
id_array_type(55,6,73),
id_array_type(2,4));
BEGIN
FOR i IN 1..id_table.COUNT LOOP
FOR j IN 1..id_table(i).COUNT LOOP
dbms_output.put_line('id_table('||i||','||j||')='||id_table(i)(j));
END LOOP;
END LOOP;
END;
/
14-48:嵌套嵌套表
DECLARE
TYPE id_table_type IS TABLE OF INT;
TYPE id_array_type IS VARRAY(10) OF id_table_type;
id_array id_array_type:=id_array_type(
id_table_type(58,100,102),
id_table_type(55,6,73)
);
BEGIN
FOR i IN 1..id_array.COUNT LOOP
FOR j IN 1..id_array(i).COUNT LOOP
dbms_output.put_line('id_array('||i||','||j||')='||id_array(i)(j));
END LOOP;
END LOOP;
END;
/
14-49:嵌套PL/SQL表
DECLARE
TYPE id_table_type IS TABLE OF INT INDEX BY BINARY_INTEGER;
id1_table id_table_type;
id2_table id_table_type;
TYPE id_array_type IS VARRAY(10) OF id_table_type;
id_array id_array_type;
j INT;
BEGIN
id1_table(-8):=1; id1_table(-2):=2;
id1_table(3):=3; id1_table(8):=4;
id2_table(-100):=5; id2_table(-15):=6;
id2_table(15):=7; id2_table(34):=8;
id_array:=id_array_type(id1_table,id2_table);
FOR i IN 1..id_array.COUNT LOOP
j:=id_array(i).first;
LOOP
dbms_output.put_line('id_array('||i||','||j||')='||id_array(i)(j));
j:=id_array(i).NEXT(j);
EXIT WHEN j IS NULL;
END LOOP;
END LOOP;
END;
/