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

最新推荐文章于 2021-11-08 15:59:24 发布

凤舞飘伶

最新推荐文章于 2021-11-08 15:59:24 发布

阅读量406

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/woshaguayi/article/details/117804066

版权

oracle 专栏收录该内容

64 篇文章 2 订阅

订阅专栏

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;
BEGIN
  SELECT salary BULK COLLECT INTO sals FROM employees where rownum<=50;
  --接下来使用集合中的数据
END;
/

在fetch into中使用bulk collect

DECLARE
  TYPE deptrectab IS TABLE OF departments%ROWTYPE;
  dept_recs deptrectab;
  CURSOR cur IS SELECT department_id,department_name FROM departments where department_id>10;
BEGIN
  OPEN 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;
BEGIN
  DELETE emp WHERE department_id=30
  RETURNING employee_id,last_name BULK COLLECT INTO enums,names;
  DBMS_OUTPUT.PUT_LINE('deleted'||SQL%ROWCOUNT||'rows:');
  FOR i IN enums.FIRST .. enums.LAST
    LOOP
      DBMS_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;
BEGIN
  OPEN mycursor;
  LOOP
    FETCH mycursor BULK COLLECT INTO v_rowid LIMIT 5000;--5000行提交一次
    EXIT WHEN v_rowid.count=0;
    FORALL i IN v_rowid.FIRST..v_rowid.LAST
      DELETE 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子句就非常有用，它可以帮助我们控制程序用多大内存来处理数据

DECLARE
  CURSOR allrows_cur IS SELECT * FROM employees;
  TYPE employee_aat IS TABLE OF allrows_cur%ROWTYPE INDEX BY BINARY_INTEGER;
  v_emp employee_aat;
BEGIN
  OPEN allrows_cur;
  LOOP
    FETCH allrows_cur BULK FETCH INTO v_emp LIMIT 100;
    
    /*通过扫描集合对数据进行处理*/
    FOR i IN 1 .. v_emp.count
      LOOP
        upgrade_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) LOOP
 delete /*+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) then
    COMMIT;
    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.machine
 
from v$process a, v$session b, v$sqlarea c
 
where a.addr = b.paddr
 
and 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
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);
BEGIN

  SELECT '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)
IS
  insert_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;

凤舞飘伶

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
PL/SQL批处理语句：BULK COLLECT 和 FORALL

这种在PL/SQL引擎和SQL引擎之间的控制转移叫做上下文却换，每次却换时，都有额外的开销请看下图：但是，FORALL和BULK COLLECT可以让PL/SQL引擎把多个上下文却换压缩成一个，这使得在PL/SQL中的要处理多行记录的SQL语句执行的花费时间骤降请再看下图：...
复制链接

扫一扫