FORALL 用法小结:
作者:sonic
本文主要翻译、整理了ORACLE官方文档上有关FORALL的部份内容,不妥之处,还希望多和大家交流。
在发送语句到SQL引擎前,FORALL语句告知PL/SQL 引擎批挷定输入集合。尽管FORALL语句包含一个迭代(iteration)模式,它并不一是个FOR循环。其语法为:
FORALL index IN lower_bound..upper_bound sql_statement;
一、如何使用批挷定提高性能(How Do Bulk Binds Improve Performance)
在PL/SQL 和SQL引擎(engines)中,太多的上下文切换(context switches)会影响性能。这个会发生在当一个循环为集合中的每个元素执行一个单个SQL语句时。而使用批挷定能显著提高性能。下图显示PL/SQL引擎 和SQL引擎之间的context switches:(PL/SQL引擎执行存过语句仅发送SQL语句到SQL引擎,SQL引擎执行语句后返回数据给PL/SQL引擎)
PL/SQL引擎发送一次SQL语句给SQL引擎,在SQL引擎中则为范围中每个index数字执行一次SQL语句。
PL/SQL挷定操作包含以下三类:
in-bind: When a PL/SQL variable or host variable is stored in the database by an INSERT or UPDATE statement.
out-bind:When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause of an INSERT, UPDATE, or DELETE statement.
define: When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement.
在SQL语句中,为PL/SQL变量指定值称为挷定(binding),
DML语句能传递所有集合元素到一个单个操作中,这过程称为批挷定(bulk binding)。
如果集合有20个元素,批挷定让你用单个操作等效于执行与20个SELECT,INSERT,UPDATE或DELETE语句。这个技术通过减少在PL/SQL和SQL引擎(engines)间的上下文切换来提高性能。批挷定包括:
1.带INSERT, UPDATE, and DELETE语句的批挷定:在FORALL语句中嵌入SQL语句
2.带SELECT语句的批挷定:在SELECT语句中用BULK COLLECT 语句代替INTO
下边的例子分别用FOR和FORALL进行数据插入,以显示用批挷定的对性能的提高:
SQL
>
SET
SERVEROUTPUT
ON
SQL
>
CREATE
TABLE
parts (pnum
NUMBER
(
4
), pname
CHAR
(
15
));
Table
created.
SQL
>
DECLARE
2
TYPE NumTab
IS
TABLE
OF
parts.pnum
%
TYPE
INDEX
BY
BINARY_INTEGER;
3
TYPE NameTab
IS
TABLE
OF
parts.pname
%
TYPE
INDEX
BY
BINARY_INTEGER;
4
pnums NumTab;
5
Pnames NameTab;
6
t1
NUMBER
;
7
t2
NUMBER
;
8
t3
NUMBER
;
9
BEGIN
10
FOR
i
IN
1
..
500000
LOOP
11
pnums(i) :
=
i;
12
pnames(i) :
=
'
Part No.
'
||
to_char(i);
13
END
LOOP;
14
t1 :
=
dbms_utility.get_time;
15
16
FOR
i
IN
1
..
500000
LOOP
17
INSERT
INTO
parts
VALUES
(pnums(i),pnames(i));
18
END
LOOP;
19
t2 :
=
dbms_utility.get_time;
20
21
FORALL i
IN
1
..
500000
22
INSERT
INTO
parts
VALUES
(pnums(i),pnames(i));
23
t3 :
=
dbms_utility.get_time;
24
25
dbms_output.put_line(
'
Execution Time (secs)
'
);
26
dbms_output.put_line(
'
---------------------
'
);
27
dbms_output.put_line(
'
FOR loop:
'
||
TO_CHAR(t2
-
t1));
28
dbms_output.put_line(
'
FORALL:
'
||
TO_CHAR(t3
-
t2));
29
END
;
SQL
>
/
Execution Time (secs)
--
-------------------
FOR
loop:
2592
FORALL:
358
PL
/
SQL
procedure
successfully completed
从而可以看出FORALL语句在性能上有显著提高。
注释:SQL语句能涉及多个集合,然而,性能提高只适用于下标集合(subscripted collections)
二、FORALL 如何影响回滚(How FORALL Affects Rollbacks)
在FORALL语句中,如果任何SQL语句执行产生未处理的异常(exception),先前执行的所有数据库改变都会被回滚。然而,如果产生的异常被捕获并处理,则回滚改变到一个隐式的保存点,该保存点在每个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
'
);
Comit;
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
;
/
PL
/
SQL
procedure
successfully completed
SQL
>
select
*
from
emp2;
DEPTNO JOB
--
-------- ---------------
10
Clerk
temp
10
Clerk
temp
20
Bookkeeper
30
Analyst
30
Analyst
上边的例子SQL引擎执行UPDATE语句3次,指定范围内的每个索引号一次。第一个(depts(10))执行成功,但是第二个(depts(20))执行失败(插入值超过了列长),因此,仅仅第二个执行被回滚。
当执行任何SQL语句引发异常时,FORALL语句中断(halt)。上边的例子中,执行第二个UPDATE语句引发异常, 因此第三个语句不会执行。
三、用%BULK_ROWCOUNT 属性计算FORALL迭代影响行数
在进行SQL数据操作语句时,SQL引擎打开一个隐式游标(命名为SQL),该游标的标量属性(scalar attribute)有 %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT。
FORALL语句除具有上边的标量属性外,还有个复合属性(composite attribute):%BULK_ROWCOUNT,该属性具有索引表(index-by table)语法。它的第i个元素存贮SQL语句(INSERT, UPDATE或DELETE)第i个执行的处理行数。如果第i个执行未影响行,%bulk_rowcount (i),返回0。FORALL与%bulk_rowcount属性使用相同下标。例如:
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
;
%ROWCOUNT 返回SQL语句所有执行处理总的行数
%FOUND和 %NOTFOUND仅与SQL语句的最后执行有关,但是,可以使用%BULK_ROWCOUNT推断单个执行的值,如%BULK_ROWCOUNT(i)为0时,%FOUND和%NOTFOUND分别是FALSE和TRUE。
四、用%BULK_EXCEPTIONS属性处理FORALL异常
在执行FORALL语句期间,PL/SQL提供一个处理异常的机制。该机制使批挷定(bulk-bind)操作能保存异常信息并继续执行。方法是在FORALL语句中增加SAVE EXCEPTIONS关键字。语法为:
FORALL index IN lower_bound..upper_bound SAVE EXCEPTIONS
{insert_stmt | update_stmt | delete_stmt}
执行期间引发的所有异常都被保存游标属性 %BULK_EXCEPTIONS中,它存贮一个集合记录,每记录有两个字段:
%BULK_EXCEPTIONS(i).ERROR_INDEX:存贮在引发异常期间FORALL语句迭代(重复:iteration)
%BULK_EXCEPTIONS(i).ERROR_CODE:存贮相应的Oracle错误代码
%BULK_EXCEPTIONS.COUNT存贮异常的数量。(该属性不是%BULK_EXCEPTIONS集合记录的字段)。如果忽略SAVE EXCEPTIONS,当引发异常时,FORALL语句停止执行。此时,SQL%BULK_EXCEPTIONS.COUNT 返回1, 且SQL%BULK_EXCEPTIONS只包含一条记录。如果执行期间无异常 SQL%BULK_EXCEPTIONS.COUNT 返回 0.例子:
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
;
该例子中,当i等于2,6,10时,产生异常ZERO_DIVIDE,完成后SQL%BULK_EXCEPTIONS.COUNT为3,其值为(2,1476), (6,1476)和(10,1476),错误输出如下:
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 COLLECT子句取回查询结果至集合中
在返回到PL/SQL引擎之前,关键字BULK COLLECT告诉SQL引擎批挷定输出集合。该关键字能用于SELECT INTO, FETCH INTO和RETURNING INTO语句中。语法如下:
... BULK COLLECT INTO collection_name[, collection_name] ...
示例1:
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
;
示例2:
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
;
示例3:
DECLARE
TYPE SalList
IS
TABLE
OF
emp.sal
%
TYPE;
sals SalList;
BEGIN
SELECT
sal
BULK
COLLECT
INTO
sals
FROM
emp
WHERE
ROWNUM
<=
100
;
...
END
;
示例4:Examples
of
Bulk
Fetching
from
a
Cursor
:
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
;
示例5:Examples
of
Bulk
Fetching
from
a
Cursor
:
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;
--
返回到一个记录(records)集合
END
;
六、用LIMIT 子句限制批取出操作行
LIMIT子句可限制从数据库中取出的行数。该子句仅能用于批(非标量 not scalar)FETCH语句.语法为:
FETCH ... BULK COLLECT INTO ... [LIMIT rows];
rows可以是文字的(literal),变量(variable)或表达式(expression),但必须返回一个数字。否则,PL/SQL引发预定义异常VALUE_ERROR,如果为负数,PL/SQ引发INVALID_NUMBER。如果需要,PL/SQL四舍五入(round)为最近的整数.
下例中,在每个循环迭代中,FETCH语句返回到索引表empnos中,先前的值会被覆盖。
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
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
/**/
/* 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
;
七、用RETURNING INTO子句取回DML结果至集合
你能在INSERT、UPDATE或DELETE语句的RETURNING INTO子句中使用BULK COLLECT子句。(注意,此处是没有SELECT语句的)
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
;
八、BULK COLLECT上的限制
1.You cannot bulk collect into an associative array that has a string type for the key. .
2.BULK COLLECT语句只能用于服务器端(server-side),而非客户端
3.在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
;.
4.复合目标(如对象)不能用于RETURNING INTO子句中.
5.当需要隐式数据类型转换时,多复合目标(如对象集合)不能用于bulk collect into子句。
九、同时使用FORALL 和BULK COLLECT
Using FORALL and BULK COLLECT Together
你能使FORALL语句与BULK COLLECT结合,如下例:如果集合depts有3个元素,每个元素导致5行被删除,则语句完成时,集合enums有15个元素:
FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE empno = depts(j)
RETURNING empno BULK COLLECT INTO enums;
注意:不能在FORALL语句中使用SELECT ... BULK COLLECT语句。
十、Using Host Arrays with Bulk Binds
客户端程序能用匿名PL/SQL块批挷定输入和输出数组(arrays)。实际上,这是与服务器端传递集合最有效的方式。
Host arrays are declared in a host environment such as an OCI or Pro*C program and must be prefixed with a colon to distinguish them from PL/SQL collections. In the example below, an input host array is used in a DELETE statement. At run time, the anonymous PL/SQL block is sent to the database server for execution.
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;
限制:以下限制将应用于FORALL语句:
1. 在FORALL循环中,UPDATE语句中的SET子句和WHERE子句中不能指向同一个集合,你可能需要获得另一个集合副本,以在WHERE子句指向新的名称。
2. INSERT,UPDATE或DELETE语句必须至少涉及一个集合。如在LOOP插入一组常量的FORALL语句将引发异常。( FORALL的INSERT之类的语句一定要用in-bind 方式,如:
SQL
>
BEGIN
2
FORALL i
IN
1
..
100
3
INSERT
INTO
parts
VALUES
(i,i);
4
END
;
5
/
ORA
-
06550
: 第
3
行, 第
5
列:
PLS
-
00435
: 没有
BULK
In-
BIND 的 DML 语句在 FORALL 内不能使用
ORA
-
06550
: 第
2
行, 第
12
列:
PL
/
SQL: Statement ignored
3. 指定范围内的所有集合元素必须存在,如果一元素丢失或删除,你将收到一个错误,如:
DECLARE
TYPE NumList
IS
TABLE
OF
NUMBER
;
depts NumList :
=
NumList(
10
,
20
,
30
,
40
);
BEGIN
depts.
DELETE
(
3
);
--
delete third element
FORALL i
IN
depts.FIRST..depts.LAST
DELETE
FROM
emp
WHERE
deptno
=
depts(i);
--
causes an error
END
;
4. 下例显示,复合值的输入集合不能被分解和跳跃数据库列
CREATE
TABLE
coords (x
NUMBER
, y
NUMBER
);
CREATE
TYPE Pair
AS
OBJECT (m
NUMBER
, n
NUMBER
);
DECLARE
TYPE PairTab
IS
TABLE
OF
Pair;
pairs PairTab :
=
PairTab(Pair(
1
,
2
), Pair(
3
,
4
), Pair(
5
,
6
));
TYPE NumTab
IS
TABLE
OF
NUMBER
;
nums NumTab :
=
NumTab(
1
,
2
,
3
);
BEGIN
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
/**/
/* The following statement fails. */
FORALL i
IN
1
..
3
UPDATE
coords
SET
(x, y)
=
pairs(i)
WHERE
x
=
nums(i);
END
;
The workaround
is
to
decompose the composite
values
manually:
DECLARE
TYPE PairTab
IS
TABLE
OF
Pair;
pairs PairTab :
=
PairTab(Pair(
1
,
2
), Pair(
3
,
4
), Pair(
5
,
6
));
TYPE NumTab
IS
TABLE
OF
NUMBER
;
nums NumTab :
=
NumTab(
1
,
2
,
3
);
BEGIN
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
/**/
/* The following statement succeeds. */
FORALL i
in
1
..
3
UPDATE
coords
SET
(x, y)
=
(pairs(i).m, pairs(i).n)
WHERE
x
=
nums(i);
END
;
5. 集合下标不能是表达式,示例:
FORALL j IN mgrs.FIRST..mgrs.LAST
DELETE FROM emp WHERE mgr = mgrs(j+1); -- invalid subscript
6. 游标属性%BULK_ROWCOUNT不能分配给其它集合,同样,它也不能作为参数传递到子程序。