資料庫預存程序模板

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;