我想建一个存储过程,实现以下功能:
从源表中先取出一条记录核查ent_id,prod_id,item_id等是否在目标表中存在,存在就更新其qty不存在就插入一条新的纪录.
检验完成后再通过截取item_id找其父类在目标表中是否存在,存在更新其qty不存在则插入父类纪录.
这里声明的局部变量不管用,在local 附近通不过,程序报错!
create or replace procedure pro_zy is
cursor zkf_cur1 is select * from ZKF for update;
cur_recp zkf_cur1%rowtype;
ent_id zkf.ent_id%type;
prod_id zkf.prod_id%type;
line_no zkf.line_no%type;
item_id zkf.item_id%type;
qty zkf.qty%type;
pur_date zkf.pur_date%type;
measure zkf.measure%type; total_amt zkf.total_amt%type;
manage_id zkf.manage_id%type;
item_id_tem1 zkf.item_id%type;
v_inner_lev integer;
cir integer;
v_inner_code1 md_cost_item.inner_code%type;
v_inner_code2 md_cost_item.inner_code%type;
begin
for cur_recp in zkf_cur1 loop declare zkf_cur2 cursor local for select * from zkf_temp;
open zkf_cur2;
fetch next from zkf_cur2 into @cur_recc;
while (@@fetch_status=0)
begin
if (cur_recc.ent_id=cur_recp.ent_id)and(cur_recc.prod_id=cur_recp.prod_id)and(cur_recc.item_id=cur_recp.item_id)and(cur_recc.cost_type='c')and(cur_recc.sum_type='m') then
update zkf_temp set qty=cur_recc.qty+cur_recp.qty where cost_id=cur_recc.cost_id;
else
insert into zkf_temp (cost_id,ent_id,nyear,nmonth,ndate,prod_id,item_id,prod_sort_id,cost_type,line_no,measure,qty,price,rmb_amt,ass_amt,total_amt,src_type,sum_type,manage_id) values ((SELECT SYS_GUID() from dual),cur_recp.ent_id,2000,22,'2008-10-11',cur_recp.prod_id,cur_recp.item_id,'xxxx','c',cur_recp.line_no,cur_recp.measure,cur_recp.qty,cur_recp.own_price,cur_recp.qty*cur_recp.own_price,0,cur_recp.total_amt,'p','m',cur_recp.manage_id);
end if;
fetch next from zkf_cur2 into @cur_recc;
end
close zkf_cur2;
deallocate zkf_cur2;
--取等级;
select inner_level,inner_code into v_inner_lev, v_inner_code1 from md_cost_item where item_id=cur_recp.item_id;
while (v_inner_lev>1)
begin
item_id_tem1:=substr(cur_recp.item_id,0,v_inner_lev*2-2);
declare zkf_cur3 cursor local for
select * from zkf_temp;
open zkf_cur3;
fetch next from zkf_cur3 into @cur_recc2;
while (@@fetch_status=0)
begin
if (cur_recc2.ent_id=cur_recp.ent_id)and(cur_recc2.prod_id=cur_recp.prod_id)and(cur_recc2.item_id=cur_recp.item_id)and(cur_recc2.cost_type='c')and(cur_recc2.sum_type='m') then
update zkf_temp set qty=cur_recc.qty+cur_recp.qty where cost_id=cur_recc.cost_id;
else
insert into zkf_temp (cost_id,ent_id,nyear,nmonth,ndate,prod_id,item_id,prod_sort_id,cost_type,line_no,measure,qty,price,rmb_amt,ass_amt,total_amt,src_type,sum_type,manage_id) values ((SELECT SYS_GUID() from dual),cur_recp.ent_id,2000,22,'2008-10-11',cur_recp.prod_id,cur_recp.item_id,'xxxx','c',cur_recp.line_no,cur_recp.measure,cur_recp.qty,cur_recp.own_price,cur_recp.qty*cur_recp.own_price,0,cur_recp.total_amt,'p','m',cur_recp.manage_id);
end if;
fetch next from zkf_cur2 into @cur_recc2;
end
close zkf_cur3;
deallocate zkf_cur3;
v_inner_lev:=v_inner_lev-1;
end
end loop;
commit;
end;
从源表中先取出一条记录核查ent_id,prod_id,item_id等是否在目标表中存在,存在就更新其qty不存在就插入一条新的纪录.
检验完成后再通过截取item_id找其父类在目标表中是否存在,存在更新其qty不存在则插入父类纪录.
这里声明的局部变量不管用,在local 附近通不过,程序报错!
create or replace procedure pro_zy is
cursor zkf_cur1 is select * from ZKF for update;
cur_recp zkf_cur1%rowtype;
ent_id zkf.ent_id%type;
prod_id zkf.prod_id%type;
line_no zkf.line_no%type;
item_id zkf.item_id%type;
qty zkf.qty%type;
pur_date zkf.pur_date%type;
measure zkf.measure%type; total_amt zkf.total_amt%type;
manage_id zkf.manage_id%type;
item_id_tem1 zkf.item_id%type;
v_inner_lev integer;
cir integer;
v_inner_code1 md_cost_item.inner_code%type;
v_inner_code2 md_cost_item.inner_code%type;
begin
for cur_recp in zkf_cur1 loop declare zkf_cur2 cursor local for select * from zkf_temp;
open zkf_cur2;
fetch next from zkf_cur2 into @cur_recc;
while (@@fetch_status=0)
begin
if (cur_recc.ent_id=cur_recp.ent_id)and(cur_recc.prod_id=cur_recp.prod_id)and(cur_recc.item_id=cur_recp.item_id)and(cur_recc.cost_type='c')and(cur_recc.sum_type='m') then
update zkf_temp set qty=cur_recc.qty+cur_recp.qty where cost_id=cur_recc.cost_id;
else
insert into zkf_temp (cost_id,ent_id,nyear,nmonth,ndate,prod_id,item_id,prod_sort_id,cost_type,line_no,measure,qty,price,rmb_amt,ass_amt,total_amt,src_type,sum_type,manage_id) values ((SELECT SYS_GUID() from dual),cur_recp.ent_id,2000,22,'2008-10-11',cur_recp.prod_id,cur_recp.item_id,'xxxx','c',cur_recp.line_no,cur_recp.measure,cur_recp.qty,cur_recp.own_price,cur_recp.qty*cur_recp.own_price,0,cur_recp.total_amt,'p','m',cur_recp.manage_id);
end if;
fetch next from zkf_cur2 into @cur_recc;
end
close zkf_cur2;
deallocate zkf_cur2;
--取等级;
select inner_level,inner_code into v_inner_lev, v_inner_code1 from md_cost_item where item_id=cur_recp.item_id;
while (v_inner_lev>1)
begin
item_id_tem1:=substr(cur_recp.item_id,0,v_inner_lev*2-2);
declare zkf_cur3 cursor local for
select * from zkf_temp;
open zkf_cur3;
fetch next from zkf_cur3 into @cur_recc2;
while (@@fetch_status=0)
begin
if (cur_recc2.ent_id=cur_recp.ent_id)and(cur_recc2.prod_id=cur_recp.prod_id)and(cur_recc2.item_id=cur_recp.item_id)and(cur_recc2.cost_type='c')and(cur_recc2.sum_type='m') then
update zkf_temp set qty=cur_recc.qty+cur_recp.qty where cost_id=cur_recc.cost_id;
else
insert into zkf_temp (cost_id,ent_id,nyear,nmonth,ndate,prod_id,item_id,prod_sort_id,cost_type,line_no,measure,qty,price,rmb_amt,ass_amt,total_amt,src_type,sum_type,manage_id) values ((SELECT SYS_GUID() from dual),cur_recp.ent_id,2000,22,'2008-10-11',cur_recp.prod_id,cur_recp.item_id,'xxxx','c',cur_recp.line_no,cur_recp.measure,cur_recp.qty,cur_recp.own_price,cur_recp.qty*cur_recp.own_price,0,cur_recp.total_amt,'p','m',cur_recp.manage_id);
end if;
fetch next from zkf_cur2 into @cur_recc2;
end
close zkf_cur3;
deallocate zkf_cur3;
v_inner_lev:=v_inner_lev-1;
end
end loop;
commit;
end;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货