【MySQL 数据库】9、存储过程

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 【MySQL 数据库】9、存储过程


一、存储过程是什么

🌱 存储过程是事先经过编译并存储在数据库中的 SQL 语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,可以提高数据处理效率

🌱 存储过程思想上很简单:就是数据库 SQL 语言层面的代码封装与重用

🍃 【封装,复用】可以把某一业务的 SQL 封装在存储过程中,需要用到的时候直接调用存储过程

🍃 可以接收参数,也可以返回数据

🍃 【减少网络交互,效率提升】如果涉及到多条 SQL,每执行一次都是一次网络传输。 而如果封装在存储过程中,只需要网络交互一次就可以了

二、存储过程的基本语法

# 创建存储过程
CREATE PROCEDURE p ( ) BEGIN
SELECT
  count( * ) '学生数量'
FROM
  student;
END;
# 执行存储过程
CALL p();
# 删除存储过程
DROP PROCEDURE IF EXISTS p;

三、MySQL 中的变量

MySQL 中的变量分为三种: 系统变量、用户定义变量、局部变量

(1) 系统变量

系统变量是 MySQL 服务器提供的,属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION

# 查看系统变量
show variables;
show session variables;
show global variables;
show global variables like 'auto%';
select @@autocommit;
select @@global.autocommit;
select @@session.autocommit;

set session autocommit = 0;
set @@session.autocommit = 1;

(2) 用户自定义变量

  • 用户定义变量:是用户自己定义的变量,用户变量不用提前声明
  • 赋值的时候直接用@变量名 就可以。
  • 其作用域为当前连接

赋值方式1:

set @my_name = '张国庆';
set @my_age = 3;
set @my_gender = 'boy', @my_hobby = 'sleep';
# 查看变量
select @my_name, @my_age, @my_gender, @my_hobby;

赋值方式2:

select @money := 16685206840;
select @money '张国庆的银行卡余额';

赋值方式3:

select count(*) into @student_num from student;
select @student_num '学生数量';

注意: 用户定义的变量无需对其进行声明或初始化,只不过获取到的值为NULL。

(3) 局部变量

🌱局部变量是用户自定义的在局部生效的变量

🌱访问之前,需要 DECLARE 声明

🌱 可用作存储过程内的局部变量和输入参数

🌱 局部变量的范围在声明的 BEGIN ... END 块内

# 创建存储过程
create procedure p_test1 () 
begin
declare stu_num int default 0;
select count(*) into stu_num from student;
select stu_num '学生人数';
end;
# 调用存储过程
call p_test1();

四、if 判断

根据定义的分数 score 变量,判定当前分数对应的分数等级:

🌼 score >= 85分,等级为优秀

🌼 score >= 60分 且 score < 85分,等级为及格

🌼 score < 60分,等级为不及格

create procedure p100() 
begin
declare score int default 66;
declare result char(3);
if score > 85 then
  set result := '优秀';
elseif score > 60 then
  set result := '及格';
else 
  set result := '不及格';
end if;
select result '分数等级';
end;
# 调用
call p100();

五、参数传递和返回值


根据传入参数 score,判定当前分数对应的分数等级,并返回

🌱 score >= 85分,等级为优秀

🌱 score >= 60分 且 score < 85分,等级为及格

🌱 score < 60分,等级为不及格

create procedure p101(in score int, out result char(3)) 
begin
if score > 85 then
  set result := '优秀';
elseif score > 60 then
  set result := '及格';
else 
  set result := '不及格';
end if;
end;
# 调用
call p101(58, @result);
# 查看返回值
select @result 'result';

🍃 将传入的 200 分制的分数换算成百分制,然后返回

create procedure p102(inout score double) 
begin
  # set score = score * 0.5;
  set score = score >> 1;
end;
# 调用
set @param_result = 78;
call p102(@param_result);
# 查看返回值
select @param_result 'score';

六、case 语句

根据传入的月份,判定月份所属的季节(要求采用 case 结构)。

🌴 1-3月份,为第一季度

🌴 4-6月份,为第二季度

🌴 7-9月份,为第三季度

🌴 10-12月份,为第四季度

create procedure p103(in `month` int) 
begin
  declare result char(4);
  
  case
    when month between 1 and 3 then set result := '第一季度';
    when month <= 4 and month >= 6 then set result := '第二季度';
    when month between 7 and 9 then set result := '第三季度';
    when month between 10 and 12 then set result := '第四季度';
    else set result = '非法参数';
  end case;
  
  select concat(`month`, '月份是', result) 'result';
end;
# 调用 
call p103(09);

七、while 循环

🌼 计算从1累加到 n 的值,n 为传入的参数值

create procedure p104(in n int) 
begin
  declare sum int default 0;
  
  while n > 0 do
    set sum := sum + n;
    set n := n - 1;
  end while;
  
  select sum;
end;
# 调用 
call p104(100);

八、repeat 循环

🌼 计算从1累加到 n 的值,n 为传入的参数值

create procedure p105(in n int) 
begin
  declare sum int default 0; 
  
  repeat
    set sum := sum + n;
    set n = n -1;
  until n <= 0
  end repeat;
  
  select sum;
end;
# 调用 
call p105(10);

九、loop 循环

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

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

☀️ LEAVE :配合循环使用,退出循环。

☀️ ITERATE:必须用在循环中,作用是跳过当前循环剩下的语句,直接进入下一次循环。

🌼 计算从1累加到 n 的值,n 为传入的参数值

create procedure p106(in n int) 
begin
  declare sum int default 0; 
  
  flag:loop
    if n <= 0 then
       leave flag;
    end if;
    
    set sum := sum + n;
    set n := n - 1;
  end loop flag;
  
  select sum;
end;
# 调用 
call p106(10);

十、游标

  • 游标(CURSOR)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理
  • 游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE

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

select * from tb_age_name_pro;
create procedure p_cursor(in uage int) 
begin
  # 局部变量声明必须在游标声明之前
  declare uname varchar(100);
  declare uprofession varchar(100); 
  
  # 定义游标(用于存储结果集)
  declare age_name_pro_cursor cursor for select `name`, profession from tb_user where age <= uage;
  
  # 创建表
  drop table if exists tb_age_name_pro;
  create table if not exists tb_age_name_pro (
    id int primary key auto_increment, 
    uname varchar(100),
    uprofession varchar(100)
  ); 
  
  # 游标操作
  open age_name_pro_cursor; # 打开游标
  
  # 循环获取游标记录
  while true do # 死循环
    fetch age_name_pro_cursor into uname, uprofession;
    # 把获取到的数据插入到表中
    insert into tb_age_name_pro values (null, uname, uprofession); 
  end while;
  
  # 关闭游标
  close age_name_pro_cursor; 
end;
call p_cursor(18);

十一、条件处理程序

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

create procedure p_cursor(in uage int) 
begin
  # 局部变量声明必须在游标声明之前
  declare uname varchar(100);
  declare uprofession varchar(100); 
  declare uuage varchar(3);
  
  # 定义游标(用于存储结果集)
  declare age_name_pro_cursor cursor for select age, `name`, profession from tb_user where age <= uage;
  
  # 创建条件处理程序
  # (1) 当 SQL 状态码是 02000 的时候触发该条件处理程序, 触发该程序后:① 关闭游标;② 终止当前程序
  # declare exit handler for sqlstate '02000' close age_name_pro_cursor;
  declare exit handler for not found close age_name_pro_cursor;
  
  # 创建表
  drop table if exists tb_age_name_pro;
  create table if not exists tb_age_name_pro (
    id int primary key auto_increment, 
    uuage varchar(3),
    uname varchar(100),
    uprofession varchar(100)
  ); 
  
  
  # 游标操作
  open age_name_pro_cursor; # 打开游标
  
  # 循环获取游标记录
  while true do
    fetch age_name_pro_cursor into uuage, uname, uprofession;
    # 把获取到的数据插入到表中
    insert into tb_age_name_pro values (null, uuage, uname, uprofession);
  end while;
  
  # 关闭游标
  close age_name_pro_cursor; 
end;
# 调用存储过程
call p_cursor(22);

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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
13天前
|
存储 关系型数据库 MySQL
MySQL基础入门:数据库操作全攻略
MySQL基础入门:数据库操作全攻略
44 0
|
13天前
|
关系型数据库 MySQL 数据库
卸载云服务器上的 MySQL 数据库
卸载云服务器上的 MySQL 数据库
30 0
|
1天前
|
关系型数据库 MySQL 分布式数据库
《MySQL 简易速速上手小册》第6章:MySQL 复制和分布式数据库(2024 最新版)
《MySQL 简易速速上手小册》第6章:MySQL 复制和分布式数据库(2024 最新版)
22 2
|
3天前
|
SQL 关系型数据库 MySQL
MySQL环境搭建——“MySQL数据库”
MySQL环境搭建——“MySQL数据库”
|
3天前
|
SQL NoSQL 关系型数据库
初识MySQL数据库——“MySQL数据库”
初识MySQL数据库——“MySQL数据库”
|
6天前
|
关系型数据库 MySQL 数据库
数据库基础(mysql)
数据库基础(mysql)
|
6天前
|
SQL 关系型数据库 数据库
【后端面经】【数据库与MySQL】SQL优化:如何发现SQL中的问题?
【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划,关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列,以及区分度高的列。大数据表改结构需谨慎,可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例,如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量,可利用上一批的最大ID进行限制。
32 3
|
7天前
|
存储 关系型数据库 MySQL
【后端面经】【数据库与MySQL】为什么MySQL用B+树而不用B树?-02
【4月更文挑战第11天】数据库索引使用规则:`AND`用`OR`不用,正用反不用,范围中断。索引带来空间和内存代价,包括额外磁盘空间、内存占用和数据修改时的维护成本。面试中可能涉及B+树、聚簇索引、覆盖索引等知识点。MySQL采用B+树,因其利于范围查询和内存效率。数据库不使用索引可能因`!=`、`LIKE`、字段区分度低、特殊表达式或全表扫描更快。索引与NULL值处理在不同数据库中有差异,MySQL允许NULL在索引中的使用。
11 3
|
8天前
|
关系型数据库 MySQL 数据库连接
Django(四):Django项目部署数据库及服务器配置详解(MySQL)
Django(四):Django项目部署数据库及服务器配置详解(MySQL)
30 11
|
11天前
|
存储 SQL 关系型数据库
mysql存储过程示例
mysql存储过程示例
11 0