SQL Loader使用小结

hongblue

　　最近使用SQL Loader实现从CSV文件导入数据库表的功能。SQL Loader使用比较简单，功能也比较强大。但是一些细节需要注意，这里总结一下，以备日后参考。
　　
　　网上有很多资料，这里整理一下：
　　
　　关于SQL Loder的基本介绍可以参考
　　SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.
　　SQL*Loader (sqlldr) is the utility to use for high performance data loads. The data can be loaded from any text file and inserted into the database.
　　参考文献：http://www.orafaq.com/wiki/SQL*Loader_FAQ
　　
　　SQL Loader的使用介绍
　　http://blog.iyunv.com/u/7040/showart.php?id=287430
　　Oracle 的SQL*LOADER可以将外部数据加载到数据库表中。下面是SQL*LOADER的基本特点：
　　1）能装入不同数据类型文件及多个数据文件的数据
2）可装入固定格式，自由定界以及可度长格式的数据
3）可以装入二进制，压缩十进制数据
4）一次可对多个表装入数据
5）连接多个物理记录装到一个记录中
6）对一单记录分解再装入到表中
7）可以用 数对制定列生成唯一的KEY
8）可对磁盘或 磁带数据文件装入制表中
9）提供装入错误报告
10）可以将文件中的整型字符串，自动转成压缩十进制并装入列表中。
　　1.2控制文件
控制文件是用一种语言写的文本文件，这个文本文件能被SQL*LOADER识别。SQL*LOADER根据控制文件可以找到需要加载的数据。并且分析和解释这些数据。控制文件由三个部分组成：
　　a) 全局选件，行，跳过的记录数等；
b) INFILE子句指定的输入数据； (如果使用sqlldr的data参数，这里用*, "INFILE *" )
c)  数据特性说明。
　　
　　SQL Loader使用实例可参考
　　http://www.blogjava.net/Unmi/archive/2009/01/05/249956.html
　　sqlldr 的使用，有两种使用方法：
1. 只使用一个控制文件，在这个控制文件中包含数据; (用BEGINDATA设置数据)
2. 使用一个控制文件(作为模板) 和一个数据文件.
　　
　　第二种方式比较常用，因为它比较灵活，模版和数据分离。
　　这里要了解一下控制文件control file 的用法
　　
　　http://www.cs.umbc.edu/portal/help/oracle8/server.815/a67792/ch05.htm
　　
　　sql loader 的基本语法格式可以参考http://blog.csdn.net/youjianbo_han_87/archive/2009/03/17/3998958.aspx
　　
　　个人建议用关键字的方式，而不是默认位置的方式，这样比较清楚，比如
　　>>sqlldr userid=username/password@sidcontrol=path/file.ctl data=path/data.csv
　　
　　在unix上使用sql loader，可以把sqlldr需要的参数配置在环境文件evironment file中，比如.sqlldr_param.env
　　
　　export LOG_FILE_PATH=/home/loader/logs
　　export LOG_FILE_NAME=loadfile
　　export DB_USERNAME="mydb"
　　export DB_PASSWORD="123456"
　　export DB_SID="testDB"
　　
　　这样可以用shell脚步来调用，实现参数的灵活配置。比如
　　#!/usr/bin/ksh
　　#########################
　　##   loadfile.sh
　　## param1 $1  environment file name
　　## param2 $2  input file name
　　#########################
　　. ${1}
　　
　　Log()
　　{
　　echo `date`: $* >> "${LOG_FILE_PATH}"/"${LOG_FILE_NAME}_`date +%Y%m%d`.log"
　　}
　　
　　Log "****Begin to load file******"
　　
　　v_input_file_name=${2}
　　
　　sqlldr ${DB_USERNAME}/${DB_PASSWORD}@{DB_SID} control=${CONTROL_FILE_PATH}${CONTROL_FILE_NAME} data=${INPUT_FILE_PATH}/${v_input_file_name}
　　
　　然后就可以调用sql loader了。
　　>>sh loadfile.sh envpath/.sqlldr_param.env employee.csv
　　
　　在使用sql loader的过程中经常会遇到的问题是control file控制文件的写法
　　control file 默认是字符类型，对于数据库中的数值类型，也可以用默认字符类型处理，例如
　　
　　OPTIONS(SKIP=1)
　　LOAD DATA
　　INFILE *
　　TRUNCATE
　　INTO TABLE EMPLOYEE
　　FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
　　TRAILING NULLCOLS           
　　{
　　NAME    "trim(:NAME)",
　　AGE       "trim(:AGE)",
　　JOIN_DATE  SYSDATE 
　　}
　　
　　如果数据库表EMPLOYEE中的字段JOIN_DATE是Date类型，可以如上所示用SYSDATE来插入系统当前日期。如果JOIN_DATE是TIMESTAMP类型，用SYSDATE就会出错。这时需要写成
　　
　　JOIN_DATE EXPRESSION "CURRENT_TIMESTAMP(3)"
　　
　　trim()的用法是先插入记录，再修改成trim(:NAME)。因此会出现导入时数据库异常，解决方法是创建一个临时表，字段长度大一些，SQL Loader执行成功后，再导入主表。可以用存储过程来执行导入操作。