Oracle 函數完整教學：建立、編譯、呼叫、刪除與實戰範例

函數 vs 存儲過程

相同點：皆為編譯後儲存在資料庫的 PL/SQL 程式單元。
不同點：函數必須有 RETURN 值，可用於 SQL 語句、賦值運算式、條件判斷中；存儲過程無返回值，通常執行業務動作。

建立函數語法

CREATE OR REPLACE FUNCTION func_test (param1 NUMBER DEFAULT 20) RETURN NUMBER IS
  val NUMBER := 20;
BEGIN
  IF param1 >= val THEN
    RETURN param1;
  ELSE
    RETURN val;
  END IF;
END;

• RETURN NUMBER：宣告返回資料型別（必填）。
• DEFAULT 20：參數預設值，呼叫時可省略。
• RETURN param1：結束函數並回傳值。

編譯函數

• PL/SQL Developer：按 Execute (F8) 即可編譯。
• 命令列：ALTER FUNCTION func_test COMPILE;

三種呼叫方式

刪除函數

DROP FUNCTION func_test;

進階特性

• DETERMINISTIC：相同輸入必定相同輸出，優化器可快取結果，適用於純計算函數。CREATE FUNCTION ... DETERMINISTIC ...
• PARALLEL_ENABLE：宣告可平行執行，配合平行查詢使用。
• PIPELINED：返回集合，配合 TABLE() 當資料表使用。CREATE FUNCTION ... RETURN type PIPELINED ...
• RESULT_CACHE：11g+，結果快取於 SGA，相同參數直接取快取。CREATE FUNCTION ... RESULT_CACHE ...

實戰範例：金額格式化函數

CREATE OR REPLACE FUNCTION fmt_amount (p_amt NUMBER) RETURN VARCHAR2 IS
BEGIN
  IF p_amt IS NULL THEN RETURN '0.00'; END IF;
  RETURN TRIM(TO_CHAR(p_amt, '999,999,999,999,999.99'));
EXCEPTION WHEN OTHERS THEN RETURN 'ERR';
END;

使用：SELECT fmt_amount(salary) FROM employees;

總結

函數 = 必須 RETURN 的 PL/SQL 单元。掌握 CREATE FUNCTION、RETURN datatype、ALTER FUNCTION COMPILE、DROP FUNCTION 四大指令，並善用 DETERMINISTIC/RESULT_CACHE 提升高頻計算效能。