Oracle通过过程定期取数
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;
*/
|
评论暂时关闭