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 = '条件值';

例子表结构:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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 实例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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;

  

 

posted @   samrv  阅读(503)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
历史上的今天:
2020-11-25 Oracle- 优化SQL(二)
点击右上角即可分享
微信分享提示