SHELL+SQLLDR读取文件更新数据至ORACLE

SQLLDR

实现目标

1.拿到一个test.txt的文件，通过shell脚本使用sqlldr命令，读取文件的内容插入到Oracle数据库中
2.注意点

• sqlldr命令可以从.ctl文件中设置数据源的格式、也可以通过sqlldr命令中写入数据
• 本文主要涉及三个文件”：
  • 数据源文件test.txt（格式：123456|+|主题bala|+|xxx|+|2019 每个字段以|+|分割）
  • slldr格式文件，test.ctl
  • shell脚本文件：读取参数，生成新的ctl文件，执行sqlldr脚本，判断sqlldr脚本是否执行成功并返回

数据源文件test.txt

123456|+|主题bala|+|xxx|+|2019  
123457|+|主题bala|+|xxx|+|2020
123458|+|主题bala|+|xxx|+|2021 

sqlldr的test.ctl文件

OPTIONS(DIRECT=true,parallel=true,ERRORS=50,skip=0 ,bindsize=512000,readsize=1024000,skip_index_maintenance=true)
LOAD DATA
CHARACTERSET UTF8
INFILE "/home/file/test_20191018.txt"
APPEND INTO TABLE test
FIELDS TERMINATED BY '|+|'
TRAILING NULLCOLS (
 UUID
,TITLE                      CHAR(2000)
,URL                        CHAR(1000)
,CRAWL_TIME
)

解释：

部分关键字:

    userid -- ORACLE username/password

   control -- 控制文件

       log -- 记录的日志文件

       bad -- 坏数据文件

      data -- 数据文件

   discard -- 丢弃的数据文件

discardmax -- 允许丢弃数据的最大值        (默认全部)

      skip -- Number of logical records to skip  (默认0)

      load -- Number of logical records to load  (默认全部)

    errors -- 允许的错误记录数          (默认50)

      rows --（每次提交的记录数，默认: 常规路径 64, 直接路径 全部，所以使用直接路径的话，效率会比普通的好太多太多）

  bindsize --（ 每次提交记录的缓冲区的大小，字节为单位，默认256000)

    silent -- 禁止输出信息 (header,feedback,errors,discards,partitions)

    direct -- 使用直通路径方式导入 (默认FALSE)

   parfile -- parameter file: name of file that contains parameter specifications

  parallel -- 并行导入  

  (默认FALSE，注意：parallel并不是让一个sqlldr语句起多个进程来加载数据,而是不锁住加载表,允许别的直接路径加载. 所以要使parallel起作用,应该先将要加载的数据文件分成多个,用多个sqlldr语句同时加载,如下例: 

skip_unusable_indexes -- disallow/allow unusable indexes or index partitions(默认FALSE)

skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable(默认FALSE,这个最好设置为TRUE)

file -- file to allocate extents from      

skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (默认 FALSE)

skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (默认 FALSE)

commit_discontinued -- commit loaded rows when load is discontinued (默认 FALSE)

readsize -- size of read buffer                  (默认 1048576)

external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (默认 NOT_USED)

columnarrayrows -- number of rows for direct path column array (默认 5000)

streamsize -- size of direct path stream buffer in bytes (默认 256000)

multithreading -- use multithreading in direct path 

resumable -- enable or disable resumable for current session (默认 FALSE)

resumable_name -- text string to help identify resumable statement

resumable_timeout -- wait time (in seconds) for RESUMABLE (默认 7200)

date_cache -- size (in entries) of date conversion cache (默认 1000)

• 入库方式

************* 以下是4种装入表的方式 

　　APPEND // 原先的表有数据 就加在后面 

　　INSERT // 装载空表 如果原先的表有数据 sqlloader会停止 默认值 

　　REPLACE // 原先的表有数据 原先的数据会全部删除 

　　TRUNCATE // 指定的内容和replace的相同 会用truncate语句删除现存数据

    *************

shell代码：输入两个参数日期、表名—>执行：从指定路径读取数据源文件，读取数据库的表结构（可空和不可空等）,创建该表对应的ctl文件，执行sqlldr脚本，完成数据插入–>返回数据是否全部成功插入

#/bin/bash
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK


input_date=$1
input_table_name=$2

#oracle数据库用户名、密码、地址
oracle_name=admin
oracle_password=admin
tns=127.0.0.1:1521/orcl

#sqlldr打日志
logdir=/usr/local/logfile
#sqlldr数据文件日志
baddir=/usr/local/badfile
#sqlldr存放ctlfile的位置
ctldir=/usr/local/ctlfile
set tw=100
set encoding=utf-8

#数据源文件存放位置,每天的存放一个文件夹里
localdir=/usr/local/$input_date

if [ -d $localdir ];then
echo 'file is existed'
else
echo 'file path is not existed'
mkdir -p $localdir
fi

#函数：create_ctl_file用来每天新建一个ctl文件，用来对每天新来的数据源文件进行指定格式、sqlldr配置
create_ctl_file(){
table_name=$1
temp=${ctldir}/temp/${table_name}.lst
table_upper_name=$(echo $table_name|tr '[a-z]' '[A-Z]')
table_lower_name=$(echo $table_name|tr '[A-Z]' '[a-z]')
src_data_file_path=${localdir}/${table_lower_name}_${input_date}.txt
echo 'create ctl:'$src_data_file_path

# 使用sqlplus读取系统结构表，获取每个字段的类型，如果某个字段的长度大于sqlldr一次最大能读取的255长度，那么要一定要指定类型CHAR（100），否则会报错
cols=`sqlplus -S $oracle_name/$oracle_password@$tns <<EOF
set pagesize 0
set heading off
set echo off
set trimspool on 
set feedback off
set linesize 1000
spool $temp
select case
             when t.COLUMN_ID = 1 then
              ' '
             else
              ','
           end || RPAD(RPAD(T.column_name, 32) || case
                         when t.DATA_TYPE in ('CHAR', 'VARCHAR', 'VARCHAR2') AND
                              T.DATA_LENGTH >= 255 THEN
                          'CHAR(' || T.DATA_LENGTH || ')'
                         ELSE
                          ''
                       END,
                      100) as colm
      from user_tab_columns t
      LEFT JOIN USER_COL_COMMENTS P
        ON T.COLUMN_NAME = P.column_name
       AND T.TABLE_NAME = P.table_name
     where T.table_name = '${table_upper_name}'
     ORDER BY T.COLUMN_ID;
spool off
EOF`                                  
#
ctlfile=${ctldir}/${table_lower_name}.ctl
#将字段类型、sqlldr的配置项写入到ctlfile里去
echo $ctlfile
echo "OPTIONS(DIRECT=true,parallel=true,ERRORS=50,skip=0 ,bindsize=512000,readsize=1024000,skip_index_maintenance=true)" > $ctlfile
echo 'run DATA' >>$ctlfile
echo 'CHARACTERSET UTF8' >>$ctlfile
echo 'INFILE '\"$src_data_file_path\" >>$ctlfile
echo 'APPEND INTO TABLE '$table_upper_name >>$ctlfile
echo "FIELDS TERMINATED BY '|+|'" >>$ctlfile
echo 'TRAILING NULLCOLS (' >>$ctlfile
cat $temp  >>$ctlfile
echo ')' >>$ctlfile
}


#函数，run：执行sqlldr，判断执行结果
run(){
table_lower_name=$(echo $1|tr '[A-Z]' '[a-z]')
table_upper_name=$(echo $1|tr '[a-z]' '[A-Z]')
table_name=$table_lower_name
ctlfile=${ctldir}/$table_name.ctl
echo 'start create :'$ctlfile

#执行创建ctlfile函数
create_ctl_file $1

 
#执行sqlldr命令
runer=`sqlldr $oracle_name/$oracle_password@$tns control=$ctlfile  log=${logdir}/${table_upper_name}_${input_date}.log bad=${baddir}/${table_upper_name}_${input_date}.bad `

#runer是执行的返回字段串，最后一个字段是插入的行数，比如"1234."，去除'.'，得出成功插入的行数sql_inserted_num
sql_inserted_num=`echo  $runer|awk -F ' ' '{print $NF}'|head -c-2`

#判断是否执行成功，即判断数据源文件的数据是否都插入的表中 
file_nums=`cat $src_data_file_path | wc -l`

if [ $file_nums -eq $sql_inserted_num ];then
  echo ${table_upper_name}"插入成功"
  exit 0
else
  echo $file_nums
  echo $sql_inserted_num
  echo ${table_upper_name}"插入失败"
  exit 1
fi
}

#执行run函数
run $input_table_name