Oracle database 12 c pl sql
This presentation is the property of its rightful owner.
Sponsored Links
1 / 40

Oracle Database 12 c 的新 PL/SQL 功能 PowerPoint PPT Presentation


  • 113 Views
  • Uploaded on
  • Presentation posted in: General

Oracle Database 12 c 的新 PL/SQL 功能. Bryn Llewellyn 资深产品经理 数据库服务器技术部门 Oracle 总部. 以下内容旨在概述产品的总体发展方向 。 该内容仅供参考 ,不可纳入任何合同 。 该内容不构成提供任何材料、代码或功能的承诺,并且不应该作为制定购买决策的依据 。 此处所述有关 Oracle 产品的任何特性或功能的开发、发布以及相应的日程安排均由 Oracle 自行决定 。. 议题. 改进的客户端 <> PL/SQL <> SQL 互操作性 新的安全功能 改进的编程人员可用性 其他.

Download Presentation

Oracle Database 12 c 的新 PL/SQL 功能

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Oracle database 12 c pl sql

Oracle Database 12c 的新 PL/SQL 功能

Bryn Llewellyn

资深产品经理

数据库服务器技术部门

Oracle 总部


Oracle database 12 c pl sql

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


Oracle database 12 c pl sql

议题

  • 改进的客户端 <> PL/SQL <> SQL 互操作性

  • 新的安全功能

  • 改进的编程人员可用性

  • 其他


Pl sql index by sql

绑定 PL/SQL index-by 表至 SQL

  • 在 12.1 之前的版本中,您可以从集合中选择,但是

    • 类型必须进行模式级定义

    • 因此,类型必须为嵌套表或可变数组

    • 非标量负载必须为 ADT

  • 12.1 中的新增特性

    • 类型可以在程序包规范中定义 - 可以是index by pls_integer 表

    • 负载可以是记录 - 但该字段必须仍为 SQL 数据类型


Oracle database 12 c pl sql

集合

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;


1 ibpi sql pl sql

示例 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;


2 table

示例 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;


3 ibpi

示例 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;


Oracle database 12 c pl sql

示例 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;


With pl sql

在子查询的 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;


Sql pl sql

针对三个数字列使用 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


Oracle database 12 c pl sql

在纯 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


Pl sql sql

借助 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;


With pl sql1

在子查询的 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


Oracle database 12 c pl sql

性能比较

  • 纯 SQL最快

  • 带 pragma UDF 的模式级函数性能接近

  • with 子句中的函数性能相似

  • 在 12.1 之前的版本中,普通模式级函数速度最慢

5.0 倍

3.9 倍

3.8 倍

1.0 - 基准


Jdbc pl sql

改进针对 JDBC 中绑定 PL/SQL 类型的支持

  • 12.1 之前的版本

    • 生成模式级对象类型,以映射非 SQL 包类型的结构

    • 填充并绑定对象至围绕着所需的 PL/SQL 子程序的自定义 PL/SQL 包装器

    • 将对象转换为包装器中的包类型,并使用该包类型调用 PL/SQL 子程序


Jdbc pl sql1

改进针对 JDBC 中绑定 PL/SQL 类型的支持

  • 12.1 中的新增特性

    • 支持将 PL/SQL 包类型作为 JDBC 中的绑定

    • 可以使用非 SQL 类型执行 PL/SQL 子程序

    • 支持的类型包括记录、index-by 表、嵌套表和可变数组

    • 还支持Table%rowtype、view%rowtype和包定义的cursor%rowtype。从技术上讲,它们都属于记录类型


1 java pl sql java

示例 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;


Oracle database 12 c pl sql

示例 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());

  • }


2 bulk collect table rowtype out

示例 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;


Oracle database 12 c pl sql

示例 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);

}


Oracle database 12 c pl sql

议题

  • 改进的客户端 <> PL/SQL <> SQL 互操作性

  • 新的安全功能

  • 改进的编程人员可用性

  • 其他


Pl sql

为 PL/SQL 单元赋予角色

  • 请参考本最佳实践

    • 仅通过 PL/SQL 子程序提供针对应用程序数据的访问

    • 将最终用户会话授权为不同于应用程序构件所有者的另一个数据库所有者,以对其进行加强

    • 在一个模式或多个模式中使用定义者权限,以对其进行安排。然后,向最终用户授予针对这些过程的 Execute 权限 - 但不要向最终用户授予针对表的权限

    • 这意味着每个单元可以访问很多表,因为单元的所有者拥有这些权限


Pl sql1

为 PL/SQL 单元赋予角色

  • 12.1 为我们提供了细粒度的模式,其中具有相同所有者的每个单元可以针对所有者的表具有不同的权限

    • 最终用户具有较低的权限,这一点与旧模式相同

    • 这些单元具有调用者权限,因此“按原样”不会允许最终用户访问数据

    • 通过赋予角色,并使角色对这些单元拥有适当的权限,每个单元的权限将获得相应提升。此类角色不能禁用。

    • 单元的所有者必须拥有相同的角色(但无需启用)


Pl sql2

为 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 同理


Pl sql3

为 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


Pl sql4

为 PL/SQL 单元赋予角色

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


Oracle database 12 c pl sql

议题

  • 改进的客户端 <> PL/SQL <> SQL 互操作性

  • 新的安全功能

  • 改进的编程人员可用性

  • 其他


Oracle database 12 c pl sql

改进的调用堆栈自检

  • 在 12.1 之前的版本中,您需要使用DBMS_Utility包中的三个函数

    • Format_Call_Stack()

    • Format_Error_Stack()

    • Format_Error_Backtrace()

  • 12.1 中的新增特性

    • UTL_Call_Stack 包通过适当方法解决了相同问题


Oracle database 12 c pl sql

需要自检的代码

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;


12 1 print call stack

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 号错误


12 1 print call stack1

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


Oracle database 12 c pl sql

改进的调用堆栈自检

  • 针对错误堆叠和回溯的对称子程序

  • 外加

    • Owner(Depth)

    • Current_Edition(Depth)

    • Lexical_Depth(Depth)


Oracle database 12 c pl sql

白名单

  • 您可以声明某个具体单元只能由其他列出的单元引用

  • 您不能列出匿名块,因此白名单列出的单元不能被动态调用,也不能从数据库外部调用


Accessible by

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


Oracle database 12 c pl sql

议题

  • 改进的客户端 <> PL/SQL <> SQL 互操作性

  • 新的安全功能

  • 改进的编程人员可用性

  • 其他


Oracle database 12 c pl sql

12.1 提供的其他功能增强

  • 您现在可以把调用者权限函数放入结果缓存(当前用户成为缓存查找键的一部分)

  • 加快了安全调出(通过extproc实施)的速度(由 Oracle R Enterprise 驱动 - 速度比以往快 20 倍)

  • 现在无需更改对象在模式中的处置方式,即可采用基于版本的重定义 - 因此,您针对每个补丁使用 EBR 也就是顺理成章的事了,而这些补丁也仅会修改 PL/SQL、视图或同义词。


  • Login