Oracle 12c中增強PL/SQL功能

Oracle 12c中增強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 modules1
失敗原因在於調用者許可權。運行期間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的定義者許可權單元，因為動態語句的許可權是在運行時檢查的。

Oracle資料庫之PL/SQL程式基礎設計 

PL/SQL Developer實用技巧分享