為什麼會想寫這篇文件呢~
主要是因為~這星期一開始~
上司給我一個工作~是我從來沒寫過沒學過的東西
(似乎滿常給我沒學過的東西~只是現在剛好現在沒事想打鐵趁熱上來寫寫)
SQL LOADER(SQLLDR)
為了完成這工作~我大概花一天半的時間在拜G大神
也學到很多東西！為了留下記念以後碰到就可以回顧一下啦！
好啦！開始吧~
我先說大概說一下這次的工作：
主要就是跑批~會有個下傳檔，將這下傳檔用SQLLDR 倒進Oracle
看到這~有用過SQLLDR的人因該會問~那不是很簡單嗎？
我只能說如果資料很一致的話很簡單
但我的下傳檔格式不是很乖
↓假裝一下他是txt的內容吧！
(喔！忘了說~SQLLDR可以用txt跟xls倒入！xls大都會轉成csv)
檔名：ABC00990210
-----------------------------------------------------------------------------------------------------
00990209
123456789012劉小魚00750602
234567890123洪小會00731105
345678901234王大元00750402
.
.
.
-----------------------------------------------------------------------------------------------------
↓假裝這是我要倒入的table欄位吧！
TABLE NAME：CUSTOMER_MAIN
------------------------------------------------------------------------------------------------------
| 日期 | 帳號 | 姓名 | 生日 |
--------------------------------------------------------------------------------------------------------
生日型別為date；日期、帳號跟姓名型別為VARCHAR2
此日期為下傳檔第一行~在我這例子來說就是『00990209』
所以麻煩來了！我看的都是很有規則的一行一行跑~
沒有看到在跑第二行的時後再指向第一行抓到日期！
本來還想先把日期抓出來~放到某變數裡然後每次跑再抓出來用！
但SQLLDR好像沒有這東東！
後來想到先把資料倒進去再把日期補上去~但TABLE PK為『日期，帳號』
OK！我們先跳過這個~我先來說一下SQLLDR怎麼跑
可能還有用多方法~但我是用這方法！
先要準備一個org檔案！這個org就像是範本，靠這範本產出ctl檔案
還有要有bat檔
bat檔內容如下：
sqlldr "rachel/rachel@RACHEL(連結DB) control=[ctl 檔的路徑(絕對路徑)] log=[跑批log檔(會自動產生)] bad=[壞檔時會傳到這檔名(沒試過，但是這麼解譯的)] errors=500000(我也不清楚這500000是指那個)"bat檔還有需多參數，如果有興趣需要可以去找找！
將bat跟ctl檔準備好後~就呼叫cmd~執行bat
如果順利資料就進入DB裡啦
要有正確的ctl出來當然要有正確的org嘍！
控制文件的格式如下：
OPTIONS （ { [SKIP=integer] [ LOAD = integer ]
[ERRORS = integer] [ROWS=integer]
[BINDSIZE=integer] [SILENT=(ALL|FEEDBACK|ERROR|DISCARD) ] )
LOAD[DATA]
[ { INFILE | INDDN } {file | * }
[STREAM | RECORD | FIXED length [BLOCKSIZE size]|
VARIABLE [length] ]
[ { BADFILE | BADDN } file ]
{DISCARDS | DISCARDMAX} integr ]
[ {INDDN | INFILE} . . . ]
[ APPEND | REPLACE | INSERT ]
[RECLENT integer]
[ { CONCATENATE integer |
CONTINUEIF { [THIS | NEXT] (start[: end])LAST }
Operator { 'string' | X 'hex' } } ]
INTO TABLE [user.]table
[APPEND | REPLACE|INSERT]
[WHEN condition [AND condition]...]
[FIELDS [delimiter] ]
(
column {
RECNUM | CONSTANT value |
SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |
[POSITION ( { start [end] | * [ + integer] }
) ]
datatype
[TERMINATED [ BY ] {WHITESPACE| [X] 'character' } ]
[ [OPTIONALLY] ENCLOSE[BY] [X]'charcter']
[NULLIF condition ]
[DEFAULTIF condotion]
}
[ ,...]
)
[INTO TABLE...]
[BEGINDATA]
[ERRORS = integer] [ROWS=integer]
[BINDSIZE=integer] [SILENT=(ALL|FEEDBACK|ERROR|DISCARD) ] )
LOAD[DATA]
[ { INFILE | INDDN } {file | * }
[STREAM | RECORD | FIXED length [BLOCKSIZE size]|
VARIABLE [length] ]
[ { BADFILE | BADDN } file ]
{DISCARDS | DISCARDMAX} integr ]
[ {INDDN | INFILE} . . . ]
[ APPEND | REPLACE | INSERT ]
[RECLENT integer]
[ { CONCATENATE integer |
CONTINUEIF { [THIS | NEXT] (start[: end])LAST }
Operator { 'string' | X 'hex' } } ]
INTO TABLE [user.]table
[APPEND | REPLACE|INSERT]
[WHEN condition [AND condition]...]
[FIELDS [delimiter] ]
(
column {
RECNUM | CONSTANT value |
SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |
[POSITION ( { start [end] | * [ + integer] }
) ]
datatype
[TERMINATED [ BY ] {WHITESPACE| [X] 'character' } ]
[ [OPTIONALLY] ENCLOSE[BY] [X]'charcter']
[NULLIF condition ]
[DEFAULTIF condotion]
}
[ ,...]
)
[INTO TABLE...]
[BEGINDATA]
上述格式轉貼自：http://blog.chinaunix.net/u/7040/showart_287430.html
看完有沒有頭暈暈的fu
這網址是我看那麼多覺得還滿受益的網址！
來公開一下我寫的org吧！
OPTIONS (skip=1)
LOAD DATA
INFILE '{{FILENAME}}'
APPEND
INTO TABLE CUSTOMER_MAIN
(
日期 CONSTANT "{{TRANSDATE}}",
帳號 POSITION(1:12) "trim(:帳號 )",
姓名POSITION(13:15) "trim(: 姓名)",
生日 "to_date(POSITION(15:22), 'yyyymmdd')"
)
LOAD DATA
INFILE '{{FILENAME}}'
APPEND
INTO TABLE CUSTOMER_MAIN
(
日期 CONSTANT "{{TRANSDATE}}",
帳號 POSITION(1:12) "trim(:帳號 )",
姓名POSITION(13:15) "trim(: 姓名)",
生日 "to_date(POSITION(15:22), 'yyyymmdd')"
)
因為我的資料不是用“,“等一些常見的分格符號，所以我只好用POSITION這方法！
看到我的org大家因該會想問我{{FILENAME}}，{{TRANSDATE}}是什麼吧！
因為我這個批次~在要跑之前要去看那些檔我已經跑過了？
有可能一天要跑0~∞(是不至於到∞！但是不可預測的！)的下傳檔！
而我要如何知道我有沒有跑過這檔~我是先去CUSTOMER_MAIN找出他最大的日期
然後看每個下傳檔第一行的日期有沒有比TABLE中最大的日期大~有的話！他就是我這次要跑的檔！
所以就趁在檢查這下傳檔是否為這次需傳的小傳檔時我就把{{FILENAME}}，{{TRANSDATE}}取代成我要的資料！
這樣產出的ctl就是我要的ctl了！
日期那加上『CONSTANT』這是因為我的日期前面有0
如果沒有CONSTANT我的資料會把0拿掉！(原因待查)
加CONSTANT表是那為常數！就不會拿掉0
最後說一下我整個流程吧！(算大整理喔)
先準備好bat，org
然後寫個Console程式處理：
1.檢查下傳檔是否為此次需下傳的檔檔
(如果沒有2、3都不用跑啦，如果多筆就廻圈跑2、3)
2.產生ctl
3.跑bat
幸好這次的業務邏輯是這樣~不然我可能到現在還在想要怎麼解決這麻煩的問題！
不知道有沒有那個高人，可以指點一下我！
當然跑批需要顧慮到效能，所以我在檔案那還有做些事，只是這不是我這篇主要的說的！
如果上述有什麼錯誤還請大家指導一下！
希望大家喜歡這篇文章！
沒有留言:
張貼留言