CREATE OR REPLACE PROCEDURE ptest
as
v_count NUMBER;
CURSOR mycur is select jh,jhdm,hzb,zzb,x0,y0 from jc_jcxx_jing;
begin
for jing in mycur loop
select count(*) into v_count from daa02 where jhdm=jing.jhdm;
if v_count>0 then
update daa02 set hzby1=jing.hzb,zzbx1=jing.zzb,hzby=jing.x0,zzbx=jing.y0 where jhdm=jing.jhdm;
else
insert into daa02 (jhdm,hzby1,zzbx1,hzby,zzbx) values (jing.jhdm,jing.hzb,jing.zzb,jing.x0,jing.y0);
end if
end loop;
end ptest;
/
as
v_count NUMBER;
CURSOR mycur is select jh,jhdm,hzb,zzb,x0,y0 from jc_jcxx_jing;
begin
for jing in mycur loop
select count(*) into v_count from daa02 where jhdm=jing.jhdm;
if v_count>0 then
update daa02 set hzby1=jing.hzb,zzbx1=jing.zzb,hzby=jing.x0,zzbx=jing.y0 where jhdm=jing.jhdm;
else
insert into daa02 (jhdm,hzby1,zzbx1,hzby,zzbx) values (jing.jhdm,jing.hzb,jing.zzb,jing.x0,jing.y0);
end if
end loop;
end ptest;
/
as
v_count NUMBER;
CURSOR mycur is select jh,jhdm,hzb,zzb,x0,y0 from jc_jcxx_jing;
begin
for jing in mycur loop
select count(*) into v_count from daa02 where jhdm=jing.jhdm;
if v_count>0 then
update daa02 set hzby1=jing.hzb,zzbx1=jing.zzb,hzby=jing.x0,zzbx=jing.y0 where jhdm=jing.jhdm;
else
insert into daa02 (jhdm,hzby1,zzbx1,hzby,zzbx) values (jing.jhdm,jing.hzb,jing.zzb,jing.x0,jing.y0);
end if;
end loop;
commit;
end ptest;
-- 从语义上看,可以直接用于 merge 语句实现。
merge into daa02 t
using (select jh,jhdm,hzb,zzb,x0,y0 from jc_jcxx_jing) s
on (s.jhdm = t.jhdm)
when matched then
update set t.hzby1=s.hzb,t.zzbx1=s.zzb,t.hzby=s.x0,t.zzbx=s.y0
when not matched then
insert (t.jhdm,t.hzby1,t.zzbx1,t.hzby,t.zzbx) value(s.jhdm,s.hzb,s.zzb,s.x0,s.y0);