4

難得寫了一下ORACLE的Function，明明是很簡單的函數，卻又搞出不愉快的收場... (我跟ORACLE肯定八字不合)

我把情境簡化如下，函數會依傳入值去資料庫查出不同的結果傳回，為了展示起見，我用SELECT SYSDATE FROM DUAL來取代查資料庫的行為:

CREATE OR REPLACE FUNCTION "SCH"."TESTNOTFOUND"
(v_SayYes VARCHAR2)
RETURN VARCHAR2
  IS
v_Return DATE;
BEGIN
  v_Return := SYSDATE;
  dbms_output.put_line('BEFORE');
  SELECT SYSDATE + 1 INTO v_Return
  FROM DUAL
  WHERE v_SayYes = 'Y';
  dbms_output.put_line('AFTER');
  RETURN v_Return;
END;

執行SELECT TestNotFound('Y') FROM DUAL，很好，我得到了明天的日期。那如果是SELECT TestNotFound('N') FROM DUAL會發生什麼事? 猜看看...

得到的結果不是SYSDATE，而是NULL，但更驚人的部分是，dbms_output.put_line('AFTER')以下的指令都不會被執行，意思是當Function中的SELECT INTO variable查不到東西時，會觸發一個無聲的錯誤而中止執行，呼叫端並不會知道出了問題，只會得到一個NULL。

改善方法是加上Exception Handling去抓NO_DATA_FOUND例外，如下:

CREATE OR REPLACE FUNCTION "SCH"."TESTNOTFOUND"
(v_SayYes VARCHAR2)
RETURN VARCHAR2
  IS
v_Return DATE;
BEGIN
  v_Return := SYSDATE;
  dbms_output.put_line('BEFORE');
  BEGIN
    SELECT SYSDATE + 1 INTO v_Return
    FROM DUAL
    WHERE v_SayYes = 'Y';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'Data Not Found!');
        --Or assign specific value
  END;
  dbms_output.put_line('AFTER');
  RETURN v_Return;
END;

原本十分鐘該寫完的東西又耗了一個多小時，孽緣呀，孽緣~~~


Comments

# by 小熊子

好的工具可以讓你上天堂 TOAD / PL SQL Developer 等工具都有產生 Function 範本，可以少打一些字，當然 Exception 也有幫你"傳便便"

# by 小熊子

我錯了，只有 TOAD 才會有…果然是貴的軟體還是有差別

# by nolem

try it : declare tt date; begin tt := TESTNOTFOUND('d'); end; without exception handle , you will get exception form your function .. you do not get any exception cause by select TESTNOTFOUND('d') from dual this statement ..

# by nolem

more about this case , your query sql statement treat your function as a column value ,so column value null is treat as normal state in oracle database

Post a comment