文件导入到数据库

只有一个目录下的若干个txt文件，将其导入到数据库的表t_ax_smsblack中

• 表结构

SQL> desc t_ax_smsblack
Name           Type       Nullable  Default Comments 
----------- ------------- -------- ------- ----------- 
BLACKS      VARCHAR2(100)                  短信关键字黑名单 
DESCRIBE    VARCHAR2(20)   Y                           
CREATE_TIME  DATE          Y       sysdate 创建时间 
ISDELETED   NUMBER                         0未删除  1已删除 

• 需求\

  • create_time字段不用导入数据，采用默认值
  • describe不用导入数据，使用空值
  • isdeleted导入的时候设置为0

• 代码实现

#!/bin/bash
source /etc/profile
source ~/.bash_profile

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
###########################################################################
# SCRIPT: ax_smsblack_logfile_load.sh
# running command: sh ax_smsblack_logfile_load.sh &
###########################################################################


## The spid after starting
pid=$$

work_path=/uloc/dfjr/
data_path=${work_path}data/
script_path=${work_path}scripts/
error_path=${work_path}error/
backup_path=${work_path}backup/
progname=ax_smsblack_logfile_load

proglog_path=${script_path}proglog/
logfile=${proglog_path}$progname.log

mkdir -p ${data_path}
mkdir -p ${script_path}
mkdir -p ${error_path}
mkdir -p ${backup_path}
mkdir -p ${proglog_path}

#oracle database service
db_srv="dfjr_new/czty_dfjr_new@192.168.150.233/ZSMS"

echo ${work_path} >>$logfile

#sqlldr file type
log_f=${proglog_path}ax_smsblack_${pid}.log
bad_f=${proglog_path}ax_smsblack_${pid}.bad

ctl_file=${script_path}ax_smsblack.ctl
lv_rows=10000
lv_bindsize=8192000
lv_readsize=8192000

#declare function 
Create_Sqlldr_Ctl(){

cat>${ctl_file} <<EOF
           load data            
           infile *
           append
           into table t_ax_smsblack
           FIELDS TERMINATED BY ','
           TRAILING NULLCOLS
           (     

BLACKS   ,
ISDELETED  constant '0'
)
EOF

}

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

cd $script_path
THE_DATE=`date +"%Y-%m-%d %H:%M:%S"`
echo "[$THE_DATE $progname]:Start $progname pid:$pid......" >>  $logfile

for file in `find $data_path -name \*.txt`
do
    file_list=${script_path}"ax_smsblack.list"
    loaded_flag=0
    if [ -f ${file_list} ];then
        loaded_flag=`grep $file $file_list | wc -l > /dev/null 2>&1`
    fi
    if (( ${loaded_flag} > 0)); then
    #  #echo "The file name:$infile has loaded into the table!" >>$logfile
      continue
    fi
    if [ -s $file ]; then
        Create_Sqlldr_Ctl
        if [ -f ${ctl_file} ]; then
          dos2unix ${file} >/dev/null 2>&1

          sqlldr userid=${db_srv} control=${ctl_file} data=${file} log=${log_f} bad=${bad_f} rows=${lv_rows} bindsize=${lv_bindsize} readsize=${lv_readsize} direct=n >/dev/null 2>&1              
          if [ $? -eq 0 ]; then                       
            echo $file >>$file_list
            mv -f ${file} ${backup_path}
          else
            cat $log_f >>$logfile
            mv -f ${file} ${error_path}
          fi
       fi
    else
        echo "file size is not bigger than 0"
    fi

done