一,利用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.两张表如下图:
update table1 set table1.val = (select val from table2 where table1.idd = table2.idd);
结果如下:
这有个问题 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)
二 利用存储过程更新
我们还是初始化数据
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;
结果如下:
存储过程如下:
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;
结果如下:
同样达到了我们想要的效果,存储过程ETL必不可少的工具哈