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;