Oracle存储过程和自定义函数详解

Oracle存储过程和自定义函数是数据库中存储的PL/SQL程序块，用于完成特定功能。存储过程无返回值，而函数必须返回一个值。它们可提高代码复用性，便于不同应用程序调用。

深入理解Oracle存储过程与自定义函数：功能、区别与实战应用

Oracle数据库中的存储过程和自定义函数是PL/SQL编程中的重要组成部分，它们用于封装一段可重用的代码，实现特定业务逻辑的处理，存储过程和自定义函数可以显著提高应用程序的性能、安全性和可维护性，本文将详细介绍Oracle存储过程和自定义函数的概念、优缺点、基本语法以及实战应用。

存储过程

1、概念

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集合，这些语句被编译并存储在数据库中，可以通过指定的名称被应用程序调用，存储过程可以接受输入参数，也可以返回输出参数，但不支持返回值。

2、优点

（1）提高性能：存储过程在数据库端编译后执行，减少了SQL语句在网络中的传输，降低了网络负载，提高了执行效率。

（2）增强安全性：通过存储过程，可以限制用户对数据库的访问权限，只允许执行指定的操作。

（3）易于维护：存储过程将业务逻辑封装在数据库端，便于管理和维护。

3、基本语法

创建存储过程的基本语法如下：

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter)]
IS
[local_declarations]
BEGIN
  executable_statements;
[EXCEPTION
  exception_handlers;]
END procedure_name;

- procedure_name：存储过程的名称。

- parameters：存储过程的参数列表，包括输入参数（IN）、输出参数（OUT）和输入输出参数（IN OUT）。

- local_declarations：局部变量声明，可选。

- executable_statements：要执行的PL/SQL语句。

- exception_handlers：异常处理，可选。

4、实战应用

下面是一个简单的存储过程示例，用于给指定员工增加工资：

CREATE OR REPLACE PROCEDURE add_salary(
  staff_name IN xgjtest.username%TYPE,
  increase_amount IN NUMBER
) AS
  old_salary xgjtest.sal%TYPE;
BEGIN
  -- 查询员工当前薪水
  SELECT sal INTO old_salary FROM xgjtest WHERE username = staff_name;
  
  -- 更新员工薪水
  UPDATE xgjtest SET sal = sal + increase_amount WHERE username = staff_name;
  
  -- 输出增加前后的薪水
  DBMS_OUTPUT.PUT_LINE('员工：' || staff_name || '，原薪水：' || old_salary || '，增加后薪水：' || (old_salary + increase_amount));
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('该员工不存在！');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('发生未知错误！');
END add_salary;

调用存储过程：

BEGIN
  add_salary('张三', 1000);
END;
/

自定义函数

1、概念

自定义函数（User-Defined Function）与存储过程类似，也是数据库中存储的已命名的PL/SQL程序块，与存储过程不同的是，函数必须有一个返回值，可以通过RETURN语句指定函数的返回类型。

2、优点

（1）提高代码复用性：函数可以被多次调用，减少了代码冗余。

（2）简化业务逻辑：函数可以将复杂的业务逻辑封装起来，使应用程序更加简洁。

（3）提高性能：与存储过程类似，减少了SQL语句在网络中的传输。

3、基本语法

创建自定义函数的基本语法如下：

CREATE [OR REPLACE] FUNCTION function_name
[(parameters)]
RETURN return_data_type
IS
[local_declarations]
BEGIN
  executable_statements;
[EXCEPTION
  exception_handlers;]
END function_name;

- function_name：函数名称。

- parameters：函数的参数列表，可选。

- return_data_type：函数返回值的数据类型。

- local_declarations：局部变量声明，可选。

- executable_statements：要执行的PL/SQL语句。

- exception_handlers：异常处理，可选。

4、实战应用

下面是一个简单的自定义函数示例，用于根据员工编号获取员工薪水：

CREATE OR REPLACE FUNCTION get_salary(
  eno IN emp.empno%TYPE
)
RETURN emp.sal%TYPE
IS
  vsal emp.sal%TYPE;
BEGIN
  SELECT sal INTO vsal FROM emp WHERE empno = eno;
  RETURN vsal;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20012, '该雇员不存在');
END get_salary;

调用自定义函数：

DECLARE
  vsal NUMBER;
BEGIN
  vsal := get_salary(7788);
  DBMS_OUTPUT.PUT_LINE('员工编号：7788，薪水：' || vsal);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('发生错误！');
END;
/

存储过程与自定义函数的区别

1、是否有返回值

存储过程没有返回值，通过输出参数（OUT或IN OUT）返回结果；自定义函数有返回值，通过RETURN语句返回。

2、调用方式

存储过程可以通过EXECUTE或BEGIN ... END; /方式调用；自定义函数不能单独作为语句调用，必须作为表达式的一部分。

3、使用场景

存储过程通常用于执行一系列的操作，如插入、更新、删除等；自定义函数通常用于计算和返回一个值。

Oracle存储过程和自定义函数是数据库编程中非常重要的组成部分，掌握它们的使用方法和实战技巧，可以有效地提高数据库应用的开发效率和维护性，在实际项目中，应根据业务需求和场景选择合适的存储过程或自定义函数，实现高效、安全的数据处理。