在PLSQL中，PLSQL块/子程序由PLSQL引擎处理，而其中的SQL语句则由PLSQL引擎发送至SQL引擎处理，后者处理完毕后再向前者返回数据，两者之间的通信称为上下文切换。过多的上下文切换将带来过量的性能负载，FORALL和BULK COLLECT子句则可批量处理数据，从而减少这方面的性能负载。
一、FORALL与DML语句的简单结合
当PLSQL中的DML语句加上FORALL子句就可以一次性将语句和数据发送至SQL引擎处理，处理结果也会一次性反馈给PLSQL引擎。
CREATE TABLE cux_employees(empno NUMBER, ename VARCHAR2(40));
/
DECLARE
TYPE empno_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE ename_tbl_type IS TABLE OF VARCHAR2(40) INDEX BY BINARY_INTEGER;
t_empno empno_tbl_type;
t_ename ename_tbl_type;
l_limit NUMBER := 5000;
l_len NUMBER := length(l_limit);
l_sql VARCHAR2(240);
BEGIN
--模拟出现有两个大量数据的表变量
FOR k IN 1 .. l_limit
LOOP
t_empno(k) := k;
t_ename(k) := 'EMP' || lpad(k,
l_len,
'0');
END LOOP;
--INSERT语句搭配FORALL
FORALL k IN 1 .. l_limit
INSERT INTO cux_employees
(empno
,ename)
VALUES
(t_empno(k)
,t_ename(k));
--UPDATE语句搭配FORALL
--这里要注意：SET节中不允许使用循环变量！
FORALL k IN 1 .. l_limit
UPDATE cux_employees
SET ename = REPLACE(ename, 'EMP', 'EMP_No.')
WHERE empno = t_empno(k);
--DELETE语句搭配FORALL
FORALL k IN floor(l_limit / 2) + 1 .. l_limit
DELETE FROM cux_employees WHERE empno = t_empno(k);
--FORALL语句也可以搭配动态SQL实现批量DML操作，例如：
l_sql := 'INSERT INTO cux_employees(empno, ename) VALUES(:1, :2)';
FORALL k IN floor(l_limit / 2) + 1 .. l_limit
EXECUTE IMMEDIATE l_sql USING t_empno(k), t_ename(k);
END;
二、SAVE EXCEPTIONS和SQL%BULK_EXCEPTIONS属性
批量DML虽然是一次性将指令和数据传送至SQL引擎，但在SQL引擎中仍然是一条条执行的，如果在处理过程中发生异常，则整个批量处理会中断，同时抛出这个异常。使用SAVE EXCEPTIONS关键字可以使在过程中即便发生异常，也能绕过异常继续，以保证整个批量处理中没有异常的处理全部执行，最终抛出一个异常，代码-24381。顾名思义，记录下来的异常则可以通过SQL%BULK_EXCEPTIONS属性查询：SQL%BULK_EXCEPTIONS是一个记录集合，每条记录都由ERROR_INDEX和ERROR_CODE两个字段组成，前者是批量处理中发生异常的迭代编号（对应着FORALL的循环变量），后者是对应异常的ORACLE错误代码；而SQL%BULK_EXCEPTIONS.COUNT则是批量处理中的异常个数了。
TRUNCATE TABLE cux_employees;
ALTER TABLE cux_employees ADD CONSTRAINT cux_employees_u1 UNIQUE(empno);
ALTER TABLE cux_employees MODIFY(empno NOT NULL);
/
DECLARE
TYPE empno_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE ename_tbl_type IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
t_empno empno_tbl_type;
t_ename ename_tbl_type;
errors_in_array_dml EXCEPTION;
PRAGMA EXCEPTION_INIT(errors_in_array_dml, -24381);
BEGIN
FOR k IN 1 .. 10 LOOP
t_empno(k) := k;
t_ename(k) := 'EMP' || lpad(k, 3, '0');
END LOOP;
--制造一些问题数据
t_empno(3) := NULL;
t_empno(5) := 10;
t_ename(7) := rpad(t_ename(7), 41, '.');
--将数据批量插入表中
FORALL k IN 1 .. 10 SAVE EXCEPTIONS
INSERT INTO cux_employees
(empno, ename)
VALUES
(t_empno(k), t_ename(k));
COMMIT;
EXCEPTION
WHEN errors_in_array_dml THEN
dbms_output.put_line('批量DML中发生了' || SQL%bulk_exceptions.count || '个错误');
FOR k IN 1 .. SQL%bulk_exceptions.count LOOP
dbms_output.put_line('第' || k || '个错误发生在第' || SQL%BULK_EXCEPTIONS(k)
.error_index || '行DML：' ||
SQLERRM(-sql%BULK_EXCEPTIONS(k).error_code));
--注意%BULK_EXCEPTIONS中的error_code不带负号
END LOOP;
END;
上例的运行结果是：
批量DML中发生了3个错误
第1个错误发生在第3行DML：ORA-01400: 无法将 NULL 插入 ()
第2个错误发生在第7行DML：ORA-12899: 列 的值太大 (实际值: , 最大值: )
第3个错误发生在第10行DML：ORA-00001: 违反唯一约束条件 (.)
三、SQL%BULK_ROWCOUNT属性
SQL%BULK_ROWCOUNT也是为FORALL设计的，SQL%BULK_ROWCOUNT是一个数字集合，用于存储FORALL中第N次DML所产生影响的实际行数，没有产生影响就返回0，若产生影响，影响了几行就返回几；SQL%BULK_ROWCOUNT的索引和FORALL的循环变量是一一对应的。
DECLARE
TYPE deptno_tbl_type IS TABLE OF NUMBER;
t_deptno deptno_tbl_type := deptno_tbl_type(10, 40);
BEGIN
FORALL k IN 1 .. t_deptno.count
UPDATE emp SET sal = sal * 1.5 WHERE deptno = t_deptno(k);
FOR i IN 1 .. t_deptno.count LOOP
dbms_output.put_line('第' || i || '次更新实际影响了' || SQL%BULK_ROWCOUNT(i) ||
'行数据.');
END LOOP;
END;上例的运行结果是
第1次更新实际影响了3行数据.
第2次更新实际影响了0行数据.
四、INDICES OF选项
如果使用FORALL操作一个索引不连续的数组，那么循环变量的上下限则无法确定，此时需要使用INDICES OF选项，可使循环变量直接在存在的索引当中遍历。
DECLARE
TYPE deptno_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
t_deptno deptno_tbl_type;
BEGIN
t_deptno(3) := 10;
t_deptno(8) := 30;
t_deptno(10) := 40;
FORALL k IN INDICES OF t_deptno
UPDATE emp SET sal = sal * 1.5 WHERE deptno = t_deptno(k);
FOR i IN t_deptno.first .. t_deptno.last LOOP
IF t_deptno.exists(i)
THEN
dbms_output.put_line('第' || i || '次更新实际影响了' || SQL%BULK_ROWCOUNT(i) ||
'行数据.');
END IF;
END LOOP;
END;上例的运行结果是
第3次更新实际影响了3行数据.
第8次更新实际影响了6行数据.
第10次更新实际影响了0行数据.
五、VALUES OF选项
VALUES OF选项可以让我们指定FORALL循环变量遍历的数据，不仅可以无序，甚至可以反复。简单来说就是在一个数组中按照我们希望的遍历顺序将索引数存入，VALUES OF就可以将数组中的数据作为循环变量遍历的范围。由于数据相当于赋值给了循环变量，所以这个数组应当是PLS_INTEGER或BINARY_INTEGER元素的数组，而且要保证这个数组中不能有NULL值，否则会引起FORALL报错ORA-22160，即便使用SAVE EXCEPTIONS也会使整个FORALL不执行，因为这不是到SQL引擎才抛出的错误。
CREATE TABLE cux_male_employees (empno NUMBER, ename VARCHAR2(40));
CREATE TABLE cux_female_employees (empno NUMBER, ename VARCHAR2(40));
/
DECLARE
TYPE emp_rcd_type IS RECORD(
empno NUMBER
,ename VARCHAR2(40)
,gender CHAR(1));
TYPE emp_tbl_type IS TABLE OF emp_rcd_type INDEX BY BINARY_INTEGER;
TYPE index_tbl_type IS TABLE OF BINARY_INTEGER;
t_emp emp_tbl_type;
t_male_emp index_tbl_type := index_tbl_type();
t_female_emp index_tbl_type := index_tbl_type();
BEGIN
--模拟出t_emp中存储了不同的员工信息
t_emp(1).empno := 1;
t_emp(1).ename := 'YUSUF';
t_emp(1).gender := 'M';
t_emp(2).empno := 2;
t_emp(2).ename := 'FATIMAH';
t_emp(2).gender := 'F';
t_emp(3).empno := 3;
t_emp(3).ename := 'HAMUZA';
t_emp(3).gender := 'M';
--将男性和女性员工的索引分开加入对应的关联数组中
FOR k IN t_emp.first .. t_emp.last LOOP
IF t_emp(k).gender = 'M'
THEN
t_male_emp.extend;
t_male_emp(t_male_emp.last) := k;
ELSIF t_emp(k).gender = 'F'
THEN
t_female_emp.extend;
t_female_emp(t_female_emp.last) := k;
END IF;
END LOOP;
--将男性和女性员工的信息分别存入表中
FORALL m IN VALUES OF t_male_emp
INSERT INTO cux_male_employees
(empno, ename)
VALUES
(t_emp(m).empno, t_emp(m).ename);
FORALL f IN VALUES OF t_female_emp
INSERT INTO cux_female_employees
(empno, ename)
VALUES
(t_emp(f).empno, t_emp(f).ename);
COMMIT;
END;
上例的运行结果是
SQL> SELECT * FROM cux_male_employees;
EMPNO ENAME
---------- ----------------------------------------
1 YUSUF
3 HAMUZA
SQL> SELECT * FROM cux_female_employees;
EMPNO ENAME
---------- ----------------------------------------
2 FATIMAH
6889
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
