[轉]sql load 的一點小總結
sqlldr userid=lgone/tiger control=a.ctl
LOAD DATA
INFILE 't.dat' // 要匯入的檔案
// INFILE 'tt.date' // 匯入多個檔案
// INFILE * // 要匯入的內容就在control檔案裡 下面的BEGINDATA後面就是匯入的內容
INTO TABLE table_name // 指定裝入的表
BADFILE 'c:\bad.txt' // 指定壞檔案地址
************* 以下是4種裝入表的方式
APPEND // 原先的表有資料 就加在後面
// INSERT // 裝載空表 如果原先的表有資料 sqlloader會停止 預設值
// REPLACE // 原先的表有資料 原先的資料會全部刪除
// TRUNCATE // 指定的內容和replace的相同 會用truncate語句刪除現存資料
************* 指定的TERMINATED可以在表的開頭 也可在表的內部欄位部分
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
// 裝載這種資料: 10,lg,"""lg""","lg,lg"
// 在表中結果: 10 lg "lg" lg,lg
// TERMINATED BY X '09' // 以十六進位制格式 '09' 表示的
// TERMINATED BY WRITESPACE // 裝載這種資料: 10 lg lg
TRAILING NULLCOLS ************* 表的欄位沒有對應的值時允許為空
************* 下面是表的欄位
(
col_1 , col_2 ,col_filler FILLER // FILLER 關鍵字 此列的數值不會被裝載
// 如: lg,lg,not 結果 lg lg
)
// 當沒宣告FIELDS TERMINATED BY ',' 時
// (
// col_1 [interger external] TERMINATED BY ',' ,
// col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,
// col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'
// )
// 當沒宣告FIELDS TERMINATED BY ','用位置告訴欄位裝載資料
// (
// col_1 position(1:2),
// col_2 position(3:10),
// col_3 position(*:16), // 這個欄位的開始位置在前一欄位的結束位置
// col_4 position(1:16),
// col_5 position(3:10) char(8) // 指定欄位的型別
// )
BEGINDATA // 對應開始的 INFILE * 要匯入的內容就在control檔案裡
10,Sql,what
20,lg,show
=====================================================================================
//////////// 注意begindata後的數值前面不能有空格
1 ***** 普通裝載
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10,Sales,"""USA"""
20,Accounting,"Virginia,USA"
30,Consulting,Virginia
40,Finance,Virginia
50,"Finance","",Virginia // loc 列將為空
60,"Finance",,Virginia // loc 列將為空
2 ***** FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情況
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY WHITESPACE
-- FIELDS TERMINATED BY x'09'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales Virginia
3 ***** 指定不裝載那一列
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( DEPTNO,
FILLER_1 FILLER, // 下面的 "Something Not To Be Loaded" 將不會被裝載
DNAME,
LOC
)
BEGINDATA
20,Something Not To Be Loaded,Accounting,"Virginia,USA"
4 ***** position的列子
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
DNAME position(*:16), // 這個欄位的開始位置在前一欄位的結束位置
LOC position(*:29),
ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting Virginia,USA
5 ***** 使用函式 日期的一種表達 TRAILING NULLCOLS的使用
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS // 其實下面的ENTIRE_LINE在BEGINDATA後面的資料中是沒有直接對應
// 的列的值的 如果第一行改為 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
(DEPTNO,
DNAME "upper(:dname)", // 使用函式
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy', // 日期的一種表達方式 還有'dd-mon-yyyy' 等
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Sales,Virginia,1/5/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
40,Finance,Virginia,15/3/2001
6 ***** 使用自定義的函式 // 解決的時間問題
create or replace
function my_to_date( p_string in varchar2 ) return date
as
type fmtArray is table of varchar2(25);
l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy',
'dd/mm/yyyy',
'dd/mm/yyyy hh24:mi:ss' );
l_return date;
begin
for i in 1 .. l_fmts.count
loop
begin
l_return := to_date( p_string, l_fmts(i) );
exception
when others then null;
end;
EXIT when l_return is not null;
end loop;
if ( l_return is null )
then
l_return :=
new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *
p_string, 'GMT', 'EST' );
end if;
return l_return;
end;
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )" // 使用自定義的函式
)
BEGINDATA
10,Sales,Virginia,01-april-2001
20,Accounting,Virginia,13/04/2001
30,Consulting,Virginia,14/04/2001 12:02:02
40,Finance,Virginia,987268297
50,Finance,Virginia,02-apr-2001
60,Finance,Virginia,Not a date
7 ***** 合併多行記錄為一行記錄
LOAD DATA
INFILE *
concatenate 3 // 透過關鍵字concatenate 把幾行的記錄看成一行記錄
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA
10,Sales, // 其實這3行看成一行 10,Sales,Virginia,1/5/2000
Virginia,
1/5/2000
// 這列子用 continueif list="," 也可以
告訴sqlldr在每行的末尾找逗號 找到逗號就把下一行附加到上一行
LOAD DATA
INFILE *
continueif this(1:1) = '-' // 找每行的開始是否有連線字元 - 有就把下一行連線為一行
// 如 -10,Sales,Virginia,
// 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000
// 其中1:1 表示從第一行開始 並在第一行結束 還有continueif next 但continueif list最理想
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA // 但是好象不能象右面的那樣使用
-10,Sales,Virginia, -10,Sales,Virginia,
1/5/2000 1/5/2000
-40, 40,Finance,Virginia,13/04/2001
Finance,Virginia,13/04/2001
8 ***** 載入每行的行號
load data
infile *
into table t
replace
( seqno RECNUM //載入每行的行號
text Position(1:1024))
BEGINDATA
fsdfasj //自動分配一行號給載入 表t 的seqno欄位 此行為 1
fasdjfasdfl // 此行為 2 ...
9 ***** 載入有換行符的資料
注意: unix 和 windows 不同 & /n
使用一個非換行符的字元
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS "replace(:comments,'\n',chr(10))" // replace 的使用幫助轉換換行符
)
BEGINDATA
10,Sales,Virginia,01-april-2001,This is the Sales\nOffice in Virginia
20,Accounting,Virginia,13/04/2001,This is the Accounting\nOffice in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting\nOffice in Virginia
40,Finance,Virginia,987268297,This is the Finance\nOffice in Virginia
使用fix屬性
LOAD DATA
INFILE demo17.dat "fix 101"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo17.dat
10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
40,Finance,Virginia,987268297,This is the Finance
Office in Virginia
// 這樣裝載會把換行符裝入資料庫 下面的方法就不會 但要求資料的格式不同
LOAD DATA
INFILE demo18.dat "fix 101"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo18.dat
10,Sales,Virginia,01-april-2001,"This is the Sales
Office in Virginia"
20,Accounting,Virginia,13/04/2001,"This is the Accounting
Office in Virginia"
30,Consulting,Virginia,14/04/2001 12:02:02,"This is the Consulting
Office in Virginia"
40,Finance,Virginia,987268297,"This is the Finance
Office in Virginia"
使用var屬性
LOAD DATA
INFILE demo19.dat "var 3"
// 3 告訴每個記錄的前3個位元組表示記錄的長度 如第一個記錄的 071 表示此記錄有 71 個位元組
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo19.dat
07110,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
07820,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
07140,Finance,Virginia,987268297,This is the Finance
Office in Virginia
使用str屬性
// 最靈活的一中 可定義一個新的行結尾符 win 回車換行 : chr(13)||chr(10)
此列中記錄是以 a|\r\n 結束的
select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;
結果 7C0D0A
LOAD DATA
INFILE demo20.dat "str X'7C0D0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo20.dat
10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia|
20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia|
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia|
40,Finance,Virginia,987268297,This is the Finance
Office in Virginia|
==============================================================================
象這樣的資料 用 nullif 子句
10-jan-200002350Flipper seemed unusually hungry today.
10510-jan-200009945Spread over three meals.
id position(1:3) nullif id=blanks // 這裡可以是blanks 或者別的表示式
// 下面是另一個列子 第一行的 1 在資料庫中將成為 null
LOAD DATA
INFILE *
INTO TABLE T
REPLACE
(n position(1:2) integer external nullif n='1',
v position(3
)
BEGINDATA
1 10
20lg
------------------------------------------------------------
如果是英文的日誌 格式，可能需要修改環境變數 nls_lang or nls_date_format
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23590362/viewspace-630446/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [轉]sql load 的一點小總結SQL
- sql load的一點小總結(轉)SQL
- sql load的一點小總結SQL
- sql loader的一點總結SQL
- 總結的小知識點（一）
- 以前學習sql的一點總結SQL
- 關於v-for的一點小總結
- data buffer cache的一點總結 -- 轉
- 學習git以及github自己的一點小總結：Github
- @RequestParam，@RequestBody，@ResponseBody，@PathVariable註解的一點小總結
- jQuery的一點小結jQuery
- 轉 ------ sql load 詳解SQL
- Duplicate的一點總結
- ash的一點總結
- 一點總結
- LOAD_BALANCE&TAF總結
- SQL語法的重要知識點總結SQL
- clickhouse使用的一點總結
- flashback database的一點總結Database
- rowid的一點總結！
- Sybase及SQL Anywhere SQL語句小結(轉)SQL
- 一點ASMM總結ASM
- load 與initialize的呼叫順序小結
- SQL*Loader的使用總結(一)SQL
- OS認證以及口令檔案的一點總結（轉）
- 關於Linux許可權設定的一點小總結Linux
- SPOOL、SQLLOADER資料匯出匯入的一點小總結SQL
- 簡單SQL語句小結(轉)SQL
- SQL Azure與SQL Server的異同點總結和歸納SQLServer
- Oracle LOAD_BALANCE&TAF總結Oracle
- 使用繫結變數的一點總結！變數
- 大學兩年的一點總結
- 線段樹的一點總結
- 關於ORACLE的一點總結Oracle
- 有關role的一點總結!
- data buffer cache的一點總結。
- 關於物流行業數字化轉型的一點總結（一）行業
- rowid一點總結