Mysql 存储过程

什么是存储过程？

存储过程是事先经过编译并存储在数据库的一段sql语句的集合

如何创建一个存储过程？

create procedure 存储过程名称([参数列表])
begin
	sql语句;
end

#例
create procedure p1()
begin
	select * from t_goods;
	select * from t_user;
end

如果是在命令行中创建存储过程，会遇见分号结束的问题，需要用delimiter来修改sql语句结束的标志
例 “delimiter #” 就是将#作为sql语句结束的标志

怎么使用存储过程？

call 存储过程名称([参数列表])
# 例
call p1()

怎么查看已经创建的存储过程？

# 查询指定数据库的存储过程及状态信息
select * from information_schema.routines where routine_schema='数据库名称' 
# 例
select * from information_schema.routines where routine_schema='seckill'

# 查询某个存储过程的定义
show create procedure 存储过程名称
# 例
show create procedure p1

怎么删除一个存储过程？

drop procedure [if exists] 存储过程名称
# 例
drop procedure p1

变量

系统变量

系统变量是mysql服务器提供，分为全局变量(global)，会话变量(session)
如果没有指定session/global，默认为session
msyql重启后，设置的全局参数都会失效，要想不失效，可以在/etc/my.cnf中配置(linux)

查看系统变量

# 查看所有系统变量
show [session|global] variables
# 例
show global variables

# 通过like模糊匹配查找系统变量
show [session|global] variables like '...'
# 例
show global variables like 'admin%'


# 查看指定变量的值
select @@[session|global] 系统变量名
# 例 查autocommit的值
select @@autocommit
select @@session.autocommit

设置系统变量

set [session|global] 系统变量名=值
# 例
set session autocommit = 0

set @@[session|global] 系统变量名=值
# 例
set @@session.autocommit = 1

用户变量

用户变量是用户自己定义的变量，用户变量不用提前声明，在用的时候直接使用"@变量名"使用就可以，其作用域为当前连接

变量赋值

# 四种方式
set @变量名 = 值, @变量名 = 值 ...
# 例
set @stu_name = 'zdy', @stu_age = 18

set @变量名 := 值, @变量名 := 值 ...
# 例
set @stu_name := 'zzz', @stu_age := 20

select @变量名:= 值, @变量名 := 值 ...
# 例
select @stu_name := 'zdz', @stu_age := 16

select 字段名 from 表名 into @变量名
# 例 将cout(*)的值存入变量
select count(*) from t_user into @user_count

使用

select @变量名1, @变量名2....
# 例
select @stu_name, @stu_age

局部变量

局部成效，要通过declare成名之后才能使用，可作为存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin…end模块

声明局部变量 （变量类型就是数据库字段类型 int、char、varchar…）

declare 变量名 变量类型[default ...]

变量赋值

set 变量名 = 值
set 变量名 := 值
select 字段名 from 表名 into 变量名 ...

整体案例

create procedure p3()
begin
	# 定义变量
	declare good_count int default 0;
	# 变量复制
	select count(*) from t_goods into good_count;
	# 查询变量值
	select good_count;
end

存储过程中使用IF条件判断

create procedure p4()
begin
	declare score int default 67;
	declare result varchar(10);
	if score >= 80 then
		set result := '优秀';
	elseif score >= 60 then
		set result := '及格';
	else
		set result := '不及格';
	end if;
	select result;
end

call p4()

存储过程参数

参数有三种类型
IN                      该类参数作为输入，也就是需要调用时传入值，也是默认类型
OUT                  该类参数作为输出，也就是该参数可以作为返回值
INOUT              该类参数既可以作为输入参数，也可以作为输出参数

使用

create procedure 存储过程名称([IN/OUT/INOUT 数名 参数类型])
begin
	sql语句;
end

案例

create procedure p5(in goods_count int, out result varchar(10))
begin
	if goods_count >= 1 then
		set result := '有库存';
	else
		set result := '库存不足';
	end if;
end

call p5(2, @result)
select @result

存储过程中使用case

语法一

# 如果case_value等于v1则执行statement1语句等于v2则执行statement2语句...否则执行statementn语句
case case_value
	when v1 then statement1
	when v2 then statement2
	...
	else statementn
end case
# 例
create procedure p7(in month int, out result varchar(10))
begin
	case month
		when 1 then
			set result := '一月';
		when 2 then
			set result := '二月';
		when 3 then
			set result := '三月';
		else
			set result := '非法参数';
	end case;
end

call p7(2, @result)
select @result

语法二

# 如果condition1满足执行statement1，如果condition2满足执行statement2...否则执行statementn语句
case
	when condition1 then statement1
	when condition2 then statement2
	...
	else statementn
end case
# 例
create procedure p6(in month int, out result varchar(10))
begin
	case
		when month >=1 && month <=3 then
			set result := '第一季度';
		when month >=4 && month <=6 then
			set result := '第二季度';
		when month >=7 && month <=9 then
			set result := '第三季度';
		when month >=10 && month <=12 then
			set result := '第四季度';
		else
			set result := '非法参数';
	end case;
end

call p6(2, @result)
select @result

存储过程中使用while

语法(满足条件进入循环)

while 条件 do
	sql逻辑
end while
# 例 计算1-100的累加
create procedure p8(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 p8(100)

存储过程中使用repeat

语法(满足条件退出循环)

repeat
	sql逻辑
	until 条件
end repeat
# 例
create procedure p9(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 p9(100)

存储过程中使用loop

语法
如果不在sql逻辑中增加退出循环的条件，可以用来实现简单的死循环，loop可以配合下面的两个语句使用
leave 退出循环，相当于Java的break
iterate 跳过当前循环，相当于Java的continue

[label:] loop
	sql逻辑
end loop [label]
# 例1 1-n的累加
create procedure p10(in n int)
begin
	declare sum int default 0;
	sum:loop
		if n<=0 then
			leave sum;
		end if;
		
		set sum := sum + n;
		set n := n - 1; 
	end loop sum;
	select sum;
end
call p10(100)
# 例2 1-n的偶数的累加
create procedure p11(in n int)
begin
	declare sum int default 0;
	count:loop
	
		if n<=0 then
			leave count;
		end if;
		
		if n%2 = 1 then
		set n := n - 1; 
			iterate count;
		end if;
		
		set sum := sum + n;
		set n := n - 1; 
	end loop count;
	
	select sum;
end

call p11(100)

游标类型

游标类型用于存储查询结果集，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、open、fetch、close

声明游标

declare 游标名 cursor for 查询语句

打开游标

open 游标名称

获取游标记录

fetch 游标名称 into 变量1,变量2...

关闭游标

close 游标名称

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

create procedure p12(in uage int)
begin
	# 1. 声明游标存储结果集 声明时要先声明变量再声明游标
	declare uname varchar(100);
	declare upro varchar(100);
	declare u_cursor cursor for select name, peofession from tb_user where age <= uage;
	# 2. 准备：创建表结构
	create table if not exists tb_user_pro(
		id int primary key auto_increment,
		name varchar(100),
		profession varchar(100)
	);
	# 3. 开启游标
	open u_cursor;
	while true do # 这里埋了一个坑 条件处理程序解决
		# 4. 将游标中的数据插入到新表中
		fetch u_cursor into uname, upro;
		insert into tb_user_pro values (null, uname, upro);
	end while;
	# 5. 关闭游标
	close u_cursor;
end

call p12(40)

条件处理程序

条件处理程序可以用来定义在流程控制结构执行过程中遇到问题时相应的处理过程，语法如下

declare handler_action hanler for condition_value...statement
handler:
	continue: # 继续执行当前程序
	exit: # 终止执行当前程序
condition_value:
	sqlstate sqlstate_value: # 状态码 如 02000
	sqlwarning: # 所有以01开头的sqlstate代码的简写
	not found: # 所有以02开头的sqlstate代码的简写
	sqlexception: # 所有没有被sqlwarning或not found捕获的sqlstate代码的简写

案例

create procedure p12(in uage int)
begin
	# 1. 声明游标存储结果集 声明时要先声明变量再声明游标
	declare uname varchar(100);
	declare upro varchar(100);
	declare u_cursor cursor for select name, peofession from tb_user where age <= uage;
	
	# 声明条件处理程序
	declare exit handler for SQLSTATE '02000' close u_cursor;
	
	# 2. 准备：创建表结构
	create table if not exists tb_user_pro(
		id int primary key auto_increment,
		name varchar(100),
		profession varchar(100)
	);
	# 3. 开启游标
	open u_cursor;
	while true do
		# 4. 将游标中的数据插入到新表中
		fetch u_cursor into uname, upro;
		insert into tb_user_pro values (null, uname, upro);
	end while;
	# 5. 关闭游标
	close u_cursor;
end

call p12(40)

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是in类型，存储函数能实现的存储过程都能实现，语法如下

create function 存储函数名称([参数列表])
returns type [characteristic...]
begin
	sql语句;
	return ...;
end;

characteristic:
	deterministic # 相同的输入参数总是产生相同的结果
	no sql # 不包含sql语句
	reads sql data # 包含读取数据的语句，但不包含写入数据的语句

# 例 1-n的累加
create function fun1(n int)
returns int deterministic
begin
	declare sum int default 0;
	while n>0 do
		set sum := sum + n;
		set n := n - 1;
	end while;
	return sum;
end

select fun1(100)