Shell同步数据到oracle数据库

　　某数据库有几张表更新，本地数据库增量更新数据，用脚本定时执行实现。
　　由于无blob/clob字段,使用sqlldr导入数据:查询目的数据库,以"|"分割导出所有数据,保存在./data/tablename.new
　　比较 tablename.new和tablename.old，将 tablename.new有,而tablename.old没有的数据更新至目的数据库，并添加至tablename.old
　　./data/tablename.ctl存放sqlldr控制文件
　　./log 存放相关日志
　　执行时发现bash中的while创建了子shell，不能保存变量的值，解决办法：
　　1、使用其他shell，如：ksh
　　2、形如 while read line ;do ... ;done < file
　　代码如下：
  1 #!/usr/bin/ksh 
  2 
  3 DEBUG="false"
  4 if [ $DEBUG = "true" ];then
  5     orig_dir="."
  6 else
  7     cd
  8     . ./.profile
  9     orig_dir="${HOME}"
 10 fi
 11 v_date=`date +%Y%m%d%H%M%S`
 12 ###############################################################################
 13 # 日志文件，大于20M时压缩半数行
 14 cd $orig_dir
 15 log="$orig_dir/log/alpha.log"
 16 [ ! -d $orig_dir/log ] && mkdir $orig_dir/log
 17 [ ! -d $orig_dir/cfg ] && mkdir $orig_dir/cfg
 18 [ ! -d $orig_dir/data ] && mkdir $orig_dir/data
 19 if [ -e $log ];then
 20     log_size=`ls -l $log | awk '{printf("%d",$5)}'`
 21     if [ -n "$log_size" -a $log_size -gt 20971520 ];then
 22         half_line=`awk 'END{printf("%d",NR/2)}' $log`
 23         perl -ni -e "print if($.>$half_line)" $log
 24     fi
 25 fi
 26 ###############################################################################
 27 # 写日志
 28 wlog() {
 29     echo "`date +%Y-%m-%d' '%H:%M:%S`: $*">>$log
 30 }
 31 ###############################################################################
 32 # 函数:get_col_type 获取列、类型 
 33 # 传递参数:用户名 密码 数据库名 表所有者 表名
 34  get_col_type() {
 35     db_user=$1
 36     db_pass=$2
 37     db_name=$3
 38     tb_own=$4
 39     tb_name=$5
 40     tmp=./cfg/${tb_name}.tmp
 41     tb_name="${tb_own}.${tb_name}"
 42 sqlplus ${db_user}/${db_pass}@${db_name}<<EOF
 43     set heading off feedback off pagesize 0 verify off echo off
 44     spool $tmp
 45         desc ${tb_name};
 46     spool off
 47     quit
 48 EOF
 49 }
 50 ###############################################################################
 51 # 函数:make_ctl 生成控制文件
 52 # 传递参数:目的表名
 53  make_ctl() {
 54     d_name=$1
 55     ctl_file=./data/${d_name}.ctl
 56     echo "load data" > $ctl_file
 57     echo "infile './data/${d_name}.unl'" >> $ctl_file
 58     echo "append into table ${d_name}" >> $ctl_file
 59     echo "fields terminated by '|'" >> $ctl_file
 60     echo "(" >> $ctl_file
 61     cat ./cfg/${d_name}.ini|grep -v '^[ 	]*$'|grep -v '^#'|while read column col_type
 62     do
 63         case $col_type in
 64             NUMBER|VARCHAR|VARCHAR2) 
 65                 echo "$column," >> $ctl_file ;;
 66             DATE)
 67                 echo "$column "to_date(:$column,'yyyymmddhh24miss')"," >> $ctl_file ;;
 68             *)
 69                 wlog "未识别的类型:$col_type" ;;
 70         esac
 71     done
 72     echo ")" >> $ctl_file
 73     # 替换末尾的,)为)
 74     perl -pi -e 'undef $/;s/,
?
)/
)/' $ctl_file
 75 }
 76 
 77 ###############################################################################
 78 # 函数:select_sql 查询数据
 79 # 传递参数:用户名 密码 数据库名 数据文件名 表所有者 查询语句 表名
 80 # select CALL_ID||'|'||to_char(END_TIME,'yyyymmddhh24miss')||'|'||CALLER||'|'||SERVICE_NO||'|'||CALL_PURPOSE from tablename; 
 81  select_sql() {
 82     db_user=$1
 83     db_pass=$2
 84     db_name=$3
 85     data_file=$4
 86     tb_own=$5
 87     sql_line=$6
 88     tb_name=$7
 89     sql_line="$sql_line from ${tb_own}.${tb_name}"
 90     wlog "查询sql:$sql_line"
 91     # set closep '|'
 92 sqlplus -S /nolog<<EOF
 93     set heading off feedback off newpage none pagesize 0 echo off termout off trimout on trimspool on linesize 800
 94     conn ${db_user}/${db_pass}@${db_name}
 95     spool ${data_file} 
 96       $sql_line;
 97     spool off
 98     quit
 99 EOF
100 }
101 
102 ###############################################################################
103 # 函数:handle 处理数据
104 # 传递参数:配置文件
105  handle() {
106     table_name=$1
107     cat $table_name|grep -v '^#'|while read suser sname dname
108     do
109         # 如果dname为空,默认使用sname
110         [ -z "$dname" ] && dname=$sname
111         sname=`echo $sname | tr 'A-Z' 'a-z'`
112         dname=`echo $dname | tr 'A-Z' 'a-z'`
113     
114         [ -e ./log/${dname}.log ] && : > ./log/${dname}.log
115         [ -e ./log/${dname}.bad ] && : > ./log/${dname}.bad
116         [ -e ./log/${dname}.unl ] && : > ./log/${dname}.unl
117 
118         if [ ! -e ./cfg/${dname}.ini ];then
119             wlog "不存在./cfg/${dname}.ini,开始获取${dname}列配置..."
120             get_col_type dest_db_user dest_db_pass DBNAME dest_db_user $dname
121             cat ./cfg/${dname}.tmp|grep -v "^SQL>" |grep -v " Name " |grep -v " -------" | tr -d '15'|awk '{gsub(/[0-9]?([0-9]+)/,"",$0);print $1,$NF}' > ./cfg/${dname}.ini    
122         fi        
123         wlog "根据./cfg/${dname}.ini拼接查询sql..."
124         wlog "${dname}列配置为:"
125         line="select"
126         cat ./cfg/${dname}.ini|grep -v '^[ 	]*$'|grep -v '^#'|while read column col_type
127         do
128             wlog "$column $col_type"
129             case $col_type in
130                 NUMBER|VARCHAR|VARCHAR2)
131                     line="${line} $column;" ;;
132                 DATE)
133                     line="${line} to_char($column,'yyyymmddhh24miss');" ;;
134                 *)
135                     wlog "未识别的类型:$col_type" ;;
136             esac
137         done
138         [ $DEBUG = "true" ] && wlog "line:$line"
139         # 去掉最后一个分号，替换所有分号为|
140         line=${line%;*}
141         line=${line//,/"||'|'||"}
142         [ $DEBUG = "true" ] && wlog "line:$line"
143         
144         # 不存在$sname.old时创建$sname.old
145         [ ! -e ./data/${sname}.old ] && touch ./data/${sname}.old
146         
147         if [ ! -s ./data/${sname}.old ];then
148             wlog "./data/${sname}.old为空，从目的表获取数据..."
149             select_sql dest_db_user dest_db_pass DBNAME ./data/${sname}.old dest_db_user "$line" $dname
150             wlog "目的表数据获取成功,从源表获取数据..."
151         fi
152             
153         select_sql source_db_user source_db_pass SDBNAME ./data/${sname}.new $suser "$line" $sname
154         # 对比文件，选出在new中但不在old中的行
155     if [ -s ./data/${sname}.old ];then
156         awk 'NR==FNR{if($0 !~ /^[ 	]*$/)arr[$0]+=1;next}{if($0 in arr || $0 ~ /^[ 	]*$/);else print;}' ./data/${sname}.old ./data/${sname}.new > ./data/${dname}.unl
157     else
158         cp ./data/${sname}.new ./data/${dname}.unl 
159     fi        
160         data_lines=`awk 'END{print NR}' ./data/${dname}.unl`        
161         if [ ! -e ./data/${dname}.ctl ];then
162             wlog "不存在./data/${dname}.ctl,开始生成./data/${dname}.ctl "
163             make_ctl ${dname}
164         fi
165 
166         if [ $data_lines -eq 0 ];then
167             wlog "本次无数据需要更新"
168         else
169             wlog "本次需要更新${data_lines}条数据"
170             wlog "执行sqlldr..."
171             sqlldr dest_db_user/dest_db_pass@DBNAME control=./data/${dname}.ctl direct=true log=./log/${dname}.log bad=./log/${dname}.bad
172             succ_cnt=`grep successfully ./log/${dname}.log | awk '{print $1}'`
173             bad_cnt=0
174             [ -e ./log/${dname}.bad ] && bad_cnt=`wc -l ./log/${dname}.bad | awk '{print $1}'`
175             wlog "成功更新:${succ_cnt}条数据,${bad_cnt}条失败"
176         fi
177 
178         # 执行完毕，将${dname}.unl添加至${sname}.old
179         cat ./data/${dname}.unl >> ./data/${sname}.old
180     done
181 }
182 ###############################################################################
183 # main
184  main() {
185     wlog "+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
186     wlog "开始执行"
187     handle $1
188     wlog "执行完毕"
189     wlog "+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
190 }
191 
192 if [ $# -ne 1 ];then
193     echo "Usage $0 file"
194     exit 1
195 else
196     main $1
197 fi