【sqlldr載入資料】

sub8412發表於2012-11-30
SQL
測試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部落格 ” ，連結：http://blog.itpub.net/20553601/viewspace-750347/，如需轉載，請註明出處，否則將追究法律責任。

相關文章