Oracle PL/SQL 程序设计读书笔记 - 第17章 过程、函数与参数

Oracle PL/SQL 程序设计读书笔记 - 第17章　过程、函数与参数

Oracle PL/SQL 程序设计读书笔记 - 第17章　过程、函数与参数

17.2 过程

过程就是执行一个或者多个动作的模块。由于在PL/SQL中，对于过程的调用是一个单独的可执行语句，一个PL/SQL代码块中可以只有一个过程调用语句。

PROCEDURE [schema.]name[( parameter [, paramter...])]
   [AUTHID DEFINER | CURRENT_USER]
IS
   [declarations]
BEGIN
   excutable statements
[EXCEPTION

exception handlers] END [name];

• AUTHID子句用来定义该过程是用定义者的权限运行，还是用当前用户的权限运行。前一种模式叫做定义者权限模式型，后一种模式叫做调用者权限模型。

17.2.2 过程的头部

在过程定义中位于关键字IS之前的部分就叫做过程的头部或者过程的签名。

• 过程的名字
• AUTHID语句（可选）
• 参数列表（可选）

17.2.3 过程体

所谓过程体就是实现这个过程的代码，是由声明单元，执行单元以及异常处理组成的。在过程定义中IS关键字后面的部分一起组成了过程体。异常处理单元和声明单元是可选的。

17.2.4 END标签

可以在END关键字后面直接跟上过程的名字来完成过程的定义。

17.2.5 RETURN语句

RETURN语句通常是和函数一起使用的，因为函数需要用RETURN语句返回一个值。但PL/SQL也允许我们在过程中使用RETURn语句。用于过程的RETURN不能带着表达式，因此也就不能给调用程序传回一个值。

17.3 函数

函数是通过RETURN语句而不是通过OUT或者IN OUT参数返回数据模块。

17.3.1 函数的结构

FUNCTION [schema.]name[( parameter [, paramter...])]

RETURN return_datatype [AUTHID DEFINER | CURRENT_USER] [DETERMINISTIC] [PARALLEL_ENABLE ...] [PIPELINED] [RESULT_CACHE ...] IS [declarations] BEGIN excutable statements [EXCEPTION exception handlers] END [name];

• DETERMINISTIC子句：这是一个优化器提示，系统可以为函数的返回值保留一个复制。查询优化器可以决定是使用保留的拷贝还是重新执行这个函数。
• PARALLEL_ENABLE子句：这也是一个优化器提示，启用了这个特性的函数当在SELECT语句中调用时可以并行处理。
• PIPELINED子句：指定这个表函数的结果应该通过PIPE ROW命令多次返回。
• RESULT_CACHE子句：这是Oracle数据库11g中的新功能。用来指出这个函数的输入值和返回结果都应该保留一个新的数结果缓存中。

17.4 参数

17.4.1 定义参数

定义参数的语法和在PL/SQL块的声明部分声明变量的语法很类似。二者有两个重要的区别：首先，参数具有传递模式，而变量声明没有;其次，参数声明必须是不受约束的。

所谓受约束的声明，就是通过数据类型对能够赋给这个变量的值进行约束或者限制。而不受约束的声明，则不会有这种限制。

比如:VARCHAR2(60)就是受约束的，如果去掉了60就是不受约束的了。

17.4.2 形参和实参

• 形参：是在模块头部的参数列表中的声明的名字。
• 实参是在真正调用模块时旋转在参数列表的真实的值或者表达式。

17.4.3 参数模式

• IN 只读的 在模块内部可以使用实参的值，但不能修改参数。如果你没有指定参数模式，就相当于是一个IN模式的参数。
• OUT 只写的 模块内部可以给参数赋值，但是参数值不能被使用

• IN OUT 可读写 模块既可以使用（读）也可以修改（写）参数的值。

• IN模式是缺省的参数模式，如果你没有给参数指定模式，参数就会自动按照IN模式处理。

• PL/SQL最多允许64K个参数。
• 我们不能把一个OUT参数的值赋值给另一个变量，甚至不能赋值给它自己。
• 如果程序执行中发生了异常，任何对于OUT参数的赋值操作都会回滚。

17.4.4 在PL/SQL中明确地把形参和实参关联在一起

• 位置表示法：隐式地根据位置把一个实参关联到一个形参。
• 命名表示法：显式地把一个实参关联到一个形参，使用的形参的名字以及一个"=>"连接符。
• 我们也可以在同一个程序调用中混合使用名字和位置表示法。

17.4.5 NOCOPY参数模式限定符

PL/SQl提供一个可以修改参数定义的选项：NOCOPY子句。NOCOPY会要求PL/SQL编译器不要对OUT和IN OUT模式的参数做拷贝。

17.4.6 缺省值

参数的缺省值和声明变量时指定缺省值是一样的。有两种方法指定参数的缺省值：或者使用DEFAULT关键字，或者使用赋值操作符(:=)。

17.5 局部或者嵌套模块

局部模块或者嵌套模块是在一个在PL/SQL块的声明部分定义的过程或者函数。之所以把这种模块叫做局部的，是因为这种模块只在父PL/SQL块中定义。它不能被它所在的包围块之外的块中调用。

17.6 模块重载

如果同属一个作用范围内的多个程序使用的都是一个名字，我们就说这些程序就是重载的。

17.6.2 重载的限制

• 重载程序的参数中至少要有一个是来自于不同的数据类型“家庭”。
• 如果重载函数只是参数列表中的参数名字不同，调用时就必须使用命名表示法。
• 不能仅靠参数列表中参数的模式来区别重载程序
• 所有重载程序必须在相同的PL/SQL作用范围内或者同一个块（匿名块，独立的过程或者函数，包）中定义
• 重载函数不能仅靠返回值的数据类型（函数的RETURN子句中指定的数据类型）区分

17.6.3 关于数字类型的重载

从Oracle数据库10g开始，如果两个子程序的区别仅在于形参是不同的数字类型，我们也能够实现重载。

17.7 前置声明

PL/SQL要求我们必须先声明一个元素，然后才能在代码中使用这个元素。前置声明只是由程序头部以及一个跟在后面的分号组成。这种结构也叫做模块头。这个模块头，必须要带着参数列表，这入包括了PL/SQL要声明它以及解析对它的引用所需要的全部信息了。

• 不能对变量或者游标进行前置声明，这个技术只对模块有效
• 使用前置声明的程序的定义，必须位于前置声明所在的PL/SQL代码块的声明部分

17.8 高级主题

17.8.1 在SQL中调用我们的函数

每当SQL运行时引擎调用一个PL/SQL函数时，它必须要“切换”到PL/SQL运行时引擎，如果调用函数的次数太多，这个上下文切换带来开销也不可忽视。

在SQL中调用函数的要求

• 函数的所有参数必须全部都是IN模式的。
• 函数的参数的数据类型，以及函数返回值的数据类型，必须是Oracle数据库可以识别的。
• 函数必须是保存在数据库中。

缺少时，用户自定义的函数在SQL中执行时，操作的是一行数据，而不是跨行的整个一列，就是SUM，MIN和AVG这些分组函数那样。当然也可以编写能够在SQL中调用的聚合函数，不过这需要使用OCIAggregate接口，这属于Oracle可扩展框架中的一部分。

在SQL中使用用户自定义函数的限制

• 函数不可以修改数据库表。不过如果我们在函数定义使用的是自治事务，这个限制可以忽略;一旦使用了自治事务，我们函数内部所做的任何改动都独立于SQL语句执行所在的外部事务。
• 如果在远程调用或者在一个并行操作中调用，函数不能读或者写包变量。
• 只有当函数是在一个选择列表或者VALUES或者SET子句中调用时，函数才能更新包变量的值。如果函数是在WHERE或者GROUP BY子句中调用的，它就不能修改包变量的值。
• 在Oracle8数据库以前，我们不能在一个用户自定义函数中调用RAISEAPPLICATIONERROR。
• 函数不能调用违反之前这些规则的其他模块。
• 函数也不能使用一个违反之前这些规则的视图。
• 在Oracle11g之前，我们只能通过位置表示法为函数的形参传递实参。

用户自定义函数和读一致性

Oracle数据库的读一致性简单明了：一旦我们开始了一个查询，查询所看到的就是在查询开始那一点数据库中的数据状态。但要在查询中使用自定义函数时就极可能违反数据库的读一致模型。 主要是在函数中再执行查找数据库的程序。这时我们需要使用下面的命令来在当前事务的SQL语句音强制保持读一致性。

SET TRANSACTION READ ONLY

 

17.8.2 表函数

表函数是一个可以在查询语句的FROM子句中调用的函数，看起来就像一个关系表一样。表函数所返回的数据类型是集合，而后者又可以调用TABLE操作符转换成可以在SQL语句中被查询的结构。

以下情况可以使用表函数：

• 要执行的数据变形操作非常复杂，得靠PL/SQL才能完成，可是又需要在SQL语句中访问这些变形后的数据。
• 要给宿主环境返回复杂的结构集。

有两类表函数值得特别注意：

• 流式表函数：数据流的意思是，当我们进行数据处理时，可以不用借助于任何中间结构就可以把数据从一个处理过程传递到下一个处理过程。
• 管理化的表函数：在函数一边还在执行着，一边就开始返回数据了。通过管理函数的头部加上PARALLEL_ENABLE，这个函数就可以在并行查询中并行执行了。

在FROM子句中调用函数

• 函数定义中的RETURN的数据类型必须是一个集合类型
• 确保函数的所有参数都是是IN模式的，而且这些参数使用的都是SQL能够识别的数据类型
• 要把这个函数调用嵌到TABLE操作符中

创建一个数据流函数

数据流函数接收一个参数形式的结果集，然后返回一个集合形式的结果集。

创建一个管理函数

管道函数也是一个返回集合形式结果集的表函数，不过这个返回动作和函数的结束不是同步进行的。换句话说，数据库不必等待函数完全结束，并把所有的结果行都保存到一个PL/SQL集合中才能开始返回数据。

PIPE ROW 语句（这个语句只对管道函数有效）把这个对象立即通过函数的“管道”传递出去。

17.8.3 确定性函数

如果一个函数对于相同的IN和IN OUT模式参数值，都返回相同的结果，就说这个函数是确定性函数。在函数头部加上DETERMINISTIC子句，那么这个函数就可以缓存其结果。

Oracle自己没有可靠的检查机制去保证你所声明的确定性函数的确是不会有任何意外情况的。这一点要由要使用这个我的你自己去负责。你的确定函数不应该依赖于包变量，也不应该以可能对结果集有影响的方式访问数据库。