oracle批量表分析,Oracle学习：批量SQL实例分析与比较

【IT168技术】PL/SQL引入过程化语言的相应元素，比如条件分支或者循环，不过，SQL本身仍然作为主体嵌套于其中，由于需要SQL引擎才能执行SQL命令，对于PL/SQL程序，往往存在许多PL/SQL引擎 - SQL引擎之间的交互，过多这样的交互会对性能产生负面影响。

相关阅读：

Oracle在PL/SQL中引入了BULK SQL，用于尽量减少PL/SQL – SQL引擎之间的交互，以期提高性能。具体而言，Oracle BULK SQL包括FORALL语句、BULK COLLECT子句。前者将多条语句(通常是DML)一次性发送给SQL引擎;后者将SQL引擎所获得的结果一次性返回给PL/SQL引擎。

FORALL

下面的两个例子对比了FORALL与FOR循环之间的区别：

SQL>createtablet_bulkasselect*fromemployees;SQL>desct_bulk;Name           Type         NullableDefaultComments-------------- ------------ -------- ------- --------EMPLOYEE_IDNUMBER(6)    Y                        FIRST_NAMEVARCHAR2(20) Y                        LAST_NAMEVARCHAR2(25)                          EMAILVARCHAR2(25)                          PHONE_NUMBERVARCHAR2(20) Y                        HIRE_DATE      DATE                                  JOB_IDVARCHAR2(10)                          SALARYNUMBER(8,2)  Y                        COMMISSION_PCTNUMBER(2,2)  Y                        MANAGER_IDNUMBER(6)    Y                        DEPARTMENT_IDNUMBER(4)    YDECLARETYPE NumListISVARRAY(20)OFNUMBER;  depts NumList :=NumList(10,30,70);BEGINFORiINdepts.FIRST..depts.LAST LOOPDELETEFROMt_bulkWHEREdepartment_id=depts(i);ENDLOOP;END;DECLARETYPE NumListISVARRAY(20)OFNUMBER;  depts NumList :=NumList(10,30,70);--department numbersBEGINFORALL iINdepts.FIRST..depts.LASTDELETEFROMt_bulkWHEREdepartment_id=depts(i);END;

虽然从内部执行机制上来说，两个循环有很大的区别，但从语法上来说，还是非常类似的。不过有一个小细节需要注意，就是FORALL语句并没有对应的END语句。

我们再来看看使用FORALL的情况下对异常的处理：

CREATETABLEt_bulk2(f1NUMBER(3));DECLARETYPE type1ISTABLEOFNUMBER;  v type1:=type1(1,2,3000,4,5,6,77777,8,9,10001);BEGINEXECUTEIMMEDIATE'TRUNCATE TABLE t_bulk2';--FORALL idxINv.FIRST..v.LASTINSERTINTOt_bulk2VALUES(v(idx));--EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLERRM);END;ORA-01438: 值大于为此列指定的允许精度PL/SQLproceduresuccessfully completedSQL>SELECT*FROMt_bulk2;  F1----12

Oracle 9i中引入了SAVE EXCEPTIONS语法及与之对应的“ORA-24381： error(s) in array DML”异常，使用它们，我们可以跳过FORALL中出现异常的语句，并将异常保存在SQL%BULK_EXCEPTIONSP这个集合中：

DECLARETYPE type1ISTABLEOFNUMBER;  v type1:=type1(1,2,3000,4,5,6,77777,8,9,10001);--BULK_ERROR EXCEPTION;  PRAGMA EXCEPTION_INIT(BULK_ERROR,-24381);BEGINEXECUTEIMMEDIATE'TRUNCATE TABLE t_bulk2';--FORALL idxINv.FIRST..v.LASTSAVEEXCEPTIONSINSERTINTOt_bulk2VALUES(v(idx));--EXCEPTIONWHENBULK_ERRORTHENFORiIN1..SQL%BULK_EXCEPTIONS.COUNTLOOP      DBMS_OUTPUT.PUT_LINE(SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)||', Statement: #'||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);ENDLOOP;WHENOTHERSTHENNULL;END;ORA-01438: 值大于为此列指定的允许精度, Statement: #3ORA-01438: 值大于为此列指定的允许精度, Statement: #7ORA-01438: 值大于为此列指定的允许精度, Statement: #10PL/SQLproceduresuccessfully completedSQL>SELECT*FROMt_bulk2;  F1----1245689

(注意使用ERROR_CODE时要加上负号。)

下面介绍如何获取第一条语句所影响的行数，这需要使用SQL%BULK_ROWCOUNT:

createtablet_bulk3(fidnumber);insertintot_bulk3values(1);insertintot_bulk3values(2);insertintot_bulk3values(2);insertintot_bulk3values(3);insertintot_bulk3values(3);insertintot_bulk3values(3);insertintot_bulk3values(3);DECLARETYPE type1ISTABLEOFNUMBER;  v type1:=type1(1,2,3,4);--BULK_ERROR EXCEPTION;  PRAGMA EXCEPTION_INIT(BULK_ERROR,-24381);BEGINFORALL idxINv.FIRST..v.LASTSAVEEXCEPTIONSDELETEFROMt_bulk3WHEREfid=v(idx);--FORidxINv.FIRST..v.LAST LOOP    DBMS_OUTPUT.PUT_LINE('Statement: #'||idx||','||SQL%BULK_ROWCOUNT(idx)||'rows were impacted.');ENDLOOP;--EXCEPTIONWHENBULK_ERRORTHENFORiIN1..SQL%BULK_EXCEPTIONS.COUNTLOOP      DBMS_OUTPUT.PUT_LINE(SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)||', Statement: #'||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);ENDLOOP;WHENOTHERSTHENNULL;END;Statement: #1,1rows were impacted.Statement: #2,2rows were impacted.Statement: #3,4rows were impacted.Statement: #4,0rows were impacted.PL/SQLproceduresuccessfully completed

BULK COLLECT

假设有一条SQL查询，返回的记录中包含5行，那么如果在PL/SQL中执行此查询，会有5次的PL/SQL – SQL交互，如果使用BULK COLLECT，可以降低到1次。

BULK COLLECT子句可以出现在以下语句中：

SELECT INTO

FETCH

RETURNING INTO

createtablet_bulk4(fidnumber, fvalvarchar2(20));insertintot_bulk4values(1,'abc');insertintot_bulk4values(2,'def');insertintot_bulk4values(3,'xyz');insertintot_bulk4values(4,'xxx');insertintot_bulk4values(5,'123');commit;DECLARETYPE type1ISTABLEOFt_bulk4%ROWTYPE;  v type1;BEGINSELECT*BULKCOLLECTINTOvFROMt_bulk4;--FORiIN1..v.COUNTLOOP    DBMS_OUTPUT.PUT_LINE(v(i).fid||''||v(i).fval);ENDLOOP;END;1abc2def3xyz4xxx5123PL/SQLproceduresuccessfully completed

另一个INDEX BY集合的示例(实际上使用跟上例一样的FOR循环也可以)：

DECLARETYPE type1ISTABLEOFt_bulk4%ROWTYPEINDEXBYPLS_INTEGER;  v type1;  idx PLS_INTEGER;BEGINSELECT*BULKCOLLECTINTOvFROMt_bulk4;--idx:=v.FIRST;WHILE(idxISNOTNULL) LOOP    DBMS_OUTPUT.PUT_LINE(v(idx).fid||''||v(idx).fval);    idx :=v.NEXT(idx);ENDLOOP;END;

FETCH cursor BULK COLLECT INTO的使用与上述例子都类似，不多写了。

关于RETURNING INTO + BULK COLLECT，我们来一个综合的例子：

createtablet_bulk5(fidnumber);DECLARETYPE type1ISTABLEOFNUMBER;  v type1:=type1(1,2,3,5);--TYPE type2ISTABLEOFt_bulk5.fid%TYPE;  v2 type2;BEGINFORALL idxINv.FIRST..v.LASTINSERTINTOt_bulk5VALUES(v(idx)) RETURNING fidBULKCOLLECTINTOv2;--DBMS_OUTPUT.PUT_LINE(v2.COUNT);END;4PL/SQLproceduresuccessfully completed

顺便比较一下使用FOR循环时是什么结果：

DECLARETYPE type1ISTABLEOFNUMBER;  v type1:=type1(1,2,3,5);--TYPE type2ISTABLEOFt_bulk5.fid%TYPE;  v2 type2;BEGINFORidxINv.FIRST..v.LAST LOOPINSERTINTOt_bulk5VALUES(v(idx)) RETURNING fidBULKCOLLECTINTOv2;ENDLOOP;--DBMS_OUTPUT.PUT_LINE(v2.COUNT);END;1PL/SQLproceduresuccessfully completed

这实际上也好理解，因为上面的INSERT语句每次影响的只有一行，所以第二例中，保留的是循环中最后一次执行的INSERT所影响的行数，当然是1;而由于FORALL语句会将所有语句一次性提交到数据库，这也使得我们可以使用RETURNING INTO + BULK COLLECT获取所有插入的数据