Oracle MERGE INTO的使用方法

標籤：blog   http   使用   資料   io   2014   

           非常多時候我們會出現例如以下情境,假設一條資料在表中已經存在,對其做update,假設不存在,將新的資料插入.假設不使用Oracle提供的merge文法的話,可能先要上資料庫select查詢一下看是否存在,然後決定怎麼操作,這種話須要寫很多其它的代碼,同一時候效能也不好,要來回資料庫兩次.使用merge的話則能夠一條SQL語句完畢.

1）主要功能 
提供有條件地更新和插入資料到資料庫表中 
假設該行存在，運行一個UPDATE操作，假設是一個新行，運行INSERT操作 
    — 避免了分開更新 
    — 提高效能並便於使用 
    — 在資料倉儲應用中十分實用 


2）MERGE語句的文法例如以下： 


MERGE [hint] INTO [schema .] table [t_alias] USING [schema .] 


{ table | view | subquery } [t_alias] ON ( condition ) 


WHEN MATCHED THEN merge_update_clause 


WHEN NOT MATCHED THEN merge_insert_clause; 

3）示範範例

MERGE INTO TABLE TARGETUSING (SELECT ‘111111111‘ STORE_NO,              ‘2014-01‘ TARGET_YM,              ‘1‘ TARGET_D01,              ‘2‘ TARGET_D02,              ‘2‘ TARGET_D03,              ‘2‘ TARGET_D04,              ‘2‘ TARGET_D05,              ‘2‘ TARGET_D06,              ‘2‘ TARGET_D07,              ‘2‘ TARGET_D08,              ‘2‘ TARGET_D09,              ‘2‘ TARGET_D10,              ‘2‘ TARGET_D11,              ‘2‘ TARGET_D12,              ‘2‘ TARGET_D13,              ‘2‘ TARGET_D14,              ‘2‘ TARGET_D15,              ‘2‘ TARGET_D16,              ‘2‘ TARGET_D17,              ‘2‘ TARGET_D18,              ‘2‘ TARGET_D19,              ‘2‘ TARGET_D20,              ‘2‘ TARGET_D21,              ‘2‘ TARGET_D22,              ‘2‘ TARGET_D23,              ‘2‘ TARGET_D24,              ‘2‘ TARGET_D25,              ‘2‘ TARGET_D26,              ‘2‘ TARGET_D27,              ‘2‘ TARGET_D28,              ‘2‘ TARGET_D29,              ‘2‘ TARGET_D30,              ‘2‘ TARGET_D31,              1 USER_ID         FROM DUAL) TEMPON (TARGET.STORE_NO = TEMP.STORE_NO AND TARGET.TARGET_YM = TEMP.TARGET_YM)WHEN MATCHED THEN  UPDATE     SET TARGET.TARGET_D01  = TEMP.TARGET_D01,         TARGET.TARGET_D02  = TEMP.TARGET_D02,         TARGET.TARGET_D03  = TEMP.TARGET_D03,         TARGET.TARGET_D04  = TEMP.TARGET_D04,         TARGET.TARGET_D05  = TEMP.TARGET_D05,         TARGET.TARGET_D06  = TEMP.TARGET_D06,         TARGET.TARGET_D07  = TEMP.TARGET_D07,         TARGET.TARGET_D08  = TEMP.TARGET_D08,         TARGET.TARGET_D09  = TEMP.TARGET_D09,         TARGET.TARGET_D10  = TEMP.TARGET_D10,         TARGET.TARGET_D11  = TEMP.TARGET_D11,         TARGET.TARGET_D12  = TEMP.TARGET_D12,         TARGET.TARGET_D13  = TEMP.TARGET_D13,         TARGET.TARGET_D14  = TEMP.TARGET_D14,         TARGET.TARGET_D15  = TEMP.TARGET_D15,         TARGET.TARGET_D16  = TEMP.TARGET_D16,         TARGET.TARGET_D17  = TEMP.TARGET_D17,         TARGET.TARGET_D18  = TEMP.TARGET_D18,         TARGET.TARGET_D19  = TEMP.TARGET_D19,         TARGET.TARGET_D20  = TEMP.TARGET_D20,         TARGET.TARGET_D21  = TEMP.TARGET_D21,         TARGET.TARGET_D22  = TEMP.TARGET_D22,         TARGET.TARGET_D23  = TEMP.TARGET_D23,         TARGET.TARGET_D24  = TEMP.TARGET_D24,         TARGET.TARGET_D25  = TEMP.TARGET_D25,         TARGET.TARGET_D26  = TEMP.TARGET_D26,         TARGET.TARGET_D27  = TEMP.TARGET_D27,         TARGET.TARGET_D28  = TEMP.TARGET_D28,         TARGET.TARGET_D29  = TEMP.TARGET_D29,         TARGET.TARGET_D30  = TEMP.TARGET_D30,         TARGET.TARGET_D31  = TEMP.TARGET_D31,         TARGET.OPT_COUNTER = TARGET.OPT_COUNTER + 1,         TARGET.UPDATE_BY   = TEMP.USER_ID,         TARGET.UPDATE_DATE = SYSDATEWHEN NOT MATCHED THEN  INSERT  VALUES    (SEQ.NEXTVAL,     TEMP.STORE_NO,     TEMP.TARGET_YM,     TEMP.TARGET_D01,     TEMP.TARGET_D02,     TEMP.TARGET_D03,     TEMP.TARGET_D04,     TEMP.TARGET_D05,     TEMP.TARGET_D06,     TEMP.TARGET_D07,     TEMP.TARGET_D08,     TEMP.TARGET_D09,     TEMP.TARGET_D10,     TEMP.TARGET_D11,     TEMP.TARGET_D12,     TEMP.TARGET_D13,     TEMP.TARGET_D14,     TEMP.TARGET_D15,     TEMP.TARGET_D16,     TEMP.TARGET_D17,     TEMP.TARGET_D18,     TEMP.TARGET_D19,     TEMP.TARGET_D20,     TEMP.TARGET_D21,     TEMP.TARGET_D22,     TEMP.TARGET_D23,     TEMP.TARGET_D24,     TEMP.TARGET_D25,     TEMP.TARGET_D26,     TEMP.TARGET_D27,     TEMP.TARGET_D28,     TEMP.TARGET_D29,     TEMP.TARGET_D30,     TEMP.TARGET_D31,     NULL,     DEFAULT,     DEFAULT,     NULL,     TEMP.USER_ID,     DEFAULT,     NULL,     NULL);

小協助：

看到資料庫表這麼多欄位是不是好煩，拷貝、寫都麻煩還easy出錯，能夠例如以下操作：

1、先查詢出表的全部欄位

SELECT COLUMN_ID,       COLUMN_NAME,       DATA_TYPE,       DATA_LENGTH,       DATA_PRECISION,       DATA_SCALE,       NULLABLE,       DATA_DEFAULT  FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ‘TABLE‘ ORDER BY COLUMN_ID

2、將該表列名複製到Excel，使用函數CONCATENATE

舉一反三啦，非常多時候都能夠用到的，整理自網路