跳到主要内容

子程序

PL/SQL子程序是可以重复调用的PL/SQL命名块。如果子程序有参数，每次调用的参数值可以不同。

子程序一般指过程和函数。通常，使用过程执行操作，使用函数计算并返回值。

使用子程序的原因​

子程序支持开发和维护可靠的、可重用的代码。具有以下特点：

  • 模块化：子程序允许将程序分解为可管理的、定义良好的模块。
  • 更简单的应用程序设计：在设计应用程序时，可以推迟子程序的实现细节，直到测试了主程序，然后一步一步地细化它们。
  • 易维护：您可以更改子程序的实现细节，而无需更改其调用程序。
  • 用包进行管理：子程序可以分组到包中，关于这部分请参考包（PACKAGE）。
  • 可复用：在许多不同的环境中应用程序可以使用相同的包子程序或独立子程序。
  • 更优的性能：每个子程序都以可执行的形式编译和存储，可以重复调用。因为存储的子程序是在数据库服务器上运行的，所以通过网络进行一次调用就可以启动一个大型作业。这种分工减少了网络流量，缩短了响应时间。存储的子程序被缓存并在用户之间共享，这降低了内存需求和调用开销。

子程序调用​

子程序调用的形式如下：

call subprogram_name [ ( [ parameter [, parameter]... ] ) ]

如果子程序没有参数，或者为每个参数指定了默认值，则可以省略参数列表或指定空参数列表。

子程序属性​

每个子程序属性只能在子程序声明中出现一次。属性可以以任何顺序出现。属性出现在子程序标题中的IS或AS关键字之前。

子程序部分​

子程序以子程序标题开始，该标题指定其名称和（可选）参数列表。子程序有以下几个部分：

  • 声明部分（可选）：本部分声明并定义局部类型、游标、常量、变量、异常。
  • 可执行部分（必需）：此部分包含一个或多个用于赋值、控制执行和操作数据的语句。
  • 异常处理部分（可选）：此部分包含处理运行时错误的代码。

★函数的附加部分

函数的结构与过程相同，并且：

  • 函数标题必须包含RETURN子句，该子句指定函数返回的值的数据类型（过程标题不能有RETURN子句）
  • 在函数的可执行部分，每个执行路径都必须指向一个RETURN语句

例如：

CREATE FUNCTION p_6_4_1 (original NUMBER)
RETURN NUMBER
AS
  original_squared NUMBER;
BEGIN
  original_squared := original * original;
  RETURN original_squared;
END;
/

调用代码及结果如下：

SELECT p_6_4_1 (100) FROM dual;

结果:
10000.0000000000

★RETURN语句

RETURN语句立即结束包含它的子程序的执行。子程序可以包含多个返回语句。

  • 函数中的RETURN语句：在函数中，每个执行路径必须指向一个RETURN语句，每个RETURN语句必须指定一个表达式。RETURN语句将表达式的值分配给函数标识符，并将控制权返回给调用程序，调用后立即恢复执行。

例如：

CREATE FUNCTION f_6_4_2 (n INTEGER)
RETURN INTEGER
IS
BEGIN
  IF n = 0 THEN
    RETURN 1;
  ELSIF n = 1 THEN
    RETURN n;
  ELSE
    RETURN n*n;
  END IF;
END;
/

调用代码及结果如下：

SELECT f_6_4_2 (0), f_6_4_2 (1), f_6_4_2 (2) FROM dual;

结果：
(expression) (expression) (expression)
          1          1          4
1 row(s) retrieved.
  • 过程中的RETURN语句：在过程中，RETURN语句将控制权返回给调用程序，调用后立即恢复执行。过程中的RETURN语句不能指定表达式。

子程序参数​

★形参和实参

如果希望子程序具有参数，请在子程序标题中声明形参。在每个形参声明中，指定参数的名称和数据类型，以及（可选）其模式和默认值。在子程序的执行部分，通过名称引用形参。

例如，过程raise_salary包含2个形参emp_id和amount：

CREATE PROCEDURE p_6_5_1 (
  emp_id NUMBER,
  amount NUMBER
) IS
BEGIN
  UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id;
END p_6_5_1;

在调用子程序时，指定要将其值赋给形参的实参。相应的实参和形参必须具有兼容的数据类型。

例如，emp_num、bonus、merit为实参：

……
  emp_num NUMBER(6) := 120;
  bonus NUMBER(6) := 100;
  merit NUMBER(4) := 50;
BEGIN
  ……
  p_6_5_1(emp_num, bonus);
  /* 执行的是如下语句:
  UPDATE employees SET salary = salary + 100 WHERE employee_id = 120; */
  p_6_5_1(emp_num, merit + bonus);
  /* 执行的是如下语句:
  UPDATE employees SET salary = salary + 150 WHERE employee_id = 120; */
  ……
END;

★子程序参数传递方法

有两种将实参传递给子程序的方法：

  • 传址：传递一个指向实参的指针，实参和形参引用相同的内存位置。
  • 传值：将实参的值赋给相应的形参，实参和形参指的是不同的内存位置。

★子程序参数模式

形参的模式决定了它的行为。

  • IN：缺省模式，将值传递给子程序
  • OUT：必须指定，向调用程序返回一个值
  • IN OUT：必须指定，将初始值传递给子程序，并将更新后的值返回给调用程序

不管OUT或IN-OUT参数是如何传递的：

  • 如果子程序成功退出，那么实参的值就是赋给形参的最终值
  • 如果子程序以异常结束，则实参的值未定义

子程序调用解析​

子程序调用时，它首先在当前作用域中搜索匹配的子程序声明，然后根据需要在连续的封闭作用域中搜索。

如果它们的子程序名和参数列表匹配，则声明和调用匹配。如果声明中每个必需的形参在调用中都有相应的实参，则参数列表匹配。

如果编译器没有找到与调用匹配的声明，那么它将生成一个语义错误。

递归子程序​

递归子程序调用自身。递归是一种强大的简化算法的技术。

递归子程序至少有两条执行路径，一条指向递归调用，另一条指向终止条件。如果没有后者，递归将继续，直到PL/SQL耗尽内存并引发预定义的异常存储错误。

子程序的每次递归调用都会创建子程序声明的每个项及其执行的每个SQL语句的实例。

例如，下面函数实现阶乘：

CREATE FUNCTION f_6_7 (n INTEGER) RETURN INTEGER
IS
BEGIN
  IF n = 1 THEN
    RETURN n;
  ELSE
    RETURN n * factorial(n-1);
  END IF;
END;

调用代码及结果如下：

CREATE OR REPLACE PROCEDURE p_6_7 AS
BEGIN
    FOR i IN 1..5 LOOP
      DBMS_OUTPUT.PUT_LINE(i || '! = ' || f_6_7(i));
    END LOOP;
END ;
/
call p_6_7();

Result:
1! =1
2! =2
3! =6
4! =24
5! =120

子程序副作用​

如果子程序改变了它自己的局部变量的值以外的任何东西，它就会产生副作用。例如，更改以下任何一项的子程序都有副作用：

  • 自身的输出或输入输出参数
  • 全局变量
  • 包中的公共变量
  • 数据库表
  • 数据库
  • 外部状态（例如，通过调用DBMS输出）

副作用可能会阻止查询的并行化，产生依赖于顺序的（因此是不确定的）结果，或者要求跨用户会话维护包状态。

SQL语句可以调用的PL/SQL函数​

为了能在SQL中被调用，存储的函数（及其调用的子程序）必须遵守以下规则，这些规则旨在控制函数副作用：

  • 当从SELECT语句或是并发的INSERT、UPDATE、DELETE语句中调用函数时，它不能修改任何数据表。
  • 当从INSERT、UPDATE、DELETE或MERGE语句中调用函数时，它不能查询或修改这些语句所能影响到的数据表。
  • 当从SELECT，INSERT，UPDATE，DELETE或MERGE语句中调用时，子程序无法执行以下任何SQL语句：事务控制语句（例如COMMIT）、系统控制语句（例如ALTER SYSTEM）、自动提交的数据库定义语言（DDL）语句（例如CREATE）。

如果函数执行部分中的任何SQL语句违反了规则，则在解析该语句时会出现运行错误。

函数的副作用越少，则可以在SELECT语句中对其进行更好得优化。