sql导数脚本

#!/bin/sh

export ORACLE_HOME=/oracle/products/10.2/db
export ORA_NLS33=/oracle/products/10.2/db/ocommon/nls/admin/data
export ORACLE_BASE=/oracle/products
export ORACLE_TERM=xterm
export SHELL=/bin/bash

if [ $# -gt 4 ] || [ $# -lt 3 ]
then
echo "-------------------------------------------------------------------------------------"
echo "程序名:load_tool.sh"
echo -e "功能:把数据文件导入到oracle数据库/n"
echo -e "程序参数: <地市> <表名> <导入的文件> <分隔符(可选择)>/n"

echo "<分隔符(可选择)> 的说明:若不输入指定的分隔符,程序自行判断文件分隔符"
echo "而且只会从以下分隔符中判断:"
echo -e "& && 空格 | || , ;  # ## @ @@ ~ / /n"

echo -e "<表名> 的值说明：输入的表名没建立，则程序根据数据文件自行建表."
echo "如下例子:"
echo "(1)把文件1.txt导入到深圳库里,分隔符是|,让程序自行建表,表名是tp_1_bak:"
echo "./load_tool.sh sz tp_1_bak 1.txt /"|/""
echo "(2)把文件1.txt导入到深圳库的tp_test表里,不输入指定的分隔符"
echo "./load_tool.sh sz tp_test 1.txt "
echo "(3)把文件1.txt导入到深圳库的tp_test表里,指定分隔符是,"
echo "./load_tool.sh sz tp_test 1.txt ,"
echo "--------------------------------------------------------------------------------------"
exit
fi;

###############定义变量并赋值###################
#exec_user=$1
#exec_pass=$2
exec_city=$1
exec_table=$2
exec_text=$3
exec_str=$4
is_load=0;
is_createtable=0
load_mode=APPEND

current_date=`date +"%Y%m%d%H%M%S"`

##########根据第2个参数<表名>判断是否建表#########
if [ "$exec_table" = "1" ]
then
is_createtable=1;
fi

############创建获取文件分隔符的函数###############

text_split()
{
 
   split_text=$1    #保存导入的文件
   split_str=0      #单个分隔符变量，并初始化
   i=0              #循环变量，并初始化
   is_stop=0        #是否退出循环变量，并初始化
   count_split=0   
   text_row=$2      #保存文件的总行数
 
   splitstr_arr=( '|' '||' " " '&' '&&' ',' ';' '#' '##' '@' '@@' '/' '//' )
 
   while [ $i -lt ${#splitstr_arr[@]} ] && [ "$is_stop" = "0" ]
   do
      split_str=${splitstr_arr[$i]}
 
    #当文件行数大于200行小于10000行,不相同分隔符的行数小于文件总行数的5%，就选择为分隔符
     if [ $text_row -lt 10000 ] && [ $text_row -gt 200 ]
     then
   
       diff_row=`echo "$text_row*0.05"|bc`
       diff_row=`echo $diff_row |awk '{printf "%.0f/n",$1}'`
     
       diff_row1=`grep -v -c "$split_str" $split_text`
     
       if [ $diff_row1 -lt $diff_row ]
       then
          is_stop=1
          echo $split_str

          break;
       fi
     
       if [ $diff_row1 -eq $text_row ]
       then
          count_split=`expr $count_split + 1`
       fi
   
    #当文件小于200行，不相同的分隔符行数小于10行，就选择为分隔符
    elif [ $text_row -lt 199 ]
    then
       diff_row=10
       diff_row1=`grep -v -c "$split_str" $split_text `
     
       if [ $diff_row1 -lt $diff_row ]
       then
          is_stop=1
          echo $split_str    
          break;
       fi
     
       if [ $diff_row1 -eq $text_row ]
       then
          count_split=`expr $count_split + 1`
       fi
     
    #当文件大于10000行,选择前1000行，不相同的分隔符小于20行，就选择为分隔符
    elif [ $text_row -gt 10001 ]
    then
     
       diff_row1=`head -1000 $split_text | grep -v -c "$split_str"`
       if [ $diff_row1 -lt 20 ]
       then
          is_stop=1
          echo $split_str
        
          break;
       fi
     
       if [ $diff_row1 -eq 1000 ]
       then
          count_split=`expr $count_split + 1`
       fi
     
     fi
   
     i=`expr $i + 1`
   
   done
 
  #当上述所有空格符都不符合文件的中的分隔符，就默认为只有一个字段
   if [ ${#splitstr_arr[@]} -eq $count_split ]
   then
      echo "null_split"
 
  #存在多种分隔符
   elif [ $is_stop = "0" ] && [ ${#splitstr_arr[@]} -ne $count_split ]
   then
      echo "no"
   fi
 
}

########创建数据库连接并执行脚本文件的函数########
oracle_con()
{

con_user=$1
con_pass=$2
con_constr=$3
con_file=$4
con_passsign=/
con_strsign=@

sqlplus $con_user$con_passsign$con_pass$con_strsign$con_constr << !
start $con_file
exit;
!

}


#############创建获取数据库连接串的函数###########
get_orastr()
{

   city=$1
  
   if [ "$city" = "fs" ] || [ "$city" = "zj" ] || [ "$city" = "mm" ] || /
      [ "$city" = "yj" ] || [ "$city" = "sg" ]
   then
      echo "fsboss"
  
   elif [ "$city" = "st" ] || [ "$city" = "zq" ] || [ "$city" = "jy" ] || [ "$city" = "hz" ]
   then
      echo "stboss"
  
   elif [ "$city" = "dg" ] || [ "$city" = "sw" ] || [ "$city" = "hy" ] || [ "$city" = "cz" ]
   then
      echo "dgboss"
  
   elif [ "$city" = "gz" ]
   then
      echo "gzboss"
  
   elif [ "$city" = "zs" ] || [ "$city" = "zh" ] || [ "$city" = "jm" ] || [ "$city" = "mz" ] || /
        [ "$city" = "yf" ] || [ "$city" = "qy" ]
   then
      echo "zsdb2"
  
   elif [ "$city" = "sz" ]
   then
      echo "szdb1"
     
   else
      echo "no"
      exit;
   fi

}

#####判断数据库账号和密码是否正在正确的函数#########
is_right_ora()
{

   is_ora_value=`sqlplus -S $1/$2@$3 << EOF
   set heading off
   set pagesize 0
   set verify off
   set echo off
   select 1 from dual
   exit
   END`
  
   if [ `echo "$is_ora_value" | grep "Invalid option"| wc -l` -gt 0 ]
   then
      echo "n"
   else
      echo "y"
   fi

}


###############根据输入的地市获取连接串############
exec_ora_sign=`get_orastr $exec_city`
if [ "$exec_ora_sign" = "no" ]
then
   echo -e "/n输入的地市不存在!/n"
   exit;
fi

###############判断输入的文件是否存在##############

if [ ! -s $exec_text ]
then
   echo -e "/n输入的文件$exec_text 不存在!/n"
   exit;
fi

###########判断用户的账号和密码是否正确#############

echo -n -e "/n数据库账号($exec_city所属的`echo $exec_ora_sign|awk '{print substr($0,1,2)}'`库):"
read -t 60 exec_user

if [ -z $exec_user ]
then
   echo -e "/n输入的账号为空或超时,退出程序!/n"
   exit;
fi

echo -n "数据库密码:"
read -s -t 60 exec_pass
if [ -z $exec_pass ]
then
   echo -e "/n输入的账号为空或超时,退出程序!/n"
   exit;
fi

is_usr_pass=`is_right_ora $exec_user $exec_pass $exec_ora_sign`

if [ "$is_usr_pass" = "n" ]
then
   echo -e "/n输入的账号或密码有误!/n"
   exit;
fi

###############去掉文件空行####################
sed '/^$/d' $exec_text > $exec_user$exec_text.bak
mv -f $exec_user$exec_text.bak $exec_text

##############统计输入的文档数据行数############

count_row=` wc -l  $exec_text | awk -F " " '{print $1}'`
echo -e "/n`date +"%Y-%m-%d %H:%M:%S"` 文件$exec_text的数据(不包括空行)共有$count_row行!"

###############获取文件分隔符##################

if [ -z $exec_str ]
then
   echo -e "`date +"%Y-%m-%d %H:%M:%S"` 正在获取文件数据的分隔符..."
   exec_str=`text_split $exec_text $count_row`
 
   if [ "$exec_str" = "null_split" ]
   then
      exec_str="|"
      echo -e "`date +"%Y-%m-%d %H:%M:%S"` $exec_text只有一个字段.."
   
   elif [ "$exec_str" = "no" ]
   then
      echo "`date +"%Y-%m-%d %H:%M:%S"` 请查看数据文件$exec_text,存在多种分隔符,请指定分隔符!"
      grep -v "$exec_str" $exec_text |head -3
      grep  "$exec_str" $exec_text |head -3
      exit;
 
   elif [ `echo $exec_str | awk '{print length($0)}'` -eq 0 ]
   then
      echo -e "`date +"%Y-%m-%d %H:%M:%S"` 获取文件数据的分隔符是:空格符"
      exec_str=" "

   fi
fi

################导出输入的表的字段#############

echo -e "`date +"%Y-%m-%d %H:%M:%S"` 正在导出表$exec_table的字段..."
 echo "
 spool desc_table_$current_date.txt
 set linesize 500
 set verify off
 set pagesize 0
 set term off
 set trims on
 set heading off
 set echo off
 set termout off
 set timing off
 set time off
 set trimspool off
 set feedback off
 set newpage none
 " > desc_table_$current_date.sql;
 
 echo "select (case when r1 <> 1 then COLUMN_NAME||',' else COLUMN_NAME||')' end ) COLUMN_NAME From (
select COLUMN_NAME,row_number()over(order by column_id desc ) r1
 from user_tab_columns where table_name = upper('$exec_table')
 order by column_id );
 spool off;" >> desc_table_$current_date.sql;

oracle_con $exec_user $exec_pass $exec_ora_sign desc_table_$current_date.sql > /dev/null

#################判断输入的表是否存在#############
if [ ! -s desc_table_$current_date.txt ]
then
   echo -e "`date +"%Y-%m-%d %H:%M:%S"` `echo $exec_ora_sign|awk '{print substr($0,1,2)}'`库不存在/"$exec_table/"表!"
   echo -e "`date +"%Y-%m-%d %H:%M:%S"` 默认创建表,表名:$exec_table,请留意!"
   echo "`date +"%Y-%m-%d %H:%M:%S"` 正在创建表..."

   file_num=`awk -F "$exec_str" 'BEGIN {max_nf=0} {if (max_nf < NF) max_nf=NF} END { print max_nf}' $exec_text`
   echo "create table $exec_table (" > create_table_$current_date.sql
   echo "(" > create_table_ctl_$current_date_$current_date.txt
  
   for ((i=1;i<=$file_num;i=i+1))
   do
      file_max=`awk -F "$exec_str" 'BEGIN {max=0 } {max=length($'$i')> max?length($'$i'):max} END {print max}'  $exec_text`
      file_max=`expr $file_max + 5`
  
      if [ $i -ne $file_num ]
      then
         echo "file$i varchar($file_max) default null," >> create_table_$current_date.sql
         echo "file$i," >> create_table_ctl_$current_date_$current_date.txt
      else
         echo "file$i varchar($file_max) default null);" >> create_table_$current_date.sql
         echo "file$i)" >> create_table_ctl_$current_date_$current_date.txt
      fi
  
   done

   is_table=`oracle_con $exec_user $exec_pass $exec_ora_sign create_table_$current_date.sql`
   if [ `echo $is_table | grep "invalid" | wc -l | awk -F " " '{print $1}'` != "0" ]
   then
      echo -e "/n输入的表名“$exec_table”不符合ORACLE建表的表名称规范，请重命名!/n"
      unset splitstr_arr
      rm -f create_table_$current_date.txt
      rm -f create_table_ctl_$current_date.txt
      rm -f desc_table_$current_date.txt
      rm -f desc_table_$current_date.sql
      rm -f create_table_$current_date.sql
      exit;
   fi

   echo "
   load data
   infile '$exec_text'
   $load_mode INTO TABLE $exec_table
   FIELDS TERMINATED BY '$exec_str'
   trailing nullcols " > loadtool_$current_date.ctl
   cat create_table_ctl_$current_date.txt >> loadtool_$current_date.ctl;

else
  
   #################导数模式######################

   echo -e "/n--------------------------导入模式选择----------------------------------"
   echo "1)insert 数据装载时要求表为空     2)append 保留原记录，添加新记录"
   echo "3)replace 把旧记录替换成新记录    4)truncate 快速删除原记录，添加新记录"
   echo "------------------------------------------------------------------------"
   echo -n "(按回车键,则取用模式是append保留原记录，添加新记录):"
  
   b_read_time=`date +%s`
   read -t 60 load_mode
   a_read_time=`date +%s`
   diff_time=$((a_read_time-b_read_time))
  
   if [ $diff_time -gt 59 ]
   then
     echo -e "/n60秒超时没响应，退出程序!/n"
     exit;
   fi
  
   while [ ! -z $load_mode ]
   do
      if [ "$load_mode" = "1" ]
      then
        load_mode="INSERT"
        break;
    
      elif [ "$load_mode" = "2" ]
      then
        load_mode="APPEND"
        break;
    
      elif [ "$load_mode" = "3" ]
      then
        load_mode="REPLACE"
        break;
    
      elif [ "$load_mode" = "4" ]
      then
        load_mode="TRUNCATE"
        break;
      
      else
        echo -n "请重新选择:"
        b_read_time=`date +%s`
        read -t 60 load_mode
        a_read_time=`date +%s`
        diff_time=$((a_read_time-b_read_time))
       
        if [ $diff_time -gt 59 ]
        then
           echo -e "/n60秒超时没响应，退出程序!/n"
        exit;
       
        elif [ -z $load_mode ]
        then
           load_mode=APPEND
           break;
        fi
      
      fi
   done
  
   if [ -z $load_mode ]
   then
     load_mode=APPEND;
   fi
 
 
#################创建控制文件####################

   echo "
   load data
   infile '$exec_text'
   $load_mode INTO TABLE $exec_table
   FIELDS TERMINATED BY '$exec_str'
   trailing nullcols
   ( " > loadtool_$current_date.ctl
   cat desc_table_$current_date.txt >> loadtool_$current_date.ctl;

fi

###############把文件的数据导入到数据库##############

echo "`date +"%Y-%m-%d %H:%M:%S"` 正在把文件$exec_text导入到$exec_city地市..."
sqlldr $exec_user/$exec_pass@$exec_ora_sign control=loadtool_$current_date.ctl,direct=y,log=loadtool_$current_date.log,bad=loadtool_bad_$current_date.bad > /dev/null

###############判断导数是否全部成功##################

if [ -s loadtool_bad_$current_date.bad ] && [ `sed '/^$/d' loadtool_bad_$current_date.bad | wc -l | awk -F " " '{print $1}'` -gt 1 ]
then
  echo -e "`date +"%Y-%m-%d %H:%M:%S"` $exec_text没有全部成功导入到$exec_city地市！"
  echo -e "`date +"%Y-%m-%d %H:%M:%S"` 详细情况请检查loadtool_$current_date.log和loadtool_bad_$current_date.bad！"

else
  echo -e "`date +"%Y-%m-%d %H:%M:%S"` $exec_text全部成功导入到$exec_city!"
fi

#################删除生成的相关文件###################

rm -f loadtool_$current_date.ctl
unset splitstr_arr
rm -f create_table_$current_date.txt
rm -f create_table_ctl_$current_date.txt
rm -f desc_table_$current_date.txt
rm -f desc_table_$current_date.sql
rm -f create_table_$current_date.sql

echo -e "`date +"%Y-%m-%d %H:%M:%S"` 取用导数模式是:$load_mode"
echo -e "`date +"%Y-%m-%d %H:%M:%S"` 运行完毕!/n"