shell学习小结：循环导出一个表数据，通过ftp命令上传后删除

#!/bin/bash

Auth:Zz

Script:导出一个表的数据，ftp上传后删除，以应对磁盘空间不够的问题。

sysname=$1

date=$2

tablelist=`grep "^${sysname};" /etl/etl/prog/tablename.list | awk -F ";" '{print $2}' | awk 'gsub(/[[:blank:]]*/,"")'`

user=`grep "^${sysname}%=" /etl/etl/prog/tablename.list |awk -F "%=" '{print $2}' | awk 'gsub(/[[:blank:]]*/,"")'`

password=`grep "^${sysname}%=" /etl/etl/prog/tablename.list|awk -F "%=" '{print $3}' | awk -F 'gsub(/[[:blank:]]*/,"")'`

tns=`grep "^{sysname}%=" /etl/etl/prog/tablename.list | awk -F '{print $4}'|awk -F 'gsub(/[[:blank:]]*/,"")'`

ftp_ip=`grep "^ftp_ip%=" /etl/etl/prog/tablename.list |awk -F "%=" '{print $2}' |awk -F 'gsub(/[[:blank:]]*/,"")'`

ftp_password=`grep "^ftp_ip%=" /etl/etl/prog/tablename.list | awk -F "%=" '{print $3}' |awk -F 'gsub(/[[:blank:]]*/,"")'`

localdmp_path=`grep "^path%=" /etl/etl/prog/tablename.list | awk -F "%=" '{print $2}' | awk -F 'gsub(/[[:blank:]]*/,"")'`

ftpdmp_path=`grep "^path%=" /etl/etl/prog/tablename.list | awk -F "%={print $3}"|awk -F 'gsub(/[[:blank:]]*/,"")'`

if [ -n "$2" ]              ###没有日期，导出全量表，有日期导出增量数据

then

for tablename in $tablelist

do

bdp_etl_date=`sqlplus -s ${user}/${password}@${tns}<<!

set heading off;

set feedback off;

set verify off;

select bdp_etl_date from ${tablename} where rownum =1;

!`                                            ##dds表etl时间有bdp_etl_date和bdp_modify_date两个，要做判断

if [ ${bdp_etl_date} -ge "0" ] || [ -z "$bdp_etl_date" ]

then

dmpname=`echo $tablename|sed 's/\./_/g'`

exp ${user}/${password}@$tns file=${localdmp_ptah}/${dmpname}.dmp tables=${tablename} query="\where bdp_etl_date=${date}\"

ftp_flg_cmd="

open ${ftp_ip}\n

prompt off\n

verbose off\n

user ${ftp_user} ${ftp_password}\n

lcd ${loacldmp_path}\n

cd ${ftpdmp_path}\n

bin\n

put ${dmpname}.dmp

bye\n

"

ftp_log=`echo ${ftp_flg_cmd}|ftp -n -v`     #上传dmp文件，并得到ftp日志

cd ${localdmp_path}

rm ${dmpname}.dmp

echo "rm ${dmpname}.dmp"

echo ${ftp_log}

else

dmpname=`echo $tablename|sed 's/\./_/g'`

exp ${user}/${password}@$tns file=${localdmp_ptah}/${dmpname}.dmp tables=${tablename} query="\where bdp_modify_date=${date}\"

ftp_flg_cmd="

open ${ftp_ip}\n

prompt off\n

verbose off\n

user ${ftp_user} ${ftp_password}\n

lcd ${loacldmp_path}\n

cd ${ftpdmp_path}\n

bin\n

put ${dmpname}.dmp

bye\n

"

ftp_log=`echo ${ftp_flg_cmd}|ftp -n -v`

cd ${localdmp_path}

rm ${dmpname}.dmp

echo "rm ${dmpname}.dmp"

echo ${ftp_log}

fi

done

else

for tablename in $tablelist

do

dmpname=`echo $tablename|sed 's/\./_/g'`

exp ${user}/${password}@$tns file=${localdmp_ptah}/${dmpname}.dmp tables=${tablename}

ftp_flg_cmd="

open ${ftp_ip}\n

prompt off\n

verbose off\n

user ${ftp_user} ${ftp_password}\n

lcd ${loacldmp_path}\n

cd ${ftpdmp_path}\n

prom\n

bin\n

put ${dmpname}.dmp

bye\n

"

ftp_log=`echo ${ftp_flg_cmd}|ftp -n -v`

cd ${localdmp_path}

rm ${dmpname}.dmp

echo "rm ${dmpname}.dmp"

echo ${ftp_log}

done

cd  /etl/etl/prog

fi

#################################################

将ftp上传步骤写成函数可以使脚本更简洁

ftp_function(){

ftp_ip=$1

ftp_user=$2

ftp_password=$3

loacldmp_path=$4

ftpdmp_path=$5

dmpname=$6

ftp_flg_cmd="

open ${ftp_ip}\n

prompt off\n

verbose off\n

user ${ftp_user} ${ftp_password}\n

lcd ${loacldmp_path}\n

cd ${ftpdmp_path}\n

prom\n

bin\n

put ${dmpname}.dmp

bye\n

"

ftp_log=`echo ${ftp_flg_cmd}|ftp -n -v`

cd ${localdmp_path}

rm ${dmpname}.dmp

echo "rm ${dmpname}.dmp"

echo ${ftp_log}

}

###############################################

tablename.list

######导出的表清单
DDS;DDS.ADD_TABLETEST1

DDS;DDS.ADD_TABLETEST2

DDS_ALL;DDS.ALL_TABLETEST1

DDS_ALL;DDS.ALL_TABLETEST2

######数据库用户名和密码及TNS

DDS%=dds%=dds%=bdpdb

DDS_ALL%=dds%=dds%=bdpdb

######ftp_ip用户名和密码

ftp_ip%=168.5.16.189%=etl%=etl

######dmp导出本地路径和ftp上传路径

path%=/etl/data/receive%=/etl/data/receive/test