PL/SQL集合与记录(2)
十二、使用集合批量绑定减少循环开销
如下图所示，PL/SQL引擎会执行过程化语句，但它把SQL语句发送给SQL引擎处理，然后SQL引擎把处理的结果返回给PL/SQL引擎。
PL/SQL和SQL引擎间的频繁切换会大大降低效率。典型的情况就是在一个循环中反复执行SQL语句。例如，下面的DELETE语句就会在FOR循环中被多次发送到SQL引擎中去：
DECLARE
TYPE numlist IS VARRAY(20) OF NUMBER;
depts numlist := numlist(10, 30, 70); -- department numbers
BEGIN
...
FOR i IN depts.FIRST .. depts.LAST LOOP
DELETE FROM emp
WHERE deptno = depts(i);
END LOOP;
END;
这种情况下，如果SQL语句影响了四行或更多行时，使用批量绑定就会显著地提高性能。
1、批量绑定如何提高性能
用SQL语句中为PL/SQL变量赋值称为绑定，PL/SQL绑定操作可以分为三种：
- 内绑定(in-bind)：用INSERT或UPDATE语句将PL/SQL变量或主变量保存到数据库。
- 外绑定(out-bind)：通过INSERT、UPDATE或DELETE语句的RETURNING子句的返回值为PL/SQL变量或主变量赋值。
- 定义(define)：使用SELECT或FETCH语句为PL/SQL变量或主变量赋值。
DML语句可以一次性传递集合中所有的元素，这个过程就是批量绑定。如果集合有20个元素，批量绑定的一次操作就相当于执行20次SELECT、 INSERT、UPDATE或DELETE语句。这项技术是靠减少PL/SQL和SQL引擎间的切换次数来提高性能的。要对INSERT、UPDATE和 DELETE语句使用批量绑定，就要用PL/SQL的FORALL语句。
如果要在SELECT语句中使用批量绑定，我们就要在SELECT语句后面加上一个BULK COLLECT子句来代替INTO子句。
- 例一：对DELETE语句应用批量绑定
下面的DELETE语句只往SQL引擎中发送一次，即使是执行了三次DELETE操作：
DECLARE
TYPE numlist IS VARRAY(20) OF NUMBER;
depts numlist := numlist(10, 30, 70); -- department numbers
BEGIN
FORALL i IN depts.FIRST .. depts.LAST
DELETE FROM emp
WHERE deptno = depts(i);
END;
- 例二：对INSERT语句应用批量绑定
下例中，我们把5000个零件编号和名称放到索引表中。所有的表元素都向数据库插入两次：第一次使用FOR循环，然后使用FORALL语句。实际上，FORALL版本的代码执行速度要比FOR语句版本的快得多。
SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));
Table created.
SQL> GET test.sql
1 DECLARE
2 TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
3 TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
4 pnums NumTab;
5 pnames NameTab;
6 t1 NUMBER(5);
7 t2 NUMBER(5);
8 t3 NUMBER(5);
9
10
11 BEGIN
12 FOR j IN 1..5000 LOOP -- load index-by tables
13 pnums(j) := j;
14 pnames(j) := 'Part No. ' || TO_CHAR(j);
15 END LOOP;
16 t1 := dbms_utility.get_time;
17 FOR i IN 1..5000 LOOP -- use FOR loop
18 INSERT INTO parts VALUES (pnums(i), pnames(i));
19 END LOOP;
20 t2 := dbms_utility.get_time;
21 FORALL i IN 1..5000 -- use FORALL statement
22 INSERT INTO parts VALUES (pnums(i), pnames(i));
23 get_time(t3);
24 dbms_output.put_line('Execution Time (secs)');
25 dbms_output.put_line('---------------------');
26 dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1));
27 dbms_output.put_line('FORALL: ' || TO_CHAR(t3 - t2));
28* END;
SQL> /
Execution Time (secs)
---------------------
FOR loop: 32
FORALL: 3
十三、使用FORALL语句
关键字FORALL能让PL/SQL引擎在将集合发送到SQL引擎之前，批量导入集合元素。虽然FORALL也包含了迭代的模式，但它并不是简单的FOR循环。它的使用语法如下：
FORALL index IN lower_bound..upper_bound
sql_statement;
index只能在FORALL语句块内作为集合下标使用。SQL语句必须是引用了集合元素的INSERT、UPDATE或DELETE语句。bound的有效范围是连续的索引号。在这个范围内，SQL引擎为每个索引号执行一次SQL语句。
- 例一：使用FORALL操作集合的部分内容
如下例所示，FORALL循环的边界值可作用于集合的部分内容，不必是全部的元素：
DECLARE
TYPE numlist IS VARRAY(10) OF NUMBER;
depts numlist := numlist(20, 30, 50, 55, 57, 60, 70, 75, 90, 92);
BEGIN
FORALL j IN 4 .. 7 -- bulk-bind only part of varray
UPDATE emp
SET sal = sal * 1.10
WHERE deptno = depts(j);
END;
- 例二：使用集合下标索引的批量绑定
SQL语句能引用一个以上的集合。但是PL/SQL引擎的批量绑定只能为一个集合添加下标索引。所以，在下面的例子中，对于传递给函数median的集合sals，并没有使用到批量绑定。
FORALL i IN 1..20
INSERT INTO emp2 VALUES (enums(i), names(i), median(sals), ...);
- 例三：使用FORALL为对象表添加数据
除了关系表之外，FORALL语句还可以操作对象表，如下例所示：
CREATE TYPE pnum AS OBJECT(
n NUMBER
);
/
CREATE TABLE partno OF pnum;
DECLARE
TYPE numtab IS TABLE OF NUMBER;
nums numtab := numtab(1, 2, 3, 4);
TYPE pnumtab IS TABLE OF pnum;
pnums pnumtab := pnumtab(pnum(1), pnum(2), pnum(3), pnum(4));
BEGIN
FORALL i IN pnums.FIRST .. pnums.LAST
INSERT INTO partno
VALUES (pnums(i));
FORALL i IN nums.FIRST .. nums.LAST
DELETE FROM partno
WHERE n = 2 * nums(i);
FORALL i IN nums.FIRST .. nums.LAST
INSERT INTO partno
VALUES (100 + nums(i));
END;
1、FORALL语句对回滚的影响
在FORALL语句中，如果SQL语句引起了一个未捕获异常，以前对数据库的所有操作都会被回滚。但是，如果我们捕获到被抛出的异常并加以处理，此次之前的操作就不会被回滚。举一个例子，假设我们创建了数据表用来存储部门编号和职别：
CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(15));
下一步，为刚才建立的数据表添加一些记录：
INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(20, 'Bookkeeper'); -- 10-char job title
INSERT INTO emp2 VALUES(30, 'Analyst');
INSERT INTO emp2 VALUES(30, 'Analyst');
然后，我们用下面的UPDATE语句为特定的职称加上七位字符串' (temp)'：
DECLARE
TYPE numlist IS TABLE OF NUMBER;
depts numlist := numlist(10, 20, 30);
BEGIN
FORALL j IN depts.FIRST .. depts.LAST
UPDATE emp2
SET job = job || ' (temp)'
WHERE deptno = depts(j);
-- raises a "value too large" exception
EXCEPTION
WHEN OTHERS THEN
COMMIT;
END;
SQL引擎会执行UPDATE语句三次，第一次成功，但在第二次会因字符串值'Bookkeeper (temp)'太长而无法赋给job字段，所以就会执行失败。这种情况下，只有第二条语句回滚。
只要有SQL语句抛出异常，FORALL语句就会终止执行。在上面的例子中，第二个UPDATE语句抛出了异常，第三个语句就不会被执行了。
2、使用%BULK_ROWCOUNT属性来计算FORALL语句所影响到的行数
处理SQL数据操作语句时，SQL引擎会隐式地打开一个名为SQL的游标。这个游标的标量属性%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT，能够提供最近一次执行的SQL数据操作语句信息。
SQL游标还有一个专门为FORALL设计的复合属性%BULK_ROWCOUNT。这个属性有些像索引表。它的第i个元素保存了第i次的 INSERT或UPDATE或DELETE语句所影响到的行数。如果第i次操作没有行被影响，%BULK_ROWCOUNT(i)就返回零。下面来看一个例子：
DECLARE
TYPE numlist IS TABLE OF NUMBER;
depts numlist := numlist(10, 20, 50);
BEGIN
FORALL j IN depts.FIRST .. depts.LAST
UPDATE emp
SET sal = sal * 1.10
WHERE deptno = depts(j);
-- Did the 3rd UPDATE statement affect any rows?
IF SQL%BULK_ROWCOUNT(3) = 0 THEN ...
END;
FORALL语句和%BULK_ROWCOUNT属性使用同样的下标索引。如果FORALL使用下标索引的范围在5到10的话，那么% BULK_ROWCOUNT的也是。对于插入操作来说，%BULK_ROWCOUNT的结果一般是1，但是对于INSERT ... SELECT这样的结构来说，%BULK_ROWCOUNT的值就有可能大于1。例如，下面的FORALL语句在循环插入数据的过程中，每次插入的行的个数都是不固定的，%BULK_ROWCOUNT可以记录每次插入的行数：
SET SERVEROUTPUT ON;
DECLARE
TYPE num_tab IS TABLE OF NUMBER;
deptnums num_tab;
BEGIN
SELECT deptno
BULK COLLECT INTO deptnums
FROM dept;
FORALL i IN 1 .. deptnums.COUNT
INSERT INTO emp_by_dept
SELECT empno, deptno
FROM emp
WHERE deptno = deptnums(i);
FOR i IN 1 .. deptnums.COUNT LOOP
-- Count how many rows were inserted for each department; that is,
-- how many employees are in each department.
DBMS_OUTPUT.put_line( 'Dept '
|| deptnums(i)
|| ': inserted '
|| SQL%BULK_ROWCOUNT(i)
|| ' records');
END LOOP;
DBMS_OUTPUT.put_line('Total records inserted =' || SQL%ROWCOUNT);
END;
/
我们还可以在批量绑定中使用标量属性%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT。例如，%ROWCOUNT会返回所有的SQL语句处理的总行数。
%FOUND和%NOTFOUND只是针对最后一次SQL语句执行的结果。但是，我们可以利用%BULK_ROWCOUNT来推断出每个单独语句的处理结果。当%BULK_ROWCOUNT(i)为零时，%FOUND和%NOTFOUND就分别为FALSE和TRUE。
3、使用%BULK_EXCEPTIONS属性来控制FORALL异常
PL/SQL为FORALL语句提供了一个异常控制机制。这个机制能让使用批量绑定的操作保存异常信息并不中断地执行直至完成操作。
为了让批量绑定在错误发生时还能够继续执行，需要在FORALL语句中添加关键字SAVE EXCEPTIONS，语法如下：
FORALL index IN lower_bound..upper_bound SAVE EXCEPTIONS
{insert_stmt | update_stmt | delete_stmt}
执行时发生的所有的异常信息都会保存在新的游标属性%BULK_EXCEPTIONS中。%BULK_EXCEPTIONS是一个记录类型集合，每个记录有两个域，分别是ERROR_INDEX和ERROR_CODE，前者保存FORALL语句的"循环"索引，后者保存对应的Oracle错误编号。
存放在%BULK_EXCEPTIONS中的值总是与最近一次FORALL语句执行的结果相关，异常的个数存放在%BULK_EXCEPTIONS 的COUNT属性中，%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。
我们不使用关键字SAVE EXCEPTIONS时，如果异常发生，FORALL语句就会停止执行，SQL%BULK_EXCEPTIONS.COUNT的值就是一，SQL% BULK_EXCEPTIONS中只包含一条记录；如果没有异常发生，SQL%BULK_EXCEPTIONS.COUNT的值就是零。下面的例子演示了 %BULK_EXCEPTIONS的一些用法：
DECLARE
TYPE numlist IS TABLE OF NUMBER;
num_tab numlist := numlist(10, 0, 11, 12, 30, 0, 20, 199, 2, 0, 9, 1);
ERRORS NUMBER;
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
FORALL i IN num_tab.FIRST .. num_tab.LAST SAVE EXCEPTIONS
DELETE FROM emp
WHERE sal > 500000 / num_tab(i);
EXCEPTION
WHEN dml_errors THEN
ERRORS := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.put_line('Number of errors is ' || ERRORS);
FOR i IN 1 .. ERRORS LOOP
DBMS_OUTPUT.put_line( 'Error '
|| i
|| ' occurred during '
|| 'iteration '
|| SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
DBMS_OUTPUT.put_line( 'Oracle error is '
|| SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
例子中，PL/SQL在i等于2、6、10时会抛出预定义异常ZERO_DIVIDE。当批量绑定完成时，SQL% BULK_EXCEPTIONS.COUNT就会返回3，SQL%BULK_EXCEPTIONS的内容就是(2,1476)，(6,1476)和 (10,1476)。如果想得到错误消息，我们可以把SQL%BULK_EXCEPTIONS(i).ERROR_CODE传递给错误报告函数 SQLERRM，这样就能得到下面的输出结果：
Number of errors is 3
Error 1 occurred during iteration 2
Oracle error is ORA-01476: divisor is equal to zero
Error 2 occurred during iteration 6
Oracle error is ORA-01476: divisor is equal to zero
Error 3 occurred during iteration 10
Oracle error is ORA-01476: divisor is equal to zero
十四、利用BULK COLLECTION子句为集合赋值
关键字BULK COLLECT会通知SQL引擎在将数据返回给PL/SQL引擎之前，把输出的数据批量地绑定到一个集合。我们可以在SELECT INTO、FETCH INTO和RETURNING INTO子句中使用BULK COLLECT。语法如下：
... BULK COLLECT INTO collection_name[, collection_name] ...
SQL引擎能批量绑定出现在INTO列表后的所有集合。对应的字段可以保存为标量类型或复合类型的值，其中也包括对象类型。在下面的例子中，SQL引擎在把数据返回给PL/SQL引擎之前，它将完整的empno和ename绑定到嵌套表中：
DECLARE
TYPE numtab IS TABLE OF emp.empno%TYPE;
TYPE nametab IS TABLE OF emp.ename%TYPE;
enums numtab; -- no need to initialize
names nametab;
BEGIN
SELECT empno, ename
BULK COLLECT INTO enums, names
FROM emp;
...
END;
接下来的例子中，SQL引擎会批量地把对象字段的值放到嵌套表中：
CREATE TYPE coords AS OBJECT(
x NUMBER,
y NUMBER
);
CREATE TABLE grid (num NUMBER, loc coords);
INSERT INTO grid
VALUES (10, coords(1, 2));
INSERT INTO grid
VALUES (20, coords(3, 4));
DECLARE
TYPE coordstab IS TABLE OF coords;
pairs coordstab;
BEGIN
SELECT loc
BULK COLLECT INTO pairs
FROM grid;
-- now pairs contains (1,2) and (3,4)
END;
SQL引擎会为我们初始化和扩展集合(但是，它不能把变长数组的长度扩大到超过变长数组的最大长度值)。然后从索引1开始，连续地插入元素并覆盖先前已存在的元素。
SQL引擎能批量绑定整个字段。所以，如果一个数据表中有50000行记录，引擎就会一次性加载50000个值到目标集合中去。但是，我们可以使用伪列ROWNUM来限制要处理的行记录个数。下例中，我们把每次处理的记录个数限制为100：
DECLARE
TYPE sallist IS TABLE OF emp.sal%TYPE;
sals sallist;
BEGIN
SELECT sal
BULK COLLECT INTO sals
FROM emp
WHERE ROWNUM <= 100;
...
END;
1、从游标中批量取得数据的例子
- 插入一个或多个集合
我们可以从游标中批量取得数据并绑定到一个或多个集合中去：
DECLARE
TYPE namelist IS TABLE OF emp.ename%TYPE;
TYPE sallist IS TABLE OF emp.sal%TYPE;
CURSOR c1 IS
SELECT ename, sal
FROM emp
WHERE sal > 1000;
names namelist;
sals sallist;
BEGIN
OPEN c1;
FETCH c1
BULK COLLECT INTO names, sals;
END;
- 绑定记录类型的集合
我们可以批量取得数据并绑定到记录类型的集合中去：
DECLARE
TYPE deptrectab IS TABLE OF dept%ROWTYPE;
dept_recs deptrectab;
CURSOR c1 IS
SELECT deptno, dname, loc
FROM dept
WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1
BULK COLLECT INTO dept_recs;
END;
2、使用LIMIT子句限制FETCH操作批量取得的数据个数
可选的LIMIT子句只允许出现在FETCH操作语句的批量中，它能够帮助我们限制批量取得的数据数量，语法如下：
FETCH ... BULK COLLECT INTO ... [LIMIT rows];
其中rows可以是文字，变量或表达式，但它的计算结果必须是一个数字。否则的话，PL/SQL就会抛出预定义异常VALUE_ERROR。如果 rows是非正数，PL/SQL会抛出INVALID_NUMBER异常。在必要的时候，PL/SQL还会将数字四舍五入到rows最接近的整数。
在下面的例子中，每次FETCH操作都会取出10条记录放到索引表empno中去，之前的数据内容会被当前的数据所覆盖：
DECLARE
TYPE numtab IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
CURSOR c1 IS
SELECT empno
FROM emp;
empnos numtab;
ROWS NATURAL := 10;
BEGIN
OPEN c1;
LOOP
/* The following statement fetches 10 rows (or less). */
FETCH c1
BULK COLLECT INTO empnos LIMIT ROWS;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
CLOSE c1;
END;
3、使用RETURNING INTO子句将DML的操作结果绑定到集合
我们还可以在INSERT、UPDATE或DELETE语句的RETURNING INTO子句中使用BULK COLLECT来进行数据绑定，示例如下：
DECLARE
TYPE numlist IS TABLE OF emp.empno%TYPE;
enums numlist;
BEGIN
DELETE FROM emp
WHERE deptno = 20
RETURNING empno
BULK COLLECT INTO enums;
-- if there were five employees in department 20,
-- then enums contains five employee numbers
END;
4、BULK COLLECT的限制
下面是使用BULK COLLECT的一些限制：
- 不能对使用字符串类型作键的关联数组使用BULK COLLECT子句。
- 只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一个不支持这个特性的错误。
- BULK COLLECT INTO的目标对象必须是集合类型，如下例所示：
DECLARE
TYPE namelist IS TABLE OF emp.ename%TYPE;
names namelist;
salary emp.sal%TYPE;
BEGIN
SELECT ename, sal
BULK COLLECT INTO names, salary -- illegal target
FROM emp
WHERE ROWNUM < 50;
...
END; - 复合目标(如对象类型)不能在RETURNING INTO子句中使用。
- 如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK COLLECT INTO子句中使用。
- 如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于BULK COLLECT INTO子句中。
5、把FORALL和BULK COLLECTION结合起来使用
我们可以把BULK COLLECT和FORALL语句结合起来使用，这时，SQL引擎会批量绑定字段值。下例中，如果集合depts有三个元素，每个元素都能执行五次DELETE操作，当语句执行完毕的时候，enums中就会有十五个元素：
FORALL j IN depts.FIRST .. depts.LAST
DELETE FROM emp
WHERE empno = depts(j)
RETURNING empno
BULK COLLECT INTO enums;
我们不能在FORALL语句中使用SELECT ... BULK COLLECT语句。否则，就会得到一条错误消息：不能在SELECT语句中同时使用FORALL和BULK COLLECT INTO(cannot use FORALL and BULK COLLECT INTO together in SELECT statements)。
6、使用主数组进行批量绑定
客户端程序可以使用匿名PL/SQL块来把数据批量地从主数组中输入或批量地输出到主数组。实际上，这是与服务器端交互传递集合的最高效的方法。
主数组是声明在主环境中的，如OCI或Pro*C程序，并且必须以冒号为前缀，以区别于PL/SQL集合。在下面的例子中，DELETE语句中使用到一个输入主数组。运行时，匿名PL/SQL块被发送到数据库服务器端执行：
DECLARE
...
BEGIN
-- assume that values were assigned to the host array
-- and host variables in the host environment
FORALL i IN :lower..:upper
DELETE FROM emp
WHERE deptno = :depts(i);
...
END;
十五、什么是记录
记录就是相关的数据项集中存储在一个单元中，每项都有它自己的名字和数据类型。假定我们有关于雇员的各种数据信息，如名字、薪水和雇佣日期，这些项在逻辑上是相关联的，但类型不相似。记录可以把它所拥有的每一项当作一个逻辑单元，这样就便于组织和表现信息。
%ROWTYPE属性能让我们声明代表数据表中一行记录的类型。但是我们不能利用它指定或声明自己的数据类型。不过没关系，RECORD关键字可以满足我们定义自己的记录的要求。
十六、定义和声明记录
要创建记录，我们就得先声明记录类型，然后声明该类型的记录。我们可以在PL/SQL块、子程序或包的声明部分使用下面的语法来定义RECORD类型：
TYPE type_name IS RECORD (field_declaration[,field_declaration]...);
其中field_declaration的形式如下：
field_name field_type [[NOT NULL] {:= | DEFAULT} expression]
type_name是声明记录用的类型区分符，field_type是除了REF CURSOR以外的任何PL/SQL数据类型，expression的结果值与field_type相同。
注意：与VARRAY类型和TABLE(嵌套)类型不同的是，RECORD是不能存在于数据库中的。
创建记录时也可以使用%TYPE和%ROWTYPE来指定记录各个域的类型。下例中，我们定义了一个名为DeptRec的记录类型：
DECLARE
TYPE deptrec IS RECORD(
dept_id dept.deptno%TYPE,
dept_name VARCHAR2(14),
dept_loc VARCHAR2(13)
);
BEGIN
...
END;
在下面的例子中，我们在记录类型中包含对象、集合和其他的记录(又叫嵌套记录)。但是对象类型中不能把RECORD类型作为它的属性。
DECLARE
TYPE timerec IS RECORD(
seconds SMALLINT,
minutes SMALLINT,
hours SMALLINT
);
TYPE flightrec IS RECORD(
flight_no INTEGER,
plane_id VARCHAR2(10),
captain employee, -- declare object
passengers passengerlist, -- declare varray
depart_time timerec, -- declare nested record
airport_code VARCHAR2(10)
);
BEGIN
...
END;
下面的例子演示了如何将函数的返回类型指定为RECORD类型：
DECLARE
TYPE emprec IS RECORD(
emp_id NUMBER(4),
last_name VARCHAR2(10),
dept_num NUMBER(2),
job_title VARCHAR2(9),
salary NUMBER(7, 2)
);
...
FUNCTION nth_highest_salary(n INTEGER)
RETURN emprec IS ...
BEGIN
...
END;
1、声明记录
一旦定义了RECORD类型，我们就可以声明该类型的记录。如下例所示，标识符item_info代表了整条记录：
DECLARE
TYPE stockitem IS RECORD(
item_no INTEGER(3),
description VARCHAR2(50),
quantity INTEGER,
price REAL(7, 2)
);
item_info stockitem; -- declare record
BEGIN
...
END;
同标量类型的变量一样，用户定义的记录也可以作为函数或过程的形式参数来使用：
DECLARE
TYPE emprec IS RECORD(
emp_id emp.empno%TYPE,
last_name VARCHAR2(10),
job_title VARCHAR2(9),
salary NUMBER(7, 2)
);
...
PROCEDURE raise_salary(emp_info emprec);
BEGIN
...
END;
2、初始化记录
下面的例子演示了如何在定义记录的时候，同时进行初始化操作。当我们声明TimeRec类型的记录时，它的三个域都被初始化为零：
DECLARE
TYPE timerec IS RECORD(
secs SMALLINT := 0,
mins SMALLINT := 0,
hrs SMALLINT := 0
);
BEGIN
...
END;
我们可以为记录添加NOT NULL约束，对于有NOT NULL约束的字段，声明时必须进行初始化：
DECLARE
TYPE stockitem IS RECORD(
item_no INTEGER(3) NOT NULL := 999,
description VARCHAR2(50),
quantity INTEGER,
price REAL(7, 2)
);
BEGIN
...
END;
3、引用记录
同集合中的元素不同，它们的引用方式是使用下标索引，而记录对于它的域的引用要使用名称。语法如下：
record_name.field_name
例如，我们想访问记录emp_info下的hire_date域，那么就要使用：
emp_info.hire_date ...
在调用一个返回用户定义的记录类型的函数时，要使用下面的语法：
function_name(parameter_list).field_name
例如，下例对函数nth_highest_sal的调用就引用到记录类型emp_info的salary域：
DECLARE
TYPE emprec IS RECORD(
emp_id NUMBER(4),
job_title VARCHAR2(9),
salary NUMBER(7, 2)
);
middle_sal NUMBER(7, 2);
FUNCTION nth_highest_sal(n INTEGER)
RETURN emprec IS
emp_info emprec;
BEGIN
...
RETURN emp_info; -- return record
END;
BEGIN
middle_sal := nth_highest_sal(10).salary; -- call function
...
END;
对于一个无参数的返回类型为记录的函数来说，要使用下面的语法引用记录中的字段：
function_name().field_name -- note empty parameter list
而对于返回类型是一个包含嵌套域的记录的函数来说，引用字段的语法如下：
function_name(parameter_list).field_name.nested_field_name
下面看一个记录包含记录的例子：
DECLARE
TYPE timerec IS RECORD(
minutes SMALLINT,
hours SMALLINT
);
TYPE agendaitem IS RECORD(
priority INTEGER,
subject VARCHAR2(100),
DURATION timerec
);
FUNCTION item(n INTEGER)
RETURN agendaitem IS
item_info agendaitem;
BEGIN
...
RETURN item_info; -- return record
END;
BEGIN
NULL;
IF item(3).duration.minutes > 30 THEN ... -- call function
END;
同样，对于包含在记录中的对象的引用方法也类似：
DECLARE
TYPE flightrec IS RECORD(
flight_no INTEGER,
plane_id VARCHAR2(10),
captain employee, -- declare object
passengers passengerlist, -- declare varray
depart_time timerec, -- declare nested record
airport_code VARCHAR2(10)
);
flight flightrec;
BEGIN
...
IF flight.captain.name = 'H Rawlins' THEN ...
END;
4、为记录赋控值
要把记录中的所有字段都设置成空值，只需用一个未初始化的同类型记录为它赋值即可，例如：
DECLARE
TYPE emprec IS RECORD(
emp_id emp.empno%TYPE,
job_title VARCHAR2(9),
salary NUMBER(7, 2)
);
emp_info emprec;
emp_null emprec;
BEGIN
emp_info.emp_id := 7788;
emp_info.job_title := 'ANALYST';
emp_info.salary := 3500;
emp_info := emp_null; -- nulls all fields in emp_info
...
END;
5、为记录赋值
我们可以把表达式的值赋给记录中特定的域，语法如下：
record_name.field_name := expression;
下例中，我们把雇员的名字转成大写形式：
emp_info.ename := UPPER(emp_info.ename);
除了每个域单独赋值之外，我们还可以一次性为整个记录进行赋值。一次性赋值有两种方法，第一个方法是把同类型的一个记录赋值给另外一个记录：
DECLARE
TYPE deptrec IS RECORD(
dept_num NUMBER(2),
dept_name VARCHAR2(14)
);
TYPE deptitem IS RECORD(
dept_num NUMBER(2),
dept_name VARCHAR2(14)
);
dept1_info deptrec;
dept2_info deptitem;
BEGIN
...
dept1_info := dept2_info; -- illegal; different datatypes
END;
下面再看一个例子，第一个是自定义记录，第二个是使用%ROWTYPE获取的记录，由于这两个记录中的字段数量和顺序相匹配，而且类型兼容，所以可以用其中的一个为另一个赋值：
DECLARE
TYPE deptrec IS RECORD(
dept_num NUMBER(2),
dept_name VARCHAR2(14),
LOCATION VARCHAR2(13)
);
dept1_info deptrec;
dept2_info dept%ROWTYPE;
BEGIN
SELECT *
INTO dept2_info
FROM dept
WHERE deptno = 10;
dept1_info := dept2_info;
...
END;
一次性赋值的第二个方法就是使用SELECT或FETCH语句把对应的字段值放入记录中去：
DECLARE
TYPE deptrec IS RECORD(
dept_num NUMBER(2),
dept_name VARCHAR2(14),
LOCATION VARCHAR2(13)
);
dept_info deptrec;
BEGIN
SELECT *
INTO dept_info
FROM dept
WHERE deptno = 20;
...
END;
但像下面这样的赋值方法是不允许的:
record_name := (value1, value2, value3, ...); -- not allowed
下面的例子演示了如何把一个嵌套记录赋给另一个，这里要保证的是被嵌套的记录类型是相同的。这样的赋值方法是允许的，即使封闭记录有着不同的数据类型：
DECLARE
TYPE timerec IS RECORD(
mins SMALLINT,
hrs SMALLINT
);
TYPE meetingrec IS RECORD(
DAY DATE,
time_of timerec, -- nested record
room_no INTEGER(4)
);
TYPE partyrec IS RECORD(
DAY DATE,
time_of timerec, -- nested record
place VARCHAR2(25)
);
seminar meetingrec;
party partyrec;
BEGIN
...
party.time_of := seminar.time_of;
END;
6、比较记录
记录不能用于空值、等值或不等的比较。例如，下面IF的条件表达式是不允许的：
BEGIN
...
IF emp_info IS NULL THEN ... -- illegal
IF dept2_info > dept1_info THEN ... -- illegal
END;
十七、操作记录
RECORD类型能让我们把事物的属性信息收集起来。这些信息很容易操作，因为我们在集合中把它们当作一个整体来处理。如下例中，我们可以从数据表asserts和liabilities中收集accounting数，然后用比率分析来比较两个子公司的生产效率：
DECLARE
TYPE FiguresRec IS RECORD (cash REAL, notes REAL, ...);
sub1_figs FiguresRec;
sub2_figs FiguresRec;
FUNCTION acid_test (figs FiguresRec) RETURN REAL IS ...
BEGIN
SELECT cash, notes, ...
INTO sub1_figs
FROM assets, liabilities
WHERE assets.sub = 1
AND liabilities.sub = 1;
SELECT cash, notes, ...
INTO sub2_figs
FROM assets, liabilities
WHERE assets.sub = 2
AND liabilities.sub = 2;
IF acid_test(sub1_figs) > acid_test(sub2_figs) THEN ...
...
END;
注意，向函数acid_test传递收集到的数字是一件很容易的事情，函数能够计算出一个财务比率。
假设我们在SQL*Plus中定义了对象类型Passenger：
SQL> CREATE TYPE Passenger AS OBJECT(
2 flight_no NUMBER(3),
3 name VARCHAR2(20),
4 seat CHAR(5));
下一步定义VARRAY类型PassengerList，用来存放Passenger对象：
SQL> CREATE TYPE PassengerList AS VARRAY(300) OF Passenger;
最后创建关系表flights，其中的一个字段的类型为PassengerList：
SQL> CREATE TABLE flights (
2 flight_no NUMBER(3),
3 gate CHAR(5),
4 departure CHAR(15),
5 arrival CHAR(15),
6 passengers PassengerList);
在字段passengers中的每一项都是一个储存给定航班的旅客名单的变长数组。现在，我们为数据表flights添加一些数据：
BEGIN
INSERT INTO flights
VALUES (109, '80', 'DFW 6:35PM', 'HOU 7:40PM',
passengerlist(passenger(109, 'Paula Trusdale', '13C'),
passenger(109, 'Louis Jemenez', '22F'),
passenger(109, 'Joseph Braun', '11B'), ...));
INSERT INTO flights
VALUES (114, '12B', 'SFO 9:45AM', 'LAX 12:10PM',
passengerlist(passenger(114, 'Earl Benton', '23A'),
passenger(114, 'Alma Breckenridge', '10E'),
passenger(114, 'Mary Rizutto', '11C'), ...));
INSERT INTO flights
VALUES (27, '34', 'JFK 7:05AM', 'MIA 9:55AM',
passengerlist(passenger(27, 'Raymond Kiley', '34D'),
passenger(27, 'Beth Steinberg', '3A'),
passenger(27, 'Jean Lafevre', '19C'), ...));
END;
下例中，我们从数据表flights中取出数据放到记录flight_into中去。那样，我们就可以把一个航班的所有的信息，包括它的旅客名单，作为一个逻辑单元来处理。
DECLARE
TYPE flightrec IS RECORD(
flight_no NUMBER(3),
gate CHAR(5),
departure CHAR(15),
arrival CHAR(15),
passengers passengerlist
);
flight_info flightrec;
CURSOR c1 IS
SELECT *
FROM flights;
seat_not_available EXCEPTION;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO flight_info;
EXIT WHEN c1%NOTFOUND;
FOR i IN 1 .. flight_info.passengers.LAST LOOP
IF flight_info.passengers(i).seat = 'na' THEN
DBMS_OUTPUT.put_line(flight_info.passengers(i).NAME);
RAISE seat_not_available;
END IF;
...
END LOOP;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN seat_not_available THEN
...
END;
1、向数据库插入PL/SQL记录
PL/SQL对INSERT语句的唯一的扩展就是能让我们使用一个独立RECORD类型或是%ROWTYPE类型变量，来代替域列表来插入一条数据。这样才可以让我们的代码更具可读性，更容易维护。
记录中域的个数必须和INTO子句后面列出的字段个数相等，对应的域和字段的类型必须兼容。这样可以保证记录与数据表兼容。
- 利用%ROWTYPE插入PL/SQL记录
这个例子用%ROWTYPE声明了一个记录类型变量。我们可以使用这个变量直接插入数据而不用指定字段列表。%ROWTYPE声明能保证记录属性的名称和类型与数据表字段完全一致。
DECLARE
dept_info dept%ROWTYPE;
BEGIN
-- deptno, dname, and loc are the table columns.
-- The record picks up these names from the %ROWTYPE.
dept_info.deptno := 70;
dept_info.dname := 'PERSONNEL';
dept_info.loc := 'DALLAS';
-- Using the %ROWTYPE means we can leave out the column list
-- (deptno, dname, loc) from the INSERT statement.
INSERT INTO dept
VALUES dept_info;
END;
2、使用记录更新数据库
PL/SQL对UPDATE语句的唯一的扩展就是能让我们使用一个独立RECORD类型或是%ROWTYPE类型变量，来代替域列表更新一条数据。
记录中域的个数必须和SET子句后面列出的字段个数相等，对应的域和字段的类型也必须兼容。
- 用记录更新行记录
我们可以使用关键字ROW代表完整的一行数据：
/* Formatted on 2006/08/30 20:27 (Formatter Plus v4.8.7) */
DECLARE
dept_info dept%ROWTYPE;
BEGIN
dept_info.deptno := 30;
dept_info.dname := 'MARKETING';
dept_info.loc := 'ATLANTA';
-- The row will have values for the filled-in columns, and null
-- for any other columns.
UPDATE dept
SET ROW = dept_info
WHERE deptno = 30;
END;
关键字ROW只允许出现在SET子句的左边。
- 不能在子查询中使用SET ROW
我们不能在子查询中使用ROW。例如，下面的UPDATE语句是不允许的：
UPDATE emp SET ROW = (SELECT * FROM mgrs); -- not allowed
- 使用包含对象的记录更新行数据
包含对象类型的记录是可以使用的：
CREATE TYPE worker AS OBJECT(
NAME VARCHAR2(25),
dept VARCHAR2(15)
);
/
CREATE TABLE teams (team_no NUMBER, team_member worker);
DECLARE
team_rec teams%ROWTYPE;
BEGIN
team_rec.team_no := 5;
team_rec.team_member := worker('Paul Ocker', 'Accounting');
UPDATE teams
SET ROW = team_rec;
END;
/
- 使用包含集合的记录更新行数据
记录可以包含集合：
CREATE TYPE worker AS OBJECT(
NAME VARCHAR2(25),
dept VARCHAR2(15)
);
/
CREATE TYPE roster AS TABLE OF worker;
/
CREATE TABLE teams (team_no NUMBER, members roster)
NESTED TABLE members STORE AS teams_store;
INSERT INTO teams
VALUES (1,
roster(worker('Paul Ocker', 'Accounting'),
worker('Gail Chan', 'Sales'),
worker('Marie Bello', 'Operations'),
worker('Alan Conwright', 'Research')));
DECLARE
team_rec teams%ROWTYPE;
BEGIN
team_rec.team_no := 3;
team_rec.members := roster(worker('William Bliss', 'Sales'),
worker('Ana Lopez', 'Sales'),
worker('Bridget Towner', 'Operations'),
worker('Ajay Singh', 'Accounting'));
UPDATE teams
SET ROW = team_rec;
END;
/
- 使用RETURNING子句
INSERT，UPDATE和DELETE语句都可以包含RETURNING子句，返回的字段值来自于被影响到的行，它们被放到PL/SQL记录变量中。这就可以省掉在插入、更新操作之后或删除操作之前执行SELECT查找被影响到的数据。我们只能在对一行数据进行操作时使用这个子句。
下面的例子中，我们更新一个雇员的工资，同时，检索雇员的姓名、职别和把新的工资值放进记录变量：
DECLARE
TYPE emprec IS RECORD(
emp_name VARCHAR2(10),
job_title VARCHAR2(9),
salary NUMBER(7, 2)
);
emp_info emprec;
emp_id NUMBER(4);
BEGIN
emp_id := 7782;
UPDATE emp
SET sal = sal * 1.1
WHERE empno = emp_id
RETURNING ename,
job,
sal
INTO emp_info;
END;
3、记录类型插入/更新操作的约束
- 记录类型变量只在下面几种情况下才允许使用：
- 在UPDATE语句中SET子句的右边
- 在INSERT语句中VALUES子句的后面
- 在RETURNING语句中INTO子句的后面
- 关键字ROW只允许在SET子句的左面出现，并且不能和子查询连用。
- UPDATE语句中，如果使用了ROW关键字，那么SET就只能使用一次。
- 如果一个INSERT语句的VALUES子句中包含了记录变量，那么就不允许出现其他变量或值。
- 如果RETURNING语句的INTO子句中包含了记录变量，那么就不允许出现其他变量或值。
- 下面三种情况是不能使用记录的：
- 含有记录嵌套。
- 函数返回记录类型。
- 记录的插入/更新是用EXECUTE IMMEDIATE语句完成的。
4、用查询结果为记录类型的集合赋值
PL/SQL的绑定操作可以分为三类：
- 定义：使用SELECT或FETCH语句为PL/SQL变量或主变量赋值。
- 内绑定：用INSERT语句插入的或UPDATE语句更新的数据库值。
- 外绑定：用INSERT、UPDATE或DELETE语句的RETURNING子句把值返回到PL/SQL变量或主变量中。
PL/SQL支持使用DML语句对记录类型的集合进行批量绑定。一个"定义"或"外绑定"变量可以是记录类型的集合，"内绑定"值可以保存到记录类型的集合中的。语法如下：
SELECT select_items BULK COLLECT
INTO record_variable_name
FROM rest_of_select_stmt
FETCH { cursor_name
| cursor_variable_name
| :host_cursor_variable_name}
BULK COLLECT INTO record_variable_name
[LIMIT numeric_expression];
FORALL index IN lower_bound..upper_bound
INSERT INTO { table_reference
| THE_subquery} [{column_name[, column_name]...}]
VALUES (record_variable_name(index)) rest_of_insert_stmt
FORALL index IN lower_bound..upper_bound
UPDATE {table_reference | THE_subquery} [alias]
SET (column_name[, column_name]...) = record_variable_name(index)
rest_of_update_stmt
RETURNING row_expression[, row_expression]...
BULK COLLECT INTO record_variable_name;
上面每个语句和子句中，记录变量存储一个记录类型的集合。记录中的域个数必须和SELECT、INSERT INTO、UPDATE ... SET或RETURNING相对应的列的个数相同。并且相对应的域和字段必须类型兼容。下面是几个例子：
CREATE TABLE tab1 (col1 NUMBER, col2 VARCHAR2(20));/
CREATE TABLE tab2 (col1 NUMBER, col2 VARCHAR2(20));
/
DECLARE
TYPE rectabtyp IS TABLE OF tab1%ROWTYPE
INDEX BY BINARY_INTEGER;
TYPE numtabtyp IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
TYPE chartabtyp IS TABLE OF VARCHAR2(20)
INDEX BY BINARY_INTEGER;
CURSOR c1 IS
SELECT col1, col2
FROM tab2;
rec_tab rectabtyp;
num_tab numtabtyp := numtabtyp(2, 5, 8, 9);
char_tab chartabtyp := chartabtyp('Tim', 'Jon', 'Beth', 'Jenny');
BEGIN
FORALL i IN 1 .. 4
INSERT INTO tab1
VALUES (num_tab(i), char_tab(i));
SELECT col1,
col2
BULK COLLECT INTO rec_tab
FROM tab1
WHERE col1 < 9;
FORALL i IN rec_tab.FIRST .. rec_tab.LAST
INSERT INTO tab2
VALUES rec_tab(i);
FOR i IN rec_tab.FIRST .. rec_tab.LAST LOOP
rec_tab(i).col1 := rec_tab(i).col1 + 100;
END LOOP;
FORALL i IN rec_tab.FIRST .. rec_tab.LAST
UPDATE tab1
SET (col1, col2) = rec_tab(i)
WHERE col1 < 8;
OPEN c1;
FETCH c1
BULK COLLECT INTO rec_tab;
CLOSE c1;
END;
posted on 2012-03-15 15:04 NewProgramer 阅读(1055) 评论(0) 收藏 举报
浙公网安备 33010602011771号
