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;
标签:150,ITEM,item,--,BULK,COLLECT,VARCHAR2,ORACLE,type From: https://www.cnblogs.com/samrv/p/17855196.html