Oracle SQL*Loader学习例子

• 例子所需材料
  
  • 测试表
  • 数据文件(.csv或.dat等格式，我们将使用.csv)
  • control文件
• 创建测试表
  假设我们有一张员工表 EMP

     create table EMP(
      EMPNO    NUMBER(4) not null,
      ename    VARCHAR2(10),
      JOB      VARCHAR2(9),
      MGR      NUMBER(4),
      HIREDATE DATE,
      SAL      NUMBER(7,2),
      COMM     NUMBER(7,2),
      DEPTNO   NUMBER(2),
             VARCHAR2(10)
    )

• data文件case.csv

empno,firstname,lastname,job,mgr,sal,comm,deptno,hiredate
2342,Tom,Smith,sr mag,8987,9090,10,25,12-Nov-99
2333,Jerry,Chan,advanced,9089,10000,10,26,12-Oct-99

• control文件case.ctl

OPTIONS (skip=1)              --1
LOAD DATA
INFILE 'case.csv'             --2
REPLACE                       --3
INTO TABLE emp                --4
fields terminated by ','      --5
optionally enclosed by '"'    --5
(empno INTEGER EXTERNAL,      --6
firstname BOUNDFILLER,        --7
lastname BOUNDFILLER,         
ename EXPRESSION "UPPER(:firstname) || ' ' || UPPER(:lastname)",            
                              --8
job CHAR "UPPER(:job)",       --9
mgr INTEGER EXTERNAL,
sal DECIMAL EXTERNAL,
comm DECIMAL EXTERNAL,
deptno INTEGER EXTERNAL,
hiredate INTEGER EXTERNAL
)    

• Control文件标注解释
  
  1. 跳过第一行，即empno,firstname,lastname,job,mgr,sal,comm,deptno,hiredate
  2. 指定data file
  3. 表示load数据前先删除表中已有的全部数据，如果不想删除，要在原有表的基础上增加数据，则将REPLACE改为APPEND
  4. 指定要插入数据的表
  5. 指定字段是由逗号分隔开的，但也有可能是双引号
  6. INTEGER EXTERNAL表示数据文件中的empno必须是INTEGER类型，EXTERNAL不能省略
  7. Boundfiller 相当于定义了一个firstname变量并赋了值
  8. EXPRESSION "UPPER(:firstname) || ' ' || UPPER(:lastname)"" 表示这个一个SQL表达式，因为用到boundfield，所以不能省略EXPRESSION
  9. 调用UPPER函数将job变成大写，注意不能省略双引号

• 执行方法
  打开case.ctl文件目录下打开cmd，输入
  >sqlldr username/password control=case.ctl

• Example(20170331 Added)

Omit datatypes and use functions like decode and nvl2 to control fields.

OPTIONS(SKIP=1)
LOAD DATA
INFILE 'case.csv'
REPLACE
--insert
INTO TABLE EMP
FIELDS TERMINATED BY ','
OPTIONALLY ENCloSED BY '"'
(
empno "1",      --6  default value is 1
firstname BOUNDFILLER,        --7
lastname BOUNDFILLER,         
ename EXPRESSION "UPPER(:firstname) || ' ' || UPPER(:lastname)",            
                              --8
--job "UPPER(:job)",       --9
job "DECODE(:JOB, 'A', 'HIGH','D','LOW')",       -- use decode function
mgr ,--"NVL2(:MGR,1,0)" ,--INTEGER EXTERNAL,     -- use2 nvl function
sal ,
comm ,
deptno,
hiredate DATE "YYYY-MM-DD" "DECODE(:hiredate,'00000000',null,:hiredate)"
)    
begindata
empno,firstname,lastname,job,mgr,sal,comm,deptno,hiredate
2342,Tom,Smith,A,123,9090,10,25,2017/01/01
2333,Jerry,Chan,D,9089,10000,10,26,2017/01/02

注意，如果在linux条件下导入要将文件转成unix编码格式，因为window和unix的空格和换行符不一样。