sql load的一點小總結(轉)
sql load的一點小總結(轉)[@more@][b][color=red]本文討論在:[url]http://www.itpub.net/showthread.php?s=abcf47c1bcb26051a3ed97ad6f45a17a&threadid=154304[/url][/color][/b]以下是本人對sql load 的一些運用總結 不足之處還多包涵sqlldr userid=lgone/tiger control=a.ctlLOAD DATAINFILE 't.dat' // 要匯入的檔案// INFILE 'tt.date' // 匯入多個檔案// INFILE * // 要匯入的內容就在control檔案裡 下面的BEGINDATA後面就是匯入的內容INTO TABLE table_name // 指定裝入的表BADFILE 'c:ad.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 lgTRAILING 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,what20,lg,show=====================================================================================//////////// 注意begindata後的數值前面不能有空格1 ***** 普通裝載LOAD DATAINFILE *INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'(DEPTNO,DNAME,LOC)BEGINDATA10,Sales,"""USA"""20,Accounting,"Virginia,USA"30,Consulting,Virginia40,Finance,Virginia50,"Finance","",Virginia // loc 列將為空60,"Finance",,Virginia // loc 列將為空2 ***** FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情況LOAD DATAINFILE *INTO TABLE DEPTREPLACEFIELDS TERMINATED BY WHITESPACE-- FIELDS TERMINATED BY x'09'(DEPTNO,DNAME,LOC)BEGINDATA10 Sales Virginia3 ***** 指定不裝載那一列LOAD DATAINFILE *INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'( DEPTNO,FILLER_1 FILLER, // 下面的 "Something Not To Be Loaded" 將不會被裝載DNAME,LOC)BEGINDATA20,Something Not To Be Loaded,Accounting,"Virginia,USA"4 ***** position的列子LOAD DATAINFILE *INTO TABLE DEPTREPLACE( DEPTNO position(1:2),DNAME position(*:16), // 這個欄位的開始位置在前一欄位的結束位置LOC position(*:29),ENTIRE_LINE position(1:29))BEGINDATA10Accounting Virginia,USA5 ***** 使用函式 日期的一種表達 TRAILING NULLCOLS的使用LOAD DATAINFILE *INTO TABLE DEPTREPLACEFIELDS 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")BEGINDATA10,Sales,Virginia,1/5/200020,Accounting,Virginia,21/6/199930,Consulting,Virginia,5/1/200040,Finance,Virginia,15/3/20016 ***** 使用自定義的函式 // 解決的時間問題create or replacefunction my_to_date( p_string in varchar2 ) return dateastype 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;beginfor i in 1 .. l_fmts.countloopbeginl_return := to_date( p_string, l_fmts(i) );exceptionwhen others then null;end;EXIT when l_return is not null;end loop;if ( l_return is null )thenl_return :=new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *p_string, 'GMT', 'EST' );end if;return l_return;end;/LOAD DATAINFILE *INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ','TRAILING NULLCOLS(DEPTNO,DNAME "upper(:dname)",LOC "upper(:loc)",LAST_UPDATED "my_to_date( :last_updated )" // 使用自定義的函式)BEGINDATA10,Sales,Virginia,01-april-200120,Accounting,Virginia,13/04/200130,Consulting,Virginia,14/04/2001 12:02:0240,Finance,Virginia,98726829750,Finance,Virginia,02-apr-200160,Finance,Virginia,Not a date7 ***** 合併多行記錄為一行記錄LOAD DATAINFILE *concatenate 3 // 透過關鍵字concatenate 把幾行的記錄看成一行記錄INTO TABLE DEPTreplaceFIELDS TERMINATED BY ','(DEPTNO,DNAME "upper(:dname)",LOC "upper(:loc)",LAST_UPDATED date 'dd/mm/yyyy')BEGINDATA10,Sales, // 其實這3行看成一行 10,Sales,Virginia,1/5/2000Virginia,1/5/2000// 這列子用 continueif list="," 也可以告訴sqlldr在每行的末尾找逗號 找到逗號就把下一行附加到上一行LOAD DATAINFILE *continueif this(1:1) = '-' // 找每行的開始是否有連線字元 - 有就把下一行連線為一行// 如 -10,Sales,Virginia,// 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000// 其中1:1 表示從第一行開始 並在第一行結束 還有continueif next 但continueif list最理想INTO TABLE DEPTreplaceFIELDS 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/2001Finance,Virginia,13/04/20018 ***** 載入每行的行號load datainfile *into table treplace( seqno RECNUM //載入每行的行號text Position(1:1024))BEGINDATAfsdfasj //自動分配一行號給載入 表t 的seqno欄位 此行為 1fasdjfasdfl // 此行為 2 ...9 ***** 載入有換行符的資料注意: unix 和 windows 不同
& /n< 1 > 使用一個非換行符的字元LOAD DATAINFILE *INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ','TRAILING NULLCOLS(DEPTNO,DNAME "upper(:dname)",LOC "upper(:loc)",LAST_UPDATED "my_to_date( :last_updated )",COMMENTS "replace(:comments,'
',chr(10))" // replace 的使用幫助轉換換行符)BEGINDATA10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia40,Finance,Virginia,987268297,This is the Finance
Office in Virginia< 2 > 使用fix屬性LOAD DATAINFILE demo17.dat "fix 101"INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ','TRAILING NULLCOLS(DEPTNO,DNAME "upper(:dname)",LOC "upper(:loc)",LAST_UPDATED "my_to_date( :last_updated )",COMMENTS)demo17.dat10,Sales,Virginia,01-april-2001,This is the SalesOffice in Virginia20,Accounting,Virginia,13/04/2001,This is the AccountingOffice in Virginia30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingOffice in Virginia40,Finance,Virginia,987268297,This is the FinanceOffice in Virginia// 這樣裝載會把換行符裝入資料庫 下面的方法就不會 但要求資料的格式不同LOAD DATAINFILE demo18.dat "fix 101"INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'TRAILING NULLCOLS(DEPTNO,DNAME "upper(:dname)",LOC "upper(:loc)",LAST_UPDATED "my_to_date( :last_updated )",COMMENTS)demo18.dat10,Sales,Virginia,01-april-2001,"This is the SalesOffice in Virginia"20,Accounting,Virginia,13/04/2001,"This is the AccountingOffice in Virginia"30,Consulting,Virginia,14/04/2001 12:02:02,"This is the ConsultingOffice in Virginia"40,Finance,Virginia,987268297,"This is the FinanceOffice in Virginia"< 3 > 使用var屬性LOAD DATAINFILE demo19.dat "var 3"// 3 告訴每個記錄的前3個位元組表示記錄的長度 如第一個記錄的 071 表示此記錄有 71 個位元組INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ','TRAILING NULLCOLS(DEPTNO,DNAME "upper(:dname)",LOC "upper(:loc)",LAST_UPDATED "my_to_date( :last_updated )",COMMENTS)demo19.dat07110,Sales,Virginia,01-april-2001,This is the SalesOffice in Virginia07820,Accounting,Virginia,13/04/2001,This is the AccountingOffice in Virginia08730,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingOffice in Virginia07140,Finance,Virginia,987268297,This is the FinanceOffice in Virginia< 4 > 使用str屬性// 最靈活的一中 可定義一個新的行結尾符 win 回車換行 : chr(13)||chr(10)此列中記錄是以 a|
結束的select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;結果 7C0D0ALOAD DATAINFILE demo20.dat "str X'7C0D0A'"INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ','TRAILING NULLCOLS(DEPTNO,DNAME "upper(:dname)",LOC "upper(:loc)",LAST_UPDATED "my_to_date( :last_updated )",COMMENTS)demo20.dat10,Sales,Virginia,01-april-2001,This is the SalesOffice in Virginia|20,Accounting,Virginia,13/04/2001,This is the AccountingOffice in Virginia|30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingOffice in Virginia|40,Finance,Virginia,987268297,This is the FinanceOffice 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 在資料庫中將成為 nullLOAD DATAINFILE *INTO TABLE TREPLACE(n position(1:2) integer external nullif n='1',v position(3:8))BEGINDATA1 1020lg------------------------------------------------------------如果是英文的日誌 格式,可能需要修改環境變數 nls_lang or nls_date_format
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8225414/viewspace-942756/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [轉]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一點總結