MySQL —— 存储过程

MySQL —— 存储过程 概念

MySQL5.0版本开始支持存储过程简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似java语言中的方法存储过程就是数据库SQL语言层面的代码封装与重用 特性

有输入输出参数，可以声明变量，有if/else、case、while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能函数的普遍特性：模块化、封装、代码复用速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤 入门案例

语法：

delimiter 自定义结束符号
create procedure 存储名([in,out,inout] 参数名 数据类型...)
begin
	sql语句
end 自定义的结束符号
delimiter;

实现：

delimiter $$ 
create procedure getStu()
begin 
	select sid,sname,age,address from student;
end $$
delimiter ;
#调用存储过程
call getStu();

变量定义

局部变量
用户自定义，在begin/end块中有效

#语法
声明变量 declare var_name type [default var_value];
#举例
declare nickname varchar(32);
#实现
delimiter $$
create procedure getStu()
begin
	declare var_name01 varhcar(20) default 'aaa'; --声明定义变量
	set var_name01 = '张三'; --给变量赋值
	select var_name01; --输出变量的值
end $$
delimiter ;
#调用存储过程
call getStu();

执行结果：
张三

扩展：
MySQL中还可以使用select…into语句为变量赋值，其基本语法如下：

delimiter $$
create procedure getStu()
begin
	declare var_name01 varhcar(20); --声明定义变量
	select sname into var_name01 from student where sid = 1; --给变量赋值
	select var_name01; --输出变量的值
end $$
delimiter ;
#调用存储过程
call getStu();

执行结果：
张三

用户变量
用户自定义，当前会话（连接）有效，类比java的成员变量

#语法
@var_name --不需要提前声明，使用即声明
#实现
delimiter $$
create procedure getStu()
begin
	set @var_name01 = '张三';
end $$
delimiter ;
#调用存储过程
call getStu();
#查询结果
select @var_name01;

执行结果：
张三

系统变量

系统变量又分为全局变量与会话变量全局变量在MySQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改会话变量在每次建立一个新的连接的时候，由MySQL来初始化，MySQL会将当前所有全局变量的值复制一份，来作为会话变量也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前会话（也就是当前的数据库连接）有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改

系统变量 —— 全局变量
由系统提供，在整个数据库有效

#语法
@@global.var_name
#实现
show global variables; --查看全局变量
select @@gloal.auto_increment_increment; --查看某全局变量
#修改全局变量的值
set global sort_buffer_size = 40000; 
set @@global.sort_buffer_size = 40000;

系统变量 —— 会话变量
由系统提供，当前会话（连接）有效

#语法
@@session.var_name
#实现
show session variables; --查看全局变量
select @@session.auto_increment_increment; --查看某全局变量
#修改全局变量的值
set session sort_buffer_size = 40000; 
set @@session.sort_buffer_size = 40000;

参数传递

in
in表示传入的参数，可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内

#封装有参数的存储过程，传入学生学号，查找学生信息
delimiter $$
create procedure getStu(in param_sid int)
begin
	select * from student where sid = param_sid;
end $$
delimiter ;
call getStu(1);

out
out表示从存储过程内部传值给调用者

#封装有参数的存储过程没传入学生学号，返回学生姓名
delimiter $$
create procedure getStu(int in_sid int,out out_sname varchar(20))
begin
	select sname into out_sname from student where sid = sid;
end $$
delimiter ;
call getStu(1,@o_sname);
select @o_sname;

inout
inout表示从外部传入的参数经过修改后可以返回的变量，即可以使用传入变量的值也可以修改变量的值（即使函数执行完）

#传入一个数字，传出这个数字的10倍值
delimiter $$
create procedure getNum(inout num int)
begin
	set num = num * 10;
end $$
delimiter ;
set @num = 10;
call getNum(@num);
select @num;

执行结果：
100

流程控制

if
if语句包含多个条件判断，根据结果为true、false执行语句，与java中的if、else if、else语法类似，其语法格式如下：

#语法
if search_condition_1 then statement_list_1
	[elseif search_condition_2 then statement_list_2]
	[else statement_list_n]
end if
#实现 输入学生的成绩，来判断学生的级别：
-- score<60：不及格;score>=60 score<90：及格;score>=90：优秀;
delimiter $$
create procedure getStart(in score int)
begin
	if score < 60 then select '不及格';
		elseif score >= 60 and score < 90 then select '及格';
		elseif score >= 90 then select '优秀';
		else select '成绩错误';
	end if
end $$
delimiter ;
call getStart(86)

执行结果：
及格
case
case是另一个条件判断的语句，类似于java中的switch语句

#语法一（类比java的switch）
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

循环
概述：

循环是一段在程序中只出现一次，但可能会连续运行多次的代码循环中的代码会运行特定的次数，或者是运行到特定条件成立时结束循环

循环分类：

whilerepeatloop

循环控制：

leave类似于break，跳出，结束当前所在的循环iterate类似于countinue，继续，结束本次循环，继续下一次

while

#语法
【标签】: while 循环条件 do
循环体;
end while【标签】;
#实现 向表中添加指定条数的数据
delimiter $$
create procedure addStu(in count int)
begin
	declare i int default 1;
	label:while i <= count do
		insert into student (sid,sname) values (i,concat('sname',i));
		set i = i + 1;
		if i = 8 then leave label;
			else if i = 2 then iterate label;
		end if;
	end while label;
end $$
delimiter ;
#执行存储过程
call addStu(10)

repeat

#语法
【标签】:repeat
	循环体;
	until 条件表达式
end repeat【标签】;

说明：repeat类似do…while，触发until后的条件表达式，循环结束
loop

#语法
【标签】:loop
	循环体;
	if 条件表达式 then leave 【标签】;
	end if
end loop【标签】;

游标

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

#声明语法
declare cursor_name cursor for select_statement;
#打开语法
open cursor_name;
#取值语法
fetch cursor_name into var_name[var_name]...;
#关闭语法
close cursor_name;

实现：

delimiter $$
create procedure getStu(in in_sid int)
	-- 定义局部变量
	declare var_sname varchar(20);
	--声明游标
	declare my_cursor cursor for select sname from student where sid = in_sid;
	--打开游标
	open my_cursor;
	--从游标中取值
	fetch my_cursor into var_sname;
	--打印信息
	select var_sname;
	--关闭游标
	close my_cursor;
end $$
delimiter ;
-- 执行存储过程
call getStu(1);

说明：可以理解为游标就是获取一个查询出的结果集的单条记录，一次获取一条，依次获取

异常处理

handler句柄
MySQL存储过程也提供了对异常处理的功能：通过定义handler来完成异常声明的实现

#语法
declare handler_action handler
	for condition_value[,condition_value]...
	statement

handler_action:{
	  continue --继续执行剩余代码
	| exit --直接终止程序
	| undo --不支持
}

condition_value:{
	  mysql_error_code --条件吗
	| condition_name --条件名
	| sqlwarning
	| not found
	| sqlexception
}