PL/SQL批处理语句：BULK COLLECT 和 FORALL

   PL/SQL程序中运行SQL语句是存在开销的，因为SQL语句是要提交给SQL引擎处理，这种在PL/SQL引擎和SQL引擎之间的控制转移叫做上下文却换，每次却换时，都有额外的开销
       请看下图：

       

       但是，FORALL和BULK COLLECT可以让PL/SQL引擎把多个上下文却换压缩成一个，这使得在PL/SQL中的要处理多行记录的SQL语句执行的花费时间骤降
       请再看下图：

       

BULK COLLECT 加速查询

采用BULK COLLECT可以将查询结果一次性地加载到collections中，而不是通过cursor一条一条地处理
 可以在select into ，fetch into ， returning into语句使用BULK COLLECT
 注意在使用BULK COLLECT时，所有的INTO变量都必须是collections

select into语句中使用bulk collect

DECLARE TYPE sallist IS TABLE OF employees.salary%TYPE;sals sallist; BEGINSELECT salary BULK COLLECT INTO sals FROM employees where rownum<=50;--接下来使用集合中的数据 END; /

在fetch into中使用bulk collect

DECLARETYPE deptrectab IS TABLE OF departments%ROWTYPE;dept_recs deptrectab;CURSOR cur IS SELECT department_id,department_name FROM departments where department_id>10; BEGINOPEN cur;FETCH cur BULK COLLECT INTO dept_recs;--接下来使用集合中的数据 END; /

returning into中使用bulk collect

CREATE TABLE emp AS SELECT * FROM employees;DECLARE TYPE numlist IS TABLE OF employees.employee_id%TYPE;enums numlist;TYPE namelist IS TABLE OF employees.last_name%TYPE;names namelist; BEGINDELETE emp WHERE department_id=30RETURNING employee_id,last_name BULK COLLECT INTO enums,names;DBMS_OUTPUT.PUT_LINE('deleted'||SQL%ROWCOUNT||'rows:');FOR i IN enums.FIRST .. enums.LASTLOOPDBMS_OUTPUT.PUT_LINE('employee#'||enums(i)||':'||names(i));END LOOP; END; /deleted6rows: employee#114:Raphaely employee#115:Khoo employee#116:Baida employee#117:Tobias employee#118:Himuro employee#119:Colmenares

 BULK COLLECT 对大数据DELETE UPDATE的优化

DECLARE --按rowid排序的cursor --删除条件是oo=xx，这个需根据实际情况来定CURSOR mycursor IS SELECT rowid FROM t WHERE OO=XX ORDER BY rowid;TYPE rowid_table_type IS TABLE OF rowid index by pls_integer;v_rowid rowid_table_type; BEGINOPEN mycursor;LOOPFETCH mycursor BULK COLLECT INTO v_rowid LIMIT 5000;--5000行提交一次EXIT WHEN v_rowid.count=0;FORALL i IN v_rowid.FIRST..v_rowid.LASTDELETE t WHERE rowid=v_rowid(i);COMMIT;END LOOP;CLOSE mycursor; END; /

限制BULK COLLECT 提取的记录数

语法：
             FETCH cursor BULK COLLECT INTO ...[LIMIT rows]；
             其中，rows可以是常量，变量或者求值的结果是整数的表达式
             
             假设你需要查询并处理1W行数据，你可以用BULK COLLECT一次取出所有行，然后填充到一个非常大的集合中
             可是，这种方法会消耗该会话的大量PGA，APP可能会因为PGA换页而导致性能下降
             
             这时，LIMIT子句就非常有用，它可以帮助我们控制程序用多大内存来处理数据
 

DECLARECURSOR allrows_cur IS SELECT * FROM employees;TYPE employee_aat IS TABLE OF allrows_cur%ROWTYPE INDEX BY BINARY_INTEGER;v_emp employee_aat; BEGINOPEN allrows_cur;LOOPFETCH allrows_cur BULK FETCH INTO v_emp LIMIT 100;/*通过扫描集合对数据进行处理*/FOR i IN 1 .. v_emp.countLOOPupgrade_employee_status(v_emp(i).employee_id);END LOOP;EXIT WHEN allrows_cur%NOTFOUND;END LOOP;CLOSE allrows_cur; END; /

 FORALL注意事项

使用FORALL时，应该遵循如下规则：

FORALL语句的执行体，必须是一个单独的DML语句，比如INSERT，UPDATE或DELETE。

不要显式定义index_row，它被PL/SQL引擎隐式定义为PLS_INTEGER类型，并且它的作用域也仅仅是FORALL。

这个DML语句必须与一个集合的元素相关，并且使用FORALL中的index_row来索引。注意不要因为index_row导致集合下标越界。

lower_bound和upper_bound之间是按照步进 1 来递增的。

在sql_statement中，不能单独地引用集合中的元素，只能批量地使用集合。

在sql_statement中使用的集合，下标不能使用表达式。

BULK COLLECT介绍

BULK COLLECT子句会批量检索结果，即一次性将结果集绑定到一个集合变量中，并从SQL引擎发送到PL/SQL引擎。

通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。下面逐一描述BULK COLLECT在这几种情形下的用法

BULK COLLECT的注意事项

BULK COLLECT INTO 的目标对象必须是集合类型。

只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一个不支持这个特性的错误。

不能对使用字符串类型作键的关联数组使用BULK COLLECT子句。

复合目标(如对象类型)不能在RETURNING INTO子句中使用。

如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK COLLECT INTO子句中使用。

如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO子句中

DECLARE CURSOR cur IS select * from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = '2021-03-09'and pushstate='3'; TYPE rec IS TABLE OF NEWLOG4%ROWTYPE; recs rec; BEGIN OPEN cur; WHILE (TRUE) LOOP FETCH cur BULK COLLECT INTO recs LIMIT 5000; FORALL i IN 1 .. recs.COUNT INSERT INTO NEWLOG4_202103 VALUES recs (i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;END;INSERT/*+parallel(10)*/ INTO NEWLOG4_202103 select * from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = '2021-03-09'and pushstate='3';delete /*+parallel(10)*/ from NEWLOG4 nologging where TO_CHAR(autudt,'YYYY-MM-DD') = '2021-03-09'and pushstate='3';INSERT/*+parallel(10)*/ INTO NEWLOG4_202103 select * from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = '2021-03-09'and pushstate='3';alter session enable parallel dml; DECLARE CURSOR cur IS select/*+parallel(8)*/ rowid from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = '2021-03-03'and pushstate='3'; --TYPE rec IS TABLE OF NEWLOG4%ROWTYPE; TYPE rowid_table_type IS TABLE OF rowid index by pls_integer;v_rowid rowid_table_type; --recs rec; BEGIN OPEN cur; WHILE (TRUE) LOOP FETCH cur BULK COLLECT INTO v_rowid LIMIT 1000; EXIT WHEN v_rowid.count=0; FORALL i IN 1 .. v_rowid.COUNT ---delete NEWLOG4 where current of recs (i); delete/*+parallel(8)*/ from NEWLOG4 nologging where rowid=v_rowid (i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;END;DECLARE v_exists NUMBER (10, 0); v_exists1 NUMBER (10, 0); --recs rec; BEGIN select /*+parallel(12)*/ count(1)into v_exists from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-dd') = '2021-03-06'and pushstate='3'; WHILE (TRUE) LOOPdelete /*+parallel(12)*/ from NEWLOG4 nologging where TO_CHAR(autudt,'YYYY-MM-DD') = '2021-03-06'and pushstate='3';EXIT WHEN v_exists1=v_exists+1;v_exists1:= v_exists1+1;if (v_exists=10000) thenCOMMIT;end if;END LOOP;COMMIT;END;select count(1) from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = '2021-03-01' select b.sid, b.username, b.serial#, a.spid, b.paddr, c.sql_text, b.machinefrom v$process a, v$session b, v$sqlarea cwhere a.addr = b.paddrand b.sql_hash_value = c.hash_value; CREATE OR REPLACE PROCEDURE NEWLOG4_SUB_TABLE2 (delete_date in varchar2) IS-- table_name1 VARCHAR2(50);-- create_table_sql VARCHAR2(4000); -- insert_data_sql VARCHAR2(4000);delete_data_sql VARCHAR2(4000); -- v_exists INT:=0;-- v_exists NUMBER (10, 0);CURSOR cur IS select * from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = delete_date and pushstate='3'; TYPE rec IS TABLE OF NEWLOG4%ROWTYPE; recs rec; BEGIN--将FATHER_TABLE表中取上月记录 添加到新创建的分表中。-- insert_data_sql := 'INSERT INTO ' || table_name1 || ' SELECT * FROM NEWLOG4 WHERE autudt <(systimestamp - NUMTODSINTERVAL(30,''day'')) and pushstate=''3''';-- EXECUTE IMMEDIATE insert_data_sql; OPEN cur; WHILE (TRUE) LOOP FETCH cur BULK COLLECT INTO recs LIMIT 5000; FORALL i IN 1 .. recs.COUNT INSERT INTO NEWLOG4_202103_10 VALUES recs (i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;--删除FATHER_TABLE表中时间在上个月范围内的所有数据--delete_data_sql := 'DELETE FROM NEWLOG4 WHERE autudt <(systimestamp-NUMTODSINTERVAL(30,''day''))and pushstate=''3''';delete_data_sql :='delete /*+parallel(10)*/ from NEWLOG4 nologging where TO_CHAR(t.autudt,''YYYY-MM-DD'') = delete_date and pushstate=''3''';EXECUTE IMMEDIATE delete_data_sql;COMMIT;--EXCEPTION--WHEN OTHERS THEN-- ROLLBACK; END NEWLOG4_SUB_TABLE2; CREATE OR REPLACE PROCEDURE NEWLOG4_SUB_TABLE IStable_name1 VARCHAR2(50);create_table_sql VARCHAR2(4000);insert_data_sql VARCHAR2(4000);delete_data_sql VARCHAR2(4000); -- v_exists INT:=0;v_exists NUMBER (10, 0); BEGINSELECT 'NEWLOG4_' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') INTO table_name1 FROM DUAL;select count(1) into v_exists from user_tables where table_name=UPPER(table_name1);--dbms_output.put_line(sname);--dbms_output.put_line(table_name1);if (v_exists <1)then-- dbms_output.put_line(sname);create_table_sql := 'create table ' || table_name1 || ' (autudt TIMESTAMP(6),authentype VARCHAR2(2000),userid VARCHAR2(2000),orgid VARCHAR2(2000),org2id VARCHAR2(2000),realname VARCHAR2(2000),success VARCHAR2(2000),idpname VARCHAR2(2000),idpip VARCHAR2(2000),vistorip VARCHAR2(2000),vistorbrowser VARCHAR2(2000),spid VARCHAR2(2000),spurl VARCHAR2(2000),info VARCHAR2(2000),autdesc VARCHAR2(2000),taketime VARCHAR2(2000),orgnamefullpath VARCHAR2(2000),ines INTEGER default 0,logid VARCHAR2(32),inputaccount VARCHAR2(2000),channel NUMBER(32),pushstate NUMBER(2) default 0,appid VARCHAR2(50))';EXECUTE IMMEDIATE create_table_sql;commit;end if;--将FATHER_TABLE表中取上月记录 添加到新创建的分表中。insert_data_sql := 'INSERT INTO ' || table_name1 || ' SELECT * FROM NEWLOG4 WHERE autudt <(systimestamp - NUMTODSINTERVAL(30,''day'')) and pushstate=''3''';EXECUTE IMMEDIATE insert_data_sql;--删除FATHER_TABLE表中时间在上个月范围内的所有数据delete_data_sql := 'DELETE FROM NEWLOG4 WHERE autudt <(systimestamp-NUMTODSINTERVAL(30,''day''))and pushstate=''3''';EXECUTE IMMEDIATE delete_data_sql;COMMIT;--EXCEPTION--WHEN OTHERS THEN-- ROLLBACK; END NEWLOG4_SUB_TABLE; CREATE OR REPLACE PROCEDURE NEWLOG4_day_TABLE (delete_date in varchar2) ISinsert_data_sql VARCHAR2(4000);delete_data_sql VARCHAR2(4000);CURSOR cur IS select * from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = delete_date and pushstate='3'; TYPE rec IS TABLE OF NEWLOG4%ROWTYPE; recs rec; BEGIN OPEN cur; WHILE (TRUE) LOOP FETCH cur BULK COLLECT INTO recs LIMIT 5000; FORALL i IN 1 .. recs.COUNT --EXECUTE IMMEDIATE insert_data_sql; INSERT INTO NEWLOG4_DAY_INTERVAL_PARTITION VALUES recs (i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;--EXCEPTION--WHEN OTHERS THEN-- ROLLBACK; END NEWLOG4_day_TABLE;

 

 

 

 

 

 

总结

以上是生活随笔为你收集整理的PL/SQL批处理语句：BULK COLLECT 和 FORALL的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。