oracle存储过程

survivals
浏览: 5723 次
性别:
来自: 北京
最近访客更多访客>>

ninghq
博主相关

博客
微博
相册
留言
关于我
文章分类

社区版块

存档分类

博客分类：
oracle
create or replace procedure project_sum(tabyear     in number,
                                        sysdeptcode in char,
                                        deptcode    in char) as
  --*-基础字段--*-
  v_category_name                varchar2(255); --单位/类别/编号
  v_dept_name                    varchar2(255); --单位名字
  v_one_id                       varchar2(255); --一级分类id
  v_id            number(10)        default 10; --dxp id
  v_sort_no       number(6)         default 10; --dxp sort_no 排序列
  v_category_one                 varchar2(255); --一级分类名字
  v_category_two                 varchar2(255); --二级分类名字
  v_one_dept_name                varchar2(255); --该一级分类下的单位名字
  v_source_name                  varchar2(255); --资金来源名字
  v_d_id          number(6)         default  1; --一级分类序号  

  --*-总计字段--*-
  total_as_investment     varchar2(255);--总计_调整前总投资
  total_investment        varchar2(255);--总计_总投资 
  total_col1              varchar2(255);--总计_2012年底累计完成投资
  total_col2              varchar2(255);--总计_2012年底累计财务用款
  total_col3              varchar2(255);--总计_调整前2013年计划投资
  total_col4              varchar2(255);--总计_2013年计划投资
  total_col5              varchar2(255);--总计_2013年已下达投资
  total_col6              varchar2(255);--总计_调整前2013年计划财务用款
  total_col7              varchar2(255);--总计_2013年计划财务用款
  total_col8              varchar2(255);--总计_2013年已下达用款
  total_col9              varchar2(255);--总计_2013年实际现金支出
  total_col10             varchar2(255);--总计_新增生产能力

  --*一级分类总投资--*-
  v_one_as_investment     varchar2(255);--一级分类_调整前总投资
  v_one_investment        varchar2(255);--一级分类_总投资  
  v_one_col1              varchar2(255);--一级分类_2012年底累计完成投资
  v_one_col2              varchar2(255);--一级分类_2012年底累计财务用款
  v_one_col3              varchar2(255);--一级分类_调整前2013年计划投资
  v_one_col4              varchar2(255);--一级分类_2013年计划投资
  v_one_col5              varchar2(255);--一级分类_2013年已下达投资
  v_one_col6              varchar2(255);--一级分类_调整前2013年计划财务用款
  v_one_col7              varchar2(255);--一级分类_2013年计划财务用款
  v_one_col8              varchar2(255);--一级分类_2013年已下达用款
  v_one_col9              varchar2(255);--一级分类_2013年实际现金支出
  v_one_col10             varchar2(255);--一级分类_新增生产能力

  --*-二级分类字段--*-
  v_two_as_investment     varchar2(255);--二级分类_调整前总投资
  v_two_investment        varchar2(255);--二级分类_总投资
  v_two_col1              varchar2(255);--二级分类_2012年底累计完成投资
  v_two_col2              varchar2(255);--二级分类_2012年底累计财务用款
  v_two_col3              varchar2(255);--二级分类_调整前2013年计划投资
  v_two_col4              varchar2(255);--二级分类_2013年计划投资
  v_two_col5              varchar2(255);--二级分类_2013年已下达投资
  v_two_col6              varchar2(255);--二级分类_调整前2013年计划财务用款
  v_two_col7              varchar2(255);--二级分类_2013年计划财务用款
  v_two_col8              varchar2(255);--二级分类_2013年已下达用款
  v_two_col9              varchar2(255);--二级分类_2013年实际现金支出
  v_two_col10             varchar2(255);--二级分类_新增生产能力

  --*单位字段--*-
  v_dept_as_investment    varchar2(255);--单位_调整前总投资
  v_dept_investment       varchar2(255);--单位_总投资
  v_dept_col1             varchar2(255);--单位_2012年底累计完成投资
  v_dept_col2             varchar2(255);--单位_2012年底累计财务用款
  v_dept_col3             varchar2(255);--单位_调整前2013年计划投资
  v_dept_col4             varchar2(255);--单位_2013年计划投资
  v_dept_col5             varchar2(255);--单位_2013年已下达投资
  v_dept_col6             varchar2(255);--单位_调整前2013年计划财务用款
  v_dept_col7             varchar2(255);--单位_2013年计划财务用款
  v_dept_col8             varchar2(255);--单位_2013年已下达用款
  v_dept_col9             varchar2(255);--单位_2013年实际现金支出
  v_dept_col10            varchar2(255);--单位_新增生产能力
  ---------------------------------------------------------------
  -- bps_project               项目表
  -- bps_prj_inves_relations   项目和投资来源关系表
  -- bps_investment_source     资金来源表
  -- bps_project_category      项目分类表
  -- bps_department            单位表
  -- bps_project_planning      计划表
  ---------------------------------------------------------------
  --*单位取值--*-
  cursor dept_current is 
    select d.department_name from bps_department d where 1 = 1;

  cursor dept_investment(v_dept_name varchar2) is --获取单位各类投资值 
    select sum(p.total_investment)
      from bps_project p,bps_project_category c,bps_department d, bps_project_planning pla
     where p.bps_category_id = c.id
       and p.bps_department_id=d.id
       and c.id = pla.category_id
       and pla.fiscal_year = '2013'
       and d.department_name=v_dept_name;

  --*一级分类取值--*- 公式(例：某一级分类总投资 = 该一级分类下所有二级分类总投资之和 或 所有单位总投资之和)
  cursor category_one_current is --获取所有一级分类 只统计 电网基建，生产性技改，非生产技改，小型基建，营销，信息，零购
    select c.category_name,c.id
      from bps_project_category c
     where c.category_level = 1
       and c.category_name != '科技项目'
       and c.category_name != '管理咨询'
       and c.category_name != '教育培训'
       and c.category_name != '非生产性大修'
       and c.category_name != '生产大修';

  --*-二级分类取值--*-公式(例：某一级分类下的二级分类总投资= 该二级分类下所有单位总投资之和)
  cursor category_two_current(v_dept_id varchar2) is --取一级分类下的二级分类游标
    select c.category_name
      from bps_project_category c
     where c.category_level=2
       and c.father_code_id=v_dept_id;

  cursor category_two_currents(v_dept_id varchar2) is --取一级分类下的二级分类的各类投资值
    select c.category_name
      from bps_project_category c
     where c.category_level=2
       and c.father_code_id=v_dept_id;

  --*单位取值--*-   
  cursor one_current(v_dept_id varchar2) is --获取所有一级分类 的单位
    select distinct d.department_name
      from bps_project          p,
           bps_department       d,
           bps_project_category c
     where p.bps_category_id = c.id
       and p.bps_department_id=d.id
       and c.father_code_id in (select id from bps_project_category ca where ca.id=v_dept_id);

  --*资金来源取值--*-     
   cursor investment_source(v_dept_id varchar2) is --获取资金来源 param:一级分类
    select s.source_name
      from bps_investment_source s,bps_project p,bps_prj_inves_relations r,bps_project_category c
     where p.bps_category_id = c.id
       and r.bps_project_id =p.id
       and r.bps_investment_source_id=s.id
       and c.father_code_id=v_dept_id;

   cursor investment_source_all is --获取所有资金来源
    select s.source_name
     from bps_investment_source s,bps_project p,bps_prj_inves_relations r,bps_project_category c
    where p.bps_category_id = c.id
      and r.bps_project_id =p.id
      and r.bps_investment_source_id=s.id;

  --*其它取值--*-
   cursor dept_investment_c(v_dept_name varchar2,v_category_one varchar2) is --获取一级分类下的单位各类投资值 
    select  pla.adjust_execution_amount
     from bps_project p,bps_project_category c,bps_department d, bps_project_planning pla
    where p.bps_category_id = c.id
      and p.bps_department_id=d.id
      and c.id = pla.category_id
      and pla.fiscal_year = '2013'
      and d.department_name=v_dept_name
      and c.category_name=v_category_one;
begin
  --dbms_output.put_line();  --插入数据前 清空dxp_42数据
   delete from dxp_42
   where tab_year = tabyear
     and dept_code = deptcode
     and sys_dept_code = sysdeptcode;  
--*总计值--*-
      insert into dxp_42(item_name, id, dept_code, sys_dept_code, tab_year, sort_no,total_investment)
      values('总计',v_id,deptcode,sysdeptcode,tabyear,v_sort_no,total_investment);
      v_id      := v_id + 1;
      v_sort_no := v_sort_no + 1;
open investment_source_all;--开启 取所有资金来源游标
loop
      fetch investment_source_all
      into v_source_name;
      exit when investment_source_all%notfound;
      insert into dxp_42(item_name, id, dept_code, sys_dept_code, tab_year, sort_no,d_source_name)
      values('',v_id,deptcode,sysdeptcode,tabyear,v_sort_no,v_source_name);
      v_id      := v_id + 1;
      v_sort_no := v_sort_no + 1;
end loop;
close investment_source_all;
--插入一行空行
      insert into dxp_42(item_name, id, dept_code, sys_dept_code, tab_year, sort_no,d_source_name)
      values('',v_id,deptcode,sysdeptcode,tabyear,v_sort_no,'');
      v_id      := v_id + 1;
      v_sort_no := v_sort_no + 1;

--*开启单位游标--*-        
open dept_current;  
loop
    fetch dept_current
    into v_dept_name; 
    exit when dept_current%notfound;
    open dept_investment(v_dept_name); --嵌套游标 param:单位名字
    loop
        fetch dept_investment
        into v_dept_investment; 
        exit when dept_investment%notfound;
        insert into dxp_42
        (item_name, id, dept_code, sys_dept_code, tab_year, sort_no,total_investment)
        values(v_dept_name,v_id,deptcode,sysdeptcode,tabyear,v_sort_no,v_dept_investment);
        v_id      := v_id + 1;
        v_sort_no := v_sort_no + 1;  
    end loop;
    close dept_investment; --关闭单位统计
end loop;
close dept_current;  --关闭单位游标
--插入一行空行  
        insert into dxp_42(item_name, id, dept_code, sys_dept_code, tab_year, sort_no,d_source_name)
        values('',v_id,deptcode,sysdeptcode,tabyear,v_sort_no,'');
        v_id      := v_id + 1;
        v_sort_no := v_sort_no + 1;
--*开启一级分类游标--*-
open category_one_current; --开启一级分类游标  先列出一行一级分类，在插入 二级分类 和 单位 实现分组目的
loop
    fetch category_one_current
    into v_category_one,v_one_id;--这里取的一级分类id 存入变量 方便下面实现动态根据一级分类id 取得相应二级分类
    exit when category_one_current%notfound;
    insert into dxp_42 (item_name, id, dept_code, sys_dept_code, tab_year, sort_no,d_id)
    values (v_category_one,v_id,deptcode,sysdeptcode,tabyear,v_sort_no,v_d_id);
    v_d_id    := v_d_id + 1; --每个一级分类 添加序号
    v_id      := v_id + 1;
    v_sort_no := v_sort_no + 1;
--*累加总计字段值--*-注:总计各类投资值是根据一级分类的资金来源拆分统计的
--	total_as_investment:=total_as_investment+v_one_as_investment;     --总计_调整前总投资
--      total_investment:=total_investment+v_one_investment;              --总计_总投资
--      total_col1:=total_col1+v_one_col1;                                --总计_2012年底累计完成投资
--	total_col2:=total_col2+v_one_col2;                                --总计_2012年底累计财务用款
--	total_col3:=total_col3+v_one_col3;                                --总计_调整前2013年计划投资
--	total_col4:=total_col4+v_one_col4;                                --总计_2013年计划投资
--	total_col5:=total_col5+v_one_col5;                                --总计_2013年已下达投资
--	total_col6:=total_col6+v_one_col6;                                --总计_调整前2013年计划财务用款
--	total_col7:=total_col7+v_one_col7;                                --总计_2013年计划财务用款
--	total_col8:=total_col8+v_one_col8;                                --总计_2013年已下达用款
--	total_col9:=total_col9+v_one_col9;                                --总计_2013年实际现金支出
--	total_col10:=total_col10+v_one_col10;                             --总计_新增生产能力
        --*累加end--*-
--插入一行空行  
        insert into dxp_42(item_name, id, dept_code, sys_dept_code, tab_year, sort_no,d_source_name)
        values('',v_id,deptcode,sysdeptcode,tabyear,v_sort_no,'');
        v_id      := v_id + 1;
        v_sort_no := v_sort_no + 1;           
open investment_source(v_one_id);--这里实现 根据资金来源分组统计  该一级分类下资金来源统计 根据该一级分类取得相应的资金来源
    loop
        fetch investment_source
        into v_source_name;
        exit when investment_source%notfound;
        insert into dxp_42 (item_name, id, dept_code, sys_dept_code, tab_year, sort_no,d_source_name)
        values ('',v_id,deptcode,sysdeptcode,tabyear,v_sort_no,v_source_name);   
        v_id      := v_id + 1;
        v_sort_no := v_sort_no + 1;
    end loop;   
close investment_source;  --关闭资金来源
--插入一行空行  
        insert into dxp_42(item_name, id, dept_code, sys_dept_code, tab_year, sort_no,d_source_name)
        values('',v_id,deptcode,sysdeptcode,tabyear,v_sort_no,'');
        v_id      := v_id + 1;
        v_sort_no := v_sort_no + 1;      
open category_two_current(v_one_id); --开启二级分类游标 根据当前一级分类查询二级分类名字
          loop  --zb 嵌套游标 插入二级分类的各类投资值
          fetch category_two_current
          into v_category_two; 
          exit when category_two_current%notfound;
          insert into dxp_42 (item_name, id, dept_code, sys_dept_code, tab_year,sort_no)
          values (v_category_two,v_id,deptcode,sysdeptcode,tabyear,v_sort_no);
          v_id      := v_id + 1;
          v_sort_no := v_sort_no + 1;
end loop;
close category_two_current;--关闭二级分类游标
--插入一行空行  
        insert into dxp_42(item_name, id, dept_code, sys_dept_code, tab_year, sort_no,d_source_name)
        values('',v_id,deptcode,sysdeptcode,tabyear,v_sort_no,'');
        v_id      := v_id + 1;
        v_sort_no := v_sort_no + 1;
open one_current(v_one_id); --开启一级分类单位 根据当前一级分类查询单位名称 以及 各类资金
          loop
          fetch one_current
          into v_one_dept_name;
          exit when one_current%notfound;
          insert into dxp_42 (item_name, id, dept_code, sys_dept_code, tab_year, sort_no) 
          values (v_one_dept_name,v_id,deptcode,sysdeptcode,tabyear,v_sort_no);
          v_id      := v_id + 1;
          v_sort_no := v_sort_no + 1;
          --*累加一级字段值--*- 注:二级分类各类投资值根据资金来源统计
 --         v_one_as_investment:=v_one_as_investment+v_two_as_investment;    --一级分类_调整前总投资
 --         v_one_investment:=v_one_investment+v_two_investment;             --一级分类_总投资  
 --         v_one_col1:=v_one_col1+v_two_col1;                               --一级分类_2012年底累计完成投资
 --         v_one_col2:=v_one_col2+v_two_col2;                               --一级分类_2012年底累计财务用款
 --         v_one_col3:=v_one_col3+v_two_col3;                               --一级分类_调整前2013年计划投资
 --         v_one_col4:=v_one_col4+v_two_col4;                               --一级分类_2013年计划投资
 --         v_one_col5:=v_one_col5+v_two_col5;                               --一级分类_2013年已下达投资
 --         v_one_col6:=v_one_col6+v_two_col6;                               --一级分类_调整前2013年计划财务用款
 --         v_one_col7:=v_one_col7+v_two_col7;                               --一级分类_2013年计划财务用款
 --         v_one_col8:=v_one_col8+v_two_col8;                               --一级分类_2013年已下达用款
 --         v_one_col9:=v_one_col9+v_two_col9;                               --一级分类_2013年实际现金支出
 --         v_one_col10:=v_one_col10+v_two_col10;                            --一级分类_新增生产能力
            --*累加end--*-
end loop;
close one_current;--关闭一级分类单位
--插入一行空行  
        insert into dxp_42(item_name, id, dept_code, sys_dept_code, tab_year, sort_no,d_source_name)
        values('',v_id,deptcode,sysdeptcode,tabyear,v_sort_no,'');
        v_id      := v_id + 1;
        v_sort_no := v_sort_no + 1;
end loop;
close category_one_current;--关闭一级分类游标
end;
/
分享到：