Oracle 12c中增强的PL/SQL功能(2)

这种方式延伸了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子程序。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/bd9976f65e0f095de14cb438f0a932d9.html