Oracle 游标逐行处理 改写为 大表直接关联

以下案例及写法来自 triger liu《专题培训-SQL写法与改写》，有部分个人测试及删改，原文请参考原课程。

一、 创建测试表

create table tb_mbi_temp2(ofr_id int);
create table tb_bil_mbi_day(ofr_id int,ofr_code int);

insert into tb_bil_mbi_day values(7902,800);
insert into tb_bil_mbi_day values(7698,1600);
insert into tb_bil_mbi_day values(7698,1250);
insert into tb_bil_mbi_day values(7839,2975);
commit;

create table tb_prd_ofr(ofr_id int,ofr_code int);
insert into tb_prd_ofr values(7782,1300);
insert into tb_prd_ofr values(7698,500);
insert into tb_prd_ofr values(7902,2000);
insert into tb_prd_ofr values(7698,1000);
insert into tb_prd_ofr values(7902,3000);
commit;

-- drop table tb_bil_mbi_day;
-- drop table tb_prd_ofr;
-- drop table tb_mbi_temp2;

查看数据

SQL> select * from tb_bil_mbi_day;

    OFR_ID V_OFR_CODE
---------- ----------
      7902        800
      7698       1600
      7698       1250
      7839       2975

SQL> select * from tb_prd_ofr;

    OFR_ID V_OFR_CODE
---------- ----------
      7782       1300
      7698        500
      7902       2000
      7698       1000
      7902       3000

二、 存储过程逐行更新

原文中的存储过程代码无法直接执行，更正了部分报错的地方

CREATE OR REPLACE procedure proc_name is
sql_2 varchar2(200);
sql_4 varchar2(200);
sql_6 varchar2(200);
v_commit int;
v_ofr_id int;
v_ofr_code int;

cursor cur_ofr_id is select a.ofr_id from tb_mbi_temp2 a;
begin

sql_2:= 'insert into tb_mbi_temp2(ofr_id)
select distinct a.ofr_id from tb_bil_mbi_day a';
execute immediate sql_2;

v_commit:=0;

open cur_ofr_id;
loop
fetch cur_ofr_id into v_ofr_id;
exit when cur_ofr_id%notfound;

sql_6:='select nvl(min(ofr_code),0) from tb_prd_ofr a
where a.ofr_id = :v_ofr_id and rownum = 1';
execute immediate sql_6 into v_ofr_code using v_ofr_id;

sql_4:='update tb_bil_mbi_day a set a.ofr_code=:v_ofr_code where a.ofr_id=:v_ofr_id' ;
execute immediate sql_4 using v_ofr_code,v_ofr_id;

v_commit:=v_commit+1;
if v_commit >= 100 then
commit;

v_commit:=0;
end if;
end loop;
end;
/

首先看看这段程序干了什么

• tb_bil_mbi_day表的ofr_id去重，并插入到tb_mbi_temp2（没想明白这步有什么用，测试去掉好像没什么不同）
• 游标：从去重后的tb_mbi_temp2表中取出ofr_id
• 对游标的每行循环执行：根据游标的 v_ofr_id，取tb_prd_ofr的最小一行 ofr_code，匹配不到的设为0（这里感觉不太合理，一般是不会这样的）。rownum = 1多余了，本来就取最值。

sql_6:='select nvl(min(ofr_code),0) from tb_prd_ofr a
where a.ofr_id = :v_ofr_id and rownum = 1';
execute immediate sql_6 into v_ofr_code using v_ofr_id;

• 用最小 ofr_code 去更新 tb_bil_mbi_day表

sql_4:='update tb_bil_mbi_day a set a.ofr_code=:v_ofr_code
where a.ofr_id=:v_ofr_id' ;
execute immediate sql_4 using v_ofr_code,v_ofr_id;

• 每update 100条提交一次

执行结果

 

三、 改写为update

主要就是根据这个update，把 v_ofr_id 逐步替换为其原SQL。去重的那步没明白为什么要加，测试两个表有重复数据的时候更新结果也是一样的，目前改写的时候没有加。

sql_4:='update tb_bil_mbi_day a set a.ofr_code=:v_ofr_code
where a.ofr_id=:v_ofr_id' ;
execute immediate sql_4 using v_ofr_code,v_ofr_id;

按照文章，这里给update加了个where条件，避免没匹配到的数据被更新。返回结果跟原程序有点不同，但通常原程序那个不是真正想要的。

update tb_bil_mbi_day a
    set a.ofr_code =
        (select nvl(min(b.ofr_code), 0) min_code
           from tb_prd_ofr b
          where a.ofr_id = b.ofr_id)
  where exists (select 1 from tb_prd_ofr b where a.ofr_id = b.ofr_id);

 如果确实要更新，把update的where条件去掉就可以

update tb_bil_mbi_day a
    set a.ofr_code =
        (select nvl(min(b.ofr_code), 0) min_code
           from tb_prd_ofr b
          where a.ofr_id = b.ofr_id)

 

四、 改写为merge

按tb_prd_ofr的ofr_id字段分组，去重取与最小值同时完成

merge into tb_bil_mbi_day b
 using (select ofr_id, nvl(ofr_code, 0) as ofr_code
          from (select ofr_id,
                       ofr_code,
                       row_number() over(partition by ofr_id order by ofr_code) as rn
                  from tb_prd_ofr) a
         where rn = 1) a
 on (b.ofr_id = a.ofr_id)
 when matched then
   update set b.ofr_code = a.ofr_code;

可以看到，原文这种改写方法也是没匹配到的不更新

五、优缺点对比

• 游标循环思路简单，开发人员容易想到和实现
• 游标循环锁粒度较小，且加锁时间较短
• 在数据量大时，单条SQL批量更新速度会远远快于循环执行，并且还可以加并行

参考：tiger liu《SQL写法与改写-第二期-20201108》