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的定义者权限单元，因为动态语句的权限是在运行时检查的。