数据装载脚本

#!/bin/bash
#script  load.sh
#author  marco xi
#created 2011/01/06
#version v1.0
####################################################################################################

#初始化参数
userid=" scott/tiger@pdb"
logdir=./"log"
loadlog=./"load.log"
load_ind='R'
colsep="'|'"
time_stamp=`date "+%Y-%m-%d-%H:%M:%S"`

#日志函数
writelog()
{
 echo "${time_stamp} $1" | tee -a $loadlog
}

#产生控制文件
make_ctl_file()
{
  local p_infile=$1
  local p_table=$2
  local p_load_ind=$3
  local p_colsep=$4
 
  #处理装载类型
  local p_load_typ="REPLACE"
  case $p_load_ind in
    r)p_load_typ="REPLACE" #先清空表再装载
      ;;
    R)p_load_typ="REPLACE" #先清空表再装载
      ;;
    i)p_load_typ="INSERT"  #给空表装载（如果表中有记录则退出）
      ;;
    I)p_load_typ="INSERT"  #给空表装载（如果表中有记录则退出）
      ;;
    a)p_load_typ="APPEND"  #在原表数据基础上插入
      ;;
    A)p_load_typ="APPEND"  #在原表数据基础上插入
      ;;
    *)p_load_typ="REPLACE" #先清空表再装载数据
      ;;
  esac
 
  local p_ctlfile="${logdir}/${table}_${time_stamp}.ctl"
  local p_tab_desc="${logdir}/${table}_${time_stamp}.desc"
 
  #获取表结构
  sqlplus $userid </dev/null
  set echo off;
  set heading off;
  set feedback off;
  set pagesize 0;
  set linesize 100;
  set termout off;
  set trimout on ;
  set trimspool on;
  spool $p_tab_desc;
  desc  $p_table;
  spool off;
  exit
EOF
 
  #检查是否成功连接数据库
  if [ $? -ne 0 ]; then
     writelog "Error: $usrid is not correct, please check it!"
     exit 1
  fi
 
  #检查表是否存在
  local p_err_msg=`head -5 $p_tab_desc | grep "object $p_table does not exist"`
  if [ "${p_err_msg}OK" != "OK" ]; then
     writelog "Error: ${p_err_msg}!"
     exit 1
  fi
   
  #产生控制文件
  if [ -f $p_tab_desc ]; then
     local p_num=`wc -l $p_tab_desc | awk '{print $1}'`
     echo "LOAD DATA INFILE $p_infile $p_load_typ INTO TABLE $p_table FIELDS TERMINATED BY $p_colsep TRAILING NULLCOLS (" > $p_ctlfile
     sed -e '1,3d' -e '/SQL>/d' -e '/^$/d' $p_tab_desc |awk -v num=`expr $p_num - 5` '{print (NR < num ? $1"," : $1"\n)")}' >> $p_ctlfile
     rm -f $p_tab_desc
  else
     writelog "Error: $p_tab_desc doesn't exist!"
     exit 1
  fi
  return 0
}

#主程序
case $# in
  2) table=$1
     infile="${2}"
     ;;
  3) table=$1
     infile="${2}"
     load_ind="'""{$3}""'"
     ;;
  *) writelog "Error: the usage of load.sh is not correct!"
     writelog "Usage: sh load.sh table infile [load_type]"
     exit 1
     ;;
esac

ctlfile="${logdir}/${table}_${time_stamp}.ctl"
logfile="${logdir}/${table}_${time_stamp}.log"
badfile="${logdir}/${table}_${time_stamp}.bad"
dscfile="${logdir}/${table}_${time_stamp}.dsc"

#检查数据文件
if [ -f $infile ]; then
   writelog "Note:  there are "`wc -l $infile | awk '{print $1 }'`" rows in ${infile}."
else
   writelog "Error: $infile doesn't exist!"
   exit 1
fi

#产生控制文件
make_ctl_file $infile $table $load_ind $colsep

#检查控制文件是否产生
if [ $? -eq 0 ] && [ -f $ctlfile ]; then
   writelog "Note:  the control file is $ctlfile."
else
   writelog "Error: Making $ctlfile failed!"
   exit 1
fi

#装载数据
echo sqlldr $userid \
     control=$ctlfile \
     log=$logfile \
     bad=$badfile \
     discard=$dscfile \
     direct=true \
     > ./"load_${table}_${time_stamp}".sh
sqlldr $userid \
       control=$ctlfile \
       log=$logfile \
       bad=$badfile \
       discard=$dscfile \
       direct=true \
       >/dev/null

#核查装数结果
rtn="$?"
if [ $rtn -ne 0 ]; then
   writelog "Error: Loading data failed!"
   exit 1
else
   writelog "Note:`grep "Rows successfully loaded" $logfile`"
   exit 0
fi

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

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