ORACLE–IF EXISTS函數

對於Oracle中沒有 if exists(...) 的文法，目前有許多種解決方案，這裡先分析常用的三種，推薦使用最後一種

第一種是最常用的，判斷count(*)的值是否為零，如下
declare
  v_cnt number;
begin
  select count(*) into v_cnt from T_VIP where col=1;
  if v_cnt = 0 then
    dbms_output.put_line('無記錄');
  end if;
end;
首先這種寫法讓人感覺很奇怪，明明只需要知道表裡有沒有記錄，卻去統計了全表的記錄數。
這種方式對於小表而言可以接受，一旦表記錄很多的時候，效能問題就非常嚴重
因此有人就作了些修改，改成 select count(*) into v_cnt from T_VIP where col=1 and rownum=1
看起來似乎解決了效能問題，但是分析執行計畫可以知道，實際上是一樣的，不推薦使用。

第二種是所謂進攻式編程，不作預先判斷，而是直接預設通過判斷，然後使用 exception 來捕獲異常
比如我這裡不判斷表中是否有滿足條件的記錄，預設它有，如果沒有就在異常中進行處理
declare
  v_1 number;
begin
  select vip_level into v_1 from T_VIP where 1=0;
exception
  when no_data_found then
    dbms_output.put_line('無記錄');
end;
這種方式從效能上講比第一種要好得多
不過首先它沒辦法適應所有的情況，如第一段代碼它就沒辦法改造
其次這種代碼看起來讓人覺得好像是發生了異常，而不是正常運行，從而造成混亂，不推薦使用。

第三種是利用 Oracle 原有的 Exists 文法，如下
declare
  v_cnt number;
begin
  select count(*)
    into v_cnt
    from dual
   where exists (select * from t_vip where col=1);
  if v_cnt = 0 then
    dbms_output.put_line('無記錄');
  end if;
end;
通過在語句的外面套上一層dual，來使用oracle原有的exists文法
雖然和第一種看起來類似，但分析執行計畫可以知道，效能比以上兩種都要好得多，與MSSQL的 if exists 最接近，推薦使用。

可以把判斷封裝成一個函數以方便使用，代碼如下

CREATE OR REPLACE FUNCTION EXISTS2 (IN_SQL IN VARCHAR2)
  RETURN NUMBER
IS
  /**********************************************************
  * 使用樣本
  * begin
  *   if EXISTS2('select * from dual where 1=1')=1 then
  *     dbms_output.put_line('有記錄');
  *   else
  *     dbms_output.put_line('無記錄');
  *   end if;
  * end;
  *****************************************************************/
  V_SQL VARCHAR2(4000);
  V_CNT NUMBER(1);
BEGIN
  V_SQL := 'SELECT COUNT(*) FROM DUAL WHERE EXISTS (' || IN_SQL || ')';
  EXECUTE IMMEDIATE V_SQL INTO V_CNT;
  RETURN(V_CNT);
END;

-

對於常用的insert判斷還有更簡單的寫法，比如以下代碼
if not exists(select * from table1 where id=1)
   insert into table1 values(1,'a');
可以改寫成
insert
  when (not exists(select * from table1 where id=1)) then
into table1
select 1 as id, 'a' as data from dual;

-

再比如以下的代碼
if not exists(select * from table1 where id=2)
   insert into table1 values(2,'b')
else
   update table1 set data='b' where id=2;
可以改寫成
merge into table1 his
using
(
  select 2 as id, 'b' as data from dual
) src
on (his.id=src.id)
when matched then
  update set his.data=src.data where id=src.id
when not matched then
  insert values(src.id,src.data);

-

這裡附帶說下，有人喜歡把count(*)寫成count(列名)，不推薦後一種，因為列名是需要額外的操作，去查詢系統資料表來定位列資訊

另外count(1)和count(*)沒有差別，推薦使用count(*)直觀明了