"地瓜哥"博客网

多表关联下的测试数据制造方法系列

1. 多表关联下的测试数据制造方法（一）：思路详解
2. 多表关联下的测试数据制造方法（二）：将数据导出为CSV文件

　　在上一节，D瓜哥给大家介绍了一下如何使用存储过程，将数据导出成CSV文件。既然有数据文件了，我们该如何将这些数据导入到数据库中呢？！Oracle数据库提供了SQL Loader这样一个将数据文件导入到数据库中的功能。这篇文章将给大家介绍一下SQL Loader的用法。另外，再炒炒剩饭，把D瓜哥以前使用TCL语言写的脚本分享给大家。

SQL Loader简介

概念

　　SQL Loader是ORACLE的数据加载工具，通常用来将操作系统文件（数据）迁移到ORACLE数据库中。SQL Loader是大型数据仓库选择使用的加载方法，因为它提供了最快速的途径（DIRECT，PARALLEL）。

用法

　　首先，建立需要导入数据的表。如果存在就不需要新建了。

　　其次，编写一个控制文件。内容如下：

LOAD DATA
INFILE 'data.dat'
BADFILE 'data.bad'
DISCARDMAX 1000

INSERT
 INTO TABLE emp
FIELDS TERMINATED BY ","
(
empno NUMBER(4) NOT NULL,
ename CHAR(10),
job CHAR(9),
mgr NUMBER(4),
hiredate DATE 'YYYYMMDD',
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
)

　　最后，SQL Loader的使用也很简单，只需要一条指令。如下：

sqlldr USERID=scott/tiger CONTROL=load1.ctl DIRECT=TRUE PARALLEL=true
# USERID是配置用户名和密码的
# CONTROL是制定配置文件的
# DIRECT设置导入模式为Direct模式
# PARALLEL设置可以并行，同时导入多个文件

　　补充一句，控制文件高亮的一行是控制SQL Loader进行何种操作的。SQL Loader共提供四种操作分类：

1. insert，为缺省方式，在数据装载开始时要求表为空；
2. append，在表中追加新记录
3. replace，删除旧记录，替换成新装载的记录；
4. truncate，同上

　　这里，只给大家介绍这么多。如果想了解详细情况，请阅读Oracle提供的官方文档，文档中详细描述了SQL Loader的方方面面。

TCL导入脚本

　　上面简单介绍了一些SQL Loader的一些知识。下面给大家分享要给由TCL写成的使用SQL Loader导入数据的脚本。

　　关于TCL语言运行环境的配置，请看“D瓜哥的开源项目：CompareDatamodel”，D瓜哥在这篇文章里做过介绍。

配置文件

　　这个配置文件不是上面提到的”控制文件”。废话不多，直接上内容：

data_repeat_msg|已经汇总过数据，请勿重复汇总。
# 下面指定文件存放路径
file_path|c://  
# 数据文件的分隔符。
separater|,
# 包裹数据项的符号
enclosedSymbol|,
# data_ok_msg|数据汇总完毕。
insert_table_failure_prompt|导入失败

# 数据库连接信息
db_sid|ORCL
db_user|SCOTT
db_password|tiger

读取配置文件

　　有配置文件了，需要有读取配置文件的脚本，如下：

# **  此脚本用来读取data_dic.txt字典文件，并将相应信息保存到label全局数组里
# **  创建日期：20100816
# **  创建人员：付骋
# **  修改日期：
# **  修改人员：
global label
set fid [open "$absoluteCurrentPath//data_dic.txt" r]
while {[eof  $fid] != 1} {
	set curLine [gets $fid]
	# 遇到空行或者以#开头的行，忽略
	if {[regexp {(^(\s)*#)|(^(\s)*$)} $curLine]} {
		continue
	}
	set curLineList [split $curLine "|"]
	# 数组索引
	set index [lindex $curLineList 0]
	# 对应值
	set value [lindex $curLineList 1]
	set label($index) $value
}
close $fid

　　注意，这个脚本不能直接使用。需要导入到其他文件才能用。具体用法见下面。

导入脚本

　　上面所有的铺垫工作已经做完。现在该主角登场了。导入脚本如下：

#!/bin/bash
# 040_load_data.tcl \
exec tclsh "$0" ${1+"$@"}
#encoding convertto "UTF-8"

# **  此脚本用来读取data_dic.txt字典文件，并将相应信息保存到label全局数组里
# **  创建日期：20100816
# **  创建人员：D瓜哥，http://www.diguage.com/
# **  修改日期：
# **  修改人员：

encoding system identity

package require Oratcl

# 获得当前路径
set currentPath [file dirname [info script]]
cd $currentPath
#获得当前绝对路径
set absoluteCurrentPath [pwd]

# 导入字典文件读取源文件
source "$absoluteCurrentPath//read_data_dic.tcl"

################################################################
#procedure query_tableFieldtype
#查询要插入数据表的表结构类型说明
#    sInsertTableName 需要插入的表名称
#    columnNamesList  列顺序
#    dateFormat       数据文件中的日期格式。默认是YYYYMMDDHH24MISS。请根据实际情况修改。
################################################################
proc query_tableFieldtype {sInsertTableName columnNamesList fhandleSelect {dateFormat "YYYYMMDDHH24MISS"} } {  
	global label
	
	set listFieldLength [llength $columnNamesList]
	set strSql "select column_name, decode(data_type, 'VARCHAR2', 'CHAR NULLIF (' || column_name || '=BLANKS) \"trim(:' || column_name || ')\"', 'FLOAT', 'DECIMAL EXTERNAL NULLIF(' || column_name || '=BLANKS)', 'NUMBER', decode(data_precision, 0, 'INTEGER EXTERNAL NULLIF (' || column_name || '=BLANKS)', decode(data_scale, 0, 'INTEGER EXTERNAL NULLIF (' || column_name || '=BLANKS)', 'DECIMAL EXTERNAL NULLIF (' || column_name || '=BLANKS)')), 'DATE', 'DATE \"[string trim $dateFormat]\" NULLIF (' || column_name || '=BLANKS)', null) data_type"
	append strSql " from USER_TAB_COLUMNS where table_name =upper('"
	append strSql $sInsertTableName "') and column_name in ("
	append strSql upper('[join $columnNamesList '),upper(']'))
	
	puts "数据类型：$strSql"
	
	if [catch {orasql $fhandleSelect $strSql} errmsg] {
		error $errmsg
	} elseif {$errmsg != 0 && $errmsg != 100 && $errmsg != 1403} {
		error $errmsg
	}
	
	set listFieldAndType [list]
	while {[oramsg $fhandleSelect] == 0} {
		orafetch $fhandleSelect  -datavariable rowResult -command {
			set sFieldName [lindex $rowResult 0]
			lappend listFieldAndType $sFieldName
			set sFieldType [lindex $rowResult 1]
			lappend listFieldAndType $sFieldType
		}
	}
	
	#获取表字段类型时,因查询不能保证字段先后顺序,加入如下判断
	set listFieldLength [llength $columnNamesList]
	set listFieldType [list]
	for {set i 0} {$i<$listFieldLength} {incr i} {
	   set fieldName [lindex $columnNamesList $i]
	   set fieldName [string trim $fieldName ']
	   for {set j 0} {$j<$listFieldLength} {incr j} {
		  set dbFieldName [lindex $listFieldAndType [expr $j*2]]
		  if {[string toupper $fieldName]==[string toupper $dbFieldName]} {
			lappend listFieldType [lindex $listFieldAndType [expr $j*2+1]]
			break
		  }
	   }
	}
	return $listFieldType  
}


#################################################################
#procedure creat_sqlLoaderCtl
#拼装sqlLoader控制文件
#    tableName    表名
#    columnName   列名
#    columnType   列数据类型
#    dataFileName 源数据文件名
#    loadType     导入类型
#    ctlFileName  需要生成临时的控制文件名
#    separater    数据文件中，各个数据项的分隔符
#    isAppend     数据的添加方式：如果传true，则是添加；如果传false，则会将表中原来的数据删除再添加。
#   （注意：删除时使用truncate，不可恢复。）
#################################################################
proc creat_sqlLoaderCtl {tableName columnName columnType dataFileName ctlFileName separater enclosedSymbol {isAppend true} } {
	set ctlFile [open $ctlFileName w]
	set fieldLength [llength $columnName]
	puts $ctlFile "LOAD DATA"
	puts $ctlFile "INFILE \"$dataFileName\""
	if {$isAppend} {
		puts $ctlFile "APPEND"
	} else {
		puts $ctlFile "TRUNCATE"
	}
	puts $ctlFile "INTO TABLE $tableName"
	puts $ctlFile "FIELDS TERMINATED BY \"[string trim $separater]\""  ;#文件的分隔符
	puts $ctlFile "OPTIONALLY ENCLOSED BY '[string trim $enclosedSymbol]'"  ;#文件的分隔符
	#puts $ctlFile "TRAILING NULLCOLS"
	puts $ctlFile (
	for {set i 0} {$i<$fieldLength } {incr i} {
	   if {$i==[expr $fieldLength-1]} { 
		   puts $ctlFile "[string toupper [lindex $columnName $i]] [lindex $columnType $i] "
	   } else { 
		   puts $ctlFile "[string toupper [lindex $columnName $i]] [lindex $columnType $i],"
	   }
	}
	puts $ctlFile )
	close $ctlFile
	return $ctlFileName
}

proc getDealFileInfo {sInsertTableName columnNamesList isAppend  periodType indiDate fileNamesList} {
	
	global label
	global env
	global argv
#	global sDataBaseUser
	
	global fhandleSelect
#	global fhandleInsert
	
	if [catch {set dbhandle [db_connect $label(db_sid) $label(db_user) $label(db_password)]} logon_errmsg] {
		  error logon_errmsg
	}
	
	if [catch {set fhandleSelect [db_open $dbhandle] } open_errmsg]   {
		  error $open_errmsg
	}
	
	global sSysDate
	global sWorkFilePath
	
	set listField [list]
	set sFilePath [string trim "$label(file_path)"]
	set sWorkFilePath [string trim "$label(file_path)"]
	set sFieldString ""
	#set sStartTime [get_currentDate $fhandleSelect]
	set llRecNum 0
	
	set sqldrLog $env(AGENTAPPLOG)/[lindex $argv 1]\_[lindex $argv 3]_
	
	set listFieldType [query_tableFieldtype $sInsertTableName $columnNamesList $fhandleSelect]
	
	#=====根据文件路径加文件名称判断改目录下是否存在改文件=====    
	foreach sFileName $fileNamesList {
		set sPathAndFileName ""
		append sPathAndFileName $sFilePath
		append sPathAndFileName [string trim $sFileName]
		
		puts $sPathAndFileName
		
		if { ! [file exists $sPathAndFileName] } {
			error "文件不存在,请检查文件:$sPathAndFileName"
		}
		#=====生成临时SQLLADER控件文件=====
		set ctlFileName [string trim $sWorkFilePath]sqlloader/sql_loader_[string trim $sInsertTableName]_[string trim $indiDate]_[string trim $periodType].ctl
		puts "控制文件：$ctlFileName"
		
		set ctlFileName [creat_sqlLoaderCtl $sInsertTableName $columnNamesList $listFieldType $sPathAndFileName $ctlFileName $label(separater) $label(enclosedSymbol) $isAppend]
		
		#拼接SQL Loader日志文件名称
		append sqldrLog [clock format [clock seconds] -format "%Y%m%d%H%M%S"]
		append sqldrLog ".sqldr.log"
		
		#执行SQLLOADER,导入数据到表
		set loadHasError false
		set errorInfo false
		if {[catch {exec sqlldr $label(db_user)/$label(db_password)@$label(db_sid) control=$ctlFileName  log=$sqldrLog errors=0 parallel=true} selectError]} {
			set loadHasError true
			set errorInfo $selectError
			puts $selectError
		}
		if {$loadHasError} {
			error "$label(insert_table_failure_prompt)$errorInfo"
		}
		
		puts "OK,check data"
		#=====文件处理完成，存放备份目录=====
		#从/data目录备份到/backup/data/YYYY-MM-DD/文件需要移走
		set sFileDescPath $indiDate
#		set sDescFileName [string trim $sFileDescPath][string trim $sFileName]
		puts "[string trim $sFileDescPath][string trim $sFileName]"
#		file copy -force $sPathAndFileName $sDescFileName
#		file delete $sPathAndFileName
		#=====记录文件处理的日志=====
	}
}

proc run {} {
	global label 

	#参数
	global argv
	
	# 使用的时候，根据实际情况修改这里
	set tableName "CMN_COL_CFG"
	set columnList [split "COL_CFG_ID,COL_NAME,CREATE_DATE" ","]
	set isAppend  true
	set periodType [lindex $argv 7]
	set date [lindex $argv 5]
	set dataFiles "td.csv"     ;#多个文件,请使用逗号(,)分割
	set dataFileList [split $dataFiles ","]
	
	
	getDealFileInfo $tableName $columnList $isAppend $periodType $date $dataFileList
}

# 调用
run

　　由于，SyntaxHighlighter不能提供TCL语言的高亮显示。所以，这里的高亮可能还有一些问题。抱歉！

　　D瓜哥最近接触了一些Ruby语言，Ruby真是一个灵活的语言，同样也很好玩。非常适合自定义DSL，以后有时间D瓜哥把这个脚本再用Ruby重写一遍。

　　到这里，这个系列基本结束。可能还有一些问题。例如：

1. 使用先制作成数据文件，然后在使用SQL Loader导入到数据库中这种方式，需要监控数据文件的生成完成时间，然后再使用进行导入。但是，制作和导入，前者在数据库中使用存储过程完成；后者使用TCL（当然也可以使用其他语言完成）。横跨两个环境，监控起来不方便；
2. 使用这种方式，需要在数据库服务器上安装TCL运行环境；

　　综上，D瓜哥认为使用Shell脚本应该可以完整完成这个全部过程。不过，D瓜哥目前还不会写Shell，这个就留给感兴趣的朋友自己练手吧。当然，如果以后D瓜哥使用Shell实现了，还会发布出来的。

　

参考资料

1. sqlldr的介绍
2. SQL*Loader 详解
3. 使用SQL Loader导入大量数据，避免使用SQL频繁写库
4. Oracle官方文档：SQL*Loader

　

修改日志

　　2013年3月28日　　补充问题列表。