PL/SQL子程序设计详解：过程与函数的区别及实际应用

1. 子程序设计概述

子程序是PL/SQL程序中可独立执行的代码块，可以理解为一段具备特定功能的模块化代码。通过将一些功能独立出来并定义为子程序，程序员可以在需要时通过调用子程序的方式来执行这些代码。这样不仅提高了代码的重用性，还便于程序的调试和维护。

2. 子程序的类型

在PL/SQL中，子程序主要分为两种形式：过程（Procedure）和函数（Function）。两者的区别如下：

• 过程（Procedure）：不返回任何值。适用于需要执行某些操作但不需要返回结果的场景。
• 函数（Function）：必须返回一个值。适用于需要执行计算并返回计算结果的场景。

3. 过程的定义与结构

过程的定义包含以下部分：

• 参数声明部分：定义传递给过程的参数。
• 可执行部分：定义实际执行的逻辑。
• 异常处理部分：处理在过程执行过程中可能出现的异常。

PROCEDURE 过程名 (参数定义1，参数定义2...) IS
    变量声明部分
BEGIN
    可执行部分
EXCEPTION
    异常处理部分
END;

4. 参数传递模式

在PL/SQL子程序中，参数的传递模式有三种：IN、OUT和IN OUT。每种模式的作用和使用场景如下：

• IN模式：默认的传递模式，表示从调用者向子程序传递一个实际参数，子程序内部只能读取此参数，不能修改它。比如：

  procedure example_proc(p_in_param IN number) IS
  BEGIN
      dbms_output.put_line('传入的参数值为: ' || p_in_param);
  END;
  

  • 调用方式：example_proc(100); 此时传入的参数为100。

• OUT模式：表示子程序通过此参数向调用者返回一个值。调用时，传递给OUT参数的值没有实际意义，子程序会在执行完毕后将结果赋值给OUT参数。例如：

  procedure example_proc(p_out_param OUT number) IS
  BEGIN
      p_out_param := 200;
  END;
  

  • 调用方式：

    DECLARE
      v_result number;
    BEGIN
      example_proc(v_result);
      dbms_output.put_line('返回的参数值为: ' || v_result);
    END;
    

    调用后，v_result的值将变为200。

• IN OUT模式：表示参数既可以从调用者传递给子程序，也可以在子程序中修改后返回给调用者。例如：

  procedure example_proc(p_inout_param IN OUT number) IS
  BEGIN
      p_inout_param := p_inout_param + 10;
  END;
  

  • 调用方式：

    DECLARE
      v_value number := 100;
    BEGIN
      example_proc(v_value);
      dbms_output.put_line('处理后的参数值为: ' || v_value);
    END;
    

    调用后，v_value的值将变为110。

5. 过程的实际应用举例

使用子程序gongzjj_add和zongsrsj来实现工资和税款的处理：

• gongzjj_add过程：

  • 功能：为指定部门或所有部门的员工增加工资和奖金。
  • 参数：
    • salary：需要增加的工资，IN模式。
    • commiss：需要增加的奖金，IN模式。
    • d_no：部门编号，IN模式，默认值为0。

  procedure gongzjj_add (salary IN integer, commiss IN integer, d_no IN integer:=0) IS
  BEGIN
      if d_no=0 then 
          UPDATE emp set sal=sal+salary, comm=comm+commiss;
      else 
          UPDATE emp set sal=sal+salary, comm=comm+commiss WHERE deptno=d_no;
      END if;
      commit;
  END;
  

  • 调用方式：

    BEGIN
        gongzjj_add(200, 100);  -- 增加所有部门员工的工资和奖金
        gongzjj_add(300, 150, 10);  -- 增加部门编号为10的员工工资和奖金
    END;
    

• zongsrsj过程：

  • 功能：计算指定部门或所有部门的总收入和总税款。
  • 参数：
    • d_no：部门编号，IN模式，默认值为0。

  procedure zongsrsj(d_no IN integer:=0) IS
      total number;
      tax number;
  BEGIN
      if d_no=0 then 
          SELECT sum(sal+nvl(comm,0)), sum(sal*0.03) INTO total, tax FROM emp;
      else 
          SELECT sum(sal+nvl(comm,0)), sum(sal*0.03) INTO total, tax FROM emp WHERE deptno=d_no;
      END if;
      dbms_output.put_line('总收入: ' || total || '  总税款: ' || tax);
  END;
  

  • 调用方式：

    BEGIN
        zongsrsj();  -- 计算所有部门的总收入和总税款
        zongsrsj(10);  -- 计算部门编号为10的总收入和总税款
    END;
    

6. 参数默认值的使用

在定义过程时，可以为某些参数设置默认值，这样在调用过程中，如果没有为该参数提供实际值，则会自动使用默认值。比如gongzjj_add过程中的d_no参数默认为0，表示默认情况下对所有部门的员工执行操作。

BEGIN
    gongzjj_add(200, 0);  -- 增加所有部门的员工工资
    gongzjj_add(d_no=>10, salary=>300, commiss=>50);  -- 为部门10增加工资和奖金
END;

7. 过程的异常处理

在PL/SQL中，异常处理部分用于处理过程执行时可能发生的错误。异常处理是可选的，但推荐在实际使用中加以实现，以增强程序的健壮性。

PROCEDURE example_proc IS
BEGIN
    -- 执行部分
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('发生错误');
END;

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。

无论你是AI新手还是AI专家，学习最前沿的AI技术，AI创富俱乐部你值得拥有！