create procedure inser_pro as num number; cursor t_sor is select * from table2; begin for v_sor in t_sor loop num:=0; select count(1) into num from table1 where id=v_sor.id; if num<>0 then update table1 set col1=v_sor.col1,col2=v_sor.col2,... where id=v_sor.id; else insert into table1 select * from table2 where id=v_sor.id; end if; end loop; end; /
不用过程也可以 ^_^>create table t1(id int primary key,value varchar2(10));表已创建。^_^>create table t2(id int primary key,value varchar2(10));表已创建。^_^>insert into t1 values(1,'hello');已创建 1 行。^_^>insert into t2 values(2,'world');已创建 1 行。^_^>insert into t1 values(2,'world');已创建 1 行。^_^>insert into t2 values(3,'other');已创建 1 行。^_^>select * from t1; ID VALUE ---------- ---------- 1 hello 2 world^_^>select * from t2; ID VALUE ---------- ---------- 2 world 3 other^_^>delete from t1 where id in (select id from t2);已删除 1 行。^_^>select * from t1; ID VALUE ---------- ---------- 1 hello^_^>insert into t1 select * from t2;已创建2行。^_^>select * from t1; ID VALUE ---------- ---------- 1 hello 2 world 3 other
to beckhambobo(beckham): 我也听说有这个特性,所以上来问一下。to more_zou(小人物) : 因为表1做为了另一张表的主表,并且是级联删除,所以不能采用先删除表1记录的办法。
insert into TABLE1 SELECT * FROM TABLE2 where talbe2.sss not in(select table1.sss from table1)
merge into table1 a using(select * from table2 b) on (a.id=b.id) when matched then update set fno='old' when not matched then insert into values(a.id,'new');
SELECT COUNT(*) FROM TABLE1 WHERE BB='ORACLE' INTO WK_COUNT; IF WK_COUNT <> 0 THEN DELETE * FROM TABLE1 WHERE BB='ORACLE'; END IF; INSERT INTO TABLE1 SELECT * FROM TABLE2 WHERE BB='ORACLE';
to clzzcl(倾城之恋(SDK)) : 这条语句只能实现插入新的不同记录的功能,可不能实现更新的功能。to 最帅的贝克汉姆 beckhambobo(beckham): 你的两个方法都可以,谢谢!分已散,谢谢大家!
as
num number;
cursor t_sor is
select * from table2;
begin
for v_sor in t_sor loop
num:=0;
select count(1) into num from table1 where id=v_sor.id;
if num<>0 then
update table1 set col1=v_sor.col1,col2=v_sor.col2,... where id=v_sor.id;
else
insert into table1 select * from table2 where id=v_sor.id;
end if;
end loop;
end;
/
^_^>create table t1(id int primary key,value varchar2(10));表已创建。^_^>create table t2(id int primary key,value varchar2(10));表已创建。^_^>insert into t1 values(1,'hello');已创建 1 行。^_^>insert into t2 values(2,'world');已创建 1 行。^_^>insert into t1 values(2,'world');已创建 1 行。^_^>insert into t2 values(3,'other');已创建 1 行。^_^>select * from t1; ID VALUE
---------- ----------
1 hello
2 world^_^>select * from t2; ID VALUE
---------- ----------
2 world
3 other^_^>delete from t1 where id in (select id from t2);已删除 1 行。^_^>select * from t1; ID VALUE
---------- ----------
1 hello^_^>insert into t1 select * from t2;已创建2行。^_^>select * from t1; ID VALUE
---------- ----------
1 hello
2 world
3 other
我也听说有这个特性,所以上来问一下。to more_zou(小人物) :
因为表1做为了另一张表的主表,并且是级联删除,所以不能采用先删除表1记录的办法。
using(select * from table2 b)
on (a.id=b.id)
when matched then
update set fno='old'
when not matched then
insert into values(a.id,'new');
IF WK_COUNT <> 0 THEN
DELETE * FROM TABLE1 WHERE BB='ORACLE';
END IF;
INSERT INTO TABLE1 SELECT * FROM TABLE2 WHERE BB='ORACLE';
这条语句只能实现插入新的不同记录的功能,可不能实现更新的功能。to 最帅的贝克汉姆 beckhambobo(beckham):
你的两个方法都可以,谢谢!分已散,谢谢大家!