mysql从oracle取数_Oracle通过过程定期取数

---oracle调试： set serveroutput on; sho errorsset autotrace on;select text from user_source where name=

---Oracle调试：

set serveroutput on;

sho errors

set autotrace on;

select text from user_source where;

---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

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;

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

* @author * @create-date <2011-3-23 9:08>

* @TODO

* @version <1.0>

* @mender

* @modify_date * @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;

*/

，

本文原创发布php中文网，转载请注明出处，感谢您的尊重！