存储过程：从创建到参数运用全攻略

发非人非

已于 2025-04-16 15:07:03 修改

阅读量584

点赞数 27

文章标签： mysql 数据库

于 2025-04-11 10:35:36 首次发布

本文链接：https://blog.csdn.net/2301_82115272/article/details/147137723

版权

一、存储过程简介

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，它存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程在数据库应用开发中具有重要作用，它可以提高数据库操作的效率、安全性以及代码的可维护性。

（一）存储过程的优点

提高性能：由于存储过程在数据库服务器上进行预编译和存储，执行时直接调用已编译的代码，减少了SQL语句的解析和编译时间，从而提高了执行效率。特别是对于复杂的查询和业务逻辑，性能提升更为明显。
增强安全性：通过将数据库操作封装在存储过程中，可以限制用户对底层表的直接访问，只授予用户执行存储过程的权限。这样可以更好地控制数据的访问和修改，防止非法的数据操作。
代码可维护性：存储过程将相关的SQL语句组合在一起，实现特定的业务功能。当业务逻辑发生变化时，只需修改存储过程的代码，而无需修改所有调用该存储过程的应用程序代码，大大提高了代码的可维护性和可扩展性。

（二）存储过程的使用场景

数据处理任务：例如批量数据的插入、更新、删除操作，复杂的数据计算和转换等。
业务逻辑封装：将企业的业务规则和流程封装在存储过程中，确保数据操作的一致性和准确性。
报表生成：通过存储过程执行复杂的查询，生成各种报表数据。

二、新建存储过程

在不同的数据库管理系统（如MySQL、SQL Server、Oracle等）中，新建存储过程的语法略有不同，但基本概念是相似的。下面以MySQL为例，介绍新建存储过程的一般步骤和语法。

（一）创建存储过程的语法结构

DELIMITER //
CREATE PROCEDURE procedure_name([IN|OUT|INOUT] parameter_name data_type[, ...])
BEGIN
    -- 存储过程的SQL语句
END //
DELIMITER ;

DELIMITER：用于临时更改语句结束符，因为在存储过程内部可能包含多条SQL语句，每条SQL语句通常以分号（;）结尾，而创建存储过程的语句本身也以分号结尾，为了避免混淆，我们临时将结束符改为其他符号（这里使用//），在存储过程定义结束后再改回分号。
CREATE PROCEDURE：创建存储过程的关键字。
procedure_name：存储过程的名称，命名应遵循数据库的命名规则，并且要具有描述性，以便于理解其功能。
parameter_name：参数名称，参数可以有多个，每个参数前面需要指定参数模式（IN、OUT或INOUT）和数据类型。
BEGIN和END：这两个关键字之间包含了存储过程的实际SQL语句逻辑。

（二）示例：创建一个简单的存储过程

假设我们有一个employees表，包含employee_id、first_name、last_name和salary字段，现在我们要创建一个存储过程，根据员工ID查询员工的姓名和薪水。

DELIMITER //
CREATE PROCEDURE get_employee_info(IN emp_id INT)
BEGIN
    SELECT first_name, last_name, salary
    FROM employees
    WHERE employee_id = emp_id;
END //
DELIMITER ;

在这个例子中，我们创建了一个名为get_employee_info的存储过程，它接受一个IN类型的参数emp_id，用于指定要查询的员工ID。在存储过程内部，执行了一条简单的SELECT语句来获取符合条件的员工信息。

三、存储过程的参数类型

存储过程的参数在数据传递和业务逻辑实现中起着关键作用。根据参数的用途和数据流向，主要分为以下三种类型：

（一）IN参数

定义和用途：IN参数用于将数据从调用者传递到存储过程内部。在存储过程执行期间，IN参数的值不能被修改（虽然在某些数据库系统中语法上允许修改，但修改后的值不会影响调用者传递进来的原始值）。IN参数通常用于传递查询条件、输入数据等。
示例：继续以上面的get_employee_info存储过程为例，emp_id参数就是IN类型。当我们调用这个存储过程时，通过传递不同的员工ID值，存储过程可以查询出相应员工的信息。

CALL get_employee_info(100);

这里将值100作为emp_id参数传递给存储过程，存储过程会查询employee_id为100的员工信息。

（二）OUT参数

定义和用途：OUT参数用于将存储过程内部产生的数据返回给调用者。在存储过程内部，可以对OUT参数进行赋值操作，当存储过程执行结束后，调用者可以获取到这个参数的值。OUT参数常用于返回计算结果、查询结果集中的某个值等。
示例：假设我们要创建一个存储过程，计算某个部门员工的平均薪水，并将结果返回给调用者。

DELIMITER //
CREATE PROCEDURE get_department_avg_salary(IN dept_id INT, OUT avg_sal DECIMAL(10, 2))
BEGIN
    SELECT AVG(salary) INTO avg_sal
    FROM employees
    WHERE department_id = dept_id;
END //
DELIMITER ;

在这个例子中，dept_id是IN参数，用于指定部门ID；avg_sal是OUT参数，用于存储计算得到的平均薪水。调用这个存储过程时，可以通过变量来接收返回的平均薪水值。

SET @average_salary = 0;
CALL get_department_avg_salary(10, @average_salary);
SELECT @average_salary;

首先声明一个变量@average_salary并初始化为0，然后调用存储过程，将部门ID10传递进去，存储过程计算出平均薪水后赋值给@average_salary变量，最后通过SELECT语句查看这个变量的值，即得到了部门的平均薪水。

（三）INOUT参数

定义和用途：INOUT参数兼具IN和OUT参数的特性。它既可以将数据从调用者传递到存储过程内部，在存储过程执行过程中又可以被修改，并且修改后的值会返回给调用者。INOUT参数常用于需要在存储过程中对输入数据进行处理并返回处理结果的场景。
示例：假设有一个存储过程，将输入的字符串反转后返回。

DELIMITER //
CREATE PROCEDURE reverse_string(INOUT input_str VARCHAR(255))
BEGIN
    SET input_str = REVERSE(input_str);
END //
DELIMITER ;

调用这个存储过程时：

SET @original_string = 'Hello, World!';
CALL reverse_string(@original_string);
SELECT @original_string;

首先声明一个变量@original_string并赋值为'Hello, World!'，然后调用存储过程，存储过程将这个字符串反转后再赋值回@original_string变量，最后通过SELECT语句可以看到@original_string的值已经变为'!dlroW ,olleH'。

四、总结

存储过程作为数据库开发中的重要工具，通过合理地创建和使用存储过程以及正确理解和运用参数类型，可以极大地提高数据库应用的性能、安全性和可维护性。在实际开发中，根据不同的业务需求，选择合适的参数类型来设计存储过程，能够更加灵活和高效地处理数据库操作。无论是进行数据处理、业务逻辑封装还是报表生成等任务，存储过程都能发挥其独特的优势，帮助开发人员构建出更加健壮和高效的数据库应用系统。希望通过本文的介绍，能让读者对存储过程有更深入的了解，并在实际工作中充分利用其强大功能。