PlSql Loader 详细学习

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

2、在NT下，SQL*LOADER的命令为SQLLDR，在UNIX下一般为sqlldr/sqlload。
如Windows下执行：
C:\Documents and Settings\Administrator>sqlldr

SQL*Loader: Release 11.2.0.1.0 - Production on 星期三 4月 18 11:45:19 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

3、在Windows下执行：C:\Documents and Settings\Administrator>sqlldr 
可以获取如下帮助信息如下，补充了自己的一部分认识：
用法: SQLLDR keyword=value [,keyword=value,...]
有效的关键字:
    userid -- ORACLE 用户名/口令：Oracle 的 username/password[@servicename]
   control -- 控制文件名
       log -- 日志文件名：记录导入时的日志文件，默认为 控制文件(去除扩展名).log
       bad -- 错误文件名：坏数据文件，默认为 控制文件(去除扩展名).bad
      data -- 数据文件名：数据文件，一般在控制文件中指定。用参数控制文件中不指定数据文件更适于自动操作
   discard -- 废弃文件名
discardmax -- 允许废弃的文件的数目         (全部默认)
      skip -- 要跳过的逻辑记录的数目  (默认 0)
      load -- 要加载的逻辑记录的数目  (全部默认)
    errors -- 允许的错误的数目         (默认 50)
      rows -- 常规路径绑定数组中或直接路径保存数据间的行数：多少条记录提交一次
               (默认: 常规路径 64, 所有直接路径)
  bindsize -- 常规路径绑定数组的大小 (以字节计)  (默认 256000)
    silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)
    direct -- 使用直接路径                     (默认 FALSE)
   parfile -- 参数文件: 包含参数说明的文件的名称
  parallel -- 执行并行加载                    (默认 FALSE)
      file -- 要从以下对象中分配区的文件
 skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区  (默认 FALSE)
skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用  (默认 FALSE)

commit_discontinued -- 提交加载中断时已加载的行  (默认 FALSE)
       readsize -- 读取缓冲区的大小               (默认 1048576)
 external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE  (默认 NOT_USED)
columnarrayrows -- 直接路径列数组的行数  (默认 5000)
     streamsize -- 直接路径流缓冲区的大小 (以字节计)  (默认 256000)
 multithreading -- 在直接路径中使用多线程
      resumable -- 启用或禁用当前的可恢复会话  (默认 FALSE)
 resumable_name -- 有助于标识可恢复语句的文本字符串
resumable_timeout -- RESUMABLE 的等待时间 (以秒计)  (默认 7200)
     date_cache -- 日期转换高速缓存的大小 (以条目计)  (默认 1000)
no_index_errors -- 出现任何索引错误时中止加载  (默认 FALSE)

PLEASE NOTE: 命令行参数可以由位置或关键字指定。前者的例子是 'sqlldr scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo userid=scott/tiger'。
位置指定参数的时间必须早于但不可迟于由关键字指定的参数。
例如,
允许 'sqlldr scott/tiger control=foo logfile=log',
但是不允许 'sqlldr scott/tiger control=foo log', 即使参数 'log' 的位置正确。

4、sql loader 使用例子
前期描述：
数据将导入的表的表结构如下
create table user_test
     (
        id   VARCHAR2(32) not null,
        username  VARCHAR2(64),
        password  VARCHAR2(128),
        birthday  VARCHAR2(32)
     )

数据文件Excel：test.xls

   a)、SQL*LOADER 将Excel 数据文件导入到 Oracle数据库指定表中。       
        (1.创建SQL*Loader 输入数据所需要的文件,均保存到C:\，用记事本编辑：
            数据文件：test.txt 是由数据文件test.xls 转换生成的，使用的是制表符为分隔符。
           
            控制文件：input.ctl，内容如下：          
        　　load data　　　　　　　　　　 --1、控制文件标识
        　　infile 'C:\test.txt'　　　　　--2、数据源文件名为test.txt
        　　append into table user_test　 --3、向表test中追加记录
        　　fields terminated by X'09'　　--4、字段终止于X'09'，是一个制表符（TAB）
        　　(id,username,password,birthday)　　 -----定义列对应顺序
         
        其中： 
        a、insert，为缺省方式，在数据装载开始时要求表为空
        b、append，在表中追加新记录
        c、replace，删除旧记录，替换成新装载的记录
        d、truncate，同上          

        在DOS窗口下使用SQL*Loader命令实现数据的输入：         
        C:\>sqlldr userid=用户名/密码@服务名 control=input.ctl
          默认日志文件名为：input.log
          默认坏记录文件为：input.bad
         
        (2.还有一种方法
        可以把Excel文件另存为CSV（逗号分隔）(*.csv),控制文件就改为用逗号分隔
        LOAD DATA
        INFILE 'C:\test.csv'
        APPEND  INTO TABLE user_test
        FIELDS TERMINATED BY ","
        (id,username,password,birthday)

    b)、在控制文件中直接导入数据
        (1、控制文件test.ctl的内容：
            LOAD DATA
            INFILE 'C:\test.csv'          
            INSERT INTO TABLE EMCCOUNTRY
            Fields terminated by ";" Optionally enclosed by '"'
            (           
              id,
              username,
              password NULLIF (CONTINENTID="NULL"),           
              birthday DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (birthday="NULL")
            )
            BEGINDATA
            "1";"JP";"Japan";"09/16/2004 16:31:32";
            "2";"CN";NULL;"09/16/2004 16:31:32";
            "3";"IN";"India";NULL;
        
        (2、执行导入命令: C:\>sqlldr userid=用户/密码@服务名 control=test.ctl
        
        如果想去掉字段中含双引号方式，可以改写控制文件，使用如下：
        控制文件test.ctl的内容：
            LOAD DATA
            INFILE 'C:\test.csv'          
            INSERT INTO TABLE EMCCOUNTRY
            Fields terminated by ";" Optionally enclosed by '"'
            (           
              id "substr(:id,2,length(:id)-2)",
              username "substr(:username,2,length(:username)-2)",
              password "substr(:password,2,length(:password)-2)" NULLIF (CONTINENTID="NULL"),           
              birthday "substr(:birthday,2,length(:birthday)-2)" DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (birthday="NULL")
            )
            BEGINDATA
            "1";"JP";"Japan";"09/16/2004 16:31:32";
            "2";"CN";"NULL";"09/16/2004 16:31:32";
            "3";"IN";"India";"NULL";

 

5、常用技巧
         （1、***** 常用的 sqlldr 命令显示的选项
             OPTIONS (skip=2,rows=128)   -- skip=2 用来跳过数据中的前两行，
             load data
             infile 'C:\test.csv'
             append
             into table user_test
             Fields terminated by ','
             (id,username,password,birthday)

            或可以直接在执行命令的时候显示地加入参数：sqlldr 用户名/密码@服务名 input.ctl skip=2

        （2、***** 普通装载
           LOAD DATA
           INFILE *
           INTO TABLE user_test
           REPLACE
           FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
           (DEPTNO,
            DNAME,
            LOC
           )
           BEGINDATA
           10,Sales,"""USA"""
           20,Accounting,"Virginia,USA"
           30,Consulting,Virginia
           40,Finance,Virginia
           50,"Finance","",Virginia // loc 列将为空
           60,"Finance",,Virginia // loc 列将为空

           注意：begindata 后的数值前面不能有空格

        （3、***** FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情况
           LOAD DATA
           INFILE *
           INTO TABLE user_test
           REPLACE
           FIELDS TERMINATED BY WHITESPACE
           -- FIELDS TERMINATED BY x'09'
           (id,
            username,
            password,
            birthday
           )
           BEGINDATA
           20 lili 123456a* 19900807

       （ 4、***** 指定不装载那一列
           LOAD DATA
           INFILE *
           INTO TABLE user_test
           REPLACE
           FIELDS TERMINATED BY ','
           (id,
            username,
            password FILLER,  --下面的 "123456a*" 将不会被装载
            birthday
           )
           BEGINDATA
           20,lili,123456a*,19900807

        （5、***** 使用函数日期的一种表达 TRAILING NULLCOLS 的使用
          LOAD DATA
          INFILE *
          INTO TABLE DEPT
          REPLACE
          FIELDS TERMINATED BY ','
          TRAILING NULLCOLS // 其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应的列的值的。如果第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
          (DEPTNO,
           DNAME "upper(:dname)", // 使用函数
           LOC "upper(:loc)",
           LAST_UPDATED date 'dd/mm/yyyy', // 日期的一种表达方式 还有'dd-mon-yyyy' 等
           ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
          )
          BEGINDATA
          10,Sales,Virginia,1/5/2000
          20,Accounting,Virginia,21/6/1999
          30,Consulting,Virginia,5/1/2000
          40,Finance,Virginia,15/3/2001

       （ 6、***** 使用自定义的函数,解决的时间问题
          create or replace
          function my_to_date( p_string in varchar2 ) return date
          as
          /
         
          LOAD DATA
          INFILE *
          INTO TABLE DEPT
          REPLACE
          FIELDS TERMINATED BY ','
          TRAILING NULLCOLS
          (DEPTNO,
           DNAME "upper(:dname)",
           LOC "upper(:loc)",
           LAST_UPDATED "my_to_date( :last_updated )" // 使用自定义的函数
          )
          BEGINDATA
          10,Sales,Virginia,01-april-2001
          20,Accounting,Virginia,13/04/2001
          30,Consulting,Virginia,14/04/2001 12:02:02
          40,Finance,Virginia,987268297
          50,Finance,Virginia,02-apr-2001

        （7、***** 合并多行记录为一行记录
          LOAD DATA
          INFILE *
          concatenate 3 // 通过关键字concatenate 把几行的记录看成一行记录
          INTO TABLE DEPT
          replace
          FIELDS TERMINATED BY ','
          (DEPTNO,
           DNAME "upper(:dname)",
           LOC "upper(:loc)",
           LAST_UPDATED date 'dd/mm/yyyy'
          )
          BEGINDATA
          10,Sales, // 其实以下3行看成一行 10,Sales,Virginia,1/5/2000
          Virginia,
          1/5/2000

 

（8、***** 载入有换行符的数据
注意: unix 和 windows 不同 & /n
< 1 > 使用一个非换行符的字符
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS "replace(:comments,'n',chr(10))" // replace 的使用帮助转换换行符
)
BEGINDATA
10,Sales,Virginia,01-april-2001,This is the SalesnOffice in Virginia
20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia
40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia

< 2 > 使用fix属性
LOAD DATA
INFILE demo17.dat "fix 101"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo17.dat
10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
40,Finance,Virginia,987268297,This is the Finance
Office in Virginia

// 这样装载会把换行符装入数据库 下面的方法就不会 但要求数据的格式不同

LOAD DATA
INFILE demo18.dat "fix 101"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo18.dat
10,Sales,Virginia,01-april-2001,"This is the Sales
Office in Virginia"
20,Accounting,Virginia,13/04/2001,"This is the Accounting
Office in Virginia"
30,Consulting,Virginia,14/04/2001 12:02:02,"This is the Consulting
Office in Virginia"
40,Finance,Virginia,987268297,"This is the Finance
Office in Virginia"

< 3 > 使用var属性
LOAD DATA
INFILE demo19.dat "var 3"
// 3 告诉每个记录的前3个字节表示记录的长度 如第一个记录的 071 表示此记录有 71 个字节
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo19.dat
07110,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
07820,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
07140,Finance,Virginia,987268297,This is the Finance
Office in Virginia

< 4 > 使用str属性
最灵活的一种可定义一个新的行结尾符 win 回车换行 : chr(13)||chr(10)
此列中记录是以 a|rn 结束的
select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;
结果 7C0D0A

LOAD DATA
INFILE demo20.dat "str X'7C0D0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)

demo20.dat
10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia|
20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia|
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia|
40,Finance,Virginia,987268297,This is the Finance
Office in Virginia|

==============================================================================