多功能倒数脚本

#!/bin/bash
#script. name unload.sh
#author  marco xi
#created 2011/01/05
#updated 2011/01/05
#version v2.0
##############################################################

#初始参数
logfile=./`basename $0`.log
usrid="username/password@dbname"
#数据分隔符
colsep="|"
#初始化数据文件和SQL
p_file="UNKNOWN_"`date "+%Y-%m-%d %H:%M:%S"`.TXT
p_sql="select * from dual"

#日志处理函数
writelog()
{
 echo `date "+%Y-%m-%d %H:%M:%S"` " $1" | tee -a $logfile
}

#帮助函数
help()
{
 writelog "the usage of unload.sh: sh unload.sh -h|H|f|F|s|S|t|T data_file sql_file|sql|table [colsep]"
}

#处理帮助信息
if [ "$1" == "-h" ] || [ "$1" == "-H" ]; then
   help
   exit 0
fi

#检查参数个数
if [ "$#" -lt 3 ] || [ "$#" -gt 4 ]; then
   writelog "Error: the number of parameters is not correct!"
   help
   exit 0
fi

case $1 in
-f) p_file="${2}"
    p_sql="@${3}"
    ;;
-F) p_file="${2}"
    p_sql="@${3}"
    ;;
-s) p_file="${2}"
    p_sql="$3"
    ;;
-S) p_file="${2}"
    p_sql="$3"
    ;;
-t) p_file="${2}"
    p_sql="select * from ${3}"
    ;;
-T) p_file="${2}"
    p_sql="select * from ${3}"
    ;;
*)  writelog "Error: the usage of parameters is not correct!"
    help
    exit 0
    ;;
esac

if [ "$#" -eq 4 ]; then
   colsep="$4"
fi

p_tmp_file="临时_${p_file}"

writelog "unloading data start ... ..."
sqlplus $usrid </dev/null
   set colsep $colsep;
   set echo off;
   set heading off;
   set feedback off;
   set pagesize 0;
   set linesize 5000;
   set termout off;
   set trimout on ;
   set trimspool on;
   spool ${p_file};
         ${p_sql};
   spool off;
exit
EOF

if [ "$?" -ne 0 ]; then
   writelog "Error: $usrid is error!"
   writelog "please check the username and password."
   exit 0
fi

#错误提示
err_msg1=`tail -50 $p_file | grep "ERROR at line"`
err_msg2=`tail -50 $p_file | grep "ORA"`

if [ "${err_msg1}OK" != "OK" ]; then
   writelog "Error: some errors occured in ${p_sql}"
   writelog "${err_msg1}"
   writelog "${err_msg2}"
   exit 0
fi

writelog "begining to process the file ${p_file} ... ..."
sed -e '/SQL>/d' -e 's/ //g' $p_file > $p_tmp_file
rm -f $p_file
mv $p_tmp_file $p_file
data_rows=`wc -l $p_file | awk '{print $1}'`
writelog "export $data_rows rows"

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

转载于:http://blog.itpub.net/21126685/viewspace-683344/