????[sql] view plaincopycreateor replace procedure Rpt_Dyhgl_year(

????--????????-??

????v_rptyear in varchar2?? --??yyyy

????--rpttype in varchar2?? --?????????1????2????

????ref_cursor out sys_refcursor --?????α?

????)

????as

????-- ????????

????.......

????begin

????delete from YWTJ_RPT_DYHGL_YEAR where RPTYEAR = v_rptyear and RPTTYPE = '1';

????commit;

????--?????????

????v_sql := 'select rptdate?? jcdlb?? jcdlbName?? sum(nvl(runtime??0)) sumruntime??sum(nvl(buhegeTime??0)) buhegeTime?? count(jcdcount) jcdcount

????from ( select rptdate?? runtime??r.chaoshangxian+r.chaoxiaxian buhegeTime?? r.objid?? 1 jcdcount??

????(select jcdlb from jczl_dwjg d whered.objid = r.objid ) jcdlb??

????(select jcdlbname from dic_jcdlb wherejcdlbid = (select jcdlb from jczl_dwjg where objid = r.objid)) jcdlbName

????from ywtj_rpt_dyhgl_month r where rptdate = :rptYear)

????group by rptdate?? jcdlb?? jcdlbName

????order by rptdate ??jcdlb ';

????-- ??????????

????v_sqlRptInsert := 'Insert IntoYWTJ_RPT_DYHGL_YEAR (RPTYEAR??RPTDATE??RPTTYPE) values(:RPTYEAR??:RPTDATE??:RPTTYPE)';

????v_sqlRptUpdate := '';

????-- ????1--12?μ????????

????for rptmonth in 1..12 loop

????-- ???α??????????

????open v_cur for v_sql using v_rptyear||LPAD(rptmonth?? 2?? '0');

????loop

????fetch v_cur intov_rptDate??v_jcdlb??v_jcdLbName??v_sumRuntime??v_sumbuhegeTime??v_jcdCount;

????exit when v_cur%notfound;

????--????????

????if v_sumRuntime <> 0 then

????v_hgl := (v_sumRuntime-v_sumbuhegeTime)/v_sumRuntime *100;

????end if;

????-- ???μ???????

????........

????end loop;

????close v_cur;

????-- ???α?????????????

????open v_cur for v_sql using (v_rptyear-1)|| LPAD(rptmonth?? 2?? '0');

????loop

????fetch v_cur into v_rptDate??v_jcdlb??v_jcdLbName??v_sumRuntime??v_sumbuhegeTime??v_jcdCount;

????exit when v_cur%notfound;

????--????????

????if v_sumRuntime <> 0 then

????v_hgl := (v_sumRuntime-v_sumbuhegeTime)/v_sumRuntime*100;

????end if;

????-- ???????????

????.........

????end loop;

????close v_cur;

????end loop;

????-- ???????????

????.........

????-- ???????????

????........

????open ref_cursor for select * fromYWTJ_RPT_DYHGL_YEAR r where r.rptyear = v_rptyear and r.rpttype = 1 order byr.rptdate;

????end Rpt_Dyhgl_year;

???????????????????????????????????????900????????1????????????????????

??????????

????1) ???????????????????????????????????;

????2) ????????????????to_char(add_months(to_date(tb.rptdate??'yyyymm')??12)??'yyyymm')???????????????????к?????????????????????