MySQL----存储过程（二）-阿里云开发者社区

MySQL----存储过程（二）

2023-08-05 121

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

RDS MySQL Serverless 高可用系列，价值2615元额度，1个月

简介： MySQL----存储过程（二）

6. case

6.1 介绍

case结构及作用，和流程控制函数很类似。

6.2 语法

-- 含义： 
-- 当case_value的值为 when_value1时，执行statement_list1，
-- 当值为 when_value2时， 执行statement_list2， 
-- 否则就执行 statement_list 
CASE case_value 
  WHEN when_value1 THEN statement_list1 
  [ WHEN when_value2 THEN statement_list2] 
  ... 
  [ ELSE statement_list ] 
END CASE;

-- 含义： 
-- 当条件search_condition1成立时，执行statement_list1，
-- 当条件search_condition2成 立时，执行statement_list2， 
-- 否则就执行 statement_list 
CASE
  WHEN search_condition1 THEN statement_list1 
  [WHEN search_condition2 THEN statement_list2] 
  ... 
  [ELSE statement_list] 
END CASE;

6.3 案例

根据传入的月份，判定月份所属的季节（要求采用case结构）。

1-3月份，为第一季度

4-6月份，为第二季度

7-9月份，为第三季度

10-12月份，为第四季度

create procedure p6(in month int) 
begin
  declare result varchar(10); 
  case
    when month >= 1 and month <= 3 then 
      set result := '第一季度'; 
    when month >= 4 and month <= 6 then 
      set result := '第二季度'; 
    when month >= 7 and month <= 9 then 
      set result := '第三季度';
    when month >= 10 and month <= 12 then 
      set result := '第四季度'; 
    else
      set result := '非法参数'; 
  end case ; 
  select concat('您输入的月份为: ',month, ', 所属的季度为: ',result); 
end; 
call p6(16);

如果判定条件有多个，多个条件之间，可以使用 and 或 or 进行连接。

7. while

7.1 语法

while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑 
WHILE 条件 DO 
  SQL逻辑... 
END WHILE;

7.2 案例

计算从1累加到n的值，n为传入的参数值。

-- A. 定义局部变量, 记录累加之后的值; 
-- B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环 
create procedure p7(in n int) 
begin
  declare total int default 0; 
  while n>0 do
    set total := total + n; 
    set n := n - 1; 
  end while; 
  select total; 
end; 
call p7(100);

8. repeat

8.1 语法

repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环。

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。
-- 如果不满足，则继续下一次循环 
REPEAT
  SQL逻辑... 
  UNTIL 条件 
END REPEAT;

8.2 案例

计算从1累加到n的值，n为传入的参数值。(使用repeat实现)

-- A. 定义局部变量, 记录累加之后的值; 
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 
create procedure p8(in n int) 
begin
  declare total int default 0; 
  repeat
    set total := total + n; 
    set n := n - 1; 
    until n <= 0 
  end repeat; 
  select total;
end; 
call p8(10); 
call p8(100);

9. loop

9.1 语法

LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。

LOOP可以配合一下两个语句使用：

LEAVE ：配合循环使用，退出循环。
ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

[begin_label:] LOOP 
  SQL逻辑... 
END LOOP [end_label];

LEAVE label; -- 退出指定标记的循环体 
ITERATE label; -- 直接进入下一次循环

上述语法中出现的 begin_label，end_label，label 指的都是我们所自定义的标记。

9.2 案例

计算从1累加到n的值，n为传入的参数值。

-- A. 定义局部变量, 记录累加之后的值; 
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx 
create procedure p9(in n int) 
begin
  declare total int default 0; 
  sum:loop 
    if n<=0 then leave sum;
    end if; 
    set total := total + n; 
    set n := n - 1; 
  end loop sum; 
  select total; 
end; 
call p9(100);

计算从1到n之间的偶数累加的值，n为传入的参数值。

-- A. 定义局部变量, 记录累加之后的值; 
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx 
-- C. 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xx 
create procedure p10(in n int) 
begin
  declare total int default 0; 
  sum:loop 
    if n<=0 then leave sum; 
    end if; 
    if n%2 = 1 then 
    set n := n - 1; 
    iterate sum; 
    end if; 
    set total := total + n; 
    set n := n - 1; 
  end loop sum;
  select total; 
end; 
call p10(100);

10. 游标

10.1 介绍

游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

声明游标

DECLARE 游标名称 CURSOR FOR 查询语句 ;

打开游标

OPEN 游标名称 ;

获取游标记录

FETCH 游标名称 INTO 变量 [, 变量 ] ;

关闭游标

CLOSE 游标名称 ;

10.2 案例

根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。

-- 逻辑: 
-- A. 声明游标, 存储查询结果集 
-- B. 准备: 创建表结构 
-- C. 开启游标 
-- D. 获取游标中的记录 
-- E. 插入数据到新表中
-- F. 关闭游标 
create procedure p11(in uage int) 
begin
  declare uname varchar(100); 
  declare upro varchar(100); 
  declare u_cursor cursor 
    for select name,profession 
      from tb_user 
      where age <= uage;
  drop table if exists tb_user_pro; 
  create table 
  if not exists 
  tb_user_pro( 
    id int primary key auto_increment, 
    name varchar(100), 
    profession varchar(100) 
  );
  open u_cursor; 
  while true do 
    fetch u_cursor into uname,upro; 
    insert into tb_user_pro 
    values (null, uname, upro); 
  end while; 
  close u_cursor; 
end; 
call p11(30);

上述的存储过程，最终我们在调用的过程中，会报错，之所以报错是因为上面的while循环中，并没有退出条件。当游标的数据集获取完毕之后，再次获取数据，就会报错，从而终止了程序的执行。

要想解决这个问题，就需要通过MySQL中提供的条件处理程序 Handler 来解决。

11. 条件处理程序

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

11.1 语法

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ;
handler_action 的取值： 
  CONTINUE: 继续执行当前程序 
  EXIT: 终止执行当前程序 
condition_value 的取值： 
  SQLSTATE sqlstate_value: 状态码，如 02000 
  SQLWARNING: 所有以01开头的SQLSTATE代码的简写 
  NOT FOUND: 所有以02开头的SQLSTATE代码的简写 
  SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

11.2 案例

我们继续来完成在上一小节提出的这个需求，并解决其中的问题。

A. 通过SQLSTATE指定具体的状态码

-- 逻辑: 
-- A. 声明游标, 存储查询结果集 
-- B. 准备: 创建表结构 
-- C. 开启游标 
-- D. 获取游标中的记录 
-- E. 插入数据到新表中 
-- F. 关闭游标 
create procedure p11(in uage int) 
begin
  declare uname varchar(100); 
  declare upro varchar(100); 
  declare u_cursor cursor 
  for select name,profession 
    from tb_user 
    where age <= uage;
  -- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出 
  declare exit handler for SQLSTATE '02000' close u_cursor; 
  drop table if exists tb_user_pro; 
  create table 
  if not exists 
  tb_user_pro( 
    id int primary key auto_increment, 
    name varchar(100), 
    profession varchar(100) 
  );
  open u_cursor; 
  while true do 
    fetch u_cursor into uname,upro; 
    insert into tb_user_pro 
    values (null, uname, upro); 
  end while; 
  close u_cursor; 
end; 
call p11(30);

B. 通过SQLSTATE的代码简写方式 NOT FOUND

02 开头的状态码，代码简写为 NOT FOUND

create procedure p12(in uage int) 
begin
  declare uname varchar(100); 
  declare upro varchar(100); 
  declare u_cursor cursor 
  for select name,profession 
    from tb_user 
    where age <= uage;
  -- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02开头时，将关闭游标u_cursor，并退出 
  declare exit handler for not found close u_cursor; 
  drop table if exists tb_user_pro; 
  create table 
  if not exists 
  tb_user_pro( 
    id int primary key auto_increment,
    name varchar(100), 
    profession varchar(100) 
  );
  open u_cursor; 
  while true do 
    fetch u_cursor into uname,upro; 
    insert into tb_user_pro 
    values (null, uname, upro); 
  end while; 
  close u_cursor; 
end; 
call p12(30);

具体的错误状态码，可以参考官方文档：

https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html

https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html