mysql 高级 | 存储过程
前言
我不生产知识,我只是知识的搬运工,以下内容是源于 B站 - MySQL数据库入门到精通 :
一、 介绍
1. 概述
存储过程时事先经过编译并存储在数据库中的一段 SQL 语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对应提高数据库处理的效率是有好处的。
存储过程的思想很简单,就是数据库 SQL 语言层面的代码封装与重用。
特点:
- 封装,复用
- 可以接受参数,也可以返回数据
- 通过存储过程可以减少应用服务器和数据库之间的网络交互,效率提升
存储函数也是事先经过编译并存储在数据库的一段 SQL,存储过程和存储函数的区别在于函数必须有返回值,而存储过程没有。
- 函数:是一个有返回值的过程;
- 过程:是一个没有返回值的函数;
二、常用语句
1. 创建存储过程
创建存储过程:
-- procedure_name 存储过程名称
-- proc_parameter[,...] 参数列表
CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
-- SQL语句
end ;
示例:
-- 创建存储过程 countStudent:查询学生总数
DELIMITER $
CREATE PROCEDURE countStudent()
BEGIN
SELECT COUNT(id) FROM student;
END$
DELIMITER ;
DELIMITE:
该关键字用来声明 SQL 语句的分隔符,告诉 MySQL 解释器,该段命令是否已经结束了,mysql 是否可以执行了。默认情况下,delimiter是分号 “;” 。在命令行客户端中,如果一行命令以分号结束,那么回车后,mysql将会执行该命令。
在命令行中定义存储函数的时候我们通常会将 SQL 语句的分隔符先定义成 ‘$’ ,或者 ’ $$ ’ 等 ,END 结束之后再将分隔符给换回来。
2. 调用存储过程
调用存储过程:
-- procedure_name 存储过程名称
-- proc_parameter[,...] 参数列表
call procedure_name([proc_parameter[,...]]);
例如:
-- 调用查询学生数量的存储过程
call countStudent();
3. 查看存储过程
成功执行存储过程之后,便会在 MySQL 中生成,一般连接 MySQL 的工具都会展示数据库中存在的存储过程,例如 :
或者可以通过以下 sql 进行查询存储过程相关的信息。
查询db_name数据库中的所有的存储过程:
-- 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';
示例:
select name from mysql.proc where db='tby';
查询存储过程的状态信息:
-- 查询存储过程的状态信息
SHOW PROCEDURE STATUS;
查询指定数据库的存储过程及状态信息:
-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'db_name'
示例:
-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'tby'
查询某个存储过程的定义:
-- 查询某个存储过程的定义
-- procedure_name 存储过程名称
show create procedure procedure_name;
示例:
show create procedure countStudent;
可以看到 Create Procedure
这个字段之中的内容如下:
CREATE DEFINER=`root`@`%` PROCEDURE `countStudent`()
begin
select count(id) from student;
end
ps:DEFINER=`root`@`%` 这个表示该存储过程是由 root 用户在主机为 % 下定义的,如果在创建存储过程的时候没有指定会默认加上
4. 删除存储过程
删除某个存储过程:
-- 删除某个存储过程
DROP PROCEDURE [IF EXISTS] procedure_name;
示例:
DROP PROCEDURE IF EXISTS countStudent;
删除之后,再执行查询存储过程的 sql
-- 查询数据库中指定存储过程
select name from mysql.proc where db='tby' and name = 'countStudent';
结果如下:
三、基本语法
存储过程是可以编程的,意味着可以使用变量,表达式,控制结构,来完成比较复杂的功能。
1. 变量
系统变量是 MySQL 服务器提供的,不是用户定义的,属于服务器层面,分为全局变量(GLOBAL)、会话变量(SESSION)。
- 查看系统变量(如果没有指定类型,默认为 SESSION)
查看所有的系统变量:
SHOW [SESSION|GLOBAL] VARIABLES;
可以通过 LIKE 模糊匹配方式查找变量:
SHOW [SESSION|GLOBAL] VARIABLES LIKE '...';
查看指定变量的值:
SELECT @@[SESSION|GLOBAL] 系统变量名;
- 设置系统变量
SET [SESSION|GLOBAL] 系统变量名;
SET @@[SESSION|GLOBAL] 系统变量名;
- DECLARE
通过 DECLARE
可以定义一个局部变量,该变量的作用范围只能在 BEGIN...END
块中。
语法:
DECLARE var_name[,...] type [DEFAULT value]
示例:
delimiter $
create procedure pro_declare()
begin
-- 定义一个变量 num 默认值为 5
declare num int default 5;
-- 返回 num 的值,并且在 num 前拼接字符串 'num的值为:'
select concat('num的值为:',num);
end$
delimiter ;
- SET
直接赋值使用 SET,可以赋常量或者赋表达式,具体语法如下:
SET var_name = expr [, var_name = expr]
示例:
DELIMITER $
CREATE PROCEDURE pro_set()
BEGIN
-- 定义一个 name 的变量
DECLARE name VARCHAR(20);
-- 对 name 变量进行赋值,为 'mike'
SET name = 'mike';
-- 返回 name
SELECT name;
END$
DELIMITER ;
也可以通过 select … into 方式进行赋值操作:
示例:
DELIMITER $
CREATE PROCEDURE pro_set_into()
BEGIN
-- 定义一个为 int 类型的变量 num
DECLARE num int;
-- 将查询到的学生总数赋值给 num
SELECT count(*) into num from student;
-- 返回学生总数
SELECT concat('学生数量共:',num);
END$
DELIMITER ;
2. 条件判断
语法结构:
if search_condition then statement_list
[elseif search_condition then statement_list] ...
[else statement_list]
end if;
示例:
根据定义的身高变量,判定当前身高的所属的身材类型
180 及以上 ----------> 身材高挑
170 - 180 ---------> 标准身材
170 以下 ---------> 一般身材
DELIMITER $
create procedure pro_if_else()
begin
declare height int default 175;
declare description varchar(50) default '';
if height >= 180 then
set description = '身材高挑';
elseif height >= 170 and height < 180 then
set description = '标准身材';
else
set description = '一般身材';
end if;
select concat('身高',height,'对应得身材类型为:',description) as description;
end$
DELIMITER ;
call pro_if_else();
调用结果如下显示:
3. 传递参数
语法格式:
-- procedure_name 存储过程名称
create procedure procedure_name([in/out/inout] 参数名 参数类型)
...
- IN:该参数可以作为输入,也就是需要调用方传入值,默认
- OUT:该参数作为输出,也就是该参数可以作为返回值
- INOUT:既可以作为输入参数,也可以作为输出参数
(1)IN-输入
需求:更据自定义身高变量,判定当前身高的所属的身材类型
DELIMITER $
create procedure pro_in(in height int)
begin
declare description varchar(50) default '';
if height >= 180 then
set description = '身材高挑';
elseif height >= 170 and height < 180 then
set description = '标准身材';
else
set description = '一般身材';
end if;
select concat('身高',height,'对应得身材类型为:',description) as description;
end$
DELIMITER ;
call pro_in(189);
调用结果如下显示:
(1)OUT-输出
需求:根据传入的身高变量,获取当前身高的所属的身材类型
DELIMITER $
create procedure pro_in_out(in height int, out description varchar(50))
begin
if height >= 180 then
set description = '身材高挑';
elseif height >= 170 and height < 180 then
set description = '标准身材';
else
set description = '一般身材';
end if;
select concat('身高',height,'对应得身材类型为:',description) as des;
end$
DELIMITER ;
call pro_in_out(169,@description);
调用结果如下显示:
PS:
@description
:这种变量要在变量名称前面加上”@”符号,叫做用户会话变量,代表整个会话过程他都是有作用的,这个类似于全局变量样。
@@global.sort_ buffer_size
:这种在变量前加上"@@"符号,叫做系统变量
查看:
select @description;
4. case 结构
语法结构:
方式一:
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE;
方式二:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE ;
需求:给定一个月份,然后计算出所在的季度
示例:
DELIMITER $
create procedure pro_case(in mon int)
begin
declare result varchar(10);
case
when mon>=1 and mon <=3 then
set result = '第一季度';
when mon>=4 and mon <=6 then
set result = '第二季度';
when mon>=7 and mon <=9 then
set result = '第三季度';
ELSE
set result = '第四季度';
end case;
select concat('传递的月份为:',mon,',计算出的结果为:',result) as content;
end$
DELIMITER ;
call pro_case(4);
调用结果如下显示:
5. while 循环
语法结构:
while search_condition do
statement_list
end while;
需求:计算从1加到n的值
示例:
DELIMITER $
create procedure pro_while(in n int)
begin
declare total int default 0;
declare num int default 1;
while num<=n do
set total = total + num;
set num = num + 1;
end while;
select total;
end$
DELIMITER ;
call pro_while(10);
调用结果如下显示:
6. repeat 结构
有条件的循环控制语句,当满足条件的时候退出循环。while 是满足条件才执行,repeat 是满足条件就退出循环(类似于 do … while 语句)。
语法结构:
REPEAT
statement_list
UNTIL search_condition
END REPEAT;
需求:计算从1加到n的值
DELIMITER $
create procedure pro_repeat(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$
DELIMITER ;
call pro_repeat(10);
调用结果如下显示:
7. loop 语句
LOOP 实现简单的循环,退出循环的条件需要使用其他的语句定义,通常可以使用 LEAVE
语句实现,具体语法如下:
[begin_labe1:] LOOP
statement_list
END LOOP [end_labe1]
如果不在 statement_list 中增加退出循环的语句,那么 LOOP 语句可以用来实现简单循环的效果。
8. leave 语句
用来从标注的流程构造中退出,通常和 BEGIN … END 或者循环一起使用。下面是一 个使用 LOOP
和 LEAVE
的简单例子,退出循环:
DELIMITER $
create procedure pro_leave_Loop(n int)
begin
declare total int default 0;
c:loop
set total = total + n;
set n = n-1;
if n <= 0 then
leave c;
end if;
end loop c;
select total;
end$
DELIMITER ;
call pro_leave_Loop(10);
调用结果如下显示:
9. 条件处理程序
条件处理程序(Handler):可以用来定义在流程控制结构过程中遇到问题时响应的处理步骤,具体语法为:
-- handler_action 条件处理程序的类型
-- condition_value 条件
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 代码的简写
关于 sqlstate_value
所对应的状态码可去 MySQL 的官方文档中去查看
MySQL 官方文档:https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
10. 游标 / 光标
游标(CURSOR)是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE,其语法分别如下。
声明游标:
-- cursor_name 游标名称
-- select_statement 查询语句
DECLARE cursor_name CURSOR FOR select_statement;
开启(OPEN)游标:
-- cursor_name 游标名称
OPEN cursor_name;
获取(FETCH)游标记录:
-- cursor_name 游标名称
FETCH cursor_name INTO var_name [,var_name] ...
关闭(CLOSE)游标:
-- cursor_name 游标名称
CLOSE cursor_name
示例:
查询学生数据,并将学生数据插入到所创建的一张新表(name,birth,sex,age,phone_number)中
DELIMITER $
create procedure pro_cursor(in n int)
begin
-- 先声明变量
declare s_name varchar(50);
declare s_birth varchar(50);
declare s_sex varchar(50);
declare s_age varchar(50);
-- 再声明游标
declare s_cursor cursor for select name,birth,sex,age from student where age <= n ;
-- 声明条件处理程序:当状态码为 '02000' 的时候退出,并且关闭掉游标
declare exit handler for SQLSTATE '02000' close s_cursor;
-- 或者:当状态码以 '02' 开头的时候退出,并且关闭掉游标
-- declare exit handler for not found close s_cursor;
-- drop table if exists tb_student_pro;
-- 如果表不存在则创建
create table if not exists tb_student_pro(
id int PRIMARY key auto_increment,
name varchar(50),
birth varchar(50),
sex tinyint(1),
age int(3)
);
-- 开启游标
open s_cursor;
while true do
-- 获取游标记录:一一对应到所声明的字段之中
fetch s_cursor into s_name,s_birth,s_sex,s_age;
-- 插入数据
insert into tb_student_pro values (null,s_name,s_birth,s_sex,s_age);
end while;
-- 关闭游标
close s_cursor;
end$
DELIMITER ;
call pro_cursor(20);
调用结果如下显示:
并且创建了 tb_student_pro
表,表中的数据如下: