SPOOL、SQLLOADER資料匯出匯入的一點小總結
1、SQLLOADER的CONTROL檔案
//********************************************************************************//
基本格式：
LOAD DATA
INFILE 'T.DAT' // 要匯入的資料檔案(格式1)
//INFILE 'TT.DAT' // 匯入多個檔案(可以和格式1並列使用)
//INFILE * // 要匯入的內容就在CONTROL檔案裡 下面的BEGINDATA後面就是匯入的內容(和格式1互斥使用)
APPEND INTO TABLE TABLE_NAME // 指定裝入的表(這裡有幾種載入方式)
//以下是4種裝入表的方式
//APPEND // 原先的表有資料就加在後面
// INSERT // 裝載空表,如果原先的表有資料SQLLOADER會停止預設值
// REPLACE // 原先的表有資料 原先的資料會全部刪除
// TRUNCATE // 指定的內容和REPLACE的相同 會用TRUNCATE語句刪除現存資料
BADFILE 'C:BAD.TXT' // 指定壞檔案地址
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
// 裝載這種資料: "10","20","30","40","50"
// TERMINATED BY X'09' // 以十六進位制格式'09'表示文字檔案用TAB鍵分隔
// 示例文字資料: "10" "20" "30" "40" "50"
// TERMINATED BY WHITESPACE // 裝載這種資料: "10" "lg" "lg"
TRAILING NULLCOLS ************* 表的欄位沒有對應的值時允許為空
************* 下面是表的欄位
(COL_1 , COL_2 ,COL_FILLER FILLER // FILLER 關鍵字 此列的數值不會被裝載)
//指定的TERMINATED可以在表的開頭 也可在表的內部欄位部分
// 當沒宣告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 '"'
// )
// 當沒宣告FIELDS TERMINATED BY ','用位置告訴欄位裝載資料
// (
// COL_1 POSITION(1:2),
// COL_2 POSITION(3:10),
// COL_3 POSITION(*:16), // 這個欄位的開始位置在前一欄位的結束位置
// COL_4 POSITION(3:10) CHAR(8), // 指定欄位的型別
// COL_5 POSITION(3:10) "TRIM(:COL_5)", // 擠壓兩端空格
// COL_6 POSITION(3:10) "SEQ.NEXTVAL", // 取SEQUENCE值
// )
BEGINDATA // 對應開始的 INFILE * 要匯入的內容就在CONTROL檔案裡
10,20,30
20,30,40
//********************************************************************************//
CONTROL檔案示例:
//注意BEGINDATA後的數值前面不能有空格
1 ***** 普通裝載
LOAD DATA
INFILE *
REPLACE INTO TABLE DEPT
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 *
REPLACE INTO TABLE DEPT
FIELDS TERMINATED BY WHITESPACE
-- FIELDS TERMINATED BY X'09'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales Virginia
3 ***** 指定不裝載那一列
LOAD DATA
INFILE *
REPLACE INTO TABLE DEPT
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 *
REPLACE INTO TABLE DEPT
( 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 *
REPLACE INTO TABLE DEPT
FIELDS TERMINATED BY ','
TRAILING NULLCOLS // 其實下面的ENTIRE_LINE在BEGINDATA後面的資料中是沒有直接對應
// 的列的值 如果第一行改為 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
(DEPTNO,
DNAME "UPPER(NAME)", // 使用函式
LOC "UPPER(:LOC)",
LAST_UPDATED DATE 'DD/MM/YYYY', // 日期的一種表達方式 還有'DD-MON-YYYY' 等
ENTIRE_LINE "EPTNO||NAME||: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 *
REPLACE INTO TABLE DEPT
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "UPPER(NAME)",
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(NAME)",
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 & /n
< 1 > 使用一個非換行符的字元
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "UPPER(NAME)",
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 SalesnOffice in Virginia
20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia
40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia
< 2 > 使用fix屬性
LOAD DATA
INFILE DEMO17.DAT "FIX 101"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "UPPER(NAME)",
LOC "UPPER(:LOC)",
LAST_UPDATED "MY_TO_DATE( :LAST_UPDATED )",
COMMENTS
)
BEGINDATA
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(NAME)",
LOC "UPPER(:LOC)",
LAST_UPDATED "MY_TO_DATE( :LAST_UPDATED )",
COMMENTS
)
BEGINDATA
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"
< 3 > 使用var屬性
LOAD DATA
INFILE DEMO19.DAT "VAR 3"
// 3 告訴每個記錄的前3個位元組表示記錄的長度 如第一個記錄的 071 表示此記錄有 71 個位元組
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "UPPER(NAME)",
LOC "UPPER(:LOC)",
LAST_UPDATED "MY_TO_DATE( :LAST_UPDATED )",
COMMENTS
)
BEGINDATA
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
< 4 > 使用STR屬性
// 最靈活的一中 可定義一個新的行結尾符 WIN 回車換行 : CHR(13)||CHR(10)
此列中記錄是以 A|RN 結束的
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(NAME)",
LOC "UPPER(:LOC)",
LAST_UPDATED "MY_TO_DATE( :LAST_UPDATED )",
COMMENTS
)
BEGINDATA
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|
10 *****象這樣的資料 用 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:8)
)
BEGINDATA
1 10
20lg
//********************************************************************************//
SQLLOADER的命令：
SQLLDR USERID=SYS/SYS@DB_SERVICE CONTROL=XXXX.CTL LOG=XXXX.LOG BINDSIZE=1048576 ROWS=100
ERRORS=10000 READSIZE=2097152 SILENT=(HEADER,FEEDBACK)
關於這些引數的幫助在命令列直接執行SQLLDR可以得到，這裡指出BINDSIZE不應該大於READSIZE的值。
關於SPOOL匯出文字資料的一些格式建議：
SQL*PLUS環境設定SET NEWPAGE NONE HEADING OFF SPACE 0 PAGESIZE 0 TRIMOUT ON TRIMSPOOL ON LINESIZE 2500
注：LINESIZE要稍微設定大些，免得資料被截斷，它應和相應的TRIMSPOOL結合使用防止匯出的文字有太多的尾部空格。
但是如果LINESIZE設定太大，會大大降低匯出的速度，另外在WINDOWS下匯出最好不要用PLSQL匯出，速度比較慢，
直接用COMMEND下的SQLPLUS命令最小化視窗執行。
對於SPOOL資料的SQL，最好要自己定義格式，以方便我們的匯入，如例子如下：
SELECT
JBSJ.JSJDM||CHR(9)||
JBSJ.ZZJGDM||CHR(9)||
JBSJ.YYZZH||CHR(9)||
REPLACE(REPLACE(JBSJ.NSRMC,CHR(10)),CHR(13))||CHR(9)||
QYRY.ZJLXDM||CHR(9)||
REPLACE(REPLACE(QYRY.ZJHM,CHR(10)),CHR(13))||CHR(9)||
REPLACE(REPLACE(QYRY.XM,CHR(10)),CHR(13))||CHR(9)||
REPLACE(REPLACE(JBSJ.ZCDZ,CHR(10)),CHR(13))||CHR(9)||
REPLACE(REPLACE(JBSJ.JYDZ,CHR(10)),CHR(13))||CHR(9)||
REPLACE(REPLACE(JBSJ.JYFW,CHR(10)),CHR(13))||CHR(9)||
REPLACE(REPLACE(JBSJ.JYDZYB,CHR(10)),CHR(13))||CHR(9)||
ZCLX.DJZCLXDM||CHR(9)||
TO_CHAR(JBSJ.KYDJRQ,'YYYY-MM-DD')||CHR(9)||
JBSJ.SWJGZZJGDM||CHR(9)||
REPLACE(REPLACE(SWJGZZJG.SWJGZZJGMC,CHR(10)),CHR(13))||CHR(9)||
JBSJ.ZCZBJE||CHR(9)||
JBSJ.NSRZT||CHR(9)||
NSRZT.NSRZTMC
FROM DJDB.DJ_JL_JBSJ JBSJ,
DJDB.DJ_JL_QYRY QYRY,
DMDB.GY_DM_SWJGZZJG SWJGZZJG,
DMDB.DJ_DM_DJZCLX ZCLX,
DMDB.DJ_DM_NSRZT NSRZT
WHERE JBSJ.DJZCLXDM=ZCLX.DJZCLXDM
AND JBSJ.JSJDM=QYRY.JSJDM
AND QYRY.ZWDM='01'
AND JBSJ.SWJGZZJGDM=SWJGZZJG.SWJGZZJGDM
AND JBSJ.NSRZT=NSRZT.NSRZTDM
AND JBSJ.NSRZT!=90
AND JBSJ.KYDJRQ < TO_DATE('20040701','YYYYMMDD')
對於欄位內包含很多回車換行符的應該給與過濾，形成比較規矩的文字檔案。
//********************************************************************************//
基本格式：
LOAD DATA
INFILE 'T.DAT' // 要匯入的資料檔案(格式1)
//INFILE 'TT.DAT' // 匯入多個檔案(可以和格式1並列使用)
//INFILE * // 要匯入的內容就在CONTROL檔案裡 下面的BEGINDATA後面就是匯入的內容(和格式1互斥使用)
APPEND INTO TABLE TABLE_NAME // 指定裝入的表(這裡有幾種載入方式)
//以下是4種裝入表的方式
//APPEND // 原先的表有資料就加在後面
// INSERT // 裝載空表,如果原先的表有資料SQLLOADER會停止預設值
// REPLACE // 原先的表有資料 原先的資料會全部刪除
// TRUNCATE // 指定的內容和REPLACE的相同 會用TRUNCATE語句刪除現存資料
BADFILE 'C:BAD.TXT' // 指定壞檔案地址
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
// 裝載這種資料: "10","20","30","40","50"
// TERMINATED BY X'09' // 以十六進位制格式'09'表示文字檔案用TAB鍵分隔
// 示例文字資料: "10" "20" "30" "40" "50"
// TERMINATED BY WHITESPACE // 裝載這種資料: "10" "lg" "lg"
TRAILING NULLCOLS ************* 表的欄位沒有對應的值時允許為空
************* 下面是表的欄位
(COL_1 , COL_2 ,COL_FILLER FILLER // FILLER 關鍵字 此列的數值不會被裝載)
//指定的TERMINATED可以在表的開頭 也可在表的內部欄位部分
// 當沒宣告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 '"'
// )
// 當沒宣告FIELDS TERMINATED BY ','用位置告訴欄位裝載資料
// (
// COL_1 POSITION(1:2),
// COL_2 POSITION(3:10),
// COL_3 POSITION(*:16), // 這個欄位的開始位置在前一欄位的結束位置
// COL_4 POSITION(3:10) CHAR(8), // 指定欄位的型別
// COL_5 POSITION(3:10) "TRIM(:COL_5)", // 擠壓兩端空格
// COL_6 POSITION(3:10) "SEQ.NEXTVAL", // 取SEQUENCE值
// )
BEGINDATA // 對應開始的 INFILE * 要匯入的內容就在CONTROL檔案裡
10,20,30
20,30,40
//********************************************************************************//
CONTROL檔案示例:
//注意BEGINDATA後的數值前面不能有空格
1 ***** 普通裝載
LOAD DATA
INFILE *
REPLACE INTO TABLE DEPT
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 *
REPLACE INTO TABLE DEPT
FIELDS TERMINATED BY WHITESPACE
-- FIELDS TERMINATED BY X'09'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales Virginia
3 ***** 指定不裝載那一列
LOAD DATA
INFILE *
REPLACE INTO TABLE DEPT
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 *
REPLACE INTO TABLE DEPT
( 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 *
REPLACE INTO TABLE DEPT
FIELDS TERMINATED BY ','
TRAILING NULLCOLS // 其實下面的ENTIRE_LINE在BEGINDATA後面的資料中是沒有直接對應
// 的列的值 如果第一行改為 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
(DEPTNO,
DNAME "UPPER(NAME)", // 使用函式
LOC "UPPER(:LOC)",
LAST_UPDATED DATE 'DD/MM/YYYY', // 日期的一種表達方式 還有'DD-MON-YYYY' 等
ENTIRE_LINE "EPTNO||NAME||: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 *
REPLACE INTO TABLE DEPT
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "UPPER(NAME)",
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(NAME)",
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 & /n
< 1 > 使用一個非換行符的字元
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "UPPER(NAME)",
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 SalesnOffice in Virginia
20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia
40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia
< 2 > 使用fix屬性
LOAD DATA
INFILE DEMO17.DAT "FIX 101"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "UPPER(NAME)",
LOC "UPPER(:LOC)",
LAST_UPDATED "MY_TO_DATE( :LAST_UPDATED )",
COMMENTS
)
BEGINDATA
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(NAME)",
LOC "UPPER(:LOC)",
LAST_UPDATED "MY_TO_DATE( :LAST_UPDATED )",
COMMENTS
)
BEGINDATA
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"
< 3 > 使用var屬性
LOAD DATA
INFILE DEMO19.DAT "VAR 3"
// 3 告訴每個記錄的前3個位元組表示記錄的長度 如第一個記錄的 071 表示此記錄有 71 個位元組
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "UPPER(NAME)",
LOC "UPPER(:LOC)",
LAST_UPDATED "MY_TO_DATE( :LAST_UPDATED )",
COMMENTS
)
BEGINDATA
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
< 4 > 使用STR屬性
// 最靈活的一中 可定義一個新的行結尾符 WIN 回車換行 : CHR(13)||CHR(10)
此列中記錄是以 A|RN 結束的
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(NAME)",
LOC "UPPER(:LOC)",
LAST_UPDATED "MY_TO_DATE( :LAST_UPDATED )",
COMMENTS
)
BEGINDATA
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|
10 *****象這樣的資料 用 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:8)
)
BEGINDATA
1 10
20lg
//********************************************************************************//
SQLLOADER的命令：
SQLLDR USERID=SYS/SYS@DB_SERVICE CONTROL=XXXX.CTL LOG=XXXX.LOG BINDSIZE=1048576 ROWS=100
ERRORS=10000 READSIZE=2097152 SILENT=(HEADER,FEEDBACK)
關於這些引數的幫助在命令列直接執行SQLLDR可以得到，這裡指出BINDSIZE不應該大於READSIZE的值。
關於SPOOL匯出文字資料的一些格式建議：
SQL*PLUS環境設定SET NEWPAGE NONE HEADING OFF SPACE 0 PAGESIZE 0 TRIMOUT ON TRIMSPOOL ON LINESIZE 2500
注：LINESIZE要稍微設定大些，免得資料被截斷，它應和相應的TRIMSPOOL結合使用防止匯出的文字有太多的尾部空格。
但是如果LINESIZE設定太大，會大大降低匯出的速度，另外在WINDOWS下匯出最好不要用PLSQL匯出，速度比較慢，
直接用COMMEND下的SQLPLUS命令最小化視窗執行。
對於SPOOL資料的SQL，最好要自己定義格式，以方便我們的匯入，如例子如下：
SELECT
JBSJ.JSJDM||CHR(9)||
JBSJ.ZZJGDM||CHR(9)||
JBSJ.YYZZH||CHR(9)||
REPLACE(REPLACE(JBSJ.NSRMC,CHR(10)),CHR(13))||CHR(9)||
QYRY.ZJLXDM||CHR(9)||
REPLACE(REPLACE(QYRY.ZJHM,CHR(10)),CHR(13))||CHR(9)||
REPLACE(REPLACE(QYRY.XM,CHR(10)),CHR(13))||CHR(9)||
REPLACE(REPLACE(JBSJ.ZCDZ,CHR(10)),CHR(13))||CHR(9)||
REPLACE(REPLACE(JBSJ.JYDZ,CHR(10)),CHR(13))||CHR(9)||
REPLACE(REPLACE(JBSJ.JYFW,CHR(10)),CHR(13))||CHR(9)||
REPLACE(REPLACE(JBSJ.JYDZYB,CHR(10)),CHR(13))||CHR(9)||
ZCLX.DJZCLXDM||CHR(9)||
TO_CHAR(JBSJ.KYDJRQ,'YYYY-MM-DD')||CHR(9)||
JBSJ.SWJGZZJGDM||CHR(9)||
REPLACE(REPLACE(SWJGZZJG.SWJGZZJGMC,CHR(10)),CHR(13))||CHR(9)||
JBSJ.ZCZBJE||CHR(9)||
JBSJ.NSRZT||CHR(9)||
NSRZT.NSRZTMC
FROM DJDB.DJ_JL_JBSJ JBSJ,
DJDB.DJ_JL_QYRY QYRY,
DMDB.GY_DM_SWJGZZJG SWJGZZJG,
DMDB.DJ_DM_DJZCLX ZCLX,
DMDB.DJ_DM_NSRZT NSRZT
WHERE JBSJ.DJZCLXDM=ZCLX.DJZCLXDM
AND JBSJ.JSJDM=QYRY.JSJDM
AND QYRY.ZWDM='01'
AND JBSJ.SWJGZZJGDM=SWJGZZJG.SWJGZZJGDM
AND JBSJ.NSRZT=NSRZT.NSRZTDM
AND JBSJ.NSRZT!=90
AND JBSJ.KYDJRQ < TO_DATE('20040701','YYYYMMDD')
對於欄位內包含很多回車換行符的應該給與過濾，形成比較規矩的文字檔案。
通常情況下，我們使用SPOOL方法，將資料庫中的表匯出為文字檔案的時候會採用兩種方法，如下述：
方法一：採用以下格式指令碼
set colsep '' ------設定列分隔符
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool 路徑+檔名
select * from tablename;
spool off
方法二：採用以下指令碼
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool 路徑+檔名
select col1||','||col2||','||col3||','||col4||'..' from tablename;
spool off
比較以上方法，即方法一採用設定分隔符然後由sqlplus自己使用設定的分隔符對欄位進行分割，方法二將分隔符拼接在SELECT語句中，即手工控制輸出格式。
在實踐中，我發現透過方法一匯出來的資料具有很大的不確定性，這種方法匯出來的資料再由sql ldr匯入的時候出錯的可能性在95%以上，尤其對大批次的資料表，如100萬條記錄的表更是如此，而且匯出的資料檔案狂大。
而方法二匯出的資料檔案格式很規整，資料檔案的大小可能是方法一的1/4左右。經這種方法匯出來的資料檔案再由sqlldr匯入時，出錯的可能性很小，基本都可以匯入成功。
因此，實踐中我建議大家使用方法二手工去控制spool檔案的格式，這樣可以減小出錯的可能性，避免走很多彎路。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18921899/viewspace-1016955/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- js匯入匯出總結與實踐JS
- phpMyAdmin匯入/匯出資料PHP
- Oracle資料匯入匯出Oracle
- 資料泵匯出匯入
- sqoop資料匯入匯出OOP
- Oracle 資料匯入匯出Oracle
- EasyPoi, Excel資料的匯入匯出Excel
- Mongodb資料的匯出與匯入MongoDB
- 匯入和匯出AWR的資料
- 資料庫 MySQL 資料匯入匯出資料庫MySql
- Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令Oracle資料庫
- 比 poi匯入匯出更好用的 EasyExcel使用小結Excel
- SQL資料庫的匯入和匯出SQL資料庫
- Oracle資料泵的匯入和匯出Oracle
- MySQL入門--匯出和匯入資料MySql
- [Docker核心之容器、資料庫檔案的匯入匯出、容器映象的匯入匯出]Docker資料庫
- Mysql 資料庫匯入與匯出MySql資料庫
- oracle資料匯出匯入（exp/imp）Oracle
- 【最佳實踐】MongoDB匯出匯入資料MongoDB
- 複雜「場景」資料匯入匯出
- ClickHouse 資料表匯出和匯入（qbit）
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- QZpython匯入匯出redis資料的實現deuPythonRedis
- 使用Dbeaver 進行資料的匯入和匯出
- MongoDB--Mongodb 中資料匯出與匯入MongoDB
- 使用csv批量匯入、匯出資料的需求處理
- MYSQL資料匯出備份、匯入的幾種方式MySql
- PHP大資料xlswriter匯入匯出(最優資料化)PHP大資料
- 模組匯入小結
- SQLServer匯出匯入資料到MySQLServerMySql
- Redis資料匯入工具優化過程總結Redis優化
- mongodb使用自帶命令工具匯出匯入資料MongoDB
- 使用VUE+SpringBoot+EasyExcel 整合匯入匯出資料VueSpring BootExcel
- sqoop用法之mysql與hive資料匯入匯出OOPMySqlHive
- 【MySQL】白話說MySQL(五)，資料的匯出與匯入MySql
- php讀取excel檔案資料的匯入和匯出PHPExcel
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- Activity 流程模型匯入匯出-activity流程模型匯入匯出模型
- 一個通用的匯入匯出excel的思路Excel