简单统计报表例子（存储过程）
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_quxzbmjdqzxfqk 10 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 for 14 15 16 select l.mc as mc, 17 count(1) as jianci, 18 sum(case when xf.xfxs='100' then 1 else 19 case when xf.xfxs='200' then xf.xfrs else 20 case when xf.xfxs in ('300','303') then 1 21 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 xiaoji 52 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 for 11 12 13 select l.mc as mc, 14 count(1) as jianci, 15 sum(case when xf.xfxs='100' then 1 else 16 case when xf.xfxs='200' then xf.xfrs else 17 case when xf.xfxs in ('300','303') then 1 18 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 xiaoji 49 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;
