oracle中 procedure(存储过程)和function(函数)本质区别

Oracle 中 Procedure 和 Function 本质区别

在 Oracle 数据库中，Procedure 和 Function 都是存储过程的一种。但是它们有各自独特的特点和用途，下面详细讲解二者本质上的区别。

Procedure

1. 定义

Procedure 是一种存储程序，它是一组 SQL 语句和控制结构。它没有返回值，只负责完成一些操作，包括增删改查等操作。它是一种应用程序，可以接收参数和变量来执行相应的命令。

下面是一个求和 Procedure 的示例：

CREATE OR REPLACE PROCEDURE cal_sum(a IN NUMBER, b IN NUMBER, c OUT NUMBER)
IS
BEGIN
  c := a + b; -- 计算 a+b 的和，并将结果赋值给 c
END;
/

上面的示例是一个传入两个数 a 和 b，然后执行相应的求和操作，结果放在 c 中的 Procedure。

2. Procedure 的特点

• Procedure 没有返回值，只具有完成一些特定操作的功能。
• Procedure 可以有多个 IN 或 OUT 参数，也可以没有参数。
• Procedure 可以调用其他 Procedure，还可以自动提交事务。
• Procedure 可以包含流程控制语句（如 IF、LOOP、WHILE）和异常处理语句（如 EXCEPTION）等。

Function

1. 定义

Function 是一种存储程序，它也是一组 SQL 语句和控制结构。与 Procedure 不同的是，Function 是有返回值的，可以返回单个值或多个值。一般情况下，Function 用于查询或处理数据，并返回一个具体的结果。

下面是一个根据学生的成绩返回等级的 Function 的示例：

CREATE OR REPLACE FUNCTION get_grade(score IN NUMBER) RETURN VARCHAR2
IS
  grade VARCHAR2(20);
BEGIN
  IF score BETWEEN 90 AND 100 THEN
    grade := 'A';
  ELSIF score BETWEEN 80 AND 89 THEN
    grade := 'B';
  ELSIF score BETWEEN 70 AND 79 THEN
    grade := 'C';
  ELSIF score BETWEEN 60 AND 69 THEN
    grade := 'D';
  ELSE
    grade := 'F';
  END IF;

  RETURN grade; -- 返回值为等级
END;
/

上面的示例是一个传入一个学生成绩，然后返回相应等级的 Function。

2. Function 的特点

• Function 具有返回值，返回单个值或多个值。
• Function 可以有零个或多个 IN 参数和一个 OUT 或 RETURN 参数。
• Function 不可以调用其他 Function，也不能进行自动提交事务。
• Function 中不能包含 DDL 语句（如 CREATE、DROP、ALTER）等。

二者区别

• Procedure 通常用于处理事务和 DML 操作，没有返回值，一般情况下需要使用 OUT 参数返回结果。
• Function 通常用于查询或处理数据，有返回值，可以组合成表达式进行使用。
• Procedure 不能包含 RETURN 语句返回结果，而 Function 必须包含 RETURN 语句返回结果。
• Procedure 和 Function 可以共享相同的变量，但是 Procedure 中对变量的改变不会影响 Function 的操作，Function 中对变量的改变也不会影响 Procedure 的操作。

示例说明

• 示例一：使用 Procedure 实现向库存表增加商品数量的操作

CREATE OR REPLACE PROCEDURE add_product_qty(p_id IN NUMBER, p_qty IN NUMBER)
IS
BEGIN
  UPDATE stock
  SET qty = qty + p_qty
  WHERE product_id = p_id;
END;
/

对于上述示例来说，采用 Procedure 的方式可以有效地实现对库存表的增加数量操作，不需要返回其它结果，且需要对同一库存表多次操作时可以复用该 Procedure。

• 示例二：使用 Function 实现聚合运算

CREATE OR REPLACE FUNCTION get_dept_avg_salary(dept_name IN VARCHAR2)
RETURN NUMBER
IS
  dept_avg_salary NUMBER;
BEGIN
  SELECT AVG(salary) INTO dept_avg_salary FROM employees WHERE department_name = dept_name;

  RETURN dept_avg_salary;
END;
/

上述示例使用 Function 的方式实现了根据部门名称进行平均薪资的统计，并返回该部门的平均薪资值。采用该函数的方式可以实现简单的聚合运算，且可以方便地进行组合使用，例如将该 Function 与查找最高薪资的 Function 进行组合，实现各种复杂的查询操作。