ORACLE: BULK COLLECT批量处理

ORACLE批量更新大数据量操作bulk collect与forall

参考： https://blog.csdn.net/ITdevil/article/details/94582857

%ROWTYPE 类型声明：
-- 规则： 变量名 表名%ROWTYPE (表示声明的变量类型与表OE_ORDER_HEADERS_ALL中的一条记录类型相同)
v_order_header_rec ont.oe_order_headers_all%ROWTYPE;

-- PLS_INTEGER精度范围在-2^31~2^21,超过精度范围会抛出异常，
-- 但在算数运算时其速度快，常用作计数器
I_COUNT PLS_INTEGER :=0;
-- 声明一个集合类型的变量，该集合中的每个元素的类型与
-- 表HR_EMPLOYEES中的字段EMPLOYEE_NUMBER是同一类型
TYPE EMPLOYEE_NUMBER_TYPE IS TABLE OF HR_EMPLOYEES.EMPLOYEE_NUMBER%TYPE ;
-- 使用时 实例化
employee_number_tb EMPLOYEE_NUMBER_TYPE;


二、BULK COLLECT 与FORALL
1、前提：表中的数据量大，要更新某个字段的值，需要更新的数据量也很大，这时可以考虑使用提取游标，批量更新的操作。
至于 BULK COLLECT 和 FORALL IN 提高性能的原因可参考https://www.jianshu.com/p/4f06b943ff73
涉及使用到的关键字及语法如下：
-- 语法规则：从澊 CURSORXXX批量提取NNN条记录放入集合变量COL1, COL2中
--效率：比循环逐条从游标中取数的效率要高
FETCH CURSORXXX BULK COLLECT INT COL1, COL2 LIMIT nnn;

-- 批量绑定，相当于同时执行 count个SELECT、UPDATE、DELETE的操作，对性能有显著提高
FORALL i IN 1..col1.count
update table_name set colxx = '赋新的值' WHERE COLxyz = '条件值';

例子表结构：

CREATE TABLE CUX.KL_ITEM_701
(
  INVENTORY_ITEM_ID  NUMBER,
  ORGANIZATION_ID    NUMBER,
  ITEM_CODE          VARCHAR2(50 BYTE),
  DESCRIPTION        VARCHAR2(500 BYTE),
  ORGANIZATION_CODE  VARCHAR2(50 BYTE),
  ATTRIBUTE1     VARCHAR2(150),
  ATTRIBUTE2     VARCHAR2(150),
  ATTRIBUTE3     VARCHAR2(150),
  ATTRIBUTE4     VARCHAR2(150),
  ATTRIBUTE5     VARCHAR2(150),
  ATTRIBUTE6     VARCHAR2(150),
  ATTRIBUTE7     VARCHAR2(150),
  ATTRIBUTE8     VARCHAR2(150),
  ATTRIBUTE9     VARCHAR2(150),
  IMPORT_STATUS  VARCHAR2(20),
  IMPORT_MESSAGE VARCHAR2(2000),
  CREATION_DATE      DATE                       DEFAULT SYSDATE,
  CREATED_BY         NUMBER                     DEFAULT -1,
  LAST_UPDATE_DATE   DATE                       DEFAULT SYSDATE,
  LAST_UPDATED_BY    NUMBER                     DEFAULT -1,
  LAST_UPDATE_LOGIN  NUMBER                     DEFAULT -1
);

　　

BULK COLLECT 实例：

declare
   -- 声明一个集合类型的变量，该集合中的每个元素的类型与表kl_item_701中的字段item_code是同一类型
   type item_code_type is table of cux.kl_item_701.item_code%type index by binary_integer;
   type description_type is table of cux.kl_item_701.description%type index by binary_integer;
   type attribute2_type is table of cux.kl_item_701.attribute2%type index by binary_integer;
   -- 使用前先实例化
   item_code_tb item_code_type;
   description_tb description_type;
   attribute2_tb attribute2_type;
   -- 定义计数器,并初始值0。
   V_COUNTER PLS_INTEGER :=0;
   -- 定义游标 
   cursor c_ki is
   select item_code, description, attribute2 from cux.kl_item_701 ki
   where ki.import_status IN ('SUCCESSED')
    and ki.item_code like '160110044___'
    ORDER BY ITEM_CODE;
   begin
     OPEN C_KI ;
     LOOP
     -- 每次从游标中提取200条数据
     FETCH C_KI BULK COLLECT 
     INTO ITEM_CODE_TB, DESCRIPTION_TB, ATTRIBUTE2_TB
     LIMIT 200;
     -- 循环到最后一次时，防止游标中的记录数不满足200条面提取不出来
     EXIT WHEN ITEM_CODE_TB.COUNT = 0;
     -- 批量更新操作 ，将 字段2内容赋给 字段9
     FORALL i  IN 1..ITEM_CODE_TB.COUNT 
     UPDATE CUX.KL_ITEM_701 KI
       SET KI.ATTRIBUTE9 = ATTRIBUTE2_TB(i),
           ki.last_update_date = sysdate 
       WHERE ITEM_CODE = ITEM_CODE_TB(I)
     ;
        dbms_lock.sleep(10); -- 处理一批（200行），等待10秒 ,以便观察变化的效果
        v_counter := v_counter +1;
        DBMS_OUTPUT.PUT_LINE('提交了 ' || v_counter ||' 次。');
    END LOOP;
    CLOSE C_KI; -- 关闭游标 
    commit;
   end;