此处不能使用分组函数 oracle
CREATE OR REPLACE PROCEDURE DBB01_1_MONTH_LOSS_date(P_rq varchar2,P_Filed varchar2) is
--------date 2013-12-12
-------for Monthly Loss Analysis
begin
declare
exist_num number(12);
exist_time varchar2(12,2);
exist_oil number(12,4);
cursor C_existdm is
select * from (select b.mc ,a.* from dba01 a,v_dab913_fpf_station b where substr(a.jhdm,0,6)=b. station ) y
where y.gjdm is not null and to_char(y.rq,'yyyymm')=P_rq and y.mc=P_Filed;
Y_existdm C_existdm%type;
begin
for Y_existdm in C_existdm loop
select count(*) into exist_num,round(sum(24-y.scsj),2) into exist_time,round(sum(y.lyrcyl),4) into exist_oil
from (select b.mc ,a.* from dba01 a,v_dab913_fpf_station b where substr(a.jhdm,0,6)=b. station ) y
where y.gjdm is not null and to_char(y.rq,'yyyymm')=P_rq and y.mc=P_Filed group by Y_existdm.gjdm;
end loop;
end;
end DBB01_1_MONTH_LOSS_date;
select count(*) into exist_num,round(sum(24-y.scsj),2) into exist_time,round(sum(y.lyrcyl),4) into exist_oil
from (select b.mc ,a.* from dba01 a,v_dab913_fpf_station b where substr(a.jhdm,0,6)=b. station ) y
where y.gjdm is not null and to_char(y.rq,'yyyymm')=P_rq and y.mc=P_Filed
