這裡講述另外一種方法透過SQL*loader建立外部表，把外部資料直接寫在控制檔案上：

---檢視hisloader表的資料：
SQL> select * from hisloader;
ID DNAME LNAME
---------- ------------ ----------
101 OPERATIONS5 BOSTON1
102 OPERATIONS6 BOSTON2
103 OPERATIONS7

---編輯外部表控制檔案：
[oracle@enmo dirhome]$ vi load1.ctl
load data
infile*
badfile 'load.bad'
into table hisloader
truncate
fields terminated by ','
trailing nullcols
(id,dname,lname)
begindata
104,OPERATIONS5,BOSTON1
105,OPERATIONS6,BOSTON2
106,OPERATIONS7,"BOSTON3"
~

--匯入資料：
[oracle@enmo dirhome]$ sqlldr scott/tiger control=load1.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 2 11:26:27 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 3
[oracle@enmo dirhome]$

--檢視錶資料：
SQL> select * from hisloader;
ID DNAME LNAME
---------- ------------ ----------
104 OPERATIONS5 BOSTON1
105 OPERATIONS6 BOSTON2
106 OPERATIONS7 "BOSTON3"
#發現資料被替換了。

---建立另外一個外部表：
SQL> create table yourloader(
2 id number(3),
3 dname varchar2(12),
4 lname varchar2(10));
Table created.

---編輯控制檔案：
[oracle@enmo dirhome]$ vi load2.ctl
load data
infile 'dt4.scv'
badfile 'base.bad'
into table yourloader
truncate
trailing nullcols
(
id TERMINATED BY ',',
dname TERMINATED BY ',',
lname TERMINATED BY ','OPTIONALLY ENCLOSED BY '"'
)
~

--匯入資料：
[oracle@enmo dirhome]$ sqlldr scott/tiger control=load2.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 2 12:27:59 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 7
[oracle@enmo dirhome]$
#匯入了7條資料。

--檢視外部表的資料：
SQL> select * from yourloader;
ID DNAME LNAME
---------- ------------ ----------
104 OPERATIONS5 BOSTON1
105 OPERATIONS6 BOSTON2
106 OPERATIONS7 BOS,TON3
101 OPERATIONS5 BOSTON1
102 OPERATIONS6 BOSTON2
103 OPERATIONS7
6 rows selected.
#實際就只有6條資料。

--使用以下語句：
trailing nullcols
(
id TERMINATED BY ',',
dname TERMINATED BY ',',
lname TERMINATED BY ','OPTIONALLY ENCLOSED BY '"'
)
既能處理空值的資料，也解決了雙引號’"’的問題。

使用SQL*Loader建立外部表之二

相關文章