Oracle通過過程定期取數

---Oracle調試：
 set serveroutput on;
 sho errors
set autotrace on;
select text from user_source where name='SP_LZW_ZJ201101300004';
---linux通過過程定期取數：
#!/bin/sh
##ZJ201101300004_fee.sh
##每月定期計費資料
## 0 7 6 * * /gmcc_data/zj/zjlzw/wg/shell/ZJ201101300004_fee.sh >> /gmcc_data/zj/zjlzw/wg/shell/ZJ201101300004_fee.log & 
export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/products/10.2/db
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/Apache/Apache/bin:$PATH
export ORACLE_TERM=xterm
export LD_LIBRARY_PATH=/oracle/products/10.2/db/lib:$LD_LIBRARY_PATH
export PATH=/oracle/products/10.2/db/bin:$PATH
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
. /etc/profile
date
datepath="/gmcc_data/zj/zjlzw"
logininfo=`cat /gmcc_data/zj/zjlzw/login.sh | grep sqlplus`

datetmp2=`date +%d`

$logininfo <<EOF
--ZJ201101300004 每月定期計費資料
call  sp_lzw_ZJ201101300004();
exit;
EOF

date
exit

cat login.sql
SET NEWPAGE NONE HEADING OFF SPACE 0 PAGESIZE 0 TRIMOUT ON TRIMSPOOL ON LINESIZE 2500 colsep | feedback off termout off pages 0
set colsep |
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set feedback on

select text from user_source where name='SP_LZW_ZJ201101300004';
create or replace PROCEDURE sp_lzw_ZJ201101300004

/** HEAD
  * @name  sp_lzw_ZJ201101300004
  * @caption 處理表
  * @type 日處理
  * @parameter iv_month in varchar2 統計日期，格式：YYYYMMDD
  * @parameter oi_return out integer 執行狀態代碼，0 正常，其它 出錯
  * @description 處理表
  * @middle <無>
  * @version <版本號碼：1.0>
  * @author <wg>
  * @create-date <2011-3-23 9:08>
  * @TODO <工作計劃>
  * @version <1.0>
  * @mender <修改人>
  * @modify_date <YYYY-M-D>
  * @modify_desc <修改原因>
  * @copyright TDS

  */

 is

  vi_task_id    integer; -- 任務日誌ID
  vv_task_name  varchar2(30); -- 任務名
  vv_table_name varchar2(30); -- 表名
  vv_task_pos   varchar2(50); -- 任務位置
  vv_return     varchar2(255); --記錄過程的傳回值
  vv_err_msg    varchar2(200); -- 出錯資訊
  vi_err_code   integer; -- 出錯代碼
  vi_result     integer; -- 臨時結果

  vd_date       date; -- 日期類型的統計日期,

  vd_now_month  date; --  vd_date 的當月1日
  vd_pre_month  date; --  vd_date 的上一個月1號

  vv_date1     varchar2(10);   --轉換為字元的日期：  yyyymm
  vv_date2     varchar2(10);   --轉換為字元的日期：  yyyymm

  vv_date_now  varchar2(10);  --轉換為字元的日期：  yyyymm
  vv_date_pre  varchar2(10);  --轉換為字元的日期：  yyyymm

  vv_date_now4  varchar2(10);  --轉換為字元的日期：  yymm
  vv_date_pre4  varchar2(10);  --轉換為字元的日期：  yymm

  exc_return    exception; -- 程式中間返回自訂異常
  exc_error     exception; -- 程式出錯返回自訂異常

  vv_datacnt integer;      --臨時變數
  FileName   varchar2(128); --臨時檔案名稱

BEGIN

  execute immediate 'alter session enable parallel dml';

  /**
  * @description 變數初始化
  * @field-mapping vv_task_name = ('<不帶使用者名稱的程式名>')
  * @field-mapping vv_table_name = ('<不帶使用者名稱的表名>')
  */

   vd_date      := sysdate ;    --取當月

   vd_now_month := trunc(sysdate,'mm');  --取本月1日
   vd_pre_month := add_months(trunc(vd_date,'mm'), -1);  --取上月1日

   vv_date_pre  := to_char(vd_pre_month,'yyyymm');       --上月月份  yyyymm
   vv_date_now  := to_char(vd_now_month,'yyyymm');       --本月月份  yyyymm

   vv_date_pre4  := to_char(vd_pre_month,'yymm');       --上月月份  yymm
   vv_date_now4  := to_char(vd_now_month,'yymm');       --本月月份  yymm

  --vv_task_name  := '';
  --vv_table_name := '';

  execute immediate 'select count(1) from user_tables where table_name = upper('''||'tmp_lzw_ZJ201101300004_'||vv_date_pre||''')' into vv_datacnt;
  if ( vv_datacnt >0 ) then
    execute immediate 'drop table tmp_lzw_ZJ201101300004_'||vv_date_pre||' purge';
  end if;

---手機報計費
execute immediate '
create table  tmp_lzw_ZJ201101300004_'||vv_date_pre||' nologging as
select  /*+ parallel(a,16) */
        subno
        ,out_route
        ,in_route
        ,CALL_Date
        ,CALL_time
        ,case when ROLLBACK_FLAG = ''0'' then AFTER_MOB_FEE else 0-AFTER_MOB_FEE end AFTER_MOB_FEE_0
        ,case when ROLLBACK_FLAG = ''0'' then AFTER_TOLL_FEE else 0-AFTER_TOLL_FEE end AFTER_TOLL_FEE_0
        ,case when ROLLBACK_FLAG = ''0'' then AFTER_INF_FEE else 0-AFTER_INF_FEE end AFTER_INF_FEE_0,
        msrn
  from DGDM_DW.TB_DW_LS_CDR_data_day a
where   a.CALL_Date >=  '''||vv_date_pre4||'01''
 and    a.CALL_Date <   '''||vv_date_now4||'01''
 and    a.msrn in
 (
''110301'',
''112335'',
''110334'',
''113135'',
''113140'',
''113141'',
''113138'',
''112319'',
''113149'',
''112384'',
''110303'',
''110304'',
''110361'',
''110362'',
''110359'',
''110360'',
''112391'',
''112395'',
''112434'',
''113103'',
''113111'',
''113130'',
''113146'',
''113153'',
''113165'',
''113168'',
''110302'',
''110339'',
''110340'',
''112364'',
''113120'',
''113121'',
''112304'',
''112305'',
''112306'',
''112308'',
''112302'',
''112303'',
''112380'',
''112381'',
''113132'',
''112309'',
''112310'',
''112311'',
''112312'',
''112314'',
''113122'',
''113166'',
''112333'',
''113162'',
''113163'',
''112327'',
''112328'',
''112367'',
''112329'',
''113109'',
''112330'',
''113161'',
''112332'',
''112345'',
''112347'',
''113144'',
''112322'',
''112323'',
''112324'',
''112325'',
''112326'',
''112338'',
''113108'',
''113107'',
''112339'',
''112340'',
''112342'',
''112344'',
''112351'',
''112352'',
''113131'',
''112386'',
''112356'',
''112357'',
''112362'',
''113123'',
''112365'',
''112366'',
''112368'',
''112388'',
''113117'',
''112369'',
''112372'',
''112373'',
''112374'',
''113126'',
''112376'',
''112375'',
''112377'',
''112383'',
''112394'',
''112417'',
''113125'',
''112387'',
''113118'',
''113145'',
''113155'',
''113119'',
''112392'',
''112427'',
''113106'',
''113133'',
''113134'',
''113142'',
''113147'',
''113169'',
''113171'',
''110325'',
''-TTKX1'',
''110332'',
''110306'',
''112301'',
''113127'',
''113167'',
''110321'',
''110322'',
''113129'',
''110323'',
''110305'',
''112390'',
''112359'',
''112259'',
''112360'',
''112431'',
''133302'',
''112430'',
''110137'',
''113110'',
''113112'',
''113114'',
''113148'',
''113152'',
''113156'',
''113157'',
''113158'',
''113170'',
''113172'',
''110349'',
''113143'',
''113159'',
''113104'',
''113105'',
''113115'',
''113124'',
''113113'',
''113160'',
''113128'',
''113151'',
''110319'',
''110320'',
''100025''
 )
 and    deal_date >= to_date('''||vv_date_pre||'01'',''yyyymmdd'')
 and    deal_date <= to_date('''||vv_date_now||'09'',''yyyymmdd'')
 '
 ;

/*
SET NEWPAGE NONE HEADING OFF SPACE 0 PAGESIZE 0 TRIMOUT ON TRIMSPOOL ON LINESIZE 2500 colsep | feedback off termout off pages 0
set colsep |
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set feedback on
*/

/*匯出
spool tmp_lzw_ZJ201101300004_201102.txt
select
 SUBNO||'|'||
 round(sum(nvl(AFTER_MOB_FEE_0,0)+nvl(AFTER_TOLL_FEE_0,0)+nvl(AFTER_INF_FEE_0,0)),2)/100||'|'||
 MSRN||'|'
 from tmp_lzw_ZJ201101300004_201102
 group by SUBNO,MSRN;
 spool off;
*/

1 2 下一頁