Oracle 游标逐行处理 改写为 大表直接关联 cursor loop left join merge into

以下案例及写法来自 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
begin proc_name; end;
   
select *from tb_mbi_temp2   
      
二、 存储过程逐行更新
原文中的存储过程代码无法直接执行，更正了部分报错的地方

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，可以参考 Oracle 关联更新 update_Hehuyi_In的博客-CSDN博客

按照文章，这里给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批量更新速度会远远快于循环执行，并且还可以加并行
————————————————

                            版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
                        
原文链接：https://blog.csdn.net/Hehuyi_In/article/details/123969861

Merge into（Oracle 9i引入的功能）语法 

merge into 目标表 a
 
using 源表 b
 
on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)  
 
when matched then update set a.更新字段=b.字段
 
when  not macthed then insert into a(字段1,字段2……)values(值1,值2……)
 

有时候在做一些根据号码排序的需求的时候，除了在需求当插入一列序号，然后用left join 最后在查询数据的时候order by序号，再就是利用merge into的方法进行数据的获取，如果不同的表中有不同的数据，需要一列一列的手动改，也是稍微有些麻烦的，不知道有没有办法可以自动修改merge into

/*

   Desc:Merge 与Left join 区别

*/
 
--tb_Employ:员工表   --tb_Depart:部门表
--1:left join
insert into 表
select * from  tb_depart t1  left join tb_Employ t2
on t1.DepartNo = t2.DepartNo
where t2.DepartNo is null
 
--Merge
merge into tb_Employ t1
using tb_Depart t2 on t1.DepartNo = t2.DepartNo
when matched then
update  set t1.Age =7
when not matched by target then
insert (EmployNo,EmployName,DepartNo,Age,Sex)
values('005','e','003',6,'男')
when not matched by source then delete
output $action as [action],
inserted.EmployNo ,inserted.EmployName ,inserted.DepartNo ,
inserted.Age,inserted.sex,deleted.EmployNo,deleted.EmployName,
deleted.DepartNo,deleted.Age,deleted.sex;
go