☆小魚的海底世界☆

2010年2月23日星期二

oracle update語法

大家看到這標題因該會想這麼簡單的東西要說什麼吧！
SQL說簡單~簡單，但要難也是很…

我先承認我是個很不愛背東西的工程師
每次碰到update，alter，delete語法！
我都要上網查一下
大概就只有select不用背

為了以後還要上網查~我決定我要先寫一下基本語法！

INSERT

INSERT INTO [TABLE NAME]([欄名1],[欄名2], ……) VALUES ( 值1, 值2, ……); 


DELETE 
DELETE FROM [TABLE NAME] WHERE 条件;


UPDATE UPDATE [TABLE NAME] SET [欄名1]=值1, [欄名2]=值2, …… WHERE 条件;

好了！
為什麼我會特地寫這篇呢~
也是拖上一篇那批次的福，其實上一篇批次進去後我還必需要去別的TABLE抓資料去UPDATE資料
解說需求：
TABLE A
---------------------------
|     A1     |     A2     |---------------------------

TABLE B
--------------------------------------
|     B1     |     B2     |     B3   |--------------------------------------

這幾個只有B3型別是DATE其他都是文字

TABLE B 的PK B1，B2，B3

我的需求：
先在TABLE B 依B1分群抓出最大的日期(B3)，再A1=B1 UPDATE A2=B2
如果B1<>A1 不 UPDATE A2
所以整個SQL 變這樣：

update A
   set A.A2 = (select s1.B2
                          from B s1,
                               (select B.B2 B2, max(B3) B3
                                  from A, B
                                 where B.B1= A.A1
                                 group by B.B2) s2
                         where s1.B1= s2.B2
                           and s1.B3= s2.B3
                           and A.A1= s1.B1)
 where exists (select * from B where A.A1= B.B1)

exists：資料是否存在

好了！下班~大家81

2010年2月22日星期一

為什麼會想寫這篇文件呢~
主要是因為~這星期一開始~
上司給我一個工作~是我從來沒寫過沒學過的東西
(似乎滿常給我沒學過的東西~只是現在剛好現在沒事想打鐵趁熱上來寫寫)
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]

上述格式轉貼自：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')"
)

因為我的資料不是用“,“等一些常見的分格符號，所以我只好用POSITION這方法！

看到我的org大家因該會想問我{{FILENAME}}，{{TRANSDATE}}是什麼吧！

因為我這個批次~在要跑之前要去看那些檔我已經跑過了？

有可能一天要跑0~∞(是不至於到∞！但是不可預測的！)的下傳檔！

而我要如何知道我有沒有跑過這檔~我是先去CUSTOMER_MAIN找出他最大的日期

然後看每個下傳檔第一行的日期有沒有比TABLE中最大的日期大~有的話！他就是我這次要跑的檔！

所以就趁在檢查這下傳檔是否為這次需傳的小傳檔時我就把{{FILENAME}}，{{TRANSDATE}}取代成我要的資料！

這樣產出的ctl就是我要的ctl了！

日期那加上『CONSTANT』這是因為我的日期前面有0

如果沒有CONSTANT我的資料會把0拿掉！(原因待查)

加CONSTANT表是那為常數！就不會拿掉0

最後說一下我整個流程吧！(算大整理喔)