流程圖貼不上,就看註釋吧,寫得很詳細得

#!/usr/bin/sh

# 名字：ftp.sh

# 功能：從伺服器端取檔案並從伺服器端刪除，然後sqlldr到庫中，並呼叫過程，然後備份資料

# 作者：wang.zhihong1@zte.com.cn

# 要求：

#1、伺服器端生成檔案完畢後才能mv到制定路徑等待ftpget.sh去取

#2、要求生成得檔名不能是"found"

#3、要求正確設定$ORACLE_HOME ，在$ORACLE_HOME/bin下能找到sqlldr

#4、需要建立以下路徑並在本指令碼正確設定：SOURCE_PATH SOURCE_PATH BACKUP_PATH LOG_PATH

#5、使用了sed去掉空格，需要確認sed得路徑是不是/usr/bin/sed,如果不是，需要修改

cd

. .profile

#ftp伺服器端ip地址及登陸使用者名稱/密碼

SERVER_IP=10.40.90.231

FTP_USERNAME=oracle

FTP_PASSWORD=oracle

#ftp伺服器端目標路徑

SOURCE_PATH=/home/oracle/wangzh/source_dir

#本機得到資料存放路徑

DEST_PATH=/home/oracle/wangzh/dest_dir

#本機得到資料後處理完畢備份路徑

BACKUP_PATH=/home/oracle/wangzh/backup

#本機日誌路徑，用於記錄ftp和sqlldr日誌

LOG_PATH=/home/oracle/wangzh/log

#用於按天建備份目錄，初步定為每天執行一次

DATE=`date '+%Y%m%d`

#日誌：時間資訊 #程式設計注意事項：如果發現什麼地方Log沒有作用一般來說是大小寫不對

Log ()

{

echo [`date '+%Y%m%d %H:%M:%S'` $$] $*

}

#呼叫過程並判斷返回值 #程式設計注意事項： EOF一定要居左開頭，並且大小寫要對應

CallProcedure ()

{

/home/oracle/product/bin/sqlplus -s thirdgs_user/thirdgs_user<

variable v_out number

call p_bill_cdr_generate($1,:v_out);

print v_out

exit

EOF

}

#呼叫過程後無論成功與否,都要清除表中的資料

TruncateAfterCallProcedure ()

{

/home/oracle/product/bin/sqlplus -s thirdgs_user/thirdgs_user<

truncate table t_TMP_bill_sms_cdr;

exit

EOF

}

##################獲取檔案##################

cd $DEST_PATH

#建立日誌檔案

exec>>$LOG_PATH/ftp`date '+%Y%m%d'`.log 2>&1

Log "############"

#開始獲取檔案列表

Log "Begin to get filelist..."

/usr/bin/ftp -i -n $SERVER_IP<

user $FTP_USERNAME $FTP_PASSWORD

bin

cd $SOURCE_PATH

ls * $DEST_PATH/filelist.txt

bye

LISTFILE

#獲取檔案列表結束

Log "ok to get filelist..."

#判斷有無檔案

if [ ! -s $DEST_PATH/filelist.txt ];then

Log "No file received......"

rm -f $DEST_PATH/filelist.txt

Log "############"

exit

fi

#開始傳輸檔案

Log "transfering start..."

for _FILENAME in `more $DEST_PATH/filelist.txt |awk '{print $NF}'` ; do

#如果沒有檔案 filelist.txt內容為"* not found"

if [ $_FILENAME = "found" ];then

Log "No file received..."

rm -f $DEST_PATH/filelist.txt

exit

fi

/usr/bin/ftp -i -n $SERVER_IP<

user $FTP_USERNAME $FTP_PASSWORD

bin

cd $SOURCE_PATH

get $_FILENAME $DEST_PATH/$_FILENAME

delete $_FILENAME

bye

GETFILES

done

#獲取檔案成功

Log "get files ok"

Log "transfering completed."

Log " "

Log "############"

Log " "

Log "make the sqlldr control file begin."

cat $DEST_PATH/filelist.txt|grep 'MO'>$DEST_PATH/mofilelist.txt

cat $DEST_PATH/filelist.txt|grep 'MT'>$DEST_PATH/mtfilelist.txt

#############開始處理mo#############

if [ -s $DEST_PATH/mofilelist.txt ];then

log "make mo sqlldr control file."

echo "load data ">$DEST_PATH/mo.ctl

for _FILENAME in `more $DEST_PATH/mofilelist.txt |awk '{print $NF}'` ; do

/usr/bin/sed 's/ //g' $_FILENAME>$_FILENAME.tmp

mv $_FILENAME.tmp $_FILENAME

echo "infile '"$_FILENAME"'" >>$DEST_PATH/mo.ctl

done

echo "append into table t_TMP_bill_sms_cdr ">>$DEST_PATH/mo.ctl

echo "fields terminated by ";" ">>$DEST_PATH/mo.ctl

echo "( ">>$DEST_PATH/mo.ctl

echo "Message_ID "nvl(:Message_ID,0)" , ">>$DEST_PATH/mo.ctl

echo "Msg_Type , ">>$DEST_PATH/mo.ctl

echo "Charge_Term_ID , ">>$DEST_PATH/mo.ctl

echo "Dest_Term_ID , ">>$DEST_PATH/mo.ctl

echo "Service_ID , ">>$DEST_PATH/mo.ctl

echo "Fee_Type , ">>$DEST_PATH/mo.ctl

echo "Fee_Code "nvl(:Fee_Code,0)" , ">>$DEST_PATH/mo.ctl

echo "Fixed_Fee "nvl(:Fixed_Fee,0)" , ">>$DEST_PATH/mo.ctl

echo "Priority , ">>$DEST_PATH/mo.ctl

echo "Msg_Length "nvl(:Msg_Length,0)" , ">>$DEST_PATH/mo.ctl

echo "Area_Code , ">>$DEST_PATH/mo.ctl

echo "SMG_No , ">>$DEST_PATH/mo.ctl

echo "Fwd_SMG_No , ">>$DEST_PATH/mo.ctl

echo "SMC_No , ">>$DEST_PATH/mo.ctl

echo "Recv_Time , ">>$DEST_PATH/mo.ctl

echo "Done_Time , ">>$DEST_PATH/mo.ctl

echo "Msg_ID "nvl(:Msg_ID,0)" ">>$DEST_PATH/mo.ctl

echo ") ">>$DEST_PATH/mo.ctl

Log "make mo sqlldr control file ok."

Log "mo sqlldr begin."

#開始sqlldr

$ORACLE_HOME/bin/sqlldr thirdgs_user/thirdgs_user control=$DEST_PATH/mo.ctl >$LOG_PATH/mosqlldr.log

if [ $? -ne 0 ];then

#如果sqlldr失敗

Log "mo sqlldr error."

Log "############"

MO_LDR=-1

else

Log "mo sqlldr end."

MO_LDR=0

fi

if [ $MO_LDR -eq 0 ];then

Log "mo call procedure begin"

#呼叫過程並判斷返回值傳入引數為1時處理mo話單

CallProcedure 1|grep '0'

if [ $? -ne 0 ];then

Log "mo call procedure failed"

Log "############"

MO_OK=-1

else

Log "mo call procedure sucess"

fi

#無論過程執行成功與否,清除資料

TruncateAfterCallProcedure ()

else

MO_OK=-2

fi

Log " "

Log "############"

Log " "

############處理mo結束############

############開始處理

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/47869/viewspace-792249/，如需轉載，請註明出處，否則將追究法律責任。

shell指令碼----轉正答辯得一部分(到目前為止,寫得覺得比較好得一個shell)----從目標機獲取檔案,生成控制檔案,呼叫過程處理然後備份

相關文章