最近给现场写的一个shell脚本

#!/bin/ksh
#########################################################################
#充值数据ETL抽取和FTP传输脚本,Encle,2007-3-27 14:27
#执行方法:
#带输入参数(可以指定充值日期作为输入参数): etl_ftp.sh 20070326
#不带输入参数(默认操作系统日期的前一天): etl_ftp.sh
#########################################################################

#获取输入的充值日期参数或者系统日期的前一天
if [ $# -eq 1 ]
then
dat=$1
else
year=`date +%Y`
mon=`date +%m`
day=`date +%d`
day=`expr $day - 1`
if [ $day -eq 0 ]
then
mon=`expr $mon - 1`
if [ $mon -eq 0 ]
then
mon=12
year=`expr $year - 1`
fi
lastline=`cal $mon $year | grep "[0-9]" | tail -1`
day=`echo $lastline | sed "s/.* ([0-9][0-9]*)/1/"`
fi
dat="$year$mon$day"
fi

#########################根据实际现场环境修改如下信息######################
#ftp主机配置
ODS_IP=10.5.239.4
ODS_USR=wps
ODS_PWD=wps
ODS_DIR=/ods/receive/wps
#数据库配置
ORACLE_SID=ORCL;export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab | grep ^$ORACLE_SID: | cut -f2 -d':'`
PATH=$ORACLE_HOME/bin:$PATH;export PATH
ORACLE_IP=172.19.96.4;export ORACLE_IP
ORACLE_PORT=1521;export ORACLE_PORT
MBOSS_USR=wacos;export MBOSS_USR
MBOSS_PWD=oss;export MBOSS_PWD
#本地设置
LOG_FILE=./etl_ftp.log;export LOG_FILE #shell处理日志
TEMP_DIR=./;export TEMP_DIR #文件临时存放目录
#获取充值数据SQL配置
SQL1="select to_date(createdate,'yyyy-mm-dd hh24:mi:ss'),phonenumber,'IVR',deposit
from rechargelog_96333
where createdate between to_date('$dat','yyyy-mm-dd hh24:mi:ss')
and to_date('$dat','yyyy-mm-dd hh24:mi:ss')+1-1/24/3600
and clientid=1 and cardtype=3000; "
#获取余额数据SQL配置
SQL2="select to_date('$dat','yyyy-mm-dd hh24:mi:ss'),b.servicecode,a.currentdeposit
from currentdeposit a,service b
where a.currentdepositid=b.currentdepositid
and b.status<>4 and b.priceplanid>1; "
#########################################################################

echo "==========操作开始: `date`==========">>$LOG_FILE
echo "充值日期: $dat">>$LOG_FILE

#执行SQL
echo "获取充值数据SQL: $SQL1">>$LOG_FILE
echo "获取余额数据SQL: $SQL2">>$LOG_FILE
sqlplus -S $MBOSS_USR/$MBOSS_PWD@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=$ORACLE_IP)(Port=$ORACLE_PORT))(CONNECT_DATA=(SID=$ORACLE_SID))) >>/dev/null <set echo off feedback off newpage none pagesize 5000 linesize 500 verify off
set term off trims on heading off timing off verify off numwidth 18
set colsep ,
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
spool SHCTWXTX_PHS_UT_YE_$dat.csv
$SQL2
spool off
#写文件信息到控制文件
!echo "SHCTWXTX_PHS_UT_YE_$dat.csv" | awk '{printf "%50s",$1}' >> INTESETTLE.ctl
!awk 'END {print NR}' SHCTWXTX_PHS_UT_YE_$dat.csv | awk '{printf "%10s",$1}' >> INTESETTLE.ctl
!date "+%Y-%m-%d %H:%M:%S" | awk -F/ '{printf "%20sn",$1}' >> INTESETTLE.ctl
spool SHCTWXTX_PHS_UT_CZ_$dat.csv
$SQL1
spool off
#写文件信息到控制文件
!echo "SHCTWXTX_PHS_UT_CZ_$dat.csv" | awk '{printf "%50s",$1}' >> INTESETTLE.ctl
!awk 'END {print NR}' SHCTWXTX_PHS_UT_CZ_$dat.csv | awk '{printf "%10s",$1}' >> INTESETTLE.ctl
!date "+%Y-%m-%d %H:%M:%S" | awk -F/ '{printf "%20sn",$1}' >> INTESETTLE.ctl
quit
END

#ftp上ODS主机并上传文件
echo "ftp开始: `date`">>$LOG_FILE
cd $TEMP_DIR
ftp -inv >>/dev/null <open $ODS_IP
user $ODS_USR $ODS_PWD
cd $ODS_DIR
mput SHCTWXTX_PHS_UT_*_$dat.csv INTESETTLE.ctl
bye
!
echo "ftp完毕.">>$LOG_FILE

#删除旧文件
cd $TEMP_DIR
rm -rf SHCTWXTX_PHS_UT_*

echo "==========操作结束: `date`==========">>$LOG_FILE
echo "">>$LOG_FILE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10881/viewspace-178018/，如需转载，请注明出处，否则将追究法律责任。