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中调用包

Oracle12c中增强的PL/SQL功能(一)