接觸程式設計以來，在資料儲存方面一直用的MS SQL。Oracle這名字對我來說是如此的熟悉，但是對其內容卻很陌生，最近公司的一個專案用起了Oracle，所以也開始高調的用起了Oracle。在沒有接觸Oracle之前，聽很多人都說Oracle的語法與MS SQL差不多，我在朋友圈裡也幫著吹噓這個觀點。告訴朋友們，Oralce與MSSQL差不多，確實，貌似一看CRUD幾乎沒區別，但是當你慢慢深入瞭解Oracle的時候，你會發現這個觀點有點愚蠢。
我們先來說個很常見的開發案例，有一張Account表，有兩個欄位分別為AccountID, AccountName，其中AccountID為主鍵，往這個表中插入資料，以主鍵為唯一標識，表中存在這條記錄則修改，不存在則新增。
一：在MS SQL中
首先建立一個Account表，為了簡單，我們都以nvarchar(50)作為欄位型別。具體程式碼如下：
|
1 2 3 4 5 6 7 |
if object_id(N'Account',N'U') is not null drop table Account create table Account ( AccountID nvarchar(50) primary key not null, AccountName nvarchar(50) ) |
接下來我們要做的事就是往這個表中插入資料
|
1 2 3 4 |
if not exists (select * from Account where AccountID = '1') insert into Account(AccountID,AccountName) values('1','Sam Xiao') else update Account set AccountName = '肖建' where AccountID = '1' |
這種程式碼，我們在SQL中是寫的如此自然和熟練，但是你在Oracle中，你用這種方式來寫，你會遇上一些麻煩。那現在我們在Oracle中來演示如何完成這樣的需求。
二：在Oracle中
首先是建立表有著細微的區別，判斷一個表是否存在，習慣了MS SQL的OBJECT_ID(‘物件表’,’物件型別’)的童鞋們，你們是不是想到Oracle中也應該有這樣的功能呢？遺憾了，Oracle中沒有此類函式來判斷一個表是否存在，那就只能通過委婉的方式來實現，MS SQL中有類似於 Select Name From SysObjects Where XType=’U’這樣的資料庫表，那對應的Oracle中就有了select * from user_tables，通過查詢系統表，判斷這個表在資料庫中是否存在，如果存在就刪除，然後再建立。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
declare num number; begin select count(1) into num from user_tables where table_name='ACCOUNT'; if num > 0 then dbms_output.put_line('存在!'); execute immediate 'drop table ACCOUNT '; end if; execute immediate 'create table Account ( AccountID nvarchar2(50) primary key, AccountName nvarchar2(50) )'; dbms_output.put_line('成功建立表!'); end; |
與MS SQL建立一個表對比，是不是還是有一些顯微的差異呢？答案當然是肯定的。
這個演示是前奏，現在來開始我們今天的主題，在Oracle中，表建立成功了，現在我要往這個表中插入資料，如果新插入的資料在表中存在則修改，不存在則插入，我在網上一搜，驚奇的發現Oracle中的exists()函式是判斷兩個資料集合的交集是否存在，與MS SQL有一定的區別。這樣的對比雖然會顯的不專業，但是我還是有對比和發表自己觀點自由。於是我在網上瘋狂的搜尋Oracle在這個問題上的解決方案，總結了以下幾種方案，以供大家選擇：
1：隱式遊標法 SQL%NOTFOUND SQL%FOUND
SQL%NOTFOUND 是SQL中的一個隱式遊標，在增刪查改的時候自動開啟，如果有至少有一條記錄受影響，都會返回false，這就就巧妙的構思出了第一種解決方案：
|
1 2 3 4 5 6 |
begin update account set AccountName = '修改-a' where AccountID = '5'; IF SQL%NOTFOUND THEN insert into account(AccountID,AccountName) values('5','新增-b'); END IF; end; |
先根據唯一ID到資料表中修改一條記錄，如果這條記錄在表中存在，則修改，並且SQL%NOTFOUND返回false。如果修改的記錄不存在，SQL%NOTFOUND返回true，並且執行插入語句。
2：異常法 DUP_VAL_ON_INDEX
當Oracle語句執行時，發生了異常exception進行處理
|
1 2 3 4 5 6 7 |
begin insert into account(AccountID,AccountName) values('6','新增-b'); exception when DUP_VAL_ON_INDEX then begin update account set AccountName = '修改-b' where AccountID = '6'; end; end; |
當往表中插入一條資料，因為表中有主鍵約束，如果插入的資料在表中已經存在，則會丟擲異常，在異常丟擲後進行修改。
3：虛擬表法 dual
dual是一個虛擬表，用來構成select的語法規則，oracle保證dual裡面永遠只有一條記錄。
|
1 2 3 4 5 6 7 8 9 10 11 |
declare t_count number; begin select count(*) into t_count from dual where exists(select 1 from account where AccountID='11'); if t_count 1 then dbms_output.put_line('新增'); insert into account(AccountID,AccountName) values('11','新增-11'); else dbms_output.put_line('修改'); update account set AccountName = '修改-11' where AccountID = '11'; end if; end; |
先宣告一個變數t_count,表dual表的值賦給t_count，如果這個值小於1，表示記錄不存在，進行插入操作，反之，存在就進行修改操作。
4：no_data_found法
先查詢要插入的記錄是否存在，存在則修改，不存在則插入。具體的實現如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
declare t_cols number; begin select AccountName into t_cols from account where AccountID = '8'; exception when no_data_found then begin --dbms_output.put_line('新增'); insert into account(AccountID,AccountName) values('8','新增-8'); end; when others then begin --dbms_output.put_line('修改'); update account set AccountName = '修改-8' where AccountID = '8'; end; end; |
5：merge法
先來看一下merge的語法，
|
1 2 3 4 5 6 7 |
MERGE INTO table_name alias1 USING (table|view|sub_query) alias2 ON (join condition) WHEN MATCHED THEN UPDATE table_name SET col1 = col_val1 WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values); |
看了merge的語法後，依葫蘆畫瓢對於我這種抄襲的人來說已經不是什麼難事了。
|
1 2 3 4 5 6 7 8 |
merge into Account t1 using (select '3' AccountID,'肖文博' AccountName from dual) t2 on (t1.AccountID = t2.AccountID) when matched then update set t1.AccountName = t2.AccountName when not matched then insert values (t2.AccountID, t2.AccountName); commit; |
至此介紹了五種方法來解決我提出的問題。問題是小，但是已經牽涉了Oracle的好幾個知識點。最後你與MS SQL相比，在用法上還是有很大的差異。至此，仁者見仁智者見智。