SQL-Loader可将外部文件中的数据加载到oracle数据库表中，它具有一个功能强大的分析引擎，因此对数据文件中数据格式没有什么限制。输入数据文件：从控制文件中指定一个或多个文件读取数据。从sql*loader角度看，数据文件中的数据是按记录组织的。一种特定数据文件可采用固定格式、可变记录格式或流记录格式。可通过控制文件中的infile参数指定记录格式。如果未指定记录格式默认为流记录格式。
不是存放在oracle里的数据，外部数据(平面文件、文本文件、excel等） 导入到oracle 就用SQL-Loader
案例
导入文本文件
有一个文件test.txt
$ cat test.txt
1,a
2,b
3,c
在scott有一个表：T
SQL> desc T
Name Null? Type
----------------------- -------- ----------------------------
ID NUMBER
PNAME CHAR(10)
SQL> select * from T;
no rows selected
要把test.txt文件的数据导入表T
需要条件：
数据文件：要导入的数据（这里是test.txt）
控制文件：必须要，格式转换
坏文件：不满足格式转换，不满足数据库约束而无法导入的数据
丢弃的文件：可选，可以不要；用户设定的赛选条件 不想导入的数据
日志文件：SQL*LOADER执行时，它会创建一个日志文件。如果不能创建日志文件，执行就会终止。日志文件包含加载操作的详细概要，包括加载过程中的任何错误说明。
控制文件可在EM操作：EM --> Data Movement --> Load Data from User Files --> Automatically Generate Control File
控制文件内容：
$ cd /u01/app/oracle/data
$ cat ldr.ctl
LOAD DATA
INFILE '/u01/app/oracle/data/test.txt' "STR '\r\n'"
APPEND
INTO TABLE SCOTT.T
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
ID INTEGER EXTERNAL,
PNAME CHAR
)
$ cd /u01/app/oracle/data
$ sqlldr scott/oracle control=ldr.ctl log=ldr.log bad=ldr.bad data=test.txt
查看T表有数据了
SQL> select * from T;
ID PNAME
---------- ----------
1 a
2 b
3 c
控制文件
导手工编写控制文件
控制文件模板：
Example 9-1 Sample Control File
-- This is a sample control file #注释
LOAD DATA #开始加载数据
INFILE 'sample.dat'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'
APPEND
INTO TABLE emp
WHEN (57) = '.'
TRAILING NULLCOLS
(hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(2)
NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,'$99,999.99')",
comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%'
":comm * 100"
)
控制文件：控制文件是一个文本文件，它是使用sql*loader可识别的语言编写。控制文件指定在何处查找数据，如何分析和解释数据，以及在何处插入数据等等。
分三部分：
第一部分：包含会话范围信息，全局选项（如输入数据文件名）和要跳过的记录，用于指定输入数据位置的infile子句，要加载的数据。
第二部分：包括一个或多个into table块，每一块包含要在其中加载数据的表信息（如表名和表列）
第三部分：用于包含输入数据
-- This is a sample control file #注释
LOAD DATA #开始加载数据
INFILE 'sample.dat' #三种写法
INFILE * #数据文件在控制文件里 用*代表数据文件
INFILE sample #相对文件名
INFILE '、u01/app/oracle/datafile.dat' #指定数据文件在哪里
APPEND 表示数据是怎么加入到表的（有三种参数）
APPEND #追加
REPLACE #先delete from 删除所有的数据，然后再加载数据
TRUNCATE #truncate 删除所有的数据，然后再加载数据
INTO TABLE emp
WHEN (57) = '.' #筛选条件，Loading Records Based on a Condition 根据你的条件加载数据 要在表后面。
例如：WHEN (deptno = '10') AND (job = 'SALES')
TRAILING NULLCOLS
案例
从excel文件导入
创建excel a.xls
1 a b
2 c d
3 e f
把excel保存为csv (comma separated values,逗号分隔值) 格式文件，文件--另存为--保存类型中选择csv
另存为a.csv
复制到数据库服务器上
sqlplus scott/oracle
create table t (id number ,c1 char(10) ,c2 char(10));
编辑控制文件
vim demo.ctl
LOAD DATA
INFILE a.csv
INTO TABLE t
FIELDS TERMINATED BY ','
(id,c1,c2)
导入数据
sqlldr userid=scott/oracle control=demo.ctl
如果要加载文件不是逗号隔开
修改数据文件的分隔符号为逗号
修改控制文件，fields terminated by 新的分隔符
包含多个分隔符，可以指定定界符
smith ,clerk ,3904
allen, "saler,m" ,2891
控制文件中指定 field terminated by "," optionally enclosed by '"'
常见分隔符：
terminated by whitespace
LOAD DATA
INFILE *
INTO TABLE DT
REPLACE
FIELDS TERMINATED BY WHITESPACE
(DEPTNO ,DNAME ,LOC)
BEGINDATA
10 Sales Virgina
terminated by x'09' (代表tab)
LOAD DATA
INFILE *
INTO TABLE DT
REPLACE
FIELDS TERMINATED BY X'09'
(DEPTNO ,DNAME ,LOC)
BEGINDATA
10 Sales Virgina
没有分隔符，使用绝对位置符：
控制文件
load data
infile *
truncate into table dt
(deptno position(1:2),
dname position(3:12),
loc position(15:26)
)
begindata
10Accounting Virginai,USA
使用相对位置符：
load data
infile *
truncate into table dt
(deptno position(1:2),
dname position(*:16),
loc position(*:29)
)
begindate
10Accounting Virginai,USA
position(3,16) 从第3个字符开始，截止到第16个字符
position(*：16) 等价于 3:16
position(*+2:16) 表示从上次结束位置+2的地方开始
position(*) char(9) 相对偏移量+类型和长度，只需要制定第一列开始位置，其他列需要制定列长度就可以了。
load data
infile *
truncate into table dpt
(deptno position(1) char(2),
dname position(*) char(14),
loc position(*) char(13)
)
begindate
10Accounting Virginai,USA
数据文件列少于表中的列：
SQL> desc bonus;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER
COMM NUMBER
使用例子是scott.bonus
load data
infile *
truncate into table bonus
(
ename position(1:5),
job position(7:11),
sal position(13:16),
comm "0"
)
begindata
smith,clerk,3904
列少了 可以直接指定列的值
comm "0"
也可以引用其他列的值
load data
infile *
truncate into table bonus
(
ename position(1:5),
job position(7:11),
sal position(13:16),
comm "substr(:sal,1,1)"
)
begindata
smith,clerk,3904
要求输入的字符都是大写
数据文件列多于表中的列：
只希望导入1,3,4 ，5跳过2 列
load data
infile *
truncate into table bonus
(
ename position(1:5),
tcol filler position(7:10),
job position(12:16),
sal position(18:20),
comm position(22:23)
)
begindata
smith,7369,clerk,800,20
指定多个数据源：
create table manager
( mgrno number ,
mname varchar2(30),
job varchar2(30),
remark varchar2(4000));
a.dat 数据文件
10,smith,sales manager
b.dat 数据文件
11,allen,tech manager
c.dat 数据文件
12,ward,server manager
load data
infile a.dat
infile b.dat
infile c.dat
truncate into table manager
fields terminated by ","
(mgrno ,mname ,job)
前N行部导入(skip参数的使用）：
前3行不导入
a.dat 数据文件内容：
10,smith,sales manager
11,allen,tech manager
12,ward,server manager
13,aa,bbbbb
14,bb,ccccc
15,cc,ddddd
16,ddd,ffff
17,fff,eeee
18,eee,kkkk
控制文件a.ctl
load data
infile a.dat
truncate into table manager
fields terminated by ","
(mgrno ,mname ,job)
执行导入（skip 表示从第一行开始忽略几行）：
sqlldr userid=scott/oracle control=a.ctl log=a.log skip =3;
只导入4-5行， load表示加载几行：
sqlldr userid=socott/oracle control=a.ctl log=a.log skip =3 load=2;
导入日期类型的数据：
alter table dt add last_updated date ;
SQL> desc dt;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(20)
LAST_UPDATED DATE
编辑控制文件：
vim a.ctl
load data
infile *
replace into table dt
fields terminated by ','
(deptno ,
dname ,
loc,
last_updated date 'dd/mm/yyyy' --指定日期类型
)
begindata
10,Sales,Virginia,1/4/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
40,Finace,Virginia,15/3/2001
使用函数：
load data
infile *
replace into table dept
fields terminated by ','
(deptno ,
dname "upper(:dname)",
loc "upper(:loc)",
last_updated date 'dd/mm/yyyy'
)
begindata
10,Sales,Virginia,1/4/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
40,Finace,Virginia,15/3/2001
