PL/SQL批处理语句(二)FORALL
PL/SQL批处理语句(二)FORALL
我们知道PL/SQL程序中运行SQL语句是存在开销的,因为SQL语句是要提交给SQL引擎处理,这种在PL/SQL引擎和SQL引擎之间的控制转移叫做上下文却换,每次却换时,都有额外的开销。
但是,FORALL和BULK COLLECT可以让PL/SQL引擎把多个上下文却换压缩成一个,这使得在PL/SQL中的要处理多行记录的SQL语句执行的花费时间骤降。
一、FORALL语法描述
FORALL loop_counter IN bounds_clause -->注意FORALL块内不需要使用loop, end loop
SQL_STATEMENT [SAVE EXCEPTIONS];
参数:
bounds_clause的形式
lower_limit .. upper_limit -->指明循环计数器的上限和下限,与for循环类似
INDICES OF collection_name BETWEEN lower_limit .. upper_limit -->引用特定集合元素的下标(该集合可能为稀疏)
VALUES OF colletion_name -->引用特定集合元素的值
SQL_STATEMENT部分:SQL_STATEMENT部分必须是一个或者多个集合的静态或者动态的DML(insert,update,delete)语句。
SAVE EXCEPTIONS部分:对于SQL_STATEMENT部分导致的异常使用SAVE EXCEPTIONS来保证异常存在时语句仍然能够继续执行。
二、使用 FORALL 代替 FOR 循环(提高性能)
-->下面的示例使用了FOR循环与FORALL循环操作进行对比,使用FORALL完成同样的功能,性能明显提高 CREATE TABLE t ( col_num NUMBER ,col_var VARCHAR2( ) ); DECLARE TYPE col_num_type IS TABLE OF NUMBER -->声明了两个联合数组 INDEX BY PLS_INTEGER; TYPE col_var_type IS TABLE OF VARCHAR2( ) INDEX BY PLS_INTEGER; col_num_tab col_num_type; col_var_tab col_var_type; v_start_time INTEGER; v_end_time INTEGER; BEGIN FOR i IN .. -->使用FOR循环向数组填充元素 LOOP col_num_tab( i ) := i; col_var_tab( i ) := 'var_' || i; END LOOP; v_start_time := DBMS_UTILITY.get_time; -->获得FOR循环向表t插入数据前的初始时间 FOR i IN 1 .. 5000 -->使用FOR循环向表t插入数据 LOOP INSERT INTO t VALUES ( col_num_tab( i ), col_var_tab( i ) ); END LOOP; v_end_time := DBMS_UTILITY.get_time; DBMS_OUTPUT.put_line( 'Duration of the FOR LOOP: ' || ( v_end_time - v_start_time ) ); v_start_time := DBMS_UTILITY.get_time; FORALL i IN 1 .. 5000 -->使用FORALL循环向表t插入数据 INSERT INTO t VALUES ( col_num_tab( i ), col_var_tab( i ) ); v_end_time := DBMS_UTILITY.get_time; DBMS_OUTPUT.put_line( 'Duration of the FORALL STATEMENT: ' || ( v_end_time - v_start_time ) ); COMMIT; END; Duration of the FOR LOOP: -->此处的计时单位为百分之一秒,即0.68s,下同 Duration of the FORALL STATEMENT: PL/SQL procedure successfully completed.
三、SAVE EXCEPTIONS(异常处理)
对于任意的SQL语句执行失败,将导致整个语句或整个事务会滚。而使用SAVE EXCEPTIONS可以使得在对应的SQL语句异常的情形下,FORALL仍然可以继续执行。如果忽略了SAVE EXCEPTIONS时,当异常发生,FORALL语句就会停止执行。因此SAVE EXCEPTIONS使得FORALL子句中的DML下产生的所有异常都将记录在SQL%BULK_EXCEPTIONS的游标属性中。SQL%BULK_EXCEPTIONS属性是个记录集合,其中的每条记录由两个字段组成,ERROR_INDEX和ERROR_CODE。ERROR_INDEX字段会存储发生异常的FORALL语句的迭代编号,而ERROR_CODE则存储对应异常的ORACLE错误代码。类似于这样:(2,01400),(6,1476)和(10,12899)。存放在%BULK_EXCEPTIONS中的值总是与最近一次FORALL语句执行的结果相关,异常的个数存放在%BULK_EXCEPTIONS的COUNT属性中,%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。
1、%BULK_EXCEPTIONS的用法
CREATE TABLE tb_emp AS -->创建表tb_emp SELECT empno, ename, hiredate FROM emp WHERE = ; ALTER TABLE tb_emp MODIFY(empno NOT NULL); -->为表添加约束 DECLARE TYPE col_num_type IS TABLE OF NUMBER -->一共定义了3个联合数组类型 INDEX BY PLS_INTEGER; TYPE col_var_type IS TABLE OF VARCHAR2( ) INDEX BY PLS_INTEGER; TYPE col_date_type IS TABLE OF DATE INDEX BY PLS_INTEGER; empno_tab col_num_type; ename_tab col_var_type; hiredate_tab col_date_type; v_counter PLS_INTEGER := ; v_total INTEGER := ; errors EXCEPTION; -->声明异常 PRAGMA EXCEPTION_INIT( errors, - ); BEGIN FOR rec IN ( SELECT empno, ename, hiredate FROM emp ) -->使用for循环将数据填充到联合数组 LOOP v_counter := v_counter + ; empno_tab( v_counter ) := rec.empno; ename_tab( v_counter ) := rec.ename; hiredate_tab( v_counter ) := rec.hiredate; END LOOP; empno_tab( ) := NULL; -->对部分数据进行处理以产生异常 ename_tab( ) := RPAD( ename_tab( ), , '*' ); empno_tab( ) := NULL; FORALL i IN .. empno_tab.COUNT -->使用forall将联合数组中的数据插入到表tb_emp SAVE EXCEPTIONS INSERT INTO tb_emp VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) ); COMMIT; SELECT COUNT( * ) INTO v_total FROM tb_emp; DBMS_OUTPUT.put_line( v_total || ' rows were inserted to tb_emp' ); EXCEPTION WHEN errors THEN DBMS_OUTPUT.put_line( 'There are ' || SQL%bulk_exceptions.COUNT || ' exceptions' ); FOR i IN .. SQL%bulk_exceptions.COUNT -->SQL%bulk_exceptions.COUNT记录异常个数来控制迭代 LOOP DBMS_OUTPUT. put_line( 'Record ' || SQL%bulk_exceptions( i ).error_index || ' caused error ' || i || ': ' || SQL%bulk_exceptions( i ).error_code || ' ' || SQLERRM( -SQL%bulk_exceptions( i ).error_code ) ); -->使用SQLERRM根据错误号抛出具体的错误信息 END LOOP; END; There are exceptions Record caused error : ORA-: cannot insert NULL into () Record caused error : ORA-: value too large for column (actual: , maximum: ) Record caused error : ORA-: cannot insert NULL into () PL/SQL procedure successfully completed.
2、%BULK_ROWCOUNT
%BULK_ROWCOUNT也是专门为FORALL设计的,用于保存第i个元素第i次insert或update或delete所影响到的行数。如果第i次操作没有行被影响,则%BULK_ROWCOUNT返回为零值。FORALL语句和%BULK_ROWCOUNT属性使用同样的下标索引。如果FORALL使用下标索引的范围在5到8的话,那么%BULK_ROWCOUNT的也是5到8。需要注意的是一般情况下,对于insert .. values而言,所影响的行数为1,即%BULK_ROWCOUNT的值为1。而对于insert .. select方式而言,%BULK_ROWCOUNT的值就有可能大于1。update与delete语句存在0,1,以及大于1的情形。 DECLARE TYPE dept_tab_type IS TABLE OF NUMBER; dept_tab dept_tab_type := dept_tab_type( , , ); -->声明及初始化嵌套表 BEGIN FORALL i IN dept_tab.FIRST .. dept_tab.LAST -->使用FORALL更新 UPDATE emp SET sal = sal * 1.10 WHERE deptno = dept_tab( i ); -- COMMIT; FOR i IN .. dept_tab.COUNT -->循环输出每次执行SQL语句影响的行数 LOOP DBMS_OUTPUT.put_line( 'Dept no ' || dept_tab( i ) || ' has ' || SQL%bulk_rowcount (i) || ' rows been updated' ); END LOOP; -- Did the 3rd UPDATE statement affect any rows? IF SQL%bulk_rowcount () = THEN DBMS_OUTPUT.put_line( 'The deptno 50 has not child record' ); END IF; END; Dept no has rows been updated Dept no has rows been updated Dept no has rows been updated The deptno has not child record PL/SQL procedure successfully completed.
四、INDICES OF 选项
INDICES OF 选项用于处理稀疏集合类型。即当集合(嵌套表或联合数组)中的元素被删除之后,对稀疏集合实现迭代。
-->下面的脚本同前面的示例基本相似,所不同的是使用了delete方式删除其中的部分记录,导致集合变得稀疏。 -->其次在forall子句处使用indices OF方式来控制循环。 TRUNCATE TABLE tb_emp; DECLARE TYPE col_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER; TYPE col_var_type IS TABLE OF VARCHAR2( ) INDEX BY PLS_INTEGER; TYPE col_date_type IS TABLE OF DATE INDEX BY PLS_INTEGER; empno_tab col_num_type; ename_tab col_var_type; hiredate_tab col_date_type; v_counter PLS_INTEGER := ; v_total INTEGER := ; BEGIN FOR rec IN ( SELECT empno, ename, hiredate FROM emp ) LOOP v_counter := v_counter + ; empno_tab( v_counter ) := rec.empno; ename_tab( v_counter ) := rec.ename; hiredate_tab( v_counter ) := rec.hiredate; END LOOP; empno_tab.delete( ); -->此处删除了数组中的第二个元素,导致数组变为稀疏型 ename_tab.delete( ); hiredate_tab.delete( ); FORALL i IN indices OF empno_tab -->此处使用了indices OF empno_tab,则所有未被delete的元素都将进入循环 INSERT INTO tb_emp VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) ); COMMIT; SELECT COUNT( * ) INTO v_total FROM tb_emp; DBMS_OUTPUT.put_line( v_total || ' rows were inserted to tb_emp' ); END; rows were inserted to tb_emp PL/SQL procedure successfully completed.
五、VALUES OF 选项
VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。
VALUES OF选项使用时有一些限制
1.如果VALUES OF子句中所使用的集合是联合数组,则必须使用PLS_INTEGER和BINARY_INTEGER进行索引
2.VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER
3.当VALUES OF 子句所引用的集合为空,则FORALL语句会导致异常
TRUNCATE TABLE tb_emp; CREATE TABLE tb_emp_ins_log AS -->创建一张与tb_emp结构类似的表tb_emp_ins_log SELECT * FROM tb_emp WHERE = ; ALTER TABLE tb_emp_ins_log MODIFY(ename VARCHAR2()); -->修改列ename的长度 DECLARE TYPE col_num_type IS TABLE OF tb_emp.empno%TYPE INDEX BY PLS_INTEGER; TYPE col_var_type IS TABLE OF VARCHAR2( ) INDEX BY PLS_INTEGER; TYPE col_date_type IS TABLE OF tb_emp.hiredate%TYPE INDEX BY PLS_INTEGER; TYPE ins_log_type IS TABLE OF PLS_INTEGER -->此处较之前的示例多声明了一个联合数组 INDEX BY PLS_INTEGER; -->用于填充异常记录的元素值 empno_tab col_num_type; ename_tab col_var_type; hiredate_tab col_date_type; ins_log_tab ins_log_type; v_counter PLS_INTEGER := ; v_total INTEGER := ; errors EXCEPTION; PRAGMA EXCEPTION_INIT( errors, - ); BEGIN FOR rec IN ( SELECT empno, ename, hiredate FROM emp ) LOOP v_counter := v_counter + ; empno_tab( v_counter ) := rec.empno; ename_tab( v_counter ) := rec.ename; hiredate_tab( v_counter ) := rec.hiredate; END LOOP; ename_tab( ) := RPAD( ename_tab( ), , '*' ); -->使记录2与记录5的ename列长度变长而产生异常 ename_tab( ) := RPAD( ename_tab( ), , '*' ); empno_tab( ) := NULL; -->使第6条记录的empno为NULL值,由于表tb_emp的empno不允许为NULL而产生异常 FORALL i IN .. empno_tab.COUNT SAVE EXCEPTIONS INSERT INTO tb_emp VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) ); COMMIT; EXCEPTION WHEN errors THEN FOR i IN .. SQL%bulk_exceptions.COUNT LOOP ins_log_tab( i ) := SQL%bulk_exceptions( i ).error_index; -->异常记录的索引值将填充ins_log_type联合数组 END LOOP; -->此处的结果是ins_log_tab()=, ins_log_tab()=, ins_log_tab()= FORALL i IN VALUES OF ins_log_tab -->使用VALUES OF子句为ins_log_type联合数组中的元素值 INSERT INTO tb_emp_ins_log VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) ); -->因此values中的i分别为2和5 COMMIT; END; PL/SQL procedure successfully completed. -->异常的记录被插入到表tb_emp_ins_log select * from tb_emp_ins_log; EMPNO ENAME HIREDATE ---------- -------------------------------------------------- --------- Henry********** -DEC- JONES********** -APR- MARTIN -SEP-
最新文章
- xenserver磁盘扩容扩不大问题解决
- asp.net mvc 后台怎么接受前端返回的array list dictionary
- webstorm安装破解版
- IEEE会议排名(转载)
- MotionEvent的getX(),getY()与getRawX(),getRawY()区别
- 2014年辛星解读Javascript之DOM之冒泡和捕获
- 跟我extjs5(38--单个模块的设计[6获得模块列表数据])
- Struts2 拦截器—拦截action
- 通过 Chrome 在 Windows 中调试运行在 iphone-safari 上的 页面
- rewirte 规则
- Angular(2+) 国际化方案(ngx-translate)
- uva140
- BZOJ_2134_单选错位——期望DP
- Scala思维导图
- 一个简单的开源PHP爬虫框架『Phpfetcher』
- 为什么不应该使用ZooKeeper做服务发现
- 找不到类SimpleJdbcTemplate ParameterizedRowMapper cannot be resolved
- JavaScript 闭包原理分析
- MySql数据的插入-replace into
- java 远程调试 remote java application