MySQL中的存储过程（详解）

Java小鬼

于 2024-09-17 22:59:30 发布

阅读量404

点赞数 19

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2401_85419489/article/details/142304271

版权

前言：

在数据库开发中，MySQL 存储过程是一种非常强大的工具，可以提高数据库的性能、可维护性和安全性。本文将介绍 MySQL 存储过程的基本概念、语法和使用方法，并通过一些实际的例子来说明其在数据库开发中的应用。

首先要知道我们为什么要学这个存储过程，因为当我们的SQL语句较为麻烦，而且我们需要根据不同的条件去实现这个逻辑，其他的地方也需要多次使用这个逻辑，那我们在去重复的写就较为麻烦，代码较为冗余，存储过程就很好的解决了这个问题。

注：如果你学过其他语言可能会比较好理解

一、什么是MySQL存储过程？

MySQL存储过程是由一组预先编好的SQL语句，像方法一样可以被重复调用，存储过程可以接收参数，执行复杂的逻辑操作，可以返回结果，是多条sql语句的集合

二、存储过程的语法

MySQL语法如下：

CREATE PROCEDURE proc_name (parameter_list)
BEGIN
    --SQL语句
END;

其中CREATE PROCEDURE是创建存储过程的关键字，proc_name是我们为这个存储过程起的一个存储过程名称，parameter_list是一些参数，将所需要实现逻辑的SQL语句写在一对BEGIN和END里面。

如果你是使用客户端：nacivat等

但是SQL中的语法规定每写完一条SQL语句就要以分号结束，而MySQL默认的也是以分号结束，那就会起冲突，你在存储过程中写完SQL语句之后以分号结束，这个时候就会报错，因为我们要写在一对begin和end中，SQL语句已经结束了，那end就没有执行了。

语法如下：先将默认的结束符号修改为$$或者//

DELIMITER $$
CREATE PROCEDURE proc_name (parameter_list)
BEGIN
    --SQL语句
END$$
DELIMITER ;

如果你使用命令符操作就没有这个问题

三、存储过程的参数

存储过程的参数：

参数也是分类型的，根据不同的需求有不同的参数类型，有输入参数，有输出参数，还有输入输出参数。

输入参数（in）：用于向存储过程内传递参数，存储过程内可以使用该参数，但是存储过程内部不可以修改此参数值，调用

输出参数（out）：用于在存储过程中返回数据，在存储过程内部可以修改此参数，调用者可以接收该参数的值

输入输出参数（inout）：即可以向存储过程内部传递参数，也可以返回数据，在存储过程可以读取和修改该参数的值

四、调用存储过程

可以使用CALL语句来调用存储过程，

语法：CALL 过程名；

以下是一个存储过程调用的示例：

#例：创建一个存储过程
CREATE PROCEDURE come(in 参数名 数据类型,out 参数名 数据类型)
BEGIN
    --SQL语句
END;

#调用存储过程
CALL come(参数列表);

注意：如果存储过程是带有参数的，声明参数的时候要声明是什么类型的参数，参数的数据类型是什么，在调用此存储过程的时候就必须要传递对应的参数。

五、变量

变量分为系统变量和自定义变量

系统变量:顾名思义就是系统自己的变量

如何查询系统变量呢？

#查看全部系统变量
SHOW variables;
#查看指定关键字的变量
SHOW variables like '%XXXX%';
#查看具体某一个系统的值
SELECT @@系统变量名;

自定义变量：就是我们自己定义的变量，自定义变量又分为会话变量和局部变量。

会话变量：声明在连接中,存储过程外的变量叫做会话变量

声明会话变量：使用set关键字声明会话变量

声明
set @变量名 = 值;
set @变量名 := 值;

赋值
set @变量名 = 值;
set @变量名 := 值;

#也可以使用SELECT INTO语句进行赋值：

#示例
SELECT column_name INTO @variable_name FROM table_name WHERE condition;

注意:= 赋值运算符也是用于在存储过程或函数中设置变量的，但在 MySQL 的上下文中，它不是用于直接声明变量，而是用于在 SQL 语句中赋值

我们说在MySQL中”=“有两种用法，即可以做比较运算符，也可以做赋值运算符

而":="只有一种用法就是赋值，这两个都可以赋值，看你想要使用哪个

局部变量：定义在存储过程内部的变量

#声明
declare 变量名 数据类型;
declare 变量名 数据类型 default 值;

#赋值
set 变量名 = 值;
set 变量名 := 值;
select 变量名 := 值;
select 字段名 into 变量名 from 表名;

注意：声明变量的语句必须是存储过程体中的第一行语句. 声明变量的语句必须是存储过程体中的第一行语句.

六、存储过程示例

假设我们要创建一个存储过程，该存储过程接受一个 user_id 参数，并返回该用户的名字和年龄。

DELIMITER //

CREATE PROCEDURE GetUserInfo(IN user_id INT, OUT user_name VARCHAR(100), OUT user_age INT)
BEGIN
    SELECT name, age INTO user_name, user_age
    FROM users
    WHERE id = user_id;
END //

DELIMITER ;

说明：

DELIMITER // 和 DELIMITER ; 用于更改和恢复默认的语句分隔符，以允许存储过程中的多行语句。
IN user_id INT 是输入参数，用于传递 user_id。
OUT user_name VARCHAR(100) 和 OUT user_age INT 是输出参数，用于返回用户的信息。

SELECT ... INTO 用于将查询结果存储到输出参数中。

-- 声明用于接收输出的变量
SET @name = '';
SET @age = 0;

-- 调用存储过程
CALL GetUserInfo(1, @name, @age);

-- 查看输出结果
SELECT @name AS UserName, @age AS UserAge;

七、使用存储过程的好处：

提高性能：存储过程在数据库服务器上预先编译好，执行时不需要再次编译，因此可以提高执行效率。
可维护性：存储过程可以将复杂的业务逻辑封装在一个独立的单元中，便于维护和修改。
安全性：存储过程可以限制用户对数据库的直接访问，提高数据库的安全性。
代码复用：存储过程可以被多个应用程序调用，提高代码的复用性。

总结：

MySQL 存储过程是一种非常强大的数据库编程工具，可以提高数据库的性能、可维护性和安全性。本文介绍了 MySQL 存储过程的基本概念、语法和使用方法，并通过一些实际的例子来说明其在数据库开发中的应用。在实际应用中，可以根据具体的需求来选择是否使用存储过程，并合理地设计存储过程的结构和功能，以提高数据库的性能和可维护性。