Oracle Database 12 c 的新 PL/SQL 功能

2. Oracle Database 12c 的新 PL/SQL 功能 Bryn Llewellyn 资深产品经理 数据库服务器技术部门 Oracle 总部

3. 以下内容旨在概述产品的总体发展方向。该内容仅供参考，不可纳入任何合同。该内容不构成提供任何材料、代码或功能的承诺，并且不应该作为制定购买决策的依据。此处所述有关 Oracle 产品的任何特性或功能的开发、发布以及相应的日程安排均由 Oracle 自行决定。

4. 议题 • 改进的客户端 <> PL/SQL <> SQL 互操作性 • 新的安全功能 • 改进的编程人员可用性 • 其他

5. 绑定 PL/SQL index-by 表至 SQL • 在 12.1 之前的版本中，您可以从集合中选择，但是 • 类型必须进行模式级定义 • 因此，类型必须为嵌套表或可变数组 • 非标量负载必须为 ADT • 12.1 中的新增特性 • 类型可以在程序包规范中定义 － 可以是index by pls_integer 表 • 负载可以是记录 － 但该字段必须仍为 SQL 数据类型

6. 集合 package Pkg authid Definer is type r is record(n integer, v varchar2(10)); type t is table of r index by pls_integer; x t; end Pkg;

7. 示例 1绑定 IBPI 至 SQL 中的 PL/SQL 函数 function f(x in Pkg.t) return varchar2 authid Definer is r varchar2(80); begin for j in 1..x.Count() loop r := r||...; end loop; return r; end f; procedure Bind_IBPI_To_Fn_In_SQL authid Definer is v varchar2(80); begin select f(Pkg.x) into v from Dual; ... execute immediate 'select f(:b) from Dual' into v using Pkg.x; end Bind_IBPI_To_Fn_In_SQL;

8. 示例 2与 table 运算符结合使用 procedure Select_From_IBPI authid Definer is y Pkg.t; begin for j in (select n, v from table(Pkg.x)) loop ... end loop; execute immediate 'select n, v from table(:b)' bulk collect into y using Pkg.x; for j in 1..y.Count() loop ... end loop; end Select_From_IBPI;

9. 示例 3绑定 IBPI 至匿名块 procedure p1(x in Pkg.t) authid Definer is begin for j in 1..x.Count() loop ...; end loop; end p1; procedure Bind_IBPI_To_Anon_Block authid Definer is begin execute immediate 'begin p1(:b); end;' using Pkg.x; end Bind_IBPI_To_Anon_Block;

10. 示例 4绑定布尔值至匿名块 procedure p2(b in boolean) authid Definer is begin DBMS_Output.Put_Line(case b when true then 'True' when false then 'False' else 'Null' end); end p2; procedure Bind_Boolean_To_Anon_Block authid Definer is Nil constant boolean := null; begin execute immediate 'begin p2(:b); end;' using true; execute immediate 'begin p2(:b); end;' using false; execute immediate 'begin p2(:b); end;' using Nil; end Bind_Boolean_To_Anon_Block;

11. 在子查询的 with 子句中定义 PL/SQL 函数 • 用例：将一个整数美化输出为 1024 适当次方的倍数：零次方、K（一次方）、M（二次方）、B（三次方）或T（四次方） function Print(n in integer) return varchar2 authid Definer is K constant number not null := 1024; M constant number not null := K*K; G constant number not null := M*K; T constant number not null := G*K; begin return case when n <= K-1 then To_Char(n, '999999')||'byte' when n/K <= K-1 then To_Char(n/K, '999999')||'K' when n/M <= K-1 then To_Char(n/M, '999999')||'M' when n/G <= K-1 then To_Char(n/G, '999999')||'G' else To_Char(n/T, '999999')||'T' end; end Print;

12. 针对三个数字列使用 SQL 中的 PL/SQL 函数 select PK, Print(n1) "n1", Print(n2) "n2", Print(n3) "n3" from t 1 1 K 1 G 566 G 2 1 K 157 M 416 G 3 2 K 1 G 971 G 4 578 byte 1 G 1 T 5 2 K 1 G 220 G 6 1 K 2 G 1 T 7 48 byte 1 G 2 T 8 992 byte 42 M 3 T 9 794 byte 2 G 1 T 10 2 K 302 M 672 G

13. 在纯 SQL 中尝试一下！ • select PK, • case • when n1 <= 1023 then To_Char(n1, '999999')||' byte' • when n1/1024 <= 1023 then To_Char(n1/1024, '999999')||' K' • when n1/1048576 <= 1023 then To_Char(n1/1048576, '999999')||' M' • when n1/1073741824 <= 1023 then To_Char(n1/1073741824, '999999')||' G' • else To_Char(n1/1099511627776, '999999')||' T' • end "n1", • case • when n2 <= 1023 then To_Char(n2, '999999')||' byte' • when n2/1024 <= 1023 then To_Char(n2/1024, '999999')||' K' • when n2/1048576 <= 1023 then To_Char(n2/1048576, '999999')||' M' • when n2/1073741824 <= 1023 then To_Char(n2/1073741824, '999999')||' G' • else To_Char(n2/1099511627776, '999999')||' T' • end "n2", • case • when n3 <= 1023 then To_Char(n3, '999999')||' byte' • when n3/1024 <= 1023 then To_Char(n3/1024, '999999')||' K' • when n3/1048576 <= 1023 then To_Char(n3/1048576, '999999')||' M' • when n3/1073741824 <= 1023 then To_Char(n3/1073741824, '999999')||' G' • else To_Char(n3/1099511627776, '999999')||' T' • end "n3" • from t

14. 借助 PL/SQL 的明确性和可重用性提升 SQL 的性能 function Print(n in integer) return varchar2 authid Definer is K constant number not null := 1024; M constant number not null := K*K; G constant number not null := M*K; T constant number not null := G*K; begin return case when n <= K-1 then To_Char(n, '999999')||'byte' when n/K <= K-1 then To_Char(n/K, '999999')||'K' when n/M <= K-1 then To_Char(n/M, '999999')||'M' when n/G <= K-1 then To_Char(n/G, '999999')||'G' else To_Char(n/T, '999999')||'T' end; end Print; pragma UDF;

15. 在子查询的 with 子句中声明 PL/SQL 函数 function Print(n in integer) return varchar2 is K constant number not null := 1024; M constant number not null := K*K; G constant number not null := M*K; T constant number not null := G*K; begin return case when n <= K-1 then To_Char(n, '999999')||' byte' when n/K <= K-1 then To_Char(n/K, '999999')||' K' when n/M <= K-1 then To_Char(n/M, '999999')||' M' when n/G <= K-1 then To_Char(n/G, '999999')||' G' else To_Char(n/T, '999999')||' T' end; end Print; with select PK, Print(n1) "n1", Print(n2) "n2", Print(n3) "n3" from t

16. 性能比较 • 纯 SQL最快 • 带 pragma UDF 的模式级函数性能接近 • with 子句中的函数性能相似 • 在 12.1 之前的版本中，普通模式级函数速度最慢 5.0 倍 3.9 倍 3.8 倍 1.0 － 基准

17. 改进针对 JDBC 中绑定 PL/SQL 类型的支持 • 12.1 之前的版本 • 生成模式级对象类型，以映射非 SQL 包类型的结构 • 填充并绑定对象至围绕着所需的 PL/SQL 子程序的自定义 PL/SQL 包装器 • 将对象转换为包装器中的包类型，并使用该包类型调用 PL/SQL 子程序

18. 改进针对 JDBC 中绑定 PL/SQL 类型的支持 • 12.1 中的新增特性 • 支持将 PL/SQL 包类型作为 JDBC 中的绑定 • 可以使用非 SQL 类型执行 PL/SQL 子程序 • 支持的类型包括记录、index-by 表、嵌套表和可变数组 • 还支持Table%rowtype、view%rowtype和包定义的cursor%rowtype。从技术上讲，它们都属于记录类型

19. 示例 1：将来自 Java 的一条记录绑定至 PL/SQL 过程，对其进行修改，然后将其绑定回 Java package Emp_Info is type employee is record(First_Name Employees.First_Name%type, Last_Name Employees.Last_Name%type, Employee_Id Employees.Employee_Id%type, Is_CEO boolean); procedure Get_Emp_Name(Emp_p in out Employee); end;

20. 示例 1： • 使用由JPub生成的EmpinfoEmployee类，执行Employee正式参数 • { … • EmpinfoEmployee Employee = new EmpinfoEmployee(); • Employee.setEmployeeId(new java.math.BigDecimal(100)); // Use Employee ID 100 • // Call Get_Emp_Name() with the Employee object • OracleCallableStatementcstmt = • (OracleCallableStatement)conn.prepareCall("call EmpInfo.Get_Emp_Name(?)"); • cstmt.setObject(1, Employee, OracleTypes.STRUCT); • // Use "PACKAGE.TYPE NAME" as the type name • cstmt.registerOutParameter(1, OracleTypes.STRUCT, "EMPINFO.EMPLOYEE"); • cstmt.execute(); • // Get and print the contents of the Employee object • EmpinfoEmployeeoraData = • (EmpinfoEmployee)cstmt.getORAData(1, EmpinfoEmployee.getORADataFactory()); • System.out.println("Employee: " + oraData.getFirstName() + " " + oraData.getLastName()); • System.out.println("Is the CEO? " + oraData.getIsceo()); • }

21. 示例 2：使用 bulk collect 语句填充table%rowtype集合，并将该集合作为out参数传回至调用者 package EmpRow is type Table_of_Emp is table of Employees%Rowtype; procedure GetEmps(Out_Rows out Table_of_Emp); end; package Body EmpRow is procedure GetEmps(Out_Rows out Table_of_Emp) is begin select * bulk collect into Out_Rows from Employees; end; end;

22. 示例 2： { … // Call GetEmps() to get the ARRAY of table row data objects CallableStatement cstmt = conn.prepareCall("call EmpRow.GetEmps(?)"); // Use "PACKAGE.COLLECTION NAME" as the type name cstmt.registerOutParameter(1, OracleTypes.ARRAY, "EMPROW.TABLE_OF_EMP"); cstmt.execute(); // Print the Employee Table rows Array a = cstmt.getArray(1); String s = Debug.printArray ((ARRAY)a, "", ((ARRAY)a).getSQLTypeName () +"( ", conn); System.out.println(s); }

23. 议题 • 改进的客户端 <> PL/SQL <> SQL 互操作性 • 新的安全功能 • 改进的编程人员可用性 • 其他

24. 为 PL/SQL 单元赋予角色 • 请参考本最佳实践 • 仅通过 PL/SQL 子程序提供针对应用程序数据的访问 • 将最终用户会话授权为不同于应用程序构件所有者的另一个数据库所有者，以对其进行加强 • 在一个模式或多个模式中使用定义者权限，以对其进行安排。然后，向最终用户授予针对这些过程的 Execute 权限 － 但不要向最终用户授予针对表的权限 • 这意味着每个单元可以访问很多表，因为单元的所有者拥有这些权限

25. 为 PL/SQL 单元赋予角色 • 12.1 为我们提供了细粒度的模式，其中具有相同所有者的每个单元可以针对所有者的表具有不同的权限 • 最终用户具有较低的权限，这一点与旧模式相同 • 这些单元具有调用者权限，因此“按原样”不会允许最终用户访问数据 • 通过赋予角色，并使角色对这些单元拥有适当的权限，每个单元的权限将获得相应提升。此类角色不能禁用。 • 单元的所有者必须拥有相同的角色（但无需启用）

26. 为 PL/SQL 单元赋予角色 • 这种情形让我们想到了一个方案 • 设置两个用户App 和 Client • 创建两个表App.t1 和 App.t2 • 创建两个 IR 过程App.Show_t1 和 App.Show_t2，以运行针对表的 select语句 • Client 具有针对App.Show_t1 和 App.Show_t2 的 Execute权限 • App创建两个角色r_Show_t1 和 r_Show_t2 • App 向 r_Show_t1授予针对App.t1 的 Select权限 － ~2 同理 • App 将 r_Show_t1赋予App.Show_t1 － ~2 同理

27. 为 PL/SQL 单元赋予角色 create procedure Show_t1 authidCurrent_User is begin for j in (select Fact from App.t1 order by 1) loop -- 注意schema-qualification ... end loop; end Show_t1; / grant Execute on App.Show_t1 to Client / -- 缺点是赋予 App 的角色具有 Admin 选项 -- 目录和版本等其他非模式对象也有相同的表现 create role r_Show_t1 / grant select on t1 to r_Show_t1 / grant r_Show_t1 to procedure Show_t1 / select Object_Name, Object_Type, Role from User_Code_Role_Privs / ....... ......... ......... SHOW_T1 PROCEDURE R_SHOW_T1

28. 为 PL/SQL 单元赋予角色 • 当 Client调用App.Show_t1时，无论编写该过程的人员此后可能犯下何种疏忽，其权限也仅限于已授予该角色的权限。

29. 议题 • 改进的客户端 <> PL/SQL <> SQL 互操作性 • 新的安全功能 • 改进的编程人员可用性 • 其他

30. 改进的调用堆栈自检 • 在 12.1 之前的版本中，您需要使用DBMS_Utility包中的三个函数 • Format_Call_Stack() • Format_Error_Stack() • Format_Error_Backtrace() • 12.1 中的新增特性 • UTL_Call_Stack 包通过适当方法解决了相同问题

31. 需要自检的代码 package body Pkg is procedure p is procedure q is procedure r is procedure p is begin Print_Call_Stack(); end p; begin p(); end r; begin r(); end q; begin q(); end p; end Pkg;

32. 12.1 之前版本的 Print_Call_Stack() procedure Print_Call_Stack authid Definer is Depth pls_integer := UTL_Call_Stack.Dynamic_Depth(); begin DBMS_Output.Put_Line(DBMS_Utility.Format_Call_Stack()); end; ----- PL/SQL Call Stack ----- object line object handle number name 0x631f6e88 12 procedure USR.PRINT_CALL_STACK 0x68587700 7 package body USR.PKG 0x68587700 10 package body USR.PKG 0x68587700 13 package body USR.PKG 0x68587700 16 package body USR.PKG 0x69253ca8 1 anonymous block • 请参阅 2003 年 1 月由 BLLEWELL 提交的 2769809 号错误

33. 12.1 的 Print_Call_Stack() procedure Print_Call_Stack authid Definer is Depth pls_integer := UTL_Call_Stack.Dynamic_Depth(); begin for j in reverse 2..Depth loop DBMS_Output.Put_Line( (j - 1)|| To_Char(UTL_Call_Stack.Unit_Line(j), '99')|| UTL_Call_Stack.Concatenate_Subprogram(UTL_Call_Stack.Subprogram(j))); end loop; end; 5 1 __anonymous_block 4 16 PKG.P 3 13 PKG.P.Q 2 10 PKG.P.Q.R 1 7 PKG.P.Q.R.P

34. 改进的调用堆栈自检 • 针对错误堆叠和回溯的对称子程序 • 外加 • Owner(Depth) • Current_Edition(Depth) • Lexical_Depth(Depth)

35. 白名单 • 您可以声明某个具体单元只能由其他列出的单元引用 • 您不能列出匿名块，因此白名单列出的单元不能被动态调用，也不能从数据库外部调用

36. accessible by 子句 _______ package Helper authid Definer accessible by (Good_Unit, Bad_Unit) is procedure p; end Helper; package body Good_Unit is procedure p is begin Helper.p(); ... end p; end Good_Guy; package body Bad_Unit is procedure p is begin Helper.p(); ... end p; end Bad_Guy; PLS-00904：权限不足，无法访问对象 HELPER

37. 议题 • 改进的客户端 <> PL/SQL <> SQL 互操作性 • 新的安全功能 • 改进的编程人员可用性 • 其他

38. 12.1 提供的其他功能增强 • 您现在可以把调用者权限函数放入结果缓存（当前用户成为缓存查找键的一部分） • 加快了安全调出（通过extproc实施）的速度（由 Oracle R Enterprise 驱动 － 速度比以往快 20 倍） • 现在无需更改对象在模式中的处置方式，即可采用基于版本的重定义 － 因此，您针对每个补丁使用 EBR 也就是顺理成章的事了，而这些补丁也仅会修改 PL/SQL、视图或同义词。