假设table A (x,y),B(x,y) 是用y对应的 create or replace procedure test (s in varchar2) as v_x varchar2(20); v_y varchar2(20); col1 number(2,0); begin ---删除 delete from B where B.y not in (select y from A); commit; open myCursor for select a.x,a.y from A a; loop fetch myCursor into v_x,v_y; EXIT WHEN myCursor%NOTFOUND ; select count(b.*) into col1 from B b where b.y=v_y; if col1=0 then ---插入 insert into B (v_x,v_y); elsif col1=1 then select b.x into v_bx from B b where b.y=v_y; if v_bx<>v_x then ---更新 update B b set b.x=v_x where b.y=v_y; end if; end if; commit; end loop; end test;
create or replace procedure test (s in varchar2) as
v_x varchar2(20);
v_y varchar2(20);
col1 number(2,0);
begin
---删除
delete from B where B.y not in (select y from A);
commit;
open myCursor for
select a.x,a.y
from A a;
loop
fetch myCursor into
v_x,v_y;
EXIT WHEN myCursor%NOTFOUND ;
select count(b.*) into col1 from B b where b.y=v_y;
if col1=0 then
---插入
insert into B (v_x,v_y);
elsif col1=1 then
select b.x into v_bx from B b where b.y=v_y;
if v_bx<>v_x then
---更新
update B b set b.x=v_x where b.y=v_y;
end if;
end if;
commit;
end loop;
end test;