MySQL：存储过程（Stored Procedure）

ZachOn1y

于 2024-08-04 13:53:21 发布

阅读量1.1k

点赞数 34

分类专栏： MySQL 文章标签： mysql 数据库数据库系统后端 oracle

本文链接：https://blog.csdn.net/Zachyy/article/details/140902266

版权

MySQL 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

存储过程

存储过程（Stored Procedure）是一组预编译过的 SQL 语句集合，是在数据库中定义和存储的可重复使用的程序，可以被多次调用并在需要时进行执行。存储过程可以接受参数、执行条件分支逻辑和循环逻辑、返回结果等，并可以在应用程序中被调用执行。存储过程用于处理数据库中的复杂业务逻辑和数据操作需求。（可理解为函数）

MySQL 从 5.0 版本开始支持存储过程

存储过程的参数

MySQL存储过程的参数（变量）有三种类型：

IN：输入参数，表示调用者向过程传入值（传入值可以是字面量或变量）。
OUT：输出参数，表示过程向调用者传出值（可以返回多个值）（传出值只能是变量）。
INOUT：输入输出参数，既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）。

操作与管理存储过程

创建存储过程

在 MySQL 中，使用 CREATE PROCEDURE 语句创建存储过程：

CREATE PROCEDURE procedure_name (
	[IN | OUT | INOUT] parameter_name parameter_type 
	[, ...]
)
BEGIN 
    -- 存储过程逻辑
END

procedure_name：存储过程名称。
IN、OUT 或 INOUT：输入、输出或输入输出参数的类型。
parameter_name：输入、输出或输入输出参数名。
parameter_type：输入、输出或输入输出参数类型。
存储过程逻辑：存储过程执行的 SQL 语句和控制流程语句。

示例，将输入参数加倍并将其赋值给输出参数：

CREATE PROCEDURE(
	IN input_num INT,
	OUT output_num INT
)
BEGIN
	SET output_num = input_num * 2;
END;

查看存储过程

可以使用 SHOW PROCEDURE STATUS 和 SHOW CREATE PROCEDURE 语句来查看存储过程的基本信息和创建语句。

调用存储过程

使用 CALL 语句可以从应用程序中调用存储过程，并传递参数。

CALL procedure_name(var1,[@user]var2,,...);

删除存储过程

使用 DROP PROCEDURE 语句可以删除已存在的存储过程。

DROP PROCEDURE [IF EXISTS] my_procedure;

修改存储过程

在 SQL 中，可以使用 ALTER PROCEDURE 语句可以修改已存在的存储过程。

但在 MySQL 中，没有直接的 ALTER PROCEDURE 语句来修改存储过程。但是，可以采用以下两种方法来达到修改的目的：

重新创建存储过程：可以先删除旧的存储过程，然后创建一个新的存储过程，新存储过程的定义包含了所需的更改。这种方法的优点是简单明了，缺点是在删除旧存储过程之前，需要确保没有其他对象（如触发器或存储函数）依赖于它。

-- 删除旧的存储过程
DROP PROCEDURE IF EXISTS old_procedure;

-- 创建新的存储过程
DELIMITER //
CREATE PROCEDURE new_procedure()
BEGIN
  -- 新的存储过程逻辑
  SELECT * FROM some_table;
END //
DELIMITER ;

使用 REPLACE 选项：MySQL 8.0.16 及更高版本引入了一个 REPLACE 选项，允许在创建存储过程时替换已存在的同名存储过程。这样可以避免显式地删除和重新创建存储过程。

DELIMITER //
CREATE OR REPLACE PROCEDURE my_procedure()
BEGIN
  -- 新的存储过程逻辑
  SELECT * FROM some_table;
END //
DELIMITER ;

注意事项

权限要求：只有具有 ALTER ROUTINE 权限的用户才能修改存储过程。
版本兼容性：CREATE OR REPLACE 语法在 MySQL 8.0.16及以上版本 可用。如果使用的是更早版本的MySQL，需要使用 DROP PROCEDURE 和 CREATE PROCEDURE 的组合来实现修改。
备份：在修改存储过程之前，最好备份原始的存储过程定义，以防万一需要恢复。

实例

以下是一个简单的存储过程创建和调用的实例：

-- 创建存储过程  
DELIMITER //  
CREATE PROCEDURE GetEmployeeName(
	IN emp_id INT, 
	OUT emp_name VARCHAR(100)
	) 
BEGIN      
	SELECT name INTO emp_name FROM employees WHERE id = emp_id;  END //  
DELIMITER ;    

-- 调用存储过程  
SET @emp_id = 1;  
SET @emp_name = '';  
CALL GetEmployeeName(@emp_id, @emp_name);  
SELECT @emp_name;