批量SQL实例分析与比较

1、批量 实例分析与比较Oracle在 PL/SQL中引入了BULK ，用于尽量减少 PL/SQLSQL引擎之间的交互，以期提高性能。具体而言，Oracle SQL包括语句、 COLLECT子句。前者将多条语句通常是一次性发送给SQL引擎后者将 SQL引擎所获得的结果一次性返回给PL/SQL引擎。FORALL下面的两个例子对比了 与 FOR循环之间的区别：SQL tablet_bulkasselect* fromemployees; descNameTypeNullableDefault - - - -EMPLOYEE_ID YFIRST_NAME YLAST_NAME VARCHAR2(25)P

2、HONE_NUMBER Y JOB_IDVARCHAR2(10)NUMBER(8,2) YCOMMISSION_PCT N) YMANAGER_ID Y_ID N YDECLARE NIS OF NUMBER; := NumList(10, 30, 70);FOR i INdepts.FIRST.depts.LAST FROM WHERE department_id = depts(END END;NumList VARRAY(20)OFNUMBER; := 30, 70); - d i depts.FIRST.depts.LAST FROM department_id =depts(i);E

3、ND;虽然从内部执行机制上来说，两个循环有很大的区别，但从语法上来说，还是非常类似的。不过有一个小细节需要注意，就是 语句并没有对应的语句。我们再来看看使用 FORALL的情况下对异常的处理：TABLEt_bulk2(f1 NUMBER(3);TYPE type1IS OF NUMBER;v type1:=type1(1, 2, 3000, 4, 5,6, 77777, 8, 9, 10001);EXECUTEIMMETRUNCATETAB t_bulk2;-idx IN v.FIRST.v.LASTINSERT INTOt_bu V- TDBMS_OUTPUT.PUT_LINE(SQLERR

4、M);END;ORA-01438: 值大于为此列指定的允许精度 proceduresuccessfullycompletedSQL * FROMF1-12Oracle9i中引入了 SA EXCEPTIONS“ORA-24381：)in DML”异常，使用它们，我们可以跳过 FORALL中出现异常的语句，并将异常保存在 SQL%BULK_EXCEPTIONSP这个集合中：DECLARE TABLEOF NUMBER;v =type1(1,2, 3000, 4, 5, 6, 77777, 8, 9, 10001);-BULK_ERROREXCEPTION;PRAGMAEXCEPTION_INIT(

5、BULK_ERROR, IMMEDIATETRUNCATE T t_bulk2;- idxIN SAVEEXCEPTIONS INTOt_bulk2 VALUES(v(idx);-EXCEPTION THENFORi IN 1.SQL%BULK_EXCEPTIONS.COUNT LOOPDBMS_OUTPUT.PUT_LINE(SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)|,#|SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);ENDWHEN THORA-01438:值大于为此列指定的允许精度, #3ORA-01438:值大于为此

6、列指定的允许精度, #7ORA-01438:值大于为此列指定的允许精度, #10 proceduresuccessfullycompletedSQL * FROMF1-1245689注意使用 ERROR_CODE时要加上负号。)下面介绍如何获取第一条语句所影响的行数，这需要使用SQL%BULK_ROWCOUNT:create tablenumber);insertintot_bulk3insert t_bulk3values(2);insert t t_bulk3 3); intot_bu vainsertintot_bulk3insert t_bulk3values(3);type1IS T

7、ABLEOF vtype1:=type1(1,2, 3, - EXCEPTION; EXCEPTION_INIT(BULK_ERROR, -24381);idx IN v.FIRST.v.LASTSAVE EFROM -FORidxIN LOOPDBMS_OUTPUT.PUT_LINE(Statement: #|SQL%BULK_ROWCOUNT(idx)| LOOP;- BULK_ERRORFOR i IN1.SQL%BULK_EXCEPTIONS.COUNT DBMS_OUTPUT.PUT_LINE(SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)|,

8、 #|SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);END WH OTHERSTHEN #1, 1 rowswere 2 rows impacted.Statement:#3, 4 rows wereimpacted.Statement:#4, 0 ro imPL/SQLproceduresuccessfully coBULK 假设有一条 查询，返回的记录中包含 5行，那么如果在中执行此查询，会有 5次的PL/SQLSQL交互，如果使用BULK ，可以降低到1次。BULK 子句可以出现在以下语句中：SELECTINTOFETCH INTOcreatetab t_bul

9、k4(fidnumber,fvalvarchar2(20); t_bulk4 values(1,abc);insertintot_bulk4insert t val,xyz); intot_bu vaxx);insert t_bulk4values(5,123);TYPE type1IS OF t_bulk4%ROWTYPE;v ;SELECT* BULKCOLLECTINTO v t_bulk4;-FORi IN 1.v.COUNT D ENDEND;1 abc2 def3 xyz4 xxx5 123 proceduresuccessfullycompleted另一个 BY集合的示例实际上使

10、用跟上例一样的FOR循环也可以：type1IS TABLEOF INDEXBY vtype1;idxPLS_INTEGER;SELECT* COLLECT v FROMt_bulk4;-IS NOT DBMS_OUTPUT.PUT_LINE(v(idx).fid| |v(idx).fval); :=END LOOP;END;FETCH BU CO INTO的使用与上述例子都类似，不多写了。关于 RETURNING + COLLECT，我们来一个综合的例子：create tablenumber);DECLARE IS OFv e1(1,2, 3,5);- IS OF t_bulk5.fid%TYPE;v2 FORALL idx INSERT t_bulk5 RE BULKCOLLECT -DBMS_OUTPUT.PUT_LINE(v2.COUNT);4PL/SQLproceduresuccessfully completed顺便比较一下使用 FOR循环时是什么结果：DECLARE IS OFv e1(1,2, 3,5);- IS OF t_bulk5.fid%TYPE;v2 FORidxIN v.FIRST.v.LAST LOOPINTO VALUES(v(idx) RETURNING CINTOv2;LOOP;-DBMS_OUTPU