Oracle 12c中增強的PL/SQL功能

英文連結：http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53plsql-1999801.html

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

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

1. 呼叫者許可權和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中我們僅需要考慮好給呼叫者許可權程式增加結果快取是否值得！

1. 在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的定義者許可權單元，因為動態語句的許可權是在執行時檢查的。