程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle12c中增強的PL/SQL功能

Oracle12c中增強的PL/SQL功能

編輯：Oracle教程

Oracle12c中增強的PL/SQL功能

Oracle 12c增強了一系列定義和執行PL/SQL程序單元的方式。本文覆蓋了Oracle 12c幾個新特性：

1.為結果緩存條件優化了調用者權限函數
2.可以在SQL語句中定義和執行PL/SQL函數
3.通過ACCESSIBLE BY條件指定一個白名單來限制程序單元的訪問
4.可直接授權角色給指定程序單元

調用者權限和PL/SQL函數結果緩存
Oracle 11g引入了PL/SQL函數結果緩存，提供了非常強大，有效和易於使用的緩存機制。主要目標是保證如果最近一次獲取的記錄未發生變化，則無需再執行任何SQL而從緩存中返回同樣結果。
這個特性對整個數據庫實例可用。也就是說，如果一個用戶USER_ONE執行了一個結果緩存函數從employees表中獲得ID=100的行。當另一個用戶USER_TWO執行同樣的函數請求ID=100的行，那麼結果將直接從緩存返回而不會執行一個SELECT語句。
如果你未使用過這個特性，我強力建議你研究一下並使用它– 注意要和DBA協調一致以設置合理的結果緩存區。
即使是在Oracle 11.2，你仍然無法結合調用者權限（AUTHID CURRENT_USER）和函數結果緩存（RESULT_CACHE）使用。這樣的嘗試只會帶來編譯失敗：

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;
/

導致這個編譯錯誤:

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

失敗原因在於調用者權限。運行期間PL/SQL引擎將使用當前用戶權限來處理相關的數據庫對象如表和視圖。但是如果函數帶有RESULT_CACHE條件，那麼用戶USER_ONE執行函數，傳入參數100後，用戶USER_TWO調用同一函數，函數體將壓根不會執行並且相關表EMPLOYEES也不會根據USER_TWO權限進行檢查。這將帶來嚴重的安全問題！好消息是這個限制是暫時的。12c中，我們可以編譯上面的last_name函數而不報錯！來看看幕後，Oracle 12c將當前用戶作為隱含參數傳遞；這個參數將伙同其他入參一起緩存起來！這就意味著對於調用者權限函數的結果緩存是按照當前用戶分區的。因此，對於調用者權限函數的結果緩存將只針對同一用戶相同參數的重復調用有性能提升。Oracle 11g中我們可以用另外一種方式實現同樣的效果，只需改變一下last_name函數的實現： Code Listing 1: “Partitioned” Oracle Database 11g Release 2 invoker’s rights function

CREATE OR REPLACE PACKAGE employee_api
   AUTHID CURRENT_USER
IS
   FUNCTION last_name (
      employee_id_in IN employees.employee_id%TYPE)
      RETURN employees.last_name%TYPE;
END;
/

CREATE OR REPLACE PACKAGE BODY employee_api
IS
   FUNCTION i_last_name (
      employee_id_in   IN employees.employee_id%TYPE,
      user_in          IN VARCHAR2 DEFAULT USER)
      RETURN employees.last_name%TYPE
      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;

   FUNCTION last_name (
      employee_id_in IN employees.employee_id%TYPE)
      RETURN employees.last_name%TYPE
   IS
      l_return   employees.last_name%TYPE;
   BEGIN
      RETURN i_last_name (employee_id_in,
                          USER);
   END;
END;
/

注意last_name函數定義在包說明並且未緩存結果。反而，公共函數僅僅是調用了一個私有函數（只定義在函數體），可以看到我們多加了第2個參數：USER！這樣以來每次我調用employee_api.last_name，Oracle 將判斷該用戶是否已緩存。顯然這種方法有點多余！在12c中我們僅需要考慮好給調用者權限程序增加結果緩存是否值得！

在SQL語句中定義PL/SQL子程序

開發者早就可以在SQL語句中調用自己的PL/SQL函數。考慮這種情況，我創建了一個函數BETWNSTR其功能類似substr自定義函數：

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語言的使用。缺點是需要在SQL和PL/SQL執行引擎間切換！來到Oracle 12c，你可以使用WITH子句定義PL/SQL函數和過程然後從子查詢調用返回結果。這個特性使我們將BETWNSTR函數和查詢升級成一個語句！！！

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

那麼為什麼開發者想復制PL/SQL函數到SQL語句呢？為了提升性能。當我在一個SQL語句中調用我自己的PL/SQL函數，SQL引擎（SQL engine）必須執行一次影響性能的上下文切換到PL/SQL引擎。而移動代碼到SQL語句中意味著不再發生上下文切換。 3. 引用一個包中常量盡管你能在SQL中調用包中函數，你卻不能引用一個包中的常量（除非將SQL語句放在PL/SQL塊中執行）。這裡的例子展示了這個限制：

SQL> CREATE OR REPLACE PACKAGE pkg
  2  IS
  3     year_number   
        CONSTANT INTEGER := 2013;
  4  END;
  5  /

Package created.

SQL> SELECT pkg.year_number 
FROM employees
  2   WHERE employee_id = 138
  3  /
SELECT pkg.year_number FROM employees
ERROR at line 1:
ORA-06553: PLS-221: 'YEAR_NUMBER' is not 
a procedure or is undefined

經典的變通方案是在包中定義一個函數來返回這個常量（夠拼的。。。(⊙﹏⊙)）

SQL> CREATE OR REPLACE PACKAGE pkg
  2  IS
  3     FUNCTION year_number
  4        RETURN INTEGER;
  5  END;
  6  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pkg
  2  IS
  3     c_year_number   
        CONSTANT INTEGER := 2013;
  4
  5     FUNCTION year_number
  6        RETURN INTEGER
  7     IS
  8     BEGIN
  9        RETURN c_year_number;
 10     END;
 11  END;
 12  /

Package body created.

SQL> SELECT pkg.year_number
  2    FROM employees
  3   WHERE employee_id = 138
  4  /

YEAR_NUMBER
———————————
       2013

為了引用一個常量多出了這麼多代碼！然而在Oracle 12c中，這種做法大可不必。我們只需要再WITH子句中創建自定義函數來返回包中的常量即可：

WITH
 FUNCTION year_number
 RETURN INTEGER
 IS
 BEGIN
   RETURN pkg.year_number;
 END;
SELECT year_number
  FROM employees
 WHERE employee_id = 138

這個WITH FUNCTION特性是對SQL語言非常有用的增強。然而你應該在用之前想一下這個問題：我需要在程序中多個地方用到它嗎？如果需要，你應該權衡WITH FUNCTION帶來的性能提升和復制、粘貼這套邏輯到多個SQL語句的弊端。 4. 白名單和ACCESSIBLE BY子句大多數基於PL/SQL的應用程序都是由許多包組成，其中一些是頂層（top level）API，供開發者調用實現用戶需求而其他則是幫助包，僅被特定的包調用。 12c以前，PL/SQL無法阻止具有包執行權限的用戶會話使用包中任一程序。自12c開始，相比之下，所有的PL/SQL程序單元都有一個ACCESSIBLE BY子句選項，目的在於指定哪一些程序單元可調用當前正在創建和修改的程序單元。來看一個例子。首先我創建一個公共包說明，供其他開發者調用以創建應用程序。

CREATE OR REPLACE PACKAGE public_pkg
IS
   PROCEDURE do_only_this;
END;
/

接下來，我創建了我的“私有”包說明。並保證只允許公共包public_pkg調用。所以我增加了ACCESSIBLE BY子句。

CREATE OR REPLACE PACKAGE private_pkg   
   ACCESSIBLE BY (public_pkg)
IS
   PROCEDURE do_this;

   PROCEDURE do_that;
END;
/

現在，是時候實現包體了。Public_pkg.do_only_this過程調用private_pkg子程序。

CREATE OR REPLACE PACKAGE BODY public_pkg
IS
   PROCEDURE do_only_this
   IS
   BEGIN
      private_pkg.do_this;
      private_pkg.do_that;
   END;
END;
/

CREATE OR REPLACE PACKAGE BODY 
private_pkg
IS
   PROCEDURE do_this
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('THIS');
   END;

   PROCEDURE do_that
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('THAT');
   END;
END;
/

現在可以毫無問題的運行這個公共包的過程：

BEGIN
   public_pkg.do_only_this;
END;
/
THIS
THAT

但是如果我試圖在匿名塊中調用私有包的子過程，出現以下錯誤：（呦吼！耍不了賴了！嗯哼，有意思！）

BEGIN
   private_pkg.do_this;
END;
/

ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00904: insufficient privilege to 
access object PRIVATE_PKG
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

程序試圖調用私有包的子程序，編譯則會報同樣的錯誤：

SQL> CREATE OR REPLACE PROCEDURE 
use_private
  2  IS
  3  BEGIN
  4     private_pkg.do_this;
  5  END;
  6  /
Warning: Procedure created with 
compilation errors.

SQL> SHOW ERRORS 

Errors for PROCEDURE USE_PRIVATE:

LINE/COL ERROR
———————— ——————————————————————————
4/4      PL/SQL: Statement ignored
4/4      PLS-00904: insufficient 
         privilege to access object 
         PRIVATE_PKG

看好了，是“PLS”錯誤提示，這個問題將在編譯期間即被捕捉。使用這個特性不會帶來任何的運行時性能影響。 5. 將角色授權給程序單元 12c以前，一個定義者權限的程序單元（以AUTHID DEFINER定義或不指定）總是以單元所有者的權限執行。一個調用者權限程序單元（以AUTHID CURRENT_USER定義）總是以單元調用者的權限執行。這種設置的一個結果是，如果一個程序需要被所有用戶執行，那麼該程序將被設置為定義者權限單元。這樣一來將擁有定義者所有權限來執行程序單元，從安全角度來看不是很好。自12c起，你可以將角色授權給PL/SQL包和模式級過程和函數。基於角色權限的程序單元使開發者更細致地分配相應的程序單元給調用者。你現在可以定義一個調用者權限的程序單元，然後通過授權有限的權限給相應角色來補足調用者權限。讓我們來走查以下例子，展示如何授權角色給程序單元。假設HR模式包含departments和employees表，定義和填充數據如下：

CREATE TABLE departments
(
   department_id     INTEGER,
   department_name   VARCHAR2 (100),
   staff_freeze      CHAR (1)
)
/

BEGIN
   INSERT INTO departments
        VALUES (10, 'IT', 'Y');

   INSERT INTO departments
        VALUES (20, 'HR', 'N');

   COMMIT;
END;
/

CREATE TABLE employees
(
   employee_id     INTEGER,
   department_id   INTEGER,
   last_name       VARCHAR2 (100)
)
/

BEGIN
   DELETE FROM employees;

   INSERT INTO employees
        VALUES (100, 10, 'Price');

   INSERT INTO employees
        VALUES (101, 20, 'Sam');

   INSERT INTO employees
        VALUES (102, 20, 'Joseph');
   INSERT INTO employees
        VALUES (103, 20, 'Smith');

   COMMIT;
END;
/

並且假設SCOTT模式下僅包含employees表，定義和填充數據如下：

CREATE TABLE employees
(
   employee_id     INTEGER,
   department_id   INTEGER,
   last_name       VARCHAR2 (100)
)
/

BEGIN
   DELETE FROM employees;

   INSERT INTO employees
        VALUES (100, 10, 'Price');

   INSERT INTO employees
        VALUES (104, 20, 'Lakshmi');

   INSERT INTO employees
        VALUES (105, 20, 'Silva');

   INSERT INTO employees
        VALUES (106, 20, 'Ling');
   COMMIT;
END;
/

HR也包含一個可以移除一個部門下所有員工的過程。我先用定義者權限創建該過程，如下： Code Listing 2: Definer’s rights procedure that removes employee records

CREATE OR REPLACE PROCEDURE remove_emps_in_dept (
   department_id_in IN employees.department_id%TYPE)
   AUTHID DEFINER
IS
   l_freeze   departments.staff_freeze%TYPE;
BEGIN
   SELECT staff_freeze
     INTO l_freeze
     FROM HR.departments
    WHERE department_id = department_id_in;

   IF l_freeze = ‘N’
   THEN
      DELETE FROM employees
            WHERE department_id = department_id_in;
   END IF;
END;
/

這個時候SCOTT可以執行該過程：

GRANT EXECUTE
   ON remove_emps_in_dept
   TO SCOTT
/

當SCOTT像以下方式執行過程時，將會從HR的表employees中移除3行！因為這個時候使用的是定義者權限單元。

BEGIN
   HR.remove_emps_in_dept (20);
END;
/

我需要改變該過程使得刪除的是SCOTT下表employees的數據，而不是HR下。此時修改為調用者權限。 AUTHID CURRENT_USER

但是運行報錯：

BEGIN
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "HR.REMOVE_EMPS_IN_DEPT", line 7
ORA-06512: at line 2

問題在於Oracle數據庫在SCOTT模式下找不到表HR.departments。畢竟SCOTT對HR.departments表無任何權限。 12c以前，DBA不得不賦予必要的權限給SCOTT。現在，DBA們可以采取以下步驟：

CREATE ROLE hr_departments
/

GRANT hr_departments TO hr
/

連接到HR, 授權想要的權限給角色然後授權角色給過程：

GRANT SELECT
   ON departments
   TO hr_departments
/

GRANT hr_departments TO PROCEDURE remove_emps_in_dept
/

回過頭來再次執行，數據從SCOTT.employees表正確移除了！

SELECT COUNT (*)
  FROM employees
 WHERE department_id = 20
/

  COUNT(*)
—————————————
         3

BEGIN
   hr.remove_emps_in_dept (20);
END;
/

SELECT COUNT (*)
  FROM employees
 WHERE department_id = 20
/


  COUNT(*)
—————————————
         0

授權給程序單元的角色不會影響編譯。取而代之的，他們影響運行時SQL語句的權限檢查。因此，過程或函數以它自己的角色和任何當前可用的角色權限運行。這個特性將對調用者權限程序單元最有用。你將更可能的考慮授予角色給執行動態SQL的定義者權限單元，因為動態語句的權限是在運行時檢查的。