orecle select function 不是group by 表达式
select a.goodsid,
b.goodsname,
b.goodsspec,
b.unitname,
a.materialid,
c.goodsname as mgoodsname,
qty,
tl_recipecost(a.goodsid ,1) as cost1,
a.qty1,
tl_recipecost(a.goodsid ,2) as cost2,
a.qty2,
tl_recipecost(a.goodsid ,3) as cost3,
a.qty3,
tl_recipecost(a.goodsid ,4) as cost4
from recipe a, goods b, goods c
where a.goodsid = b.goodsid
and a.materialid = c.goodsid
create or replace function tl_recipecost
(
I_goodsid in int,
I_type in varchar2
)
return varchar2
--------------------------------------------
--------------------------------------------
as
v_caseno varchar2(64);
v_count int;
o_cost number(10,6);
begin
o_cost:=0;
if(I_type =1 )then
For R in
(
select min(a.cost) AS COST, b.materialid ,b.qty ,b.goodsid
from goodsshop a ,recipe b where a.goodsid = b.materialid and b.goodsid = i_goodsid
group by b.materialid ,b.qty ,b.goodsid
)
loop
o_cost := o_cost+ R.COST*b.qty;
end loop ;
elsif (I_type =2 )then
For R in
(
select min(a.cost) AS COST, b.materialid ,b.qty1 ,b.goodsid
from goodsshop a ,recipe b where a.goodsid = b.materialid and b.goodsid = i_goodsid
group by b.materialid ,b.qty ,b.goodsid
)
loop
o_cost := o_cost+ R.COST*b.qty1;
end loop ;
elsif (I_type =3 )then
For R in
(
&nb