MySQL存储过程及语法详解
这篇文章主要介绍了MySQL存储过程及语法详解，存储过程，也叫做存储程序，是一条或者多条SQL语句的集合，可以视为批量处理，但是其作用不仅仅局限于批量处理!
1. 存储过程基本用法
1.1 概念
存储过程，也叫做存储程序，是一条或者多条SQL语句的集合，可以视为批量处理，但是其作用不仅仅局限于批量处理。其中针对存储过程也有多种操作：如何创建存储过程，以及如何调用、查看、修改、删除存储过程。存储过程也可以调用其他存储过程。(类似于Java函数之间的相互调用)存储过程和函数是：事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据库和应用之间的传输，对于提高数据处理的效率是非常有好处的。
函数：是一个有返回值的过程；过程：是一个没有返回值的函数
存储过程和自定义函数的区别：
存储过程实现的功能要复杂一些；而函数的针对性更强。存储过程可以返回多个值；函数只能有一个返回值。存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分实现出来。
1.2 创建存储过程
1.2.1 语法格式
# 这个地方其实是用来声明SQL语句的结束符号的 delimiter // # 这个地方此时真正的用来创建一个存储过程的 create procedure 存储过程名称(参数列表) begin -- sql语句 end// # 当创建完一个存储过程之后再将分隔符替换为分号，为了不影响其他的操作 delimiter ;
2.2.2 语法介绍
delimiter: 用于设置sql语句分割符，默认为分号。因为在MySQL中每一条SQL语句都必须以 ；进行结束，当我们换行的时候就会执行这条SQL语句，但是我们此时的存储过程并没有结束，就会造成直接执行没有写完的存储过程造成报错，所以此时需要声明其他的结束符，不让其使用默认的分隔符结束SQL语句。sql语句：在这个部分编写sql语句，编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其他符号作为分割符，此时使用 // , 也可以使用其它字符。//: 声明结束符号，这个符号可以是任意的，是自定义的。相当于就是把 分号替换为 //
创建存储过程查询学生信息
# 将SQL语句的结束符号分隔符替换为// delimiter // create procedure proc_stu() begin select * from students; # 此时这个分号并不会结束这个语句,存储过程中的SQL语句还是用分隔符进行分隔 end // # 这里使用这个结束符号代表这个存储过程创建完成 delimiter; # 创建完一个存储过程之后将结束符号替换为分号，防止进行其他操作的时候有问题
1.3 调用存储过程
语法格式：
call proc_stu(); # 调用的时候需要加上括号，因为可能存在参数
1.4 查看存储过程
语法格式：
# 查询 studnet 数据库中的所有的存储过程 select name from mysql.proc where db='studnet'; # 查询存储过程的状态信息 show procedure status;
1.5 删除存储过程
语法格式：
drop procedure proc_stu; # 删除的时候不要加小括号，直接给定存储过程的名字即可。 drop procedure if exists proc_stu; # 如果存储删除，不存在不删除并且不会报错
2. 存储过程中的语法结构
存储过程是可以编程的，意味着可以使用变量、表达式、控制语句来完成比较复杂的功能。
2.1 变量的声明以及赋值
2.1.1 DECLARE 声明变量
DECLARE : 通过 DECLARE 关键字可以定义一个局部变量，该变量的作用范围只能在 BEGIN..,END 块中。
语法格式：
DECLARE 变量名[,...] type [DEFAULT value]
注意：声明变量的时候可以一次性声明多个，使用逗号隔开。
示例：
delimiter $ create procedure proc_stu() begin declare num int default 5; select num + 10; # 输出结果为15 end $ delimiter ;
2.1.2 SET 变量赋值
SET : 直接赋值使用SET关键字，可以赋常量或者是表达式，具体语法如下：
SET 变量名 = 变量值 [,变量名 = 变量值] ...
注意：一次可以给多个变量赋值，中间使用逗号隔开。
delimiter $ create procedure proc_stu() begin declare name varchar(20); # 可以指定变量类型以及变量的范围 set name = 'MySQL'; # 给变量直接赋值 select name; # 输出结果为：MySQL end $ delimiter ;
2.1.3 select...into 赋值
delimiter $ create procedure proc_student() begin declare count_num int(10); select count(*) into count_num from student; select count_num; end $ delimiter ;
2.2 条件判断
2.2.1 if条件判断
语法结构：
# 只有满足差选条件才会执行 then 后面的SQL语句 if search_condition(查询条件) then statement_list(SQL语句) [else if search_condition(查询条件) then statement_list(SQL语句)]... [else statement_list(SQL语句)] end if;
需求：
根据身高，判断当前身高所属的身材类型 180及以上 --------> 身材高挑 170 - 180 --------> 标准身材 170以下 ----------> 一般身材
实现这个简单的逻辑：
delimiter $ create procedure pro_figure() begin # 定义一个身高的变量 declare height int(11) default 175; # 定义一个存储身高类型的变量 declare figure varchar(50) default ''; # 使用 if 语句判断身材类型 if height >= 180 then set figure = '身材高挑'; else if height < 180 and height >= 170 then set figure = '标准身材'; else set figure = '一般身材'; end if; # 输出结果 select concat(height + '身高的身材为：' + figure); end $ delimiter ;
2.3 传递参数
语法格式：
delimiter $ # 我们可以不指定 [in/out/inout] , 默认为 in,输入参数 create procedure pro_name([in/out/inout]参数名 参数类型) begin -- sql语句 end $ delimiter ; # in : 该参数可以作为输入，调用该存储过程需要传入的值，默认 # out : 该参数作为输出，调用该存储过程之后返回的值。 # inout : 既可以作为输入参数也可以作为输出参数。
2.3.1IN - 输入参数
需求：根据输入的身高变量的值，判断当前身高对应的身材类型
实现：
delimiter $ # 此时调用者在调用这个存储过程的时候必须传递身高的变量值 create procedure pro_name(in height int(11)) begin # 定义一个存储身高类型的变量 declare figure varchar(50) default ''; # 使用 if 语句判断身材类型 if height >= 180 then set figure = '身材高挑'; else if height < 180 and height >= 170 then set figure = '标准身材'; else set figure = '一般身材'; end if; # 输出结果 select concat(height + '身高的身材为：' + figure); end $ delimiter ;
调用：
# 调用该存储过程。需要传递其中的身高值 call pro_name(175); # 输出结果为：
2.3.2 out - 输出参数
需求：根据输入的身高，返回当前身高所处的身材类型
实现：
delimiter $ create procedure pro_output(in height int(11) , out figure varchar(100)) begin # 使用 if 语句判断身材类型 if height >= 180 then set figure = '身材高挑'; else if height < 180 and height >= 170 then set figure = '标准身材'; else set figure = '一般身材'; end if; end $ delimiter ;
调用：
# @标识符：在MySQL中代表的就是用户定义的一个变量，这里我们使用这个变量来接收这个存储过程的返回值 call pro_output(175 , @figure)； # 查看存储过程返回的结果 select @figure;
@标识符的作用
@figure ：这种在变量名前面加上”@“符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。当前会话就是代表的，比如我们在命令提示窗口中给好多带有 @ 符号变量进行赋值，此时这些变量的值只作用于当前的会话，当我们把这个窗口关闭的时候，此时这些变量的值就会释放掉。@@global : 这种在变量名前加上 "@@" 符号，叫做系统变量。
2.4 case 结构
语法格式：
# 方式一 case case_value(判断的值) when when_value(比较的值) then statement_list(SQL语句) [when when_value(比较的值) then statement_list(SQL语句)]... [else statement_list(SQL语句)] end case; # 方式二 case when search_condition(查询条件) then statement_list(SQL语句) [when search_condition(查询条件) then statement_list(SQL语句)]... [else statement_list(SQL语句)] end case;
需求：给定一个月份，判断该月份所属的季度
实现：
delimiter $ create procedure pro_quarter(in mon int(11)) 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 result; end $ delimiter ;
2.5 while循环
有条件的循环控制语句，当满足条件的时候进入循环，不满足条件的时候退出循环。
语法结构：
# 只要查询条件一直成立就会一直指定do后面的SQL语句，当查询条件不成立的时候直接跳出while循环 while search_condition(查询条件) do statement_list(SQL语句) end while;
需求：计算从1加到n的值
实现：
delimiter $ create procedure pro_sum(in num int(11)) begin # 定义存储总数的变量 declare total int(255) default 0; # 定义存储循环次数的数量 declare number int(255) default 1; while number <= num do set total = total + number; set number = number + 1; end while; select total; end $ delimiter ;
2.6 repeat循环
有条件的循环控制语句，当不满足条件的时候进入循环，满足条件的时候跳出循环。他和while循环是反着的
语法结构：
repeat statement_list(SQL语句) until search_condition(查询添加) end repeat;
需求：计算从1加到n的值
实现：
delimiter $ create procedure pro_sum(in num int(11)) begin # 定义存储总数的变量 declare total int(255) default 0; repeat set total = total + number; set num = num - 1; # 注意：这个 unti 后的查询条件不要加分号，加分号会报错。 until num = 0 end repeat; select total; end $ delimiter ;
2.7 loop循环
loop实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用leave语句实现，具体语法如下：
语法格式：
[begin_label:] loop statement_list end loop [end_label]
如果不在statement_list中增加退出循环的语句，那么loop语句可以永安里实现简单的死循环。
2.8 leave语句
用来从标注的流程构造中退出，通常和 begin...end 或循环一起使用。下面是一个使用loop和leave的简单例子，退出循环：
需求：计算从1加到n的值 ---> 使用loop...leave的形式进行退出循环
实现：
delimiter $ create procedure pro_sum(in num int(11)) begin # 定义存储总数的变量 declare total int(255) default 0; c(该循环的别名):loop set total = total + num; set num = num - 1; # 借助leave组织退出条件 if num <= 0 then leave c; end if; end loop c; select total; end $ delimiter ;