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

Oracle 12c PL/SQL 增強功能深度解析：結果快取、WITH 子句、白名單與角色授權

編輯：Oracle 教學

Oracle 12c PL/SQL 增強功能深度解析

Oracle Database 12c 對 PL/SQL 程式單元的定義與執行機制進行多項重要增強，大幅提升開發彈性、安全性與效能。本文詳細探討四大核心新特性：調用者權限函數支援結果快取、SQL 陳述式中定義 PL/SQL 子程式、ACCESSIBLE BY 白名單機制、以及將角色直接授權給程式單元。

1. 調用者權限函數現在支援結果快取

背景與 11g 限制

Oracle 11g 引入 PL/SQL 函數結果快取（RESULT_CACHE），可跨 Session 共用函數執行結果，避免重複查詢。然而 11g 禁止在調用者權限（AUTHID CURRENT_USER）函數上使用 RESULT_CACHE，否則編譯失敗：

PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms in Invoker-Rights modules

原因在於安全性：調用者權限函數依當前使用者權限存取資料表，若快取不區分使用者，USER_TWO 呼叫時可能直接拿到 USER_ONE 的快取結果，繞過權限檢查。

12c 解決方案：隱含使用者分區快取

12c 將「當前使用者」作為隱含參數納入快取鍵值，實現依使用者分區的結果快取。同一函數、同一參數、不同使用者會有獨立快取項目，完美解決安全疑慮：

CREATE OR REPLACE FUNCTION last_name (employee_id_in IN employees.employee_id%TYPE)
RETURN employees.last_name%TYPE
AUTHID CURRENT_USER RESULT_CACHE IS
  l_return employees.last_name%TYPE;
BEGIN
  SELECT last_name INTO l_return FROM employees WHERE employee_id = employee_id_in;
  RETURN l_return;
END;
/

此寫法在 12c 編譯通過且安全。效能提升僅限於同一使用者重複呼叫相同參數時，符合預期。

2. SQL 陳述式中定義與執行 PL/SQL 函數（WITH 子句）

消除 SQL/PL/SQL 引擎切換成本

傳統作法需先建立資料庫函數，再在 SQL 中呼叫，每次呼叫皆觸發「上下文切換」。12c 允許在 WITH 子句直接定義函數，查詢成為單一陳述式，零切換開銷：

WITH FUNCTION betwnstr (string_in IN VARCHAR2, start_in IN PLS_INTEGER, end_in IN PLS_INTEGER)
RETURN VARCHAR2 IS
BEGIN
  RETURN SUBSTR(string_in, start_in, end_in - start_in + 1);
END;
SELECT betwnstr(last_name, 3, 5) FROM employees;

引用套件常數的新寫法

過去 SQL 無法直接引用套件常數，需包裝成函數。12c 透過 WITH FUNCTION 即可輕鬆存取：

WITH FUNCTION year_number RETURN INTEGER IS
BEGIN
  RETURN pkg.year_number;  -- pkg 為現有套件
END;
SELECT year_number FROM employees WHERE employee_id = 138;

決策建議：若邏輯在多個 SQL 重複使用，權衡「複製貼上維護成本」與「零切換效能增益」；核心邏輯仍建議集中在套件中。

3. ACCESSIBLE BY 白名單：限制程式單元存取權

大型 PL/SQL 應用常分「公開 API 套件」與「內部輔助套件」。12c 前無法阻止具備執行權限的 Session 直接呼叫內部套件。12c 引入 ACCESSIBLE BY 子句，於編譯期強制管控：

-- 僅允許 public_pkg 呼叫
CREATE OR REPLACE PACKAGE private_pkg ACCESSIBLE BY (public_pkg) IS
  PROCEDURE do_this;
  PROCEDURE do_that;
END;
/

匿名區塊或非白名單程式呼叫 private_pkg 將拋出 PLS-00904: insufficient privilege to access object。
錯誤發生在編譯期，零執行期效能影響。
支援多層級巢狀呼叫檢查，建構安全的分層架構。

4. 角色授權給程式單元：最小權限原則落地

定義者/調用者權限困境

定義者權限（AUTHID DEFINER）擁有建立者完整權限，風險高；調用者權限（AUTHID CURRENT_USER）僅具呼叫者權限，常因缺乏底表權限而失敗。

12c 新語法：GRANT ROLE TO PROCEDURE/FUNCTION/PACKAGE

現可建立角色、授予精確物件權限，再將角色授給程式單元。執行時程式單元擁有「自身角色權限 + 呼叫者當前啟用角色權限」：

-- 建立角色並授予底表權限
CREATE ROLE hr_departments;
GRANT SELECT ON hr.departments TO hr_departments;

-- 授權角色給調用者權限程序
GRANT hr_departments TO PROCEDURE hr.remove_emps_in_dept;

程式改為 AUTHID CURRENT_USER，SCOTT 執行時即能正確刪除 SCOTT 自己的 employees 資料，而非 HR 的資料，完美實現最小權限、資料隔離。