oracle 表 自动值,oracle根据一张表的值更新另一张表的值 简单SQL实现

一,利用SQL语句,直接更新

1.建表语句:

create table table1( idd varchar2(10) , val varchar2(20) );

create table table2( idd varchar2(10), val varchar2(20) );

2.插入数据:

insert into table1 values ('01','1111');

insert into table1 values ('02','222');

insert into table1 values ('02','2222');

insert into table1 values ('03','3333');

insert into table1 values ('04','4444');

insert into table1 values ('06','6666');

commit;

insert into table2 values ('01','aaaa');

insert into table2 values ('02','bbbb');

insert into table2 values ('03','cccc');

insert into table2 values ('04','dddd');

insert into table2 values ('05','eee');

commit;

3.两张表如下图:

4e7a95de2daa1c5cce285530c073e7e8.png

baa94fc0150c74b22cc63d71d004c034.png

update table1 set table1.val = (select val from table2 where table1.idd = table2.idd);

结果如下:

5873a3daeeba7b2ef1c6e0da481ce0fb.png

这有个问题  06 在table2表中,没有,但是照样被更新了,

SQL改进:

update table1 set val = (select val from table2 where table1.idd = table2.idd) where exists (select 1 from table2 where table1.idd = table2.idd)

d9c42adbdc55aa7a97e3b09e65134d12.png

二 利用存储过程更新

我们还是初始化数据

drop table table1;

drop table table2;

create table table1(

idd varchar2(10) ,

val varchar2(20)

);

create table table2(

idd varchar2(10),

val varchar2(20)

);

insert into table1 values ('01','1111');

insert into table1 values ('02','222');

insert into table1 values ('02','2222');

insert into table1 values ('03','3333');

insert into table1 values ('04','4444');

insert into table1 values ('06','6666');

commit;

insert into table2 values ('01','aaaa');

insert into table2 values ('02','bbbb');

insert into table2 values ('03','cccc');

insert into table2 values ('04','dddd');

insert into table2 values ('05','eee');

commit;

select * from table1;

select * from table2;

结果如下:

2f7e2443a845924d46acc31bd0c6ba52.png    

6e9ff78e14c852ab1c4139580a1573fd.png

存储过程如下:

CREATE OR REPLACE PROCEDURE test3 is

cursor uat_over_cursor is

select * from      table2;

uatover uat_over_cursor%ROWTYPE;

begin

for uatover in uat_over_cursor LOOP

update table1 set table1.val=uatover.val where table1.idd= uatover.idd;

end LOOP;

end;

调用存储过程:

call test3();

select * from table1;

select * from table2;

结果如下:

2c7ebdfe3226f7d3bbfa9487cd26a2e1.png  

6eea666730b764d3ce7d9b6b288ea417.png

同样达到了我们想要的效果,存储过程ETL必不可少的工具哈

本文分享 CSDN - wangyonglin1123。

如有侵权,请联系 support@oschina.cn 删除。

本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。