MySQL中的存储过程详解(下篇)

使用语言 MySQL

使用工具 Navicat Premium 16

代码能力快速提升小方法，看完代码自己敲一遍，十分有用

• 拖动表名到查询文件中就可以直接把名字拉进来
• 中括号，就代表可写可不写 

目录

1. 查看存储过程 

1.1 查看存储过程的状态 

1.1.1 基础语法

1.1.2 字段模糊查询连接方法

1.2 查看存储过程的创建代码 

1.2.1 基础语法

1.2.2 示例 

2. 修改存储过程 

2.1 基本语法

2.2 示例 

3. 删除存储过程 

3.1 基本语法

4. 存储过程中的控制语句 

4.1 条件语句

4.1.1 if-elseif-else条件语句 

4.1.2 if-elseif-else条件语句示例

4.1.3 case条件语句 

4.1.4 以上if示例使用case写法

4.1.5 case语句示例 

4.2 循环语句 

4.2.1 while循环语句 

4.2.2 while循环示例 

---

 

1. 查看存储过程 

MySQL提供了查询存储过程的方式。

1.1 查看存储过程的状态 

1.1.1 基础语法

类似查看数据库中的数据表信息，MySQL用户也可以查看数据库中已创建的存储过程。语法格式如下:

• show procedure status；

查看hospital数据库中创建的存储过程代码如下:

• show procedure status where DB='hospilal'；

除了通过指定数据库名查询存储过程，还可以通过like关键字匹配存储过程名称。例如:

• show procedure status like ;%patient%;

1.1.2 字段模糊查询连接方法

• 字段名 like concat('%',变量名/字段名,'%')

示例 

运行结果

 

1.2 查看存储过程的创建代码 

1.2.1 基础语法

除了查询存储过程的状态，还可以查询存储过程的创建代码。语法格式如下: 

• show create procedure 存储过程名; 

意思就是展示创建的存储过程 

1.2.2 示例 

 

运行结果 

 

2. 修改存储过程 

2.1 基本语法

在MySQL中，使用alter procedure语句可以修改创建存储过程时定义的特性。语法格式如下:

• alter procedure 存储过程名 [特性.....];

2.2 示例 

修改存储过程pro_show的SQL security特性为invoker。关键代码如下。

• alter procedure pro_show SQL security invoker； 

3. 删除存储过程 

3.1 基本语法

类似删除数据库中的数据表，MySQL用户可以使用drop procedure语句删除已创建的存储过程。语法如下:

• drop procedure [if exists] 存储过程名; 

用法与删除数据表相同。 

注:在创建存储过程前，可以使用if exists语句检查存储过程是否存在。如果不存在，再进行创建。 

先drop再create;

4. 存储过程中的控制语句 

在实际应用中，要解决复杂的问题，往往涉及复杂的流程控制。类似于Java语言中的流程控制语句，MySQL提供的控制语句包括条件语句、循环语句和迭代语句。

4.1 条件语句

MySQL提供两种条件语句，分别时if-elseif-else条件语句和case条件语句。 

4.1.1 if-elseif-else条件语句 

• if-elseif-else条件语句时最常用的一种条件语句，语法如下:

if 条件 then 语句列表
[elseif条件then语句列表]
[else语句列表]
end if; 

4.1.2 if-elseif-else条件语句示例

运行结果 

 

• 条件一般从大写到小 

4.1.3 case条件语句 

在MySQL中的case条件语句有两种写法。 

• 两种写法 

case
 when 条件 then 语句列表
 [when 条件 then 语句列表]
 [else 语句列表]
end case;

• 在case语句中，若条件为真，则相应的SQL语句列表将被执行。若没有条件匹配，则在else子句里的语句列表被执行。另外，case语句值返回一个符合条件的值，剩下的部分将会被自动忽略。

case 列名
 when 条件 then 语句列表
 [when 条件 then 语句列表]
 [else 语句列表]
end case; 

• case语句的两种写法可以实现相同的功能。在某种情况下(如做等值判断)，使用第二种写法更加简洁，但是因为case后面有列名，功能上有一些限制。因此，使用时要根据需求进行选择。 

4.1.4 以上if示例使用case写法

• 注意：在case语句中，else也是可以写的 

4.1.5 case语句示例 

• 在 MySQL 存储过程中，你可以使用 CASE 表达式来根据条件执行不同的逻辑。如果你想在存储过程中使用 CASE 表达式，并且希望将列名作为条件，你可以像在普通 SQL 查询中一样引用列名。以下是一个示例：
• 假设我们有一个名为 orders 的表，其中包含订单信息，包括订单状态列 status 和订单金额列 amount。我们想要创建一个存储过程，根据传入的订单状态参数返回相应状态的订单总金额。 

DELIMITER //

CREATE PROCEDURE getOrderTotalByStatus(IN orderStatus VARCHAR(50))
BEGIN
    DECLARE totalAmount DECIMAL(10, 2);
    
    SET totalAmount = (
        SELECT 
            SUM(CASE WHEN status = orderStatus THEN amount ELSE 0 END)
        FROM orders
    );
    
    SELECT totalAmount;
END //

DELIMITER ;

•  在这个存储过程中，我们首先定义了一个变量 totalAmount 来存储订单总金额。然后，我们使用一个 SELECT 语句来计算满足指定订单状态的订单总金额，并将结果存储到 totalAmount 变量中。在 CASE 表达式中，我们引用了列名 status 作为条件，并将其与传入的参数 orderStatus 进行比较。最后，我们通过另一个 SELECT 语句返回计算得到的订单总金额。
• 这就是一个使用 CASE 表达式带列名的示例，用于 MySQL 存储过程中。

4.2 循环语句 

MySQL语句提供多种循环语句，包括while循环语句、loop循环语句和repeat循环语句。 

这里主讲while循环;

4.2.1 while循环语句 

• while循环语句是最普遍使用的循环语句，它首先判断条件是否成立，若成立，则执行循环体。语法格式如下:

[label:] while 条件 do
  语句列表
end while [label] 

4.2.2 while循环示例 

运行结果