萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 子程序詳解(二):函數建立、返回值與在 SQL 中調用的關鍵限制

函數 vs 過程

  • 函數 **必須** 有 RETURN 子句且執行路徑必須返回值。
  • 函數可用於 SQL 陳述式 (SELECT/WHERE/VALUES),過程不行。
  • 帶 OUT/IN OUT 參數的函數**不可**在 SQL 中呼叫。

建立語法

CREATE [OR REPLACE] FUNCTION func_name
  (param1 [IN] datatype, ...)
RETURN return_datatype
IS | AS
BEGIN
  RETURN expr;
EXCEPTION
  ...
END;

範例集

1. 無參函數

CREATE OR REPLACE FUNCTION current_time RETURN VARCHAR2 AS
BEGIN
  RETURN TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日" HH24"時"MI"分"SS"秒"');
END;

調用:SELECT current_time FROM DUAL; 或 PL/SQL 變數指派。

2. IN 參數函數

CREATE OR REPLACE FUNCTION get_ename (p_eno IN NUMBER) RETURN VARCHAR2 AS
  v_ename VARCHAR2(20);
BEGIN
  SELECT ENAME INTO v_ename FROM EMP WHERE EMPNO = p_eno;
  RETURN v_ename;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RETURN '無此員工';
END;

3. OUT 參數函數 (僅 PL/SQL 可呼叫)

CREATE OR REPLACE FUNCTION get_ename_job (
  p_eno IN NUMBER,
  p_job OUT VARCHAR2) RETURN VARCHAR2 AS
  v_ename VARCHAR2(20);
BEGIN
  SELECT ENAME, JOB INTO v_ename, p_job FROM EMP WHERE EMPNO = p_eno;
  RETURN v_ename;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_job := 'N/A'; RETURN 'NOT FOUND';
END;

調用:

DECLARE v_job VARCHAR2(20); BEGIN
  DBMS_OUTPUT.PUT_LINE(get_ename_job(7788, v_job));
  DBMS_OUTPUT.PUT_LINE(v_job);
END;

決定性與純度

  • DETERMINISTIC:相同輸入必得相同輸出,優化器可快取結果 (適用於函數索引、物化視圖)。
  • PARALLEL_ENABLE:宣告可平行執行 (12c+)。
  • 純度等級 (WNDS/RNDS/WNPS/RNPS) 11g 起由編譯器自動推斷,無需 PRAGMA RESTRICT_REFERENCES。

效能陷阱

  • SQL 中呼叫 PL/SQL 函數 → Context Switch 開銷大。大量資料改用純 SQL 或 WITH FUNCTION (12c) 內聯。
  • 避免在函數執行 DML (除非 AUTONOMOUS_TRANSACTION),否則 SQL 呼叫會報 ORA-14551。
copyright © 萬盛學電腦網 all rights reserved