是要做一个存储过程,从一张表往另外一张表传数据,如果有就更新,没有就插入。
create or replace procedure proc_im_lh_dhandlersite(begintime in timestamp) as
cursor c_cursor is
select UNIT_CODE,name,code,enname
from t_code_view
where catalogname like 'billAccount:%'
and apptime >= begintime;
c_listrow c_cursor%rowtype;
counts integer := 0;
begin
for c_listrow in c_cursor loop
select count(*)
into counts
from d_handlersite
where unitcode = c_listrow.UNIT_CODE
and handlercode = c_listrow.name
and offertype = c_listrow.enname;
--没有记录,采用insert,插入一条新记录
if counts = 0 then
insert into d_handlersite
(unitcode, handlercode, offersite, offertype)
values
(c_listrow.unit_code,
c_listrow.name,
c_listrow.code,
c_listrow.enname);
end if;
--有记录,采用update,更新原有的记录
if counts > 0 then
update d_handlersite
set offersite = c_listrow.code
where unitcode = c_listrow.unit_code
and handlercode = c_listrow.name
and offertype = c_listrow.enname;
end if;
end loop;
end proc_im_lh_dhandlersite;
create or replace procedure proc_im_lh_dhandlersite(begintime in timestamp) as
cursor c_cursor is
select UNIT_CODE,name,code,enname
from t_code_view
where catalogname like 'billAccount:%'
and apptime >= begintime;
c_listrow c_cursor%rowtype;
counts integer := 0;
begin
for c_listrow in c_cursor loop
select count(*)
into counts
from d_handlersite
where unitcode = c_listrow.UNIT_CODE
and handlercode = c_listrow.name
and offertype = c_listrow.enname;
--没有记录,采用insert,插入一条新记录
if counts = 0 then
insert into d_handlersite
(unitcode, handlercode, offersite, offertype)
values
(c_listrow.unit_code,
c_listrow.name,
c_listrow.code,
c_listrow.enname);
end if;
--有记录,采用update,更新原有的记录
if counts > 0 then
update d_handlersite
set offersite = c_listrow.code
where unitcode = c_listrow.unit_code
and handlercode = c_listrow.name
and offertype = c_listrow.enname;
end if;
end loop;
end proc_im_lh_dhandlersite;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货