流程图贴不上,就看注释吧,写得很详细得

#!/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一定要居左开头，并且大小写要对应

#调用过程后无论成功与否,都要清除表中的数据

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

fi

Log " "

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

Log " "

############处理mo结束############

############开始处理

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