谁能帮我修改下这个存储过程,让时间和价格都可以随意覆盖~~而不是简单的插入~,谢谢create or replace procedure p_importgrplimit is
v_prodid varchar2(200);
v_price number(10,2);
v_enddate varchar2(200);
v_grpid varchar2(200);
v_lprice number(10,2);
v_num integer; cursor c_AAA is
select t.a,
t.b,
t.c
from aaa t; cursor c_BBB is
select t.a
from bbb t;begin
open c_BBB;
loop
fetch c_BBB
into v_grpid;
exit when c_BBB%notfound;
open c_AAA;
loop
fetch c_AAA
into v_prodid, v_price,v_enddate;
exit when c_AAA%notfound; v_lprice:=-1;
select count(*) into v_num from iagent.productgrplimit t where t.prodid=v_prodid and t.grpid=v_grpid;
if v_num>0 then
select t.lprice into v_lprice from iagent.productgrplimit t where t.prodid=v_prodid and t.grpid=v_grpid;
end if;
if v_lprice=-1 then
insert into iagent.productgrplimit(prodid,grpid,lprice,status,enddt)
values(v_prodid,v_grpid,v_price,'-1',to_date(v_enddate,'yyyy-mm-dd'));
else if v_lprice>v_price then
update iagent.productgrplimit q set q.lprice=v_price where q.prodid=v_prodid and q.grpid=v_grpid;
end if;
end if; commit;
end loop;
close c_AAA;
end loop;
close c_BBB;
end p_importgrplimit;
v_prodid varchar2(200);
v_price number(10,2);
v_enddate varchar2(200);
v_grpid varchar2(200);
v_lprice number(10,2);
v_num integer; cursor c_AAA is
select t.a,
t.b,
t.c
from aaa t; cursor c_BBB is
select t.a
from bbb t;begin
open c_BBB;
loop
fetch c_BBB
into v_grpid;
exit when c_BBB%notfound;
open c_AAA;
loop
fetch c_AAA
into v_prodid, v_price,v_enddate;
exit when c_AAA%notfound; v_lprice:=-1;
select count(*) into v_num from iagent.productgrplimit t where t.prodid=v_prodid and t.grpid=v_grpid;
if v_num>0 then
select t.lprice into v_lprice from iagent.productgrplimit t where t.prodid=v_prodid and t.grpid=v_grpid;
end if;
if v_lprice=-1 then
insert into iagent.productgrplimit(prodid,grpid,lprice,status,enddt)
values(v_prodid,v_grpid,v_price,'-1',to_date(v_enddate,'yyyy-mm-dd'));
else if v_lprice>v_price then
update iagent.productgrplimit q set q.lprice=v_price where q.prodid=v_prodid and q.grpid=v_grpid;
end if;
end if; commit;
end loop;
close c_AAA;
end loop;
close c_BBB;
end p_importgrplimit;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货