您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle PL/SQL 函數建立與呼叫完整指南：從基礎語法到 SQL*Plus 實戰

編輯：Oracle 資料庫基礎

前言

PL/SQL 函數是將商業邏輯封裝在資料庫層的核心機制。本文涵蓋函數建立語法、參數模式、SQL 中呼叫、匿名區塊呼叫、SQL*Plus 顯示結果完整流程。

一、函數建立語法


CREATE [OR REPLACE] FUNCTION function_name
  (parameter_name IN datatype [, ...])
RETURN return_datatype
[DETERMINISTIC] [PARALLEL_ENABLE] [PIPELINED]
[AUTHID DEFINER | CURRENT_USER]
IS | AS
  -- 宣告區 (變數、游標、例外)
BEGIN
  -- 執行區
  RETURN return_value;
  [EXCEPTION
    -- 例外處理]
END [function_name];
/

關鍵字解析

OR REPLACE：存在時覆蓋，保留權限
IN：唯一支援的參數模式 (函數不可有 OUT/IN OUT)
DETERMINISTIC：相同輸入必得相同輸出，優化器可快取結果 (函數索引必須)
PARALLEL_ENABLE：宣告可在平行查詢中安全執行
AUTHID：定義者權限 (預設) vs 呼叫者權限

二、基礎範例：字串串接函數


CREATE OR REPLACE FUNCTION addtext(
  str1 IN VARCHAR2,
  str2 IN VARCHAR2
) RETURN VARCHAR2
IS
BEGIN
  RETURN str1 || str2;
END addtext;
/

三、呼叫方式完整示範

1. 在 SQL 查詢中呼叫


SELECT id, name, addtext(name, ' just') AS full_name
FROM test;

2. 在匿名 PL/SQL 區塊中呼叫


SET SERVEROUTPUT ON
DECLARE
  v_result VARCHAR2(100);
BEGIN
  v_result := addtext('just', ' do it');
  DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

輸出： just do it

3. 在 SQL*Plus 直接執行 (需 SERVEROUTPUT)


SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE(addtext('just', ' do it'));
END;
/

四、進階範例：帶區域變數的數值運算函數


CREATE OR REPLACE FUNCTION addnum(
  sum1 IN NUMBER,
  sum2 IN NUMBER
) RETURN NUMBER
IS
  sum3 NUMBER;  -- 區域變數
BEGIN
  sum3 := sum1 + sum2;
  RETURN sum3;
END addnum;
/

五、實戰進階模式

1. 確定性函數 + 函數索引


CREATE OR REPLACE FUNCTION compute_tax(
  amount IN NUMBER,
  rate   IN NUMBER DEFAULT 0.05
) RETURN NUMBER
DETERMINISTIC
IS
BEGIN
  RETURN ROUND(amount * rate, 2);
END;
/

-- 建立函數索引
CREATE INDEX idx_orders_tax ON orders (compute_tax(amount, tax_rate));

-- 查詢自動使用函數索引
SELECT * FROM orders WHERE compute_tax(amount, tax_rate) > 100;

2. 管線函數：回傳集合供 SQL 直接查詢


CREATE OR REPLACE TYPE num_tab IS TABLE OF NUMBER;
/

CREATE OR REPLACE FUNCTION generate_series(
  p_start IN NUMBER,
  p_end   IN NUMBER
) RETURN num_tab PIPELINED
IS
BEGIN
  FOR i IN p_start..p_end LOOP
    PIPE ROW(i);
  END LOOP;
  RETURN;
END;
/

-- 直接在 SQL 使用
SELECT * FROM TABLE(generate_series(1,10));

3. 結果快取函數 (11g+)


CREATE OR REPLACE FUNCTION get_dept_name(
  p_dept_id IN NUMBER
) RETURN VARCHAR2
RESULT_CACHE RELIES_ON (departments)
IS
  v_name VARCHAR2(100);
BEGIN
  SELECT department_name INTO v_name
  FROM departments WHERE department_id = p_dept_id;
  RETURN v_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RETURN 'Unknown';
END;
/

六、常見錯誤與避坑

錯誤	原因	解決
PLS-00103: Encountered symbol "RETURN"	RETURN 放在錯誤位置	確保 RETURN 在 BEGIN-END 區塊內
ORA-06553: PLS-306: wrong number/types of arguments	呼叫參數不匹配	檢查參數順序、型別、預設值
ORA-04068: existing state of packages has been discarded	函數重編譯導致狀態失效	重新執行呼叫端，或使用 EDITION BASED REDEFINITION
函數在 WHERE 條件中導致全表掃描	無函數索引、非確定性	加 DETERMINISTIC + 函數索引
跨 Session 呼叫無權限	缺少 EXECUTE 權限	GRANT EXECUTE ON func TO user/role