备案控制台
开发者社区 数据库 文章 正文

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

2023-08-05 126
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
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可以配合一下两个语句使用：

  1. LEAVE ：配合循环使用，退出循环。
  2. 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 案例

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

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

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

文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
MySQL
存储
关键词：
云数据库 RDS MySQL 版存储过程
存储过程mysql
mysql存储过程
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
萤火虫的小尾巴
目录
相关文章
半夜敲代码的夜猫子
|
7天前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇（视图/存储过程/触发器）
MySQL数据库进阶第四篇（视图/存储过程/触发器）
半夜敲代码的夜猫子
11 1
红目香薰
|
10天前
|
存储 关系型数据库 MySQL
MySQL周内训参照5、存储过程创建
MySQL周内训参照5、存储过程创建
红目香薰
27 1
sumith
|
1天前
|
存储 SQL 关系型数据库
在MySQL中使用存储过程返回更新前的记录
在MySQL中使用存储过程返回更新前的记录
sumith
3 0
宇宙小超人
|
4天前
|
存储 关系型数据库 MySQL
索引的威力--记一次MySQL存储过程优化
在MySQL存储过程中，一个`INSERT INTO SELECT`语句起初执行超过130秒，优化后，执行时间降低到1秒内，实现了100倍的性能提升。问题在于`NOT IN`子查询导致的慢查询，最终通过创建单列索引获得了最佳效果。文章还介绍了索引创建的基本语法，并讨论了单列索引与组合索引的优缺点。作者强调，随着数据量增加，索引对于查询性能的重要性，计划未来采用读写分离来进一步优化处理大量插入和查询的场景。
宇宙小超人
54 0
红目香薰
|
10天前
|
存储 SQL 关系型数据库
MySQL存储过程_触发器_游标——Baidu Comate
MySQL存储过程_触发器_游标——Baidu Comate
红目香薰
12 0
红目香薰
|
10天前
|
存储 SQL 关系型数据库
MySQL存储过程——if判断示例
MySQL存储过程——if判断示例
红目香薰
9 0
红目香薰
|
10天前
|
SQL 存储 关系型数据库
MySQL存储过程——Baidu Comate智能代码助手添加20条DML语句——测试索引效果
MySQL存储过程——Baidu Comate智能代码助手添加20条DML语句——测试索引效果
红目香薰
9 0
以山向海
|
1天前
|
存储 关系型数据库 MySQL
关系型数据库MySQL的MyISAM
【6月更文挑战第17天】
以山向海
18 11
以山向海
|
2天前
|
存储 关系型数据库 MySQL
关系型数据库mysql文件系统支持文件结构
【6月更文挑战第16天】
以山向海
16 9
以山向海
|
2天前
|
SQL 关系型数据库 MySQL
关系型数据库mysql文件系统支持跨平台兼容性
【6月更文挑战第16天】
以山向海
15 8

热门文章

最新文章

  • 1
    02-PostgreSQL 存储过程的进阶介绍（含游标、错误处理、自定义函数、事务）
  • 2
    一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法（上）
  • 3
    一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法（下）
  • 4
    01-PostgreSQL 存储过程的基本介绍以及入门（基本结构、声明和赋值、控制结构）（下）
  • 5
    DataWorks产品使用合集之在DataWorks中调用存储过程（PROCEDURE）如何解决
  • 6
    01-PostgreSQL 存储过程的基本介绍以及入门（基本结构、声明和赋值、控制结构）（上）
  • 7
    Vue+Django+MySQL搭建指南（个人全栈快速开发）
  • 8
    MySQL无损复制
  • 9
    MySQL 8.0 Access denied for user 'root'@'localhost' (using password: YES) 问题解决
  • 10
    mysql总结8----游标的学习
  • 1
    视图、触发器和存储过程：提升数据库功能
    40
  • 2
    sql serve存储过程
    23
  • 3
    好多人都说存储过程很难？认真看这篇文章就够了
    34
  • 4
    sqlserver存储过程
    29
  • 5
    sqlserver中常用的几个存储过程
    69
  • 6
    Oracle系列十五：存储过程
    36
  • 7
    MSSQL存储过程的功能和用法
    55
  • 8
    Msql第四天,存储过程和函数
    50
  • 9
    【MySQL 数据库】9、存储过程
    239
  • 10
    物料清单应用输入模板的SQL存储过程设计
    29

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 极致弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • 如何通过RDS MySQL数据库内网访问
  • RDS MySQL Serverless资源与负载自适应

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）