??????????

??????????birt????????????oracle10g????????????????????е????????????д?洢?????????????????????????????????洢???????????(????????????)??

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

????rptyear in varchar2?? --??yyyy

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

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

????)

????As

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

??????????????

????begin

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

????commit;

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

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

????sum(nvl(tongbiRuntime??0)) tongbiRuntime?? sum(nvl(tongbiBhg??0))tongbiBhg from (

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

????(select runtime fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate??''yyyymm'')??12)??''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiRuntime??

????(select chaoshangxian + chaoxiaxian fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate??''yyyymm'')??12)??''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiBhg??

????(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

????execute immediate v_sqlRptInsertusing rptyear?? rptmonth?? '1';

????commit;

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

????loop

????fetch v_cur intov_rptDate??v_jcdlb??v_jcdName??v_sumRuntime??v_sumbuhegeTime??v_jcdCount??v_tongbiRuntime??v_tongbiBhg;

????exit when v_cur%notfound;

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

????????????????

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

????????????????

????end loop;

????close v_cur;

????end loop;

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

????????????????

????commit;

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

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

????end Rpt_Dyhgl_year;

??????????????????????????30-40??

??????????????????л????к???????????????2??3 ?????????г???!??γ?????????????????????940????15?????!???????????????????!???pl/sql dev???????????Rpt_Dyhgl_year????й?????з???????????????е??????????

????fetch v_cur into v_rptDate??v_jcdlb??v_jcdName??v_sumRuntime??v_sumbuhegeTime??v_jcdCount??v_tongbiRuntime??v_tongbiBhg;

????(???????????????У????????????????????????dr.Read()??????????????????)

?????????????????fetch????????????

????[sql] view plaincopyv_sql :='select rptdate?? jcdlb?? jcdlbName??sum(nvl(runtime??0)) sumruntime?? sum(nvl(buhegeTime??0)) buhegeTime??count(jcdcount) jcdcount??

????sum(nvl(tongbiRuntime??0)) tongbiRuntime?? sum(nvl(tongbiBhg??0))tongbiBhg from (

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

????(select runtime fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate??''yyyymm'')??12)??''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiRuntime??

????(select chaoshangxian + chaoxiaxian fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate??''yyyymm'')??12)??''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiBhg??

????(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 '; ??

?????????ó?????У????5.7???

??????????е???????

????[sql] view plaincopyselectrptdate?? runtime?? r.chaoshangxian+r.chaoxiaxian buhegeTime?? r.objid?? 1jcdcount??

????(select runtime fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate??''yyyymm'')??12)??''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiRuntime??

 ????(select chaoshangxian + chaoxiaxian fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate??''yyyymm'')??12)??''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiBhg??

????(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; ??

?????????ó?????У?????????????????????????????????????????????????ò???????????з???????????

???????????е?tongbiRuntime??tongbiBhg????????????????????????????????е?tongbiRuntime??tongbiBhg??????????????????????????????0.157??

???????????????????????????????????????????????!??????·???????????????????(????????????)??