测试sqlldr功能使用
实例一：导入的表表已有数据。
SQL> create user test identified by test default tablespace users temporary tablespace temp;
User created.
SQL> grant dba,resource to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create table t1(col1 varchar2(2),col2 varchar2(10),col3 number);
Table created.
控制文件case1.ctl
[oracle@databak sqlldr]$ cat case1.ctl
LOAD DATA
INFILE *
INTO TABLE T1
FIELDS TERMINATED BY ","
(COL1,COL2,COL3)
BEGINDATA
A,SCOTT,1
B,ZHANGSAN,2
C,LISI,3
D,WANGWU,4
E,MALIU,6
E,CHENGQI,7
执行 sqlldr 导入
[oracle@databak sqlldr]$ sqlldr test/test case1.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Fri Nov 30 15:00:47 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 7
[oracle@databak sqlldr]$
查看导入结果
SQL> select * from t1;
CO COL2 COL3
-- ---------- ----------
A SCOTT 1
B ZHANGSAN 2
C LISI 3
D WANGWU 4
E MALIU 6
E CHENGQI 7
6 rows selected.
再次执行导入
[oracle@databak sqlldr]$ sqlldr test/test case1.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Fri Nov 30 15:05:00 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-601: For INSERT option, table must be empty. Error on table T1.
此处报错，INTO TABLE T1这条命令，INTO前还有一些参数：
INSERT:默认，向表中插入记录，表必须为空。
APPEND:向表中追加数据，不管表中是否有数据。
REPLACE:替换表中的数据，相当于先delete，再insert。
TRUNCATE:通过truncate的方式删除数据，再insert。
重新修改控制文件 case1.ctl
LOAD DATA
INFILE *
APPEND INTO TABLE T1
FIELDS TERMINATED BY ","
(COL1,COL2,COL3)
BEGINDATA
A,SCOTT,1
B,ZHANGSAN,2
C,LISI,3
D,WANGWU,4
E,MALIU,6
E,CHENGQI,7
[oracle@databak sqlldr]$ sqlldr test/test case1.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Fri Nov 30 15:43:05 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 7
[oracle@databak sqlldr]$
查看结果：
SQL> select * from t1;
CO COL2 COL3
-- ---------- ----------
A SCOTT 1
B ZHANGSAN 2
C LISI 3
D WANGWU 4
E MALIU 6
E CHENGQI 7
A SCOTT 1
B ZHANGSAN 2
C LISI 3
D WANGWU 4
E MALIU 6
E CHENGQI 7
12 rows selected.
实例二：控制文件与数据文件分离
控制文件 case2.ctl
[oracle@databak sqlldr]$ cat case2.ctl
LOAD DATA
INFILE case2.dat
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ","
(COL1,COL2,COL3)
数据文件 case2.dat
[oracle@databak sqlldr]$ cat case2.dat
A,SCOTT,1
B,ZHANGSAN,2
C,LISI,3
D,WANGWU,4
E,MALIU,6
E,CHENGQI,7
导入数据
[oracle@databak sqlldr]$ sqlldr test/test case2.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Fri Nov 30 15:52:36 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 7
查看结果
SQL> select * from t1;
CO COL2 COL3
-- ---------- ----------
A SCOTT 1
B ZHANGSAN 2
C LISI 3
D WANGWU 4
E MALIU 6
E CHENGQI 7
6 rows selected.
实例三：要加载的数据文件中包含分隔符
[oracle@databak sqlldr]$ cat case2.dat
A,SCOTT,1
B,ZHANGSAN,2
C,LISI,3
D,WANGWU,4
E,MALIU,6
E,CHENGQI,7
E,"SHI,JIU",7
[oracle@databak sqlldr]$ cat case2.ctl
LOAD DATA
INFILE case2.dat
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
(COL1,COL2,COL3)
执行导入命令：
[oracle@databak sqlldr]$ sqlldr test/test case2.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Fri Nov 30 16:47:51 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 7
查看导入结果
SQL> select * from t1;
CO COL2 COL3
-- ---------- ----------
A SCOTT 1
B ZHANGSAN 2
C LISI 3
D WANGWU 4
E MALIU 6
E CHENGQI 7
E SHI,JIU 7
7 rows selected.
实例四：数据文件中没有分割符
[oracle@databak sqlldr]$ cat case3.dat
A SCOTT 1
B ZHANGSAN 2
C LISI 3
D WANGWU 4
E MALIU 6
E CHENGQI 7
E "SHI,JIU" 7
[oracle@databak sqlldr]$ cat case3.ctl
LOAD DATA
INFILE case3.dat
TRUNCATE INTO TABLE T1
(
COL1 position(1:1),
COL2 position(6:14),
COL3 position(16:16)
)
[oracle@databak sqlldr]$ sqlldr test/test case3.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Fri Nov 30 17:04:01 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 7
查看导入结果：
SQL> select * from t1;
CO COL2 COL3
-- ---------- ----------
A SCOTT 1
B ZHANGSAN 2
C LISI 3
D WANGWU 4
E MALIU 6
E CHENGQI 7
E "SHI,JIU" 7
7 rows selected.
实例五：数据文件中的列比要导入的表中的列少
[oracle@databak sqlldr]$ cat case3.dat
A SCOTT 1
B ZHANGSAN 2
C LISI 3
D WANGWU 4
E MALIU 6
E CHENGQI 7
E "SHI,JIU" 7
[oracle@databak sqlldr]$ cat case3.ctl
LOAD DATA
INFILE case3.dat
TRUNCATE INTO TABLE T2
(
COL1 position(1:1),
COL2 position(6:14),
COL3 position(16:16)
)
SQL> create table t2(col1 varchar2(2),col2 varchar2(10),col3 number,col4 number);
Table created.
SQL> desc t2
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(2)
COL2 VARCHAR2(10)
COL3 NUMBER
COL4 NUMBER
[oracle@databak sqlldr]$ sqlldr test/test case3.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Fri Nov 30 17:17:20 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 7
查看导入结果：
SQL> select * from t2;
CO COL2 COL3 COL4
-- ---------- ---------- ----------
A SCOTT 1 0
B ZHANGSAN 2 0
C LISI 3 0
D WANGWU 4 0
E MALIU 6 0
E CHENGQI 7 0
E "SHI,JIU" 7 0
7 rows selected.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20553601/viewspace-750347/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
