FORALL的文法與注意事項

http://qurtyy.blog.163.com/blog/static/57443681201052112417441/

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進行資料插入，以顯示用批挷定的對性能的提高：

Java代碼

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> 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語句執行前被标記。之前的改變不會被復原。例如：

Java代碼

FORALL的文法與注意事項

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;

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

20 Bookkeeper

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

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

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

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不能配置設定給其它集合，同樣，它也不能作為參數傳遞到子程式。

FORALL的文法與注意事項

繼續閱讀

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

如何存取圖檔到資料庫(SQL Server)

DOS常用指令的使用

DOS指令(2) 磁盤操作類指令

Oracle的基本操作

設定某一行背景顔色的CListCtrl

磁盤結構及在Linux中的命名

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離