简单统计报表例子（存储过程）

 1 create or replace procedure bb_quxzbmjdqzxfqk_ces(kssj in date, 2                                          jssj in date, 3                                          wtsd2 varchar2, 4                                          tjjg out sys_refcursor) as 5  6 begin 7    delete from A_TJBB_quxzbmjdqzxfqk; 8 commit; 9 insert into A_TJBB_quxzbmjdqzxfqk10 select jj.iidd,jj.isedit,jj.djjgbh,jj.cfxfjbz,jj.lmbz,jj.xfrs,jj.xfxs from visit_xfj jj,11  hr_organization n where n.jglb='1200' and  n.region_dm=wtsd2 and n.bh=jj.djjgbh and jj.djsj between kssj and jssj;12 commit;13   open tjjg for14 15 16 select l.mc as mc,17  count(1) as jianci,18 sum(case when xf.xfxs='100' then 1 else19  case when xf.xfxs='200' then xf.xfrs else20  case when xf.xfxs in ('300','303') then 121  else 0 end end end) as rci,22 sum(case when xf.xfxs='100' and xf.lmbz='0'and xf.cfxfjbz='0' then 1 else 0 end)as grlx,23 sum(case when xf.xfxs='100' and xf.lmbz='1'and xf.cfxfjbz='0' then 1 else 0 end)as lmx,24 sum(case when xf.xfxs='100' and xf.lmbz='0'and xf.cfxfjbz='1' then 1 else 0 end)as cfgrlx,25 sum(case when xf.xfxs='100' and xf.lmbz='1'and xf.cfxfjbz='1' then 1 else 0 end)as cflmx,26 sum(case when xf.xfxs='100' then 1 else 0 end)as xiaojie,27 sum(case when xf.xfxs='200' and xf.xfrs<=4 and xf.cfxfjbz='0' then 1 else 0 end)as c1lfcjjc,28 sum(case when xf.xfxs='200' and xf.xfrs<=4 and xf.cfxfjbz='0' then xf.xfrs else 0 end)as c1lfcjrc,29 sum(case when xf.xfxs='200' and( xf.xfrs>=5 and xf.xfrs<=49) and xf.cfxfjbz='0' then 1 else 0 end)as c5lfcjjc,30 sum(case when xf.xfxs='200' and( xf.xfrs>=5 and xf.xfrs<=49) and xf.cfxfjbz='0' then xf.xfrs else 0 end)as c5lfcjrc,31 sum(case when xf.xfxs='200' and( xf.xfrs>=50 and xf.xfrs<=499) and xf.cfxfjbz='0' then 1 else 0 end)as c50lfcjjc,32 sum(case when xf.xfxs='200' and( xf.xfrs>=50 and xf.xfrs<=499) and xf.cfxfjbz='0' then  xf.xfrs else 0 end)as c50lfcjrc,33 sum(case when xf.xfxs='200' and xf.xfrs>=500 and xf.cfxfjbz='0' then 1 else 0 end)as c500lfcjjc,34 sum(case when xf.xfxs='200' and xf.xfrs>=500 and xf.cfxfjbz='0' then  xf.xfrs else 0 end)as c500lfcjrc,35 sum(case when xf.xfxs='200' and xf.xfrs<=4 and xf.cfxfjbz='1' then 1 else 0 end)as cf1lfcjjc,36 sum(case when xf.xfxs='200' and xf.xfrs<=4 and xf.cfxfjbz='1' then xf.xfrs else 0 end)as cf1lfcjrc,37 sum(case when xf.xfxs='200' and( xf.xfrs>=5 and xf.xfrs<=49) and xf.cfxfjbz='1' then 1 else 0 end)as cf5lfcjjc,38 sum(case when xf.xfxs='200' and( xf.xfrs>=5 and xf.xfrs<=49) and xf.cfxfjbz='1' then xf.xfrs else 0 end)as cf5lfcjrc,39 sum(case when xf.xfxs='200' and( xf.xfrs>=50 and xf.xfrs<=499) and xf.cfxfjbz='1' then 1 else 0 end)as cf50lfcjjc,40 sum(case when xf.xfxs='200' and( xf.xfrs>=50 and xf.xfrs<=499) and xf.cfxfjbz='1' then  xf.xfrs else 0 end)as cf50lfcjrc,41 sum(case when xf.xfxs='200' and xf.xfrs>=500 and xf.cfxfjbz='1' then 1 else 0 end)as cf500lfcjjc,42 sum(case when xf.xfxs='200' and xf.xfrs>=500 and xf.cfxfjbz='1' then  xf.xfrs else 0 end)as cf500lfcjrc,43 sum(case when xf.xfxs='200' then 1 else 0 end)as lxxiaojjc,44 sum(case when xf.xfxs='200' then xf.xfrs else 0 end)as lxxiaojrc,45 sum(case when xf.xfxs in('300','303') and xf.cfxfjbz='0'and xf.lmbz='0' then 1 else 0 end) as grts,46 sum(case when xf.xfxs in('300','303') and xf.cfxfjbz='0'and xf.lmbz='1' then 1 else 0 end) as lmts,47 48 sum(case when xf.xfxs in('300','303') and xf.cfxfjbz='1'and xf.lmbz='0' then 1 else 0 end) as cfgrts,49 50 sum(case when xf.xfxs in('300','303') and xf.cfxfjbz='1'and xf.lmbz='1' then 1 else 0 end) as cflmts,51 sum(case when xf.xfxs in('300','303') then 1 else 0 end) as xiaoji52 53   from hr_organization l,A_TJBB_quxzbmjdqzxfqk xf where  l.jglb='1200' and  l.region_dm='500101' and l.bh=xf.djjgbh group by l.mc;54 55   end bb_quxzbmjdqzxfqk_ces;

————————————————————————————————————————————————————————————————————

 1 create or replace procedure bb_quxzbmjdqzxfqk_ces(kssj in date, 2                                          jssj in date, 3                                          wtsd2 varchar2, 4                                          isquxian varchar2, 5                                          tjjg out GLOBALPKG.RCT1) as 6  7 begin 8    v_kssj1 :=kssj; 9    v_jssj1 :=jssj;10   open tjjg for11 12 13 select l.mc as mc,14  count(1) as jianci,15 sum(case when xf.xfxs='100' then 1 else16  case when xf.xfxs='200' then xf.xfrs else17  case when xf.xfxs in ('300','303') then 118  else 0 end end end) as rci,19 sum(case when xf.xfxs='100' and xf.lmbz='0'and xf.cfxfjbz='0' then 1 else 0 end)as grlx,20 sum(case when xf.xfxs='100' and xf.lmbz='1'and xf.cfxfjbz='0' then 1 else 0 end)as lmx,21 sum(case when xf.xfxs='100' and xf.lmbz='0'and xf.cfxfjbz='1' then 1 else 0 end)as cfgrlx,22 sum(case when xf.xfxs='100' and xf.lmbz='1'and xf.cfxfjbz='1' then 1 else 0 end)as cflmx,23 sum(case when xf.xfxs='100' then 1 else 0 end)as xiaojie,24 sum(case when xf.xfxs='200' and xf.xfrs<=4 and xf.cfxfjbz='0' then 1 else 0 end)as c1lfcjjc,25 sum(case when xf.xfxs='200' and xf.xfrs<=4 and xf.cfxfjbz='0' then xf.xfrs else 0 end)as c1lfcjrc,26 sum(case when xf.xfxs='200' and( xf.xfrs>=5 and xf.xfrs<=49) and xf.cfxfjbz='0' then 1 else 0 end)as c5lfcjjc,27 sum(case when xf.xfxs='200' and( xf.xfrs>=5 and xf.xfrs<=49) and xf.cfxfjbz='0' then xf.xfrs else 0 end)as c5lfcjrc,28 sum(case when xf.xfxs='200' and( xf.xfrs>=50 and xf.xfrs<=499) and xf.cfxfjbz='0' then 1 else 0 end)as c50lfcjjc,29 sum(case when xf.xfxs='200' and( xf.xfrs>=50 and xf.xfrs<=499) and xf.cfxfjbz='0' then  xf.xfrs else 0 end)as c50lfcjrc,30 sum(case when xf.xfxs='200' and xf.xfrs>=500 and xf.cfxfjbz='0' then 1 else 0 end)as c500lfcjjc,31 sum(case when xf.xfxs='200' and xf.xfrs>=500 and xf.cfxfjbz='0' then  xf.xfrs else 0 end)as c500lfcjrc,32 sum(case when xf.xfxs='200' and xf.xfrs<=4 and xf.cfxfjbz='1' then 1 else 0 end)as cf1lfcjjc,33 sum(case when xf.xfxs='200' and xf.xfrs<=4 and xf.cfxfjbz='1' then xf.xfrs else 0 end)as cf1lfcjrc,34 sum(case when xf.xfxs='200' and( xf.xfrs>=5 and xf.xfrs<=49) and xf.cfxfjbz='1' then 1 else 0 end)as cf5lfcjjc,35 sum(case when xf.xfxs='200' and( xf.xfrs>=5 and xf.xfrs<=49) and xf.cfxfjbz='1' then xf.xfrs else 0 end)as cf5lfcjrc,36 sum(case when xf.xfxs='200' and( xf.xfrs>=50 and xf.xfrs<=499) and xf.cfxfjbz='1' then 1 else 0 end)as cf50lfcjjc,37 sum(case when xf.xfxs='200' and( xf.xfrs>=50 and xf.xfrs<=499) and xf.cfxfjbz='1' then  xf.xfrs else 0 end)as cf50lfcjrc,38 sum(case when xf.xfxs='200' and xf.xfrs>=500 and xf.cfxfjbz='1' then 1 else 0 end)as cf500lfcjjc,39 sum(case when xf.xfxs='200' and xf.xfrs>=500 and xf.cfxfjbz='1' then  xf.xfrs else 0 end)as cf500lfcjrc,40 sum(case when xf.xfxs='200' then 1 else 0 end)as lxxiaojjc,41 sum(case when xf.xfxs='200' then xf.xfrs else 0 end)as lxxiaojrc,42 sum(case when xf.xfxs in('300','303') and xf.cfxfjbz='0'and xf.lmbz='0' then 1 else 0 end) as grts,43 sum(case when xf.xfxs in('300','303') and xf.cfxfjbz='0'and xf.lmbz='1' then 1 else 0 end) as lmts,44 45 sum(case when xf.xfxs in('300','303') and xf.cfxfjbz='1'and xf.lmbz='0' then 1 else 0 end) as cfgrts,46 47 sum(case when xf.xfxs in('300','303') and xf.cfxfjbz='1'and xf.lmbz='1' then 1 else 0 end) as cflmts,48 sum(case when xf.xfxs in('300','303') then 1 else 0 end) as xiaoji49 50   from hr_organization l,A_TJBB_quxzbmjdqzxfqk xf where  l.region_dm='500101' and   ((v_kssj1 is not null and j.adddatetime between v_kssj1 and v_jssj1) 51 or (v_kssj1 is not nulll and 1=1))  and((isquxian is not null and ion.dm = isquxian ) or (isquxian is null and 1 = 1)) and l.jglb='1200' and 52 53 l.bh=xf.djjgbh group by l.mc;54 55   end bb_quxzbmjdqzxfqk_ces;