Shell+sqlldr载数卸数

1、先下载外围系统dat文件到本地服务器

#!/bin/bash
sftp_Host="10.4.1.2"
sftp_userName="gqgl"
sftp_passWord="gqgl"
sftp_port=22
#远端机器文件路径
sftpRemotePath="/myhome"
#下载到本地文件路径
sftpLocalPath="/batchfile"
sftpLoadPath=$sftpRemotePath
fileFilter=$1
cd $sftpLocalPath
# SFTP非交互式操作
sftp_download()
{
echo "下载文件"
expect <<- EOF
set timeout 5
spawn sftp -P $sftp_port $sftp_userName@$sftp_Host

expect {
"(yes/no)?" {send "yes\r"; expect_continue }
"*assword:" {send "$sftp_passWord\r"}
}
expect "sftp>"
send "cd $sftpLoadPath \r"
expect "sftp>"
set timeout -1

   send "get C_CBS_BIFM06_$fileFilter_ADD.dat \r"
   expect "sftp>"
#有多少文件就下载多少个
   send "get C_CBS_DPFM02_$fileFilter_ADD.ctl \r"
   expect "sftp>"
send "bye\r"
EOF
}
sftp_download
if [ 1$? -ne 10 ]; then #返回值不是0，存储过程运行失败
echo 1
exit 1
fi
echo 0
exit 0

2、将外围系统dat文件放进数据库

#!/bin/bash
echo 'start imp table'
id=$1
datenow=$2
tablename=$3
#往后加一天
newdate=$(date -d "$2 1 days" "+%Y%m%d")
echo ${newdate}
#
#ctl文件路径
ctl_path=/dbhome/oracle/etl/
#数据库服务器
db=10.2.4.77
#服务名
servername=gqxt
#端口号
port=1521
#数据库用户名
orauser=equity
#数据库用户名
orapasswd=equity
#昨天
#datenow=$(date -d "1 days ago" +%Y%m%d)

while true
do
uppname=$(echo $tablename | tr 'a-z' 'A-Z')
cd $ctl_path
if [ -d "/${datenow}/" ];then
echo "文件夹已存在"
else
mkdir ${datenow}
fi
cd $ctl_path/$datenow
if [ -d "/ctl" ];then
echo "文件夹已存在"
else
mkdir ctl
fi
if [ -d "/logs" ];then
echo "文件夹已存在"
else
mkdir logs
fi
cd $ctl_path
cp $tablename.ctl $ctl_path/$datenow/ctl

sqlplus -s ${orauser}/${orapasswd}@${db}:${port}/${servername} <<!
update EQUITY_ETL_TAB_LOGS set status='1',begtime=to_char(sysdate,'yyyymmdd hh24:mi:ss'),endtime=null where logid=${id};

exit;
!

sqlldr userid=equity/equity@10.2.4.77:1522/nmoadb control=$ctl_path/ctl/${tablename}.ctl log=$ctl_path/logs/$datenow/${tablename}.log

err_num=$(cat $ctl_path/logs/$datenow/${tablename}.log | grep -A4 ${uppname}:| sed -n '3p' | awk '{print $1}')
suc_num=$(cat $ctl_path/logs/$datenow/${tablename}.log | grep -A4 ${uppname}:| sed -n '2p' | awk '{print $1}')

echo $err_num
echo $suc_num

if [ $err_num -eq 0 ];then
sqlplus -s ${orauser}/${orapasswd}@${db}:${port}/${servername} <<!
update EQUITY_ETL_TAB_LOGS set error_log='$ctl_path/logs/$datenow/${tablename}.log',suc_num='${suc_num}',err_num='${err_num}',status='0',endtime=to_char(sysdate,'yyyymmdd hh24:mi:ss') where logid=${id};
exit;
!
break;
else
sqlplus -s ${orauser}/${orapasswd}@${db}:${port}/${servername} <<!
update EQUITY_ETL_TAB_LOGS set error_log='$ctl_path/logs/$datenow/${tablename}.log',suc_num='${suc_num}',err_num='${err_num}',status='2',endtime=to_char(sysdate,'yyyymmdd hh24:mi:ss') where logid=${id};
exit;
!
sleep 600
fi
done

3、本地表信息导出dat文件

echo "table name is ------$1"
export FILE_PATH='/hqtTest/'
export FILE_DATE=$(date +%Y%m%d)
export FILE_TIME=$(date +%H%M%S)
pwd
if [ -d "${FILE_PATH}${FILE_DATE}" ];then
echo ${FILE_PATH}${FILE_DATE}" is created"
else
mkdir ${FILE_PATH}${FILE_DATE}
echo $FILE_PATH$FILE_DATE
fi
export S=C_GQGL_
export LOGO=_
export ALL=_ALL
export DATA_FILE=$FILE_PATH$FILE_DATE/$S$1$LOGO${FILE_DATE}$ALL.dat
export CTL_FILE=$FILE_PATH$FILE_DATE/$S$1$LOGO${FILE_DATE}$ALL.ctl
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_GW=$ORACLE_BASE/product/gw11.2.0/dbhome_1
export ORACLE_SID=gqxt
export ORACLE_TERM=xterm
export ORA_NLS33=$ORACLE_HOME/nls/data
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_OWNER=oracle
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/usr/X11R6/lib64/
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export LC_ALL=zh_CN.UTF_8
export NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
export equity_shinfo_sql="select a.id||'$|$'||a.bus_id from equity_shinfo a where rownum <10;"
#本行社基本信息表 EQUITY_BANK_INF
export EQUITY_BANK_INF_SQL="select ID||'$|$'||BUS_ID||'$|$'||NAME||'$|$'||TYPE||'$|$'||CARD_TYPE||'$|$'||CARD_NO||'$|$'||LEGEL||'$|$'||ADDRESS||'$|$'||ESTAB_DATE||'$|$'||STOCK||'$|$'||YEAR||'$|$'||INVEST_STOCK||'$|$'||PERFER_STOCK||'$|$'||BUSINESS_TERM||'$|$'||STOCK_JE_LAST_YEAR||'$|$'||TOTAL_ASSETS||'$|$'||CAPITAL_QUAR1||'$|$'||CAPITAL_QUAR2||'$|$'||CAPITAL_QUAR3||'$|$'||CAPITAL_QUAR4||'$|$'||SUPERVISE_GRATE||'$|$'||REMARK||'$|$'||REMARK1||'$|$'||REMARK2||'$|$'||REMARK3||'$|$'||REMARK4||'$|$'||IS_EFFECT||'$|$'||WF_STATUS||'$|$'||WF_DATE||'$|$'||CREATE_ORG||'$|$'||CREATE_USER||'$|$'||CAPITAL||'$|$'||IS_HIGH_RISK||'$|$'||IS_LISTED||'$|$'||CURRENT_PRICE from EQUITY_BANK_INF where WF_STATUS='2';"
export EQUITY_BANK_INF_COUNT_SQL="select count(*)from EQUITY_BANK_INF where WF_STATUS='2';"
#黑名单信息表 EQUITY_BLACK_LIST_INF
export EQUITY_BLACK_LIST_INF_SQL="select ID||'$|$'||BUS_ID||'$|$'||NAME||'$|$'||CUST_TYPE||'$|$'||CARD_TYPE||'$|$'||CARD_NO||'$|$'||IS_EFFECT||'$|$'||ADDR||'$|$'||BELONG_ORG||'$|$'||BLACK_RSON||'$|$'||OTHER_RSON||'$|$'||CHOOSE_RSON||'$|$'||DETIAL_RSON||'$|$'||REMARK||'$|$'||REMARK1||'$|$'||REMARK2||'$|$'||REMARK3||'$|$'||REMARK4||'$|$'||CREATE_ORG||'$|$'||CREATE_USER||'$|$'||DATA_FROM||'$|$'||OLD_NAME||'$|$'||WF_STATUS from EQUITY_BLACK_LIST_INF where WF_STATUS='2';"
export EQUITY_BLACK_LIST_INF_COUNT_SQL="select count(*)from EQUITY_BLACK_LIST_INF where WF_STATUS='2';"
if [ ${1} = "EQUITY_BANK_INF" ]; then
   export EXCUTE_SQL=${EQUITY_BANK_INF_SQL}
   export EXCUTE_COUNT_SQL=${EQUITY_BANK_INF_COUNT_SQL}
elif [ ${1} = "EQUITY_BLACK_LIST_INF" ]; then
   export EXCUTE_SQL=${EQUITY_BLACK_LIST_INF_SQL}
   export EXCUTE_COUNT_SQL=${EQUITY_BLACK_LIST_INF_COUNT_SQL}
else
   echo "未匹配到对应的表"
fi
sqlplus equity/equity <<EOF
set echo off
set feedback off
set pagesize 0
set linesize 4000
set trimspool on
set term off
set termout on
set heading off
spool $DATA_FILE
$EXCUTE_SQL
spool off
exit;
EOF
sed -i '/^SQL/d' $DATA_FILE
sed -i '/^$/d' $DATA_FILE
export COUNT_RESULT=`
sqlplus -S equity/equity <<COUNT
set echo off
set term off
set heading off
${EXCUTE_COUNT_SQL}
exit;
COUNT
`
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<CTLFILES>
<CTLFILE>
<FILE_NAME>${S}${1}${LOGO}${ADD}.dat</ FILE_NAME>
<TABLE_NAME>${1}</TABLE_NAME>
<SYS_NAME>股权管理系统</SYS_NAME>
<DATA_DT>${FILE_DATE}</DATA_DT>
<LOAD_TIME>${FILE_TIME}</LOAD_TIME>
<ROW_COUNT>${COUNT_RESULT}</ROW_COUNT>
<LIST_SEP>&|&</LIST_SEP>
</CTLFILE>
</CTLFILES>" > $CTL_FILE
echo 0
exit 0

4、本地表信息文件传到其他服务器

#!/bin/bash

#SFTP配置信息
#用户名
export USER='ftpcs'
#密码
export PASSWORD='123'
#待上传文件根目录
export FILE_PATH='/hqtTest/'
export FILE_DATE=$(date +%Y%m%d)
export SRCDIR=${FILE_PATH}${FILE_DATE}
#FTP目录
DESDIR='/u01/oradata/data/export'
#IP
IP='10.2.2.12'
#端口
PORT='22'

#获取文件
cd ${SRCDIR} ;
echo $SRCDIR
#目录下的所有文件
#FILES=`ls`
#查找目录下，指定后缀的文件集合，支持通配符
FILES=`find ${SRCDIR} -name '*.*'`

#for FILE in ${FILES}
#do
# echo ${FILE}
# #发送文件 (关键部分）
# lftp -u ${USER},${PASSWORD} sftp://${IP}:${PORT} <<EOF
# cd ${DESDIR}/
# lcd ${SRCDIR}
# mput ${FILE}
# by
#EOF