[数据迁移] 使用 sqlldr 加载数据

1. 调用
sqlldr user/pass control=test.ctl

2. 帮助信息
sqlldr

3. 注意
3-1. pl/sql 不能调用 sqlldr
3-2. truncate table t reuse storage
3-3. sqlldr 默认使用 char(255)
3-4. 命令行参数覆盖控制文件中的参数

4. 控制文件示例
--
load data
infile 'aaa.dat'          -- * 用于数据记录在控制文件中时
badfile 'aaa.bad'         -- 使 sql 出错的记录
discardfile 'aaa.dsc'     -- 不符合表约束等的记录
into table test
truncate                  -- insert, replace, truncate, append
fields terminated by ","  -- X'09' (识别单个制表符) 或 whitespace (识别多个制表符)
(col1,col2,col3)
begindata                 -- 数据
aaa,111,AAA
bbb,222,BBB               -- 结束行, 无换行符
--

5. 各种情况下书写方法
--------------------------------------------
5-1. 使用定界符 (要导入的字段包含分隔符[,], 使用定界符 ["])

5-1-1. fields terminated by "," optionally enclosed by '"'
aaa        -> aaa (如果不包含分隔符, 可以加也可以不加双引号, 数据不变)
"a,aa"     -> a,aa
a"aa       -> a"aa
aa"a       -> aa"a
aaa"       -> aaa"
"""a,aa""" -> "a,aa"
""aaa""    -> bad file
"aaa       -> bad file

不加 optionally enclosed by '"' 子句, 数据原封不动的导入
"aaa"      -> "aaa"
a"aa       -> a"aa
aaa"       -> aaa"

5-1-2. fields terminated by whitespace
aaa\tbbb\tccc       -> aaa bbb ccc
aaa\t\tbbb\t\tccc   -> aaa bbb ccc

5-1-3 fields terminated by X'09'
aaa\tbbb\tccc       -> aaa bbb ccc
aaa\t\tbbb\t\tccc   -> aaa     ccc


5-2. 定长数据 (不使用 fields terminated by 子句)

5-2-1. 绝对偏移量
col1 position(1:5),
col2 position(6:10)
col3 position(11:15)

5-3-2. 相对偏移量
col1 position(1:5),
col2 position(*:10),  -> (6:10)
col3 position(*:15)   -> (11:15)
col4 position(*+2:20) -> (17:20) (偏移量相加)

5-3-3. 利用字段长度
col1 position(1) char(5), -> (1:5)
col2 position(*) char(5), -> (6:10)
col3 position(*) char(5), -> (11:15)
col4 position(1) char(15) -> (1:15) (字段反复使用)


5-3. 某列设置默认值
col1 "abcde"
col2 "substr(:col1, 1, 2)" -- 可任意加 sql 函数


5-4. 跳过某列数据

5-4-1.
col1,dummy1 filler,col2,col3
aaa,bbb,ccc,ddd -> aaa ccc ddd

5-4-2.
col1,col2 filler,col3
aaa,bbb,ccc -> aaa null ccc


5-5. 加载日期字段
col1 date 'yyyy/mm/dd' -- 日期掩码与 sql 函数相同
2000/01/01 -> 2000-01-01


5-6. 多文件导入一张表 (数据文件必须统一格式)
infile file1.dat
infile file2.dat


5-7. 使用复杂的 sql 函数
col1 "upper(:col1)"
col1 "case..when..then..else..end"


5-8. 同一文件导入不同表 (不满足条件的写入 dsc 文件, 只支持 AND, 不支持 OR)
truncate
  into table tbl1
    when tab='t1'
    (
     tab filler position(1:4),
     col1 position(5:9)
    )
  into table tbl1
    when tab='t2'
    (
     tab filler position(1:4),
     col1 position(5:9)
    )


5-9. 跳过行, 只导入某些行
sqlldr scott/tiger control=ldr_case2.ctl skip=1 load=10


5-10. 数据含有换行符 (Windows chr(13)+chr(10), Linux/Unix chr(10))

5-10-1. 数据文件中已经把换行符转化为特殊字符 (输入文件编辑的情况)
col1 [position(1:10)] "replace(:col1,'\\n',chr(10))"

5-10-2. FIX 属性 (定长, 数据必须存储于单独数据文件)
infile test.dat "fix n" -- 指定行长度, 包括尾部的换行符

5-10-3. VAR 属性 (行头部标识换行)
infile test.dat "var n" -- n 表示记录的字节数(包括尾部换行符), 不能超过40，默认为5

5-10-4. STR 属性 (制定一个新的换行符, 或字符串序列)
infile test.dat "str X'7C0A'" -- '|\n'


5-11. LOB字段

5-11-1. pl/sql 加载 (LoadFormFile LoadBLOBFromFile LoadCLOBFromFile)

5-11-1-1. 创建目录对象
create directory go_dir as '/tmp/';

5-11-1-2. 执行过程
declare
    l_clob clob;
    l_bfile bfile;
begin
    insert into t values (1,empty_clob())
    returning theclob into l_clob;
    l_bfile := bfilename('GO_DIR','test.txt'); -- 目录大写
    dbms_lob.fileopen(l_bfile);
    dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
    dbms_lob.fileclose(l_bfile);
end;
/

5-11-2. sqlldr 加载

5-11-2-1. LOB 数据内嵌在控制文件中 (指定 LOB 字段长度超过数据长度)
col1 char(100000)

5-11-2-2. LOB 数据在单独文件中, 指定 LOBFILE 参数
filename position(1,10)
filedata lobfile(filename) terminated by eof

5-11-2-3. 将 LOB 数据加载到对象列 (TODO)


5-12. 某些字段无值 (trailing nullcols)
fields terminated by "," trailing nullcols


5-13. 效率优化

5-13-1. 可以调整的参数
bindsize
rows
direct
streamsize
date_cache
去掉索引，字段指明类型，定长格式等

5-13-2. 百万级数据导入的例子
sqlldr scott/tiger control=test.ctl rows=5000 bindsize=10485760 -- 10M
sqlldr scott/tiger control=ldr_object.ctl direct=true streamsize=10485760 date_cache=5000 -- 直接路径,含有日期列