Oracle 存儲函數開發指南：語法、參數、調用方式與純度控制

Oracle 存儲函數開發指南：語法、參數、調用方式與純度控制

函數與過程最大的區別在於必須有返回值，且可直接嵌入 SQL 語句（SELECT/WHERE/ORDER BY）調用，極大擴展了 SQL 的表達能力。本文系統講解函數語法、參數模式、三種調用方式、純度級別與實戰案例。

函數語法結構

CREATE [OR REPLACE] FUNCTION func_name (
  param_name [IN | OUT | IN OUT] [NOCOPY] datatype [DEFAULT expr],
  ...
) RETURN return_datatype
[AUTHID {DEFINER | CURRENT_USER}]
{IS | AS}
[PRAGMA AUTONOMOUS_TRANSACTION;]
DECLARE
  -- 聲明區
BEGIN
  -- 執行區
  RETURN return_value;  -- 必須有 RETURN
EXCEPTION
  -- 異常處理
  RETURN fallback_value;
END [func_name];

三種調用方式

1. PL/SQL 塊賦值調用：v_result := func_name(args);
2. 過程中調用：同 PL/SQL 賦值。
3. SQL 語句中直接調用：SELECT func_name(args) FROM dual; 或 WHERE func_name(col) > 100

基礎示例

1. 無參函數：返回系統時間字符串

CREATE OR REPLACE FUNCTION datetime_fun RETURN VARCHAR2 AS
BEGIN
  RETURN TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS');
END;

-- 調用
BEGIN DBMS_OUTPUT.PUT_LINE(datetime_fun); END;
SELECT datetime_fun FROM dual;

2. 帶 IN/OUT 參數：查詢員工姓名並返回職位

CREATE OR REPLACE FUNCTION getinfo_fun (
  p_eno   EMP.EMPNO%TYPE,
  p_job   OUT EMP.JOB%TYPE
) RETURN VARCHAR2 AS
  v_name EMP.ENAME%TYPE;
  v_cnt  NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM emp WHERE empno = p_eno;
  IF v_cnt > 0 THEN
    SELECT ename, job INTO v_name, p_job FROM emp WHERE empno = p_eno;
  END IF;
  RETURN v_name;
END;

-- 調用
DECLARE v_id EMP.EMPNO%TYPE := 7369; v_name EMP.ENAME%TYPE; v_job EMP.JOB%TYPE;
BEGIN
  v_name := getinfo_fun(v_id, v_job);
  DBMS_OUTPUT.PUT_LINE('編號:'||v_id||' 姓名:'||v_name||' 職位:'||v_job);
END;

參數模式示例

IN 模式（支持默認值）

CREATE OR REPLACE FUNCTION in_fun (
  p_a VARCHAR2 DEFAULT 'Java開發',
  p_b IN VARCHAR2 DEFAULT '好好學習'
) RETURN VARCHAR2 AS
BEGIN
  RETURN p_b;
END;

-- 調用
DECLARE v_res VARCHAR2(100);
BEGIN
  v_res := in_fun;  -- 使用兩個默認值
  DBMS_OUTPUT.PUT_LINE(v_res);  -- 輸出: 好好學習
END;

OUT 模式

CREATE OR REPLACE FUNCTION out_fun (
  p_a OUT VARCHAR2,
  p_b OUT VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
  p_a := 'Java實戰';
  p_b := 'Oracle實戰';
  RETURN p_b;
END;

-- 調用
DECLARE v_a VARCHAR2(100); v_b VARCHAR2(100); v_r VARCHAR2(100);
BEGIN
  v_r := out_fun(v_a, v_b);
  DBMS_OUTPUT.PUT_LINE('返回:'||v_r||' OUT_a:'||v_a||' OUT_b:'||v_b);
END;

實戰案例：函數實現部門新增（返回狀態碼）

CREATE OR REPLACE FUNCTION dept_insert_fun (
  p_dno   DEPT.DEPTNO%TYPE,
  p_dname DEPT.DNAME%TYPE,
  p_loc   DEPT.LOC%TYPE
) RETURN NUMBER AS
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM dept WHERE deptno = p_dno;
  IF v_cnt > 0 THEN RETURN -1; END IF;
  INSERT INTO dept(deptno, dname, loc) VALUES (p_dno, p_dname, p_loc);
  COMMIT;
  RETURN 0;
END;

-- SQL 中調用
SELECT dept_insert_fun(99, 'AI', 'Shanghai') FROM dual;

-- PL/SQL 調用
DECLARE v_res NUMBER;
BEGIN
  v_res := dept_insert_fun(98, 'ML', 'Beijing');
  IF v_res = 0 THEN DBMS_OUTPUT.PUT_LINE('成功'); ELSE DBMS_OUTPUT.PUT_LINE('失敗'); END IF;
END;

純度級別控制

函數在 SQL 中調用時，Oracle 要求聲明其副作用範圍（8i/9i 必需，10g+ 部分放寬）：

PRAGMA RESTRICT_REFERENCES(func_name, WNDS [, WNPS] [, RNDS] [, RNPS]);

CALL 語句調用（SQL*Plus）

VAR v_sal NUMBER;
CALL get_sal_fun(7369) INTO :v_sal;
PRINT v_sal;

函數 vs 過程 選擇指南

最佳實踐

1. SQL 調用的函數必須聲明 WNDS（10g+ 可自動推斷，但顯式聲明更安全）。
2. 避免在函數中執行 DML，若必須執行，使用 PRAGMA AUTONOMOUS_TRANSACTION 並明確文檔說明。
3. 確定性函數（相同輸入必得相同輸出）添加 DETERMINISTIC 關鍵字，啟用結果緩存優化。
4. 高頻調用的純計算函數考慮 RESULT_CACHE（11g+）實現跨會話緩存。