create or replace function get_year_total(reportyear in nvarchar2,unit in nvarchar2,type1 in nvarchar2,type2 in nvarchar2,type3 in nvarchar2)
return number is result number;
begin
select total.TYPE_YEAR_TOTAL into result from
(select sum(sumtype.CURRENT_MONTH) TYPE_YEAR_TOTAL,sumtype.FILL_UNIT,sumtype.REPORT_YEAR,
sumtype.PRODUCE_TYPE1,sumtype.PRODUCE_TYPE2,sumtype.PRODUCE_TYPE3 from
(select r.REPORT_YEAR,r.FILL_UNIT,r.CURRENT_MONTH,pl.PRODUCE_TYPE1,pl.PRODUCE_TYPE2,pl.PRODUCE_TYPE3 from
T_SYNTHE_REP r, T_SYNTHE_PLAN pl
where r.PLAN_ID=pl.ID) sumtype
group by sumtype.FILL_UNIT,sumtype.REPORT_YEAR,sumtype.PRODUCE_TYPE1,sumtype.PRODUCE_TYPE2,sumtype.PRODUCE_TYPE3) total
where total.FILL_UNIT=unit and total.REPORT_YEAR=reportyear and total.PRODUCE_TYPE1=type1
and ((total.PRODUCE_TYPE2 is not null and total.PRODUCE_TYPE2=type2) or total.PRODUCE_TYPE2 is null)
and ((total.PRODUCE_TYPE3 is not null and total.PRODUCE_TYPE3=type3) or total.PRODUCE_TYPE3 is null);
return(result);
end get_year_total;
oracle自定义函数
最新推荐文章于 2022-12-15 22:00:00 发布