Oracle SQL*Loader

-- Start

SQL*Loader 是 Oracle 提供的一个工具用来将文件中的数据导入到一个或多个表中。你可以在 Oracle 的安装目录中找到它 C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlldr.exe

目前，我们最常用的用来传输数据的文件格式有 2 种，一种是分隔符文件（如：CSV），另一种是定长文件，下面我们通过例子来看看如果导入这两种文件。

首先，我们创建如下的表。

CREATE TABLE EMPLOYEES(    EMPLOYEE_ID         NUMBER(6,0)     NOT NULL,    FIRST_NAME          VARCHAR2(20)    NOT NULL,    LAST_NAME           VARCHAR2(25)    NOT NULL,    EMAIL               VARCHAR2(50)    NULL,    HIRE_DATE           DATE            NULL,    SALARY              NUMBER(8,2)     NULL,    UPDATE_DATE_TIME    TIMESTAMP       NULL);

分隔符文件例子1

假设我们有下面的文件（C:\log\employees.txt）

1,bo,shang,2015-01-01,8888.88,2015-02-19 22:15:00.0002,san,zhang,2015-02-02,9999.99,2015-02-19 22:15:00.000

试一试下面的命令导入文件到 EMPLOYEES 表吧。

sqlldr USERID=DBuser/shangbo         -- 指定连接数据库用户名，密码ROWS=1000                     -- 每 1000 条提交一次BAD=C:\log\employees.bad      -- 保存那些在导入的过程中，由于格式等问题出错的行DISCARD=C:\log\employees.dsc  -- 保存那些由于某些条件不满足而丢弃的行LOG=C:\log\employees.log      -- 保存日志文件CONTROL=C:\log\employees.ctl  -- 控制文件，用来告诉 SQL*Loader 如何导入数据

啊，不好使啊，那是因为你还没有 employees.ctl 控制文件，没有它，SQL*Loader 就不知道如何导入数据，来吧，把下面的内容保存到 employees.ctl 再试一试。

-- 要导入的数据文件LOAD DATA INFILE 'C:\log\employees.txt'-- APPEND 表示将数据添加到表中-- REPLACE 表示先 DELETE 所有数据，然后导入-- TRUNCATE 表示先 TRUNCATE 所有数据，然后导入-- INSERT 只适用于空表，如不是空表会报错APPEND INTO TABLE employeesFIELDS TERMINATED BY ',' -- 表示文件中字段之间使用逗号分隔(EMPLOYEE_ID         CHAR, -- 此处的字段名必须和表的列名一致FIRST_NAME        , -- 如没有指定数据类型，默认为 CHARLAST_NAME        , -- 注意，此处的数据类型是 SQL*Loader 的数据类型，和表的数据类型不一样HIRE_DATE        DATE "YYYY-MM-DD",  -- 指定 DATE 类型和格式SALARY            CHAR, -- SQL*Loader 的 CHAR 类型可以自动转成任何表的数据类型UPDATE_DATE_TIME    TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF3")

上面的控制文件中，我们没有指定 EMAIL 列如何导入，所以它为 NULL。如果你想让某个列是 NULL，不指定它即可。

分隔符文件例子2

有些分隔符文件的字段是用双引号括起来的，还有些字段的前面或后面有空格，如何处理这样的文件呢？

1,"bo","shang","2015-01-01",8888.88,"2015-02-19 20:15:00.000"2,"san","zhang","2015-02-02",9999.99,"2015-02-18 21:15:00.000"3    ,"si"    ,"li"    ,"2015-03-01"    ,6666.66    ,"2015-02-17 22:15:00.000"4,    "wu",    "wang",    "2015-04-02",    7777.77,    "2015-02-16 23:15:00.000"

要导入这样的文件，我们只要在控制文件中加入 OPTIONALLY ENCLOSED BY 子句即可。

LOAD DATA INFILE 'C:\log\employees.txt'REPLACE INTO TABLE employeesFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'(EMPLOYEE_ID         ,FIRST_NAME        ,LAST_NAME        ,HIRE_DATE        DATE "YYYY-MM-DD",SALARY            ,UPDATE_DATE_TIME    TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF3")

分隔符文件例子3

上面两个文件都是比较完美的，有时候实际的情况是非常复杂的，例如，现在要求你把下面的文件导入到下面的表中。要求
1. 忽略第一行和最后一行
2. EMPLOYEE_ID 由数据库自动生成
3. NAME 由 FIRST_NAME 和 LAST_NAME 组成且要转成大写，如：SHANGBO
4. 忽略 ENGLISH_NAME 列
5. 如果 HIRE_DATE 是空，则插入 NULL
6. 如果 SALARY 是空，则插入 0
7. ENTER_USER 插入默认值 'SYSTEM'
8. ENTER_DATE_TIME 插入当前系统时间
9. 忽略 ACTIVE 是 n 的行

要导入的文件

FIRST_NAME,LAST_NAME,ENGLISH_NAME,HIRE_DATE,SALARY,ACTIVEbo,shang,scott,2015-01-01,8888.88,ysan,zhang,mike,,,ysi    ,li    ,darren    ,    ,    ,ywu,    wang,    eric,    2015-04-02,    7777.77,    nEND OF FILE

要导入的表

CREATE TABLE EMPLOYEES(EMPLOYEE_ID    NUMBER(6,0)       NOT NULL,NAME        VARCHAR2(20 BYTE) NOT NULL,HIRE_DATE    DATE              NULL,SALARY        NUMBER(8,2)       DEFAULT 0 NOT NULL,ENTER_USER    VARCHAR2(20 BYTE) NULL,ENTER_DATE_TIMETIMESTAMP(6)      NULL);

控制文件

LOAD DATA INFILE 'C:\log\employees.txt'REPLACE INTO TABLE employeesWHEN (ACTIVE!='n')FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'(EMPLOYEE_ID     SEQUENCE(1, 1),FIRST_NAMEBOUNDFILLER, -- BOUNDFILLER 表示该列不保存在表中，但可以用在下面的 EXPRESSION 表达式中LAST_NAMEBOUNDFILLER,NAME            EXPRESSION "UPPER(RTRIM(:LAST_NAME) || RTRIM(:FIRST_NAME))", -- EXPRESSION 表示后面是一个SQL表达式ENGLISH_NAME    FILLER, -- FILLER 表示该列不保存在表中，它和 BOUNDFILLER 区别是它不能用在 EXPRESSION 表达式中HIRE_DATEDATE "YYYY-MM-DD" NULLIF HIRE_DATE=BLANKS, -- NULLIF 表示如果文件中 HIRE_DATE 是一个或多个空白字符，则插入 NULL 到表中SALARY          "NVL(:SALARY, 0)", -- :SALARY 表示引用文件中的 SALARY 列ACTIVE          FILLER, -- 忽略此列ENTER_USER      CONSTANT 'SYSTEM', -- CONSTANT 表示后面是一个常量ENTER_DATE_TIME  EXPRESSION "CURRENT_TIMESTAMP")

导入命令

-- SKIP=1 表示跳过第一条sqlldr USERID=DBuser/shangbo ROWS=1000 BAD=C:\log\employees.bad DISCARD=C:\log\employees.dsc LOG=C:\log\employees.log CONTROL=C:\log\employees.ctl SKIP=1

定长文件例子

现在我们把“分隔符文件例子3”中的文件改成定长文件，看看如何导入定长文件。

要导入的文件

FIRST_NAME LAST_NAME ENGLISH_NAME HIRE_DATE  SALARY   ACTIVEbo         shang     scott        2015-01-01 8888.88  y     san        zhang     mike                             y     si         li        darren                           y     wu         wang      eric         2015-04-02 7777.77  n     END OF FILE                                                 

要导入的表

CREATE TABLE EMPLOYEES(EMPLOYEE_ID    NUMBER(6,0)       NOT NULL,NAME        VARCHAR2(20 BYTE) NOT NULL,HIRE_DATE    DATE              NULL,SALARY        NUMBER(8,2)       DEFAULT 0 NOT NULL,ENTER_USER    VARCHAR2(20 BYTE) NULL,ENTER_DATE_TIMETIMESTAMP(6)      NULL);

控制文件

-- fix 表示它是一个定长文件，62表示每行的长度，注意包含换行符哦LOAD DATA INFILE 'C:\log\employees.txt' "fix 62"REPLACE INTO TABLE employeesWHEN (ACTIVE='y')(EMPLOYEE_ID    SEQUENCE(1, 1),FIRST_NAME   BOUNDFILLER POSITION(1:11), -- 位置从1开始LAST_NAME   BOUNDFILLER POSITION(12-21), -- : 和 - 完全相同NAME           EXPRESSION "UPPER(:LAST_NAME || :FIRST_NAME)",ENGLISH_NAME   FILLER POSITION(*:34), -- * 表示相对上一个位置开始HIRE_DATEPOSITION(*:45) DATE "YYYY-MM-DD"  NULLIF HIRE_DATE=BLANKS, SALARY         POSITION(*:54) "NVL(:SALARY, 0)",ACTIVE         FILLER POSITION(*),ENTER_USER     CONSTANT 'SYSTEM',ENTER_DATE_TIME  EXPRESSION "CURRENT_TIMESTAMP")

直接路径导入

上面例子的导入方法本质上是构造 INSERT 语句，Oracle 还提供了一种直接路径导入的方法，它是将要导入的文件直接格式化成数据库文件，所以速度会更快。

在使用直接路径导入之前，我们需要运行下面的 SQL 设置一下数据库，这个 SQL 只需要设置一次。

@C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\catldr.sql

然而，有利就有弊，使用直接路径导入我们不能让数据库自动帮我们生成主键，也无法执行 CHECK 和 外键约束，也很容易导致索引不可用。所以，通常我们先删除索引，导入后重建索引，这样可以提高导入效率。那么如何使用直接路径导入呢？很简单，将 DIRECT 参数设置成 true 就可以了，为了进一步提高效率，我们还可以设置 PARALLEL 参数为 true。

下面的命令使用直接路径导入“分隔符文件例子1”的文件

-- DIRECT=true 设置直接路径导入-- PARALLEL=true 使用并行导入sqlldr USERID=DBuser/shangbo DIRECT=true PARALLEL=true ROWS=10000 BAD=C:\log\employees.bad DISCARD=C:\log\employees.dsc LOG=C:\log\employees.log CONTROL=C:\log\employees.ctl

-- 更多参见：Oracle 精萃

-- 声明：转载请注明出处

-- Last edited on 2015-02-21

-- Created by ShangBo on 2015-02-20

-- End