就业管理树形结构

create or replace procedure JYGL_INIT_SJSBYB(p_dwh in varchar2,
                                           o_msg OUT VARCHAR2) is
  /**
  *  就业管理_数据上报原表初始化
  *  功能：把九张原表的数据集union all 放到JYGL_SJSBYB表中

  *  2015年10月16日

  p_dwh 单位号
  */

  v_caluse VARCHAR2(50);  --单位号查询条件，不存在赋值1=1，存在则赋值dwh=p_dwh
  p_sql  VARCHAR(10000);

begin
  IF p_dwh is not null THEN
      v_caluse:=' and dwh='''||p_dwh||'''';
    ELSE
      v_caluse:=' and 1=1';
    END IF;

    DBMS_OUTPUT.PUT_LINE('caluse条件:'||v_caluse);
  --先删除JYGL_SJSBYB原表数据再插入
  execute immediate 'truncate table JYGL_SJSBYB';
  commit;
  --把九张表已经上报的数据插入原表JYGL_SJSBYB
    p_sql:='insert into JYGL_SJSBYB(YBZJ, NF, YF, DWH, LX)
            SELECT gzhyzj, nf, yf, dwh, ''jygz'' lx FROM JYGL_ZKJYGZHY WHERE sfsb = 1 '|| v_caluse||'
            union all
            SELECT ZCXCZJ, nf, yf, dwh, ''zcxc'' lx FROM JYGL_ZCXCQK WHERE sfsb = 1 '|| v_caluse||'
            union all
            SELECT JBXXZJ, nf, yf, dwh, ''jbxx'' lx FROM JYGL_JBXX WHERE 1=1 '|| v_caluse||'
            union all
            SELECT ZPHZJ, nf, yf, dwh, ''zph'' lx FROM JYGL_ZPH WHERE sfsb = 1 '|| v_caluse||'
            union all
            SELECT ZPHZJ, nf, yf, dwh, ''zpjh'' lx FROM JYGL_ZPJH WHERE sfsb = 1 '|| v_caluse||'
            union all
            SELECT XJHZJ, nf, yf, dwh, ''xjh'' lx FROM JYGL_XJH WHERE sfsb = 1 '|| v_caluse||'
            union all
            SELECT JZZJ, nf, yf, dwh, ''jzzt'' lx FROM JYGL_JZZTBG WHERE sfsb = 1 '|| v_caluse||'
            union all
            SELECT DYZJ, nf, yf, dwh, ''yrdw'' lx FROM JYGL_YRDWGZ WHERE sfsb = 1 '|| v_caluse||'
            union all
            SELECT KYZJ, nf, yf, dwh, ''jyky'' lx FROM JYGL_JYKYQK WHERE sfsb = 1 '|| v_caluse||'
            ';
  execute immediate p_sql;
  commit;

  exception
    when others then
    o_msg:='程序运行出现内部错误。';
      rollback;
end JYGL_INIT_SJSBYB;

 

create or replace procedure JYGL_QUERY_TREE(p_nf  in varchar2,
                                           o_msg OUT VARCHAR2) is
  /**
  *  就业管理_数据上报_树形结构
  *  功能：把原表的数据集组合为树形结构
  *  2015年10月16日

  p_nf 年份
  */

begin
  ---把原表数据拆分成有父子关系的tree数据结构形式，插入JYGL_TREESJ树表中
  --先删除JYGL_SJSBYB原表数据再插入
  execute immediate 'truncate table JYGL_TREESJ';
  commit;
  insert into JYGL_TREESJ(ID, TITLE, CS, SSID, NF,YF,LX,SFLEAF)
  SELECT ID, TITLE, CS, SSID, NF,YF,LX,SFLEAF FROM JYGL_VIEW_TREESJ WHERE nf=p_nf ;  --添加年份条件查询
  commit;
  exception
    when others then
    o_msg:='程序运行出现内部错误。';
      rollback;

end JYGL_QUERY_TREE;

 

-------------------查询视图------

CREATE OR REPLACE VIEW JYGL_VIEW_TREESJ AS
(
---项目统计
SELECT nf||lx id,
case lx
when 'jygz' then '就业工作会议情况'
when 'zcxc' then '政策宣传情况'
when 'jbxx' then '基本信息'
when 'zph' then '招聘会'
when 'zpjh' then '招聘计划'
when 'xjh' then '宣讲会'
when 'jzzt' then '讲座专题'
when 'yrdw' then '用人单位跟踪调研'
else '就业科研情况'
end
title,count(1)||'次' cs,'-1' ssid,nf,null yf,lx,'false' sfleaf FROM JYGL_SJSBYB WHERE 1=1 group by nf,lx
---月份统计
union all
SELECT id,title,cs,ssid,nf,yf,lx,sfleaf from
(SELECT nf||yf||lx id,yf||'月份' title,count(1)||'次' cs,nf||lx ssid,nf,yf,lx,'false' sfleaf FROM JYGL_SJSBYB WHERE 1=1
group by nf,yf,lx order by to_number(yf))
union all
---院系统计
SELECT id,title,cs,ssid,nf,yf,lx,sfleaf from
(SELECT nf||yf||lx||'-'||dwh id,dw.dwmc title,count(1)||'次' cs,nf||yf||lx ssid,nf,yf,lx,'true' sfleaf FROM JYGL_SJSBYB
left join xtgl_dwjbsjzl dw on dwh = dw.dwdm
 WHERE 1=1 group by nf,yf,dwh,dw.dwmc,lx order by to_number(yf)));