PL/SQL 杂谈(三)（sql plsql）

承接（二），我们来聊聊关于 PL/SQL 版本的问题。

每个版本的oracle数据库都有对应于它的PL/SQL版本。但是在早期数据库版本中，PL/SQL版本号和数据库版本号是不同的，自从oracle 8i 以后，他们的版本号开始保持一致。我个人目前既用 11g，也用 19c。因为有些新特性需要在12c版本以上才有，当然官网上现在已经没有12c了。 下面总结出了对于 PL/SQL 开发者来说，oracle 数据库12C中的PL/SQL新特性中最重要的变化。

（1）在 PL/SQL-to-SQL 接口处，增加了更多的仅适用于PL/SQL的数据类型

在12.1版本之前，我们不能将PL/SQL特有的数据类型（比如关联数组）放到一个动态的SQL语句中，但在12.1版本中，允许在匿名过程块、SQL查询的函数调用、call语句、SQL语句中的表操作中绑定PL/SQL特有的数据类型的值；

（2）accessible_by 子句

现在可以在包声明中包括 accessible_by 子句，用以指定哪些程序单元可以调用过程包中的子程序。这个特性，使得我们可以在帮助程序包中公开那些只能由特定单元调用的子程序。这个特性为程序包提供了一种类似白名单的功能。

（3）隐式语句结果

在12C之前，一个PL/SQL存储子程序的结果，是通过SQL查询的 out ref cursor 参数或者 return 子句显式的返回。客户端的程序则需要显式的绑定这些参数，以便收到结果集。而现在，一个PL/SQL存储过程子程序可以隐式的将查询结果返回到客户端，不再使用 out ref cursor 参数，而是使用 DBMS_SQL 程序包。这个功能将使那些需要从存储子程序（Transact SQL 等语言支持的）中得到查询结果的应用程序，轻松的从第三方数据库平台迁移到oracle数据库平台上。

（4）BEQUEATH CURRENT_USER 视图

在12C之前的版本，视图被作为一个定义者的权限单元（authid definer）来进行访问控制，即使它是在调用者的权限单元中被调用的(authid current_user)。在新的版本，一个视图既能是 BEQUEATH definer（默认配置），表现为定义者的权限单元，也能是 BEQUEATH CURRENT_USER，也就是类似调用者权限单元的表现。

（5）给程序单元定义角色

在12C之前，一个调用者的权限单元，总是以调用者的权限进行运行。如果它的调用者的权限大于它的所有者，在这样的情况下，程序单元的运行就可能越过所有者预设的边界，造成意想不到的后果。在新版本，我们可以给单个PL/SQL过程包甚至是里面单独的子程序定义角色，我们不是使用定义者权限单元，而是创建一个调用者权限单元，然后将角色赋予给它。调用者权限单元就在调用者和角色的权限规定之下运行，不再拥有从定义者 schema 下继承的任何其他权限。现在只有当它的所有者对调用者拥有 inherit privileges 权限 或者 inherit any privileges 权限时，一个调用者权限单元才能在它的调用者的权限范围运行。要注意的是，在安装或升级时，inherit privileges 权限就被赋予了所有的 schema

（6）新增的条件编译指令

在 12.1 版本中，新增了两个预定义的查询指令，$PLSQL_UNIT_OWNER 和 $PLSQL_UNIT_TYPE ，他们的返回值是当前PL/SQL程序单元的所有者和类型。

（7）在SQL中优化函数的执行

在SQL语句中，oracle 目前提供两种提高PL/SQL函数执行性能的办法：我们可以用 with 语句，直接在SQL语句中定义函数；或者我们可以在程序单元中增加 UDF（用户定义函数）编译指令，告知编译器此函数将会主要在SQL语句中使用。

（8）在不可见列使用%rowtype

在新版本，我们可以定义“不可见”列。在PL/SQL内部，已经对%rowtype这种列的各种属性有了对应的处理方法。

（9）fetch first 子句和 bulk collect

在新版本，我们可以使用 fetch first 子句来限制一个查询返回行的数量，相比通常使用 top n 来进行限定的查询，可以显著降低SQL的复杂度。fetch first 最大的益处在于，当从第三方数据库平台迁移应用到oracle数据库平台时，可以极大的简化迁移难度。fetch first 也可以帮助提高一些 select bulk collect into 语句的性能。

（10）UTL_CALL_STACK 过程包

在12C之前，DBMS_UTILITY 过程包提供了3个函数（format_call_stack、format_error_stack和format_error_backtrace)来提供程序运行调用堆栈、错误堆栈和错误回溯的对应信息。在新版本中，新的过程包 UTL_CALL_STACK 不仅可以提供相同的功能，同时对访问字符串的内容进行了更细粒度的控制。