如题,但是麻烦是在参数写在单引号里面的,我试了几种方法,都得不到数.请高手指点.函数的参数是currentprice,写在t.commscode like '''commsid''||%'里面了.create or replace function getcurrentprice(commsid varchar2)
return number is
currentprice number;
begin
select
round(avg(t.price), 0) price into currentprice
from
(select
aa.price,
aa.enterid
from
(select
t.price,
t.enterid
from aa_price_data t
join aa_price tt
on t.reportid = tt.id
join enter_user ent
on ent.id = tt.enterid
where t.reportid = tt.id
and tt.rptdate = to_date('2008-4-1','yyyy-mm-dd')
and t.commscode like '''commsid''||%'
and ent.iskernal=1
and ent.isvalid = 1
and tt.is_valid = 1
and tt.is_ontime = 1
and t.price <> 0
) aa
) t;
return currentprice;
end;
return number is
currentprice number;
begin
select
round(avg(t.price), 0) price into currentprice
from
(select
aa.price,
aa.enterid
from
(select
t.price,
t.enterid
from aa_price_data t
join aa_price tt
on t.reportid = tt.id
join enter_user ent
on ent.id = tt.enterid
where t.reportid = tt.id
and tt.rptdate = to_date('2008-4-1','yyyy-mm-dd')
and t.commscode like '''commsid''||%'
and ent.iskernal=1
and ent.isvalid = 1
and tt.is_valid = 1
and tt.is_ontime = 1
and t.price <> 0
) aa
) t;
return currentprice;
end;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货