求一store procedure

显示全部楼层 · 2007-9-26 18:42:10

单计算出产品所需原材料的数量,其中配方表中的mat_no可能来至'原材料'表也可能来至'子配方表',
表结构如下(不知这样描述得够不够清楚)

create table a1--配方表
(

product_no varchar2(20),--产品编号

mark varchar2(6), --配方编号

mat_no varchar2(20), --原材料编码(也可能是子配方编号)

ratenumber(4,2)--比率
)
create table a2 --子配方表
(

mats_no varchar2(20), --子配方编号

mat_no varchar2(20),--原材料编码

rate number(4,2)--比率
)
create table a3 --原材料
(
mat_no varchar2(20),--原材料编码
mat_cn varchar2(60),--原材料名称
)

千问 · 2007-9-26 18:42:10

首先请确认子配方编号和原材料编码是否可能一样?
如果可能,那请问一样时给如何处理?

千问 · 2007-9-26 18:42:10

写一个函数，不知道是不是满足要求
create or replace function func_get_mat
(prodno in varchar2) return varchar2
as
type rec_mat is record
(

mat_no varchar2(20),

rate number(4,2)
)
type arr_mat is table of rec_mat;

mat_info rec_mat;
mat_list arr_mat;
cnt number;
ret varchar2(4000);
begin

for c1 in (select mat_no,rate from a1 where procudt_no = prodno) loop

-- 判断是否是子配方

select count(*) into cnt from a2 where mats_no = c1.mat_no;

if cnt > 1 then

for c2 in (select mat_no,rate from a2 where mats_no = c1.mat_no) loop

mat_info.mat_no := c2.mat_no;

mat_info.rate := c1.rate*c2.rate;

mat_list.extend;

mat_list(mat_list.count) := mat_info;

end loop;

else

mat_info.mat_no := c1.mat_no;

mat_info.rate := c1.rate;

mat_list.extend;

mat_list(mat_list.count) := mat_info;

end if;

end loop;

--将列表拼串

for i in 1 .. mat_list.count loop

ret := ret||mat_list(i).mat_no||'|'||mat_list(i).rate||';';

end loop;

return ret;
exception when others then

dbms_output.put_line('error:'||sqlerrm);

rollback;
end ;

千问 · 2007-9-26 18:42:10

create or replace procedure PRO_PRODUCTQTYCALC(VfactNo in varchar2,Vyyyymm in varchar2,Vretrun out varchar2)
is
cursor C1 is
select a.order_no,b.product_no,a.purchase_date,b.inner_qty
from inner_order_m a,inner_order_d b
where a.fact_no = b.fact_no
and a.inorder_no = b.inorder_no
and substr(a.purchase_date,1,6) = Vyyyymm
and a.fact_no = VfactNo ;
cursor C2(productNo varchar2) is
select mark,mat_no,weight
from product_bomd
where fact_no = VfactNo
and mark =

(

select mark from product_bomm where fact_no = VfactNo

and product_no = productNo

) ;
c_2 C2%rowtype;
cursor C3(mats_no varchar2) is

select mat_no,weight

from mat_sd

where fact_no = VfactNo

and mats_no = mats_no;
c_3 C3%rowtype;
Vc_ops char(1) := 0 ;
begin
for cprod in C1 loop
open C2(cprod.product_no);
loop
fetch C2 into c_2;
exit when C2%notfound;

open C3(c_2.mat_no);

loop

fetch C3 into c_3;

exit when C3%notfound;

if C3%found then

insert into TEMPQTYCAL(mat_no,qty)

values (c_3.mat_no,nvl(cprod.inner_qty,0)*c_2.weight*c_3.weight);

elsif C3%notfound then

insert into TEMPQTYCAL(mat_no,qty)

values (c_2.mat_no,nvl(cprod.inner_qty,0)*c_2.weight);

end if;

end loop;

close C3;
end loop;
close C2;
end loop;

insert into matdosage(fact_no,mm,mat_no,qty)
select VfactNo,Vyyyymm, mat_no,sum(qty) qty
fromTEMPQTYCAL
group by mat_no;
commit;
Vretrun := Vretrun ||'成功';
end PRO_PRODUCTQTYCALC;