数据库存储过程模板

create or replace procedure SP_OM_(过程名应明显表达程序功能)(IC_STAT_CYCLE in  varchar2,IC_CORP_HEAD in varchar2 default null) as
/***************************************************************************************************************
    版    本: v0.0.1
    功    能: (描述程序功能)
    前置条件：(填写程序运行所依赖的前置程序)
    源    表：(填写程序所需要使用到的源表)
    结果  表：(填写程序生成的结果表名)
    逻辑描述: (描述程序实现逻辑)
    参数说明：IC_STAT_CYCLE ：统计周期(月报为：YYYYMM;日报为：YYYYMMDD)
         IC_CORP_HEAD  : 分公司编码(如：广州为：GZ)
    运行频率：每月
    创建信息：(作者姓名 提交发版日期)
    修改日志: [格式：修改内容  XXX(作者）  日期]
    日   志1:
    日   志2：
   
    //描述性注释示例
    功    能: 服务话务量（月报）
    前置条件：任务66003
    源    表：OM_SERV_TIME_MID_YYYYMM
    结果  表：OM_SERV_TIME_YYYYMM
    逻辑描述: 从中间表汇总服务话务量
    运行频率：每月
    创建信息：XXX 20110619
    //示例结束
**************************************************************************************************************/
  --变量定义规范：遵循数据库操作规范
  dataerror      EXCEPTION;     --自定义异常类型
   vc_error_code   varchar2(20);     --异常信息代码
   vc_error_text   varchar2(200);    --异常信息
  
    vc_stat_cycle   varchar2(20); --统计周期
    vc_sql          varchar2(4000);--SQL变量，用以存放动态SQL
    vc_corp_id      varchar2(10); --分公司代码，如：200（代表广州）
    vc_corp_name    varchar2(30);  --分公司名称，如：广州
begin
 --判断传入的统计参数是否有误
 if length(IC_STAT_CYCLE) <> (月报为6，日报为8) then
     raise DATAERROR;
  end if;

 dbms_output.put_line('直销客户概况（月报）开始!');
 
 vc_stat_cycle := substr(ic_stat_cycle,0,6);

 --IC_CORP_HEAD:在分公司用户下可不用输入，在odsc_cent下调，必须输入。
  if IC_CORP_HEAD is null then
     select corp_id,corp_name into vc_corp_id,vc_corp_name from odsc_cent.rpt_param_config where upper(corp_head) = upper(substr(user,-2,2));
  else
     select corp_id,corp_name into vc_corp_id,vc_corp_name from odsc_cent.rpt_param_config where upper(corp_head) = upper(IC_CORP_HEAD);
  end if;
---------------------------------------主程序处理---------------------------------------------------------------------

  sp_write_log(vc_stat_cycle,'SP_OM_CCUST_SUMMARY','0','直销客户概况（月报）开始');

  if is_table_exists('OM_CCUST_SUMMARY_'||vc_stat_cycle) then
     execute immediate ' drop table OM_CCUST_SUMMARY_'||vc_stat_cycle;
  end if;

  vc_sql := 'create table OM_CCUST_SUMMARY_'||vc_stat_cycle||' as select * from OM_CCUST_SUMMARY_201012 where rownum<1';
 execute immediate vc_sql;
  commit;
  vc_sql := '
  insert into OM_CCUST_SUMMARY_'||vc_stat_cycle||'
       (ENT_ID,
        ENT_NBR,
        ENT_NAME,
        STAT_CYCLE,
        CITY_ID,
        cust_num,
        SERV_NUM,
        PSTN_NUM,
        ADSL_NUM,
        IPTV_NUM,
        CDMA_NUM,
        WXKD_NUM,
        PHS_NUM)
 select /*+parallel(t,5)*/ t.ccust_id ENT_ID,t.ccust_code ENT_NBR,t.ccust_name ENT_NAME,
        '||vc_stat_cycle||'01 STAT_CYCLE,
        '||vc_corp_id||' CITY_ID,count(distinct t.cust_id) as cust_num,
        count(distinct t.serv_id) SERV_NUM,
        count(distinct case when t.terminal_id=10 then t.serv_id end) PSTN_NUM,
        sum(case when prod_id in (48,52,57,51,47,950,56,1004) then 1 else 0 end) ADSL_NUM,
        sum(case when prod_id =1004   then 1 else 0 end) IPTV_NUM,
        count(distinct case when t.terminal_id=30 then t.serv_id end) CDMA_NUM,
        sum(case when t.prod_id=710 then 1 else 0 end) WXKD_NUM,
        count(distinct case when t.terminal_id=20 then t.serv_id end) PHS_NUM
   from cmms_serv_pre_'||vc_stat_cycle||' t
  group by t.ccust_id ,t.ccust_code,t.ccust_name ';
 execute immediate vc_sql;
  commit;

 sp_write_log(vc_stat_cycle,'SP_OM_CCUST_SUMMARY','88','直销客户概况（月报）完成');

 dbms_output.put_line('直销客户概况（月报）结束!');

exception
   WHEN DATAERROR THEN
      vc_error_code := -1;
      vc_error_text := '输入参数错误，参数必须是月份格式(例:200804)';
      sp_write_log(IC_STAT_CYCLE, 'SP_OM_CCUST_SUMMARY', vc_error_code, vc_error_text);
      commit;
    WHEN OTHERS THEN rollback;
      DBMS_OUTPUT.PUT_LINE('异常号:' || substr(to_char(sqlcode), 1, 200) || ';异常信息:' || sqlerrm);
     vc_error_code := substr(SQLCODE,1,20);
     vc_error_text := substr(SQLERRM,1,200);
     sp_write_log(IC_STAT_CYCLE,'SP_OM_CCUST_SUMMARY',vc_error_code,vc_error_text || VC_SQL);
end SP_OM_CCUST_SUMMARY;