本文主要翻譯、整理了ORACLE官方文檔上有關(guān)FORALL的部份內(nèi)容，不妥之處，還希望多和大家交流。

在發(fā)送語句到SQL引擎前，FORALL語句告知PL/SQL 引擎批挷定輸入集合。盡管FORALL語句包含一個迭代(iteration)模式，它并不一是個FOR循環(huán)。其語法為：

FORALL index IN lower_bound..upper_bound sql_statement;

一、如何使用批挷定提高性能(How Do Bulk Binds Improve Performance)

在PL/SQL 和SQL引擎(engines)中，太多的上下文切換(context switches)會影響性能。這個會發(fā)生在當(dāng)一個循環(huán)為集合中的每個元素執(zhí)行一個單個SQL語句時。而使用批挷定能顯著提高性能。下圖顯示PL/SQL引擎 和SQL引擎之間的context switches：(PL/SQL引擎執(zhí)行存過語句僅發(fā)送SQL語句到SQL引擎，SQL引擎執(zhí)行語句后返回數(shù)據(jù)給PL/SQL引擎)

PL/SQL引擎發(fā)送一次SQL語句給SQL引擎，在SQL引擎中則為范圍中每個index數(shù)字執(zhí)行一次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個元素，批挷定讓你用單個操作等效于執(zhí)行與20個SELECT，INSERT，UPDATE或DELETE語句。這個技術(shù)通過減少在PL/SQL和SQL引擎(engines)間的上下文切換來提高性能。批挷定包括：

1．帶INSERT, UPDATE, and DELETE語句的批挷定：在FORALL語句中嵌入SQL語句

2．帶SELECT語句的批挷定：在SELECT語句中用BULK COLLECT 語句代替INTO

下邊的例子分別用FOR和FORALL進(jìn)行數(shù)據(jù)插入，以顯示用批挷定的對性能的提高：

SQL>SETSERVEROUTPUTONSQL>CREATETABLEparts?(pnumNUMBER(4),?pnameCHAR(15));

Tablecreated.

SQL>DECLARE2TYPE?NumTabISTABLEOFparts.pnum%TYPEINDEXBYBINARY_INTEGER;

3TYPE?NameTabISTABLEOFparts.pname%TYPEINDEXBYBINARY_INTEGER;

4pnums?NumTab;

5Pnames?NameTab;

6t1NUMBER;

7t2NUMBER;

8t3NUMBER;

9BEGIN10FORiIN1..500000LOOP

11pnums(i)?:=i;

12pnames(i)?:='Part?No.'||to_char(i);

13ENDLOOP;

14t1?:=dbms_utility.get_time;

1516FORiIN1..500000LOOP

17INSERTINTOpartsVALUES(pnums(i),pnames(i));

18ENDLOOP;

19t2?:=dbms_utility.get_time;

2021FORALL?iIN1..50000022INSERTINTOpartsVALUES(pnums(i),pnames(i));

23t3?:=dbms_utility.get_time;

2425dbms_output.put_line('Execution?Time?(secs)');

26dbms_output.put_line('---------------------');

27dbms_output.put_line('FOR?loop:'||TO_CHAR(t2-t1));

28dbms_output.put_line('FORALL:'||TO_CHAR(t3-t2));

29END;

SQL>/Execution?Time?(secs)

---------------------FORloop:2592FORALL:358PL/SQLproceduresuccessfully?completed

從而可以看出FORALL語句在性能上有顯著提高。

注釋：SQL語句能涉及多個集合，然而，性能提高只適用于下標(biāo)集合(subscripted collections)

二、FORALL 如何影響回滾(How FORALL Affects Rollbacks)

在FORALL語句中，如果任何SQL語句執(zhí)行產(chǎn)生未處理的異常(exception)，先前執(zhí)行的所有數(shù)據(jù)庫改變都會被回滾。然而，如果產(chǎn)生的異常被捕獲并處理，則回滾改變到一個隱式的保存點(diǎn)，該保存點(diǎn)在每個SQL語句執(zhí)行前被標(biāo)記。之前的改變不會被回滾。例如：

CREATETABLEemp2?(deptnoNUMBER(2),?jobVARCHAR2(15));

INSERTINTOemp2VALUES(10,'Clerk');

INSERTINTOemp2VALUES(10,'Clerk');

INSERTINTOemp2VALUES(20,'Bookkeeper');--10-char?job?titleINSERTINTOemp2VALUES(30,'Analyst');

INSERTINTOemp2VALUES(30,'Analyst');

Comit;

DECLARETYPE?NumListISTABLEOFNUMBER;

depts?NumList?:=NumList(10,20,30);

BEGINFORALL?jINdepts.FIRST..depts.LAST

UPDATEemp2SETjob=job||'(temp)'WHEREdeptno=depts(j);

--raises?a?"value?too?large"?exceptionEXCEPTION

WHENOTHERSTHENCOMMIT;

END;

/PL/SQLproceduresuccessfully?completed

SQL>select*fromemp2;

DEPTNO?JOB

----------?---------------10Clerktemp10Clerktemp20Bookkeeper

30Analyst

30Analyst

上邊的例子SQL引擎執(zhí)行UPDATE語句3次，指定范圍內(nèi)的每個索引號一次。第一個(depts(10))執(zhí)行成功，但是第二個(depts(20))執(zhí)行失敗(插入值超過了列長)，因此，僅僅第二個執(zhí)行被回滾。

當(dāng)執(zhí)行任何SQL語句引發(fā)異常時，FORALL語句中斷(halt)。上邊的例子中，執(zhí)行第二個UPDATE語句引發(fā)異常， 因此第三個語句不會執(zhí)行。

三、用%BULK_ROWCOUNT 屬性計算FORALL迭代影響行數(shù)

在進(jìn)行SQL數(shù)據(jù)操作語句時，SQL引擎打開一個隱式游標(biāo)(命名為SQL)，該游標(biāo)的標(biāo)量屬性(scalar attribute)有 %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT。

FORALL語句除具有上邊的標(biāo)量屬性外，還有個復(fù)合屬性(composite attribute)：%BULK_ROWCOUNT，該屬性具有索引表(index-by table)語法。它的第i個元素存貯SQL語句(INSERT, UPDATE或DELETE)第i個執(zhí)行的處理行數(shù)。如果第i個執(zhí)行未影響行，%bulk_rowcount (i)，返回0。FORALL與%bulk_rowcount屬性使用相同下標(biāo)。例如：

DECLARETYPE?NumListISTABLEOFNUMBER;

depts?NumList?:=NumList(10,20,50);

BEGINFORALL?jINdepts.FIRST..depts.LAST

UPDATEempSETsal=sal*1.10WHEREdeptno=depts(j);

--Did?the?3rd?UPDATE?statement?affect?any?rows?IFSQL%BULK_ROWCOUNT(3)=0THEN...

END;

%ROWCOUNT 返回SQL語句所有執(zhí)行處理總的行數(shù)

%FOUND和 %NOTFOUND僅與SQL語句的最后執(zhí)行有關(guān)，但是，可以使用%BULK_ROWCOUNT推斷單個執(zhí)行的值，如%BULK_ROWCOUNT(i)為0時，%FOUND和%NOTFOUND分別是FALSE和TRUE。

四、用%BULK_EXCEPTIONS屬性處理FORALL異常

在執(zhí)行FORALL語句期間，PL/SQL提供一個處理異常的機(jī)制。該機(jī)制使批挷定(bulk-bind)操作能保存異常信息并繼續(xù)執(zhí)行。方法是在FORALL語句中增加SAVE EXCEPTIONS關(guān)鍵字。語法為：

FORALL index IN lower_bound..upper_bound SAVE EXCEPTIONS

{insert_stmt | update_stmt | delete_stmt}

執(zhí)行期間引發(fā)的所有異常都被保存游標(biāo)屬性 %BULK_EXCEPTIONS中，它存貯一個集合記錄，每記錄有兩個字段：

%BULK_EXCEPTIONS(i).ERROR_INDEX：存貯在引發(fā)異常期間FORALL語句迭代(重復(fù)：iteration)

%BULK_EXCEPTIONS(i).ERROR_CODE：存貯相應(yīng)的Oracle錯誤代碼

%BULK_EXCEPTIONS.COUNT存貯異常的數(shù)量。(該屬性不是%BULK_EXCEPTIONS集合記錄的字段)。如果忽略SAVE EXCEPTIONS，當(dāng)引發(fā)異常時，FORALL語句停止執(zhí)行。此時，SQL%BULK_EXCEPTIONS.COUNT 返回1, 且SQL%BULK_EXCEPTIONS只包含一條記錄。如果執(zhí)行期間無異常 SQL%BULK_EXCEPTIONS.COUNT 返回 0.例子：

DECLARETYPE?NumListISTABLEOFNUMBER;

num_tab?NumList?:=NumList(10,0,11,12,30,0,20,199,2,0,9,1);

errorsNUMBER;

dml_errors?EXCEPTION;

PRAGMA?exception_init(dml_errors,-24381);

BEGINFORALL?iINnum_tab.FIRST..num_tab.LASTSAVEEXCEPTIONS

DELETEFROMempWHEREsal>500000/num_tab(i);

EXCEPTION

WHENdml_errorsTHENerrors?:=SQL%BULK_EXCEPTIONS.COUNT;

dbms_output.put_line('Number?of?errors?is'||errors);

FORiIN1..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));

ENDLOOP;

END;

該例子中，當(dāng)i等于2，6,10時，產(chǎn)生異常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子句取回查詢結(jié)果至集合中

在返回到PL/SQL引擎之前，關(guān)鍵字BULK COLLECT告訴SQL引擎批挷定輸出集合。該關(guān)鍵字能用于SELECT INTO, FETCH INTO和RETURNING INTO語句中。語法如下：

... BULK COLLECT INTO collection_name[, collection_name] ...

示例1：

DECLARETYPE?NumTabISTABLEOFemp.empno%TYPE;

TYPE?NameTabISTABLEOFemp.ename%TYPE;

enums?NumTab;--no?need?to?initializenames?NameTab;

BEGINSELECTempno,?enameBULKCOLLECTINTOenums,?namesFROMemp;

...

END;

示例2：

CREATETYPE?CoordsASOBJECT?(xNUMBER,?yNUMBER);

CREATETABLEgrid?(numNUMBER,?loc?Coords);

INSERTINTOgridVALUES(10,?Coords(1,2));

INSERTINTOgridVALUES(20,?Coords(3,4));

DECLARETYPE?CoordsTabISTABLEOFCoords;

pairs?CoordsTab;

BEGINSELECTlocBULKCOLLECTINTOpairsFROMgrid;

--now?pairs?contains?(1,2)?and?(3,4)END;

示例3：

DECLARETYPE?SalListISTABLEOFemp.sal%TYPE;

sals?SalList;

BEGINSELECTsalBULKCOLLECTINTOsalsFROMemp

WHEREROWNUM<=100;

...

END;

示例4：ExamplesofBulkFetchingfromaCursor：

DECLARETYPE?NameListISTABLEOFemp.ename%TYPE;

TYPE?SalListISTABLEOFemp.sal%TYPE;

CURSORc1ISSELECTename,?salFROMempWHEREsal>1000;

names?NameList;

sals?SalList;

BEGINOPENc1;

FETCHc1BULKCOLLECTINTOnames,?sals;--可返回到一個或多個集合END;

示例5：ExamplesofBulkFetchingfromaCursor：

DECLARETYPE?DeptRecTabISTABLEOFdept%ROWTYPE;

dept_recs?DeptRecTab;

CURSORc1ISSELECTdeptno,?dname,?locFROMdeptWHEREdeptno>10;

BEGINOPENc1;

FETCHc1BULKCOLLECTINTOdept_recs;--返回到一個記錄(records)集合END;

總結(jié)

以上是生活随笔為你收集整理的forall oracle 游标,FORALL用法小结的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。