for循环和forall循环的区别

2、使用批量绑定优化PL/SQL

当SQL在集合的循环内执行时，PL/SQL和SQL引擎间的频繁切换就会影响到执行速度。例如，下面的UPDATE语句就在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
    ...
    UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
  END LOOP;
END;

在这样的情况下，如果SQL语句影响到四行或更多行数据时，使用批量绑定就会显著地提高性能。例如，下面的UPDATE语句可以一次就把整个嵌套表的数据发送到SQL引擎中：

FORALL i IN depts.FIRST..depts.LAST
  UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);

要想尽最大可能地提高性能，我们就需要像下面这样编写程序：

1. 如果一条INSERT、UPDATE或DELETE语句在循环内执行，并且引用到集合中的元素，那么，就把它放到FORALL语句中去。
2. 如果SELECT INTO、FETCHE INTO或RETURNING INTO子句引用了一个集合，那就配合BULK COLLECT子句一起使用。
3. 如果可能的话，尽量在应用程序和服务器之间使用主数组传递集合。
4. 如果DML操作失败时且不是很重大的问题，就可以在FORALL语句中使用SAVE EXCEPTIONS，然后在以后的循环中使用%BULK_EXCEPTIONS属性报告或清除错误。

不要忽略这些小问题，因为它们可以帮助我们分析流程控制和程序的依赖性。

 

 

同事问了我一个问题，使用FOR ALL和FOR LOOP的区别到底是什么。以前也一直没有深究这个问题，唯一清楚的是FOR ALL是一个批量操作，效率明显比FOR LOOP的方式要高，至于FOR ALL到底是一个SQL语句，还是将多个SQL语句同时提交给ORACLE还真不太清楚。

 

 

于是，首先做了个简单的例子，但是通过触发器来观察二者的区别：

SQL> CREATE TABLE TTT (ID NUMBER);

表已创建。

SQL> CREATE OR REPLACE TRIGGER TRI_TTT BEFORE INSERT ON TTT
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE('A');
4 END;
5 /

触发器已创建

SQL> SET SERVEROUT ON
SQL> DECLARE
2 TYPE T_NUM_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_NUM T_NUM_TABLE;
4 BEGIN
5 SELECT ROWNUM BULK COLLECT INTO V_NUM FROM TAB;
6 FOR I IN 1..V_NUM.COUNT LOOP
7 INSERT INTO TTT VALUES (V_NUM(I));
8 END LOOP;
9 END;
10 /
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A

PL/SQL 过程已成功完成。

SQL> DECLARE
2 TYPE T_NUM_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_NUM T_NUM_TABLE;
4 BEGIN
5 SELECT ROWNUM BULK COLLECT INTO V_NUM FROM TAB;
6 FORALL I IN 1..V_NUM.COUNT
7 INSERT INTO TTT VALUES (V_NUM(I));
8 END;
9 /
A

PL/SQL 过程已成功完成。

从触发器的除非动作上可以看出，FOR ALL语法和FOR LOOP的区别，FOR ALL对INSERT语句只调用了一次。

通过SQL_TRACE的方式也可以清楚的看到这一点：

SQL> DROP TRIGGER TRI_TTT;

触发器已删除。

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

会话已更改。

SQL> DECLARE
2 TYPE T_NUM_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_NUM T_NUM_TABLE;
4 BEGIN
5 SELECT ROWNUM BULK COLLECT INTO V_NUM FROM TAB;
6 FORALL I IN 1..V_NUM.COUNT
7 INSERT INTO TTT VALUES (V_NUM(I));
8 END;
9 /

PL/SQL 过程已成功完成。

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

会话已更改。

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

会话已更改。

SQL> DECLARE
2 TYPE T_NUM_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_NUM T_NUM_TABLE;
4 BEGIN
5 SELECT ROWNUM BULK COLLECT INTO V_NUM FROM TAB;
6 FOR I IN 1..V_NUM.COUNT LOOP
7 INSERT INTO TTT VALUES (V_NUM(I));
8 END LOOP;
9 END;
10 /

PL/SQL 过程已成功完成。

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

会话已更改。

SQL> HOST TKPROF E:ORACLEADMINYTKUDUMPYTK_ORA_3964.TRC E:REPORT.TXT SYS=NO AGGREGATE=NO

其中FOR ALL语句对应的INSERT语句为：

INSERT INTO TTT
VALUES
(:B1 )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 5 16
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ---------
total 2 0.00 0.00 0 1 5 16

而FOR LOOP对应的INSERT语句为：

INSERT INTO TTT
VALUES
(:B1 )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------
Parse 1 0.00 0.00 0 0 0 0
Execute 16 0.00 0.06 4 1 20 16
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ---------
total 17 0.00 0.06 4 1 20 16

从上面的TKPROF可以更清楚的看出，对于FOR ALL语法，INSERT语句只是执行了一次，但是产生了16条记录。从这一点上，FOR ALL语法于INSERT INTO SELECT语法更为相似。但FOR ALL又和INSERT INTO SELECT有着本质的区别：

SQL> TRUNCATE TABLE TTT;

表被截断。

SQL> ALTER TABLE TTT MODIFY ID NUMBER(3);

表已更改。

SQL> INSERT INTO TTT SELECT 994 + ROWNUM FROM TAB;
INSERT INTO TTT SELECT 994 + ROWNUM FROM TAB
*第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度

SQL> SELECT * FROM TTT;

未选定行

INSERT INTO SELECT语法属于一条语句，根据Oracle的语句级回滚，当插入由于个别数据发生错误的时候，整个插入语句被回滚。

但对于FOR ALL语句，虽然Oracle只执行了INSERT语句一次，但是，如果发生了错误，是可以捕获的，且错误发生之间的操作是可以保留下来的。

SQL> DECLARE
2 TYPE T_NUM_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_NUM T_NUM_TABLE;
4 BEGIN
5 SELECT ROWNUM BULK COLLECT INTO V_NUM FROM TAB;
6 V_NUM(5) := 1000;
7 BEGIN
8 FORALL I IN 1..V_NUM.COUNT
9 INSERT INTO TTT VALUES (V_NUM(I));
10 EXCEPTION
11 WHEN OTHERS THEN
12 COMMIT;
13 END;
14 END;
15 /

PL/SQL 过程已成功完成。

SQL> SELECT * FROM TTT;

ID
----------
1
2
3
4

从这一点看，FOR ALL语法和INSERT INTO SELECT又有着本质的区别。个人感觉FOR ALL语法和Oracle的OCI中数组绑定语法十分类似。二者都采用数据绑定变量的方式，通过调用一次SQL，将整个数组的内容提交给Oracle，并且出现错误后，可以通过捕获错误的方式保留出错前已经进行的修改。

（转帖时注：请注意试验中是写了commit的，所以保留出错前的修改，但是这里如果考虑事物一致性就要判断是否应该写commit。）

个人认为，FOR ALL语法和OCI的数组绑定具有相同的内部机制。二者分别为PL/SQL和OCI提供了相同的批量处理功能。