Sql知识总结-MySql存储过程

一、存储过程概述

存储过程其实完全可以类比成高级语音里的方法和函数。这样看起来就不会抽象，也有个类比。通常函数体都会定义入参、出参，函数内部会定义该函数的计算过程，这里可能会有加减乘除、循环遍历、等等操作。procedure自然也是如此，因此高级语言中的函数里需要的那些东西，这里也是需要的，比如在函数里定义一个局部变量，在函数内引用静态、成员变量，procedure里也有这些操作。再比如函数里可以写for循环、while循环、异常抓取，procedure里也是可以的。procedure拥有了这些，才让存储过程可以解决一些普通sql处理不了的问题。

二、存储过程的参数

上面说过procedure也是支持入参出参的吗，先来看下procedure的标准写法吧：

delimiter $$
create procedure sp_name([proc_parameter[,...]])
[characteristic...]  -- 这块目前没啥用，一般不会使用
begin 
...
end $$
delimiter ;
1
2
3
4
5
6
7

对上面的写法略作解读：

1. delimiter 是为了更改procedure的结束符的，我们正常的结束符都是分号，它和这里的procedure结束符重复了，procedure里可能会有很多分号，程序读到分号以为procedure结束了，就会不正常结束，所以这里需要更改成其他的符号，$$ 是常用的符号
2. procedure支持无参，入参可以使用in来声明，格式是：in 参数名 参数类型，出参可以使用out来声明：out 出参名 出参类型，同时也可以使用intou一次解决出参入参：inout 参数名 参数类型
3. procedure的写法必须是begin、end来进行书写的。
   下面来看下最简单的使用in来传入一个参数的procedure。

1.in

下面是使用in的一个简单场景：

-- 删除存储过程
drop procedure if exists in_procudure;
-- 创建存储过程
delimiter $$
create procedure if not exists in_procudure(in param integer)
begin 
select param;
end $$
delimiter ;
-- 调用存储过程
call in_procudure(1);
1
2
3
4
5
6
7
8
9
10
11

2.out

下面是一个使用out的一个简单场景：
这里出现了一些上面没有的内容，作下说明，

1. into 用来将数据输出到out（或者inout）指定的输出参数上
2. 调用存储过程时传入@param_three，意思是建立用户变量param_three用来接收procedure的返回参数param_out
3. select @param_three;意思是查询用户变量的值，因为我们把值放入了用户变量中，就只能这么查出来了。关于变量下一块会详细说下。

drop procedure if exists out_procedure;
delimiter $$
create procedure if not exists out_procedure(in param_one integer,out param_out varchar(50))
begin 
select room_name into param_out from room where id = param_one;
end $$
delimiter ;
call out_procedure('1',@param_three);
select @param_three;
1
2
3
4
5
6
7
8
9

3.inout

下面是inout的简单实例：
这里又出现了一些上面没有的内容，作下说明：

1. set @id = ‘1’;表示设置一个用户变量id，把1赋给它。
2. 参数列表里的参数类型若是字符串尽量声明下长度，此外正常使用中使用inout比in、out会更多些，因为方便

drop procedure if exists inout_procedure;
delimiter $$
create procedure inout_procedure(inout param_one varchar(100))
begin 
select sale_price into param_one from room where id = param_one;
end $$
delimiter ;

set @id = '1';
call inout_procedure(@id);
select @id;
1
2
3
4
5
6
7
8
9
10
11

三、存储过程中的变量

上一章节说的是存储过程的基本写法，这一节会对其进行扩充，在基础写法的基础上增加变量的操作，类比高级语言可以发现，复杂的程序离不开成员变量、局部变量的配合使用，procedure也是一样。这里的局部变量指的也是函数体内部的变量，这里的用户变量可以类比成员变量。

1.局部变量

关于局部变量有以下使用规定：

1. 必须通过declare来声明局部变量，可以使用default赋默认值，写法为：declare paramOne varchar(20) default ‘初始值’;
2. 局部变量的定义必须写在符合语句的开头，也就是先声明后使用。
3. 局部变量的声明周期只在begin…end范围内有效
4. 局部函数的赋值使用set 即可。

下面是局部变量的简单使用：

drop procedure if exists jubu_procedure;
delimiter $$
create procedure if not exists jubu_procedure()
begin 
declare jubu_param varchar(50) default null;
set jubu_param = '我是局部变量';
select jubu_param;
end $$
delimiter ;

call jubu_procedure();
1
2
3
4
5
6
7
8
9
10
11

2.用户变量

局部变量是只能在procedure中使用的，下面要说的用户变量和系统变量都是mysql本来就支持的，和procedure没有必然联系，当然他们也是可以在procedure中使用的了。用户变量就是当前用户在一次数据库访问中创建的变量，该变量只在一次与数据库的会话中有效，会话结束用户变量的声明周期也就结束。
关于用户变量的使用规定：

1. 声明格式为 set @param = ‘用户变量’;
2. 作用域是mysql的一次连接会话，只对当前会话有效

下面是一个使用用户变量的例子：

drop procedure if EXISTS user_procedure;
delimiter $$
create procedure if not exists user_procedure(inout param_one varchar(50))
begin 
declare jubu_param varchar(50) DEFAULT '';
select param_one;
set jubu_param = param_one;
select jubu_param;
set @user_param = jubu_param;
select @user_param;
end $$
delimiter;
set @param_two = '测试用户变量';
call user_procedure(@param_two);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.系统变量

系统变量是mysql启动时自动设置的，当然我们也可以

1. 系统变量有分为全局变量与会话变量，会话变量是全局变量的拷贝
2. 全局变量在MYSQL启动的时候由服务器自动将他的初始化为默认值,这些默认值可以通过更改my.ini这个文件来更改
3. 会话变量在每次建立一个新的连接的时候,由MYSQL来初始化,MYSQL会将当前所有全局变量的值复制一份,来作为会话变量
4. 网上说更改会话变量不会影响系统变量的值，笔者使用root用户测试发现值已经被更改

下面是系统变量的写法（一般在存储过程能用到系统变量的地方少一些）：

set @@global.sort_buffer_size = 262144;
select  @@global.sort_buffer_size;
1
2

四、存储过程的条件定义和处理

这里的条件处理其实就像是java里的try…catch，都是为了定义发生某种异常场景时程序该如何处理。这么一类比可能就会非常清晰了，一起看下存储过程中条件处理的定义吧。

1.条件处理

下面是条件处理的标准写法（需要写在procedure里）：

declare handler_type handler for condition_value[,...] sp_statement;
-- 参数解释
handler_type:
continue -- 忽略错误，继续执行
|exit    -- 碰到错误，退出
|undo    -- 这个值目前没有正式使用，回滚之前的操作

condition_value:
sqlstate[value] sqlstate_value -- 直接声明异常码
|condition_name  -- 自定义的条件
|sqlwarning      -- 涵盖所有以01开头的sqlstate的状态码
|not found       -- 涵盖所有以02开头的sqlstate的状态码
|sqlexception    -- 涵盖所有非sqlwarning、not found以外的所有异常
|mysql_error_code-- error类型的异常 
1
2
3
4
5
6
7
8
9
10
11
12
13
14

可以看到其实条件处理的语句写起来很简单

2.条件的定义

我们可以看到在条件处理时，他的codition_value 可以是 condition_name，那这个condition_name是啥呢，其实就是我们自己定义的条件。就是说我们先定义条件，条件里面声明异常场景，处理时直接引用这个条件就行。在笔者看来这是把条件处理搞复杂了，我们完全可以使用条件处理直接声明异常场景，为甚么非要多写个条件声明呢，笔者建议直接使用条件处理。
条件声明的标准写法如下：

declare condition_name condition for condition_value;
1

3.条件的几个例子

下面是一条主键冲突的异常场景的处理

drop procedure if exists condition_procedure;
delimiter $$
create procedure if not exists condition_procedure()
begin 
declare continue handler for sqlstate '23000' set @ex = 1;
set @msg = '';

insert into room values (1,'105',2000);

if @ex=1 then set @msg = '异常了';
else set @msg = '无异常';
end if;
end $$
delimiter;
call condition_procedure();
select @msg;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

若是没有加条件处理的语句，那肯定是要报异常的，下面再看下上面的操作修改成使用条件定义+条件处理的方式的写法

delimiter $$ 
create procedure if not exists cond_procedure()
begin 

declare primaryError condition for sqlstate '23000';
declare continue handler for primaryError set @ex = 1;
insert into room values (1,'105',2000);
if @ex = 1 then set @msg = '主键异常了';
else set @msg = '无异常';
end if;
end $$
delimiter ;
call cond_procedure();
select @msg;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

五、存储过程中的流程控制

可以说之类介绍的每一个点都是使用存储过程解决实际问题必不可少的要素，流程控制也是如此，这里的前两个是做条件判断的有些相似，后面三个则都是循环控制的，他们也有些相似。其实和java里的都类似，类比下即可。

1.if、else

格式如下：

if ... then ...;
[elseif ... then ...;]
[else ...;]
end if;
1
2
3
4

使用举例：

drop procedure if exists if_procedure;
delimiter $$
create procedure if not exists if_procedure()
begin 
	declare var_param integer default 0;
	if var_param = 1 then set @msg = '局部变量是1';
	elseif var_param = 2 then set @msg = '局部变量是2';
	elseif var_param = 3 then set @msg = '局部变量是3';
	else set @msg = '局部变量不是123';
	end if;
	select @msg;
end $$
delimiter ;
call if_procedure();
1
2
3
4
5
6
7
8
9
10
11
12
13
14

如上所示，需要注意的是，存储过程每一个句子的结束都是需要分号结尾的，这个不能少，因为他也是一句一句执行嘛，不告诉他从哪里到哪里他也解析不出来啊。其次还需要注意if … end if;必须成对出现。

2.case when

有sql基础的话，那这个就好写了，因为这里的语法与sql中一模一样，就会每行结束需要加分号就是。
标准写法有两种,第一种如下：

case ...
when ... then ...;
when ... then ...;
else ...;
end case;
1
2
3
4
5

第二种如下：

case when ... then ...;
         when ... then ...;
         when ... then ...;
         else ... ;
end case;
1
2
3
4
5

这两种的写法，在sql中应该都挺常用的，应该都不陌生，这里就简单举个粒子：

drop procedure if exists case_procedure;
delimiter $$ 
create procedure if not exists case_procedure()
begin 
	declare var_param varchar(20) default '张三';
	-- 第一种case 用法
	case var_param
		when '张三' then set @msg = '他是张三';
		when '李四' then set @msg = '他是李四';
		when '王五' then set @msg = '他是王五';
		else set @msg = '未知人';
	end case;
	select @msg;
	-- 第二种case 用法
	case 
		when var_param = '张三' then set @msg = '第二种张三';
		when var_param = '李四' then set @msg = '第二种李四';
		-- 这里的when后面可以跟任何条件
		when true = false then set @msg = '这里恒不等';
		else set @msg = '未知人';
	end case;
	select @msg;
end $$
delimiter ;
call case_procedure();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

对比下两种写法，会发现第二种更灵活些，第一种写法稍简单，怎么使用其实差别不大。

3.loop

后面这三个就是循环的使用了，其实从上面的一些粒子我们应该能够总结出一些规律了，比如说 他们格式都是这种 ==关键字… end 关键字；==下面的这三种循环的流程控制也是如此，都会遵循这个规律，看下loop的标准写法吧：

[begin_label:]loop
...
end loop [end_label];
1
2
3

看上面的标准写法我们不难看出，loop并没有声明怎么退出循环体，类比java，会发现有点类似于java里的while（true），这个循环会一直执行下去。那怎么才能退出去呢，下面就要说下iterate与leave了，他们都是用来退出循环控制的语句。

4.循环跳出之leave

使用leave退出整个循环，当前循环会完全退出。类似java语言里的breake。
使用leave结合loop的简单例子：

drop procedure if exists loop_procedure;
delimiter $$
create procedure if not exists loop_procedure()
begin 
	declare int_param int default 10;
	declare int_var int default 0;
	label:loop 
		if int_var = int_param then leave label;
		else set int_var = int_var + 1;
		end if;
	end loop label;
	set @msg = int_var;
	select @msg;
end $$
delimiter ;
call loop_procedure();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

注意：

1. label最好不要省略了，因为退出循环时，无论是leave还是iterate都是必须声明label的。

5.循环跳出之iterate

使用iterate退出当前循环，继续执行下一层的循环，类似java语言里的continue。
使用iterate结合loop的简单例子：

drop procedure if exists loop_procedure;
delimiter $$
create procedure if not exists loop_procedure()
begin 
	declare int_param int default 10;
	declare int_var int default 0;
	-- loop 的开始和结束标签都是可以省略的
	label:loop 
		if int_var = int_param then set int_var = int_var + 1;iterate label;
		elseif int_var = 20 then leave label;
		else set int_var = int_var + 1;
		end if;
	end loop label;
	set @msg = int_var;
	select @msg;
end $$
delimiter ;
call loop_procedure();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

6.repeat

下面是要说repeat

1. repeat与loop不同的是，他可以自己定义退出条件，也就是说不会死循环，其他iterate、leave使用都没啥区别。

下面是标准写法：

[begin_label:]repeat
until ... end repeat end_label;
1
2

简单例子：

delimiter $$
create procedure if not exists repeat_procedure()
begin 
	declare var_int int default 10;
	label:repeat 
		if var_int < 10 then set var_int = var_int + 1;
		end if;
	until var_int = 10 end repeat label;
	set @msg = var_int;
	select @msg;
end $$
delimiter ;
call repeat_procedure();
1
2
3
4
5
6
7
8
9
10
11
12
13

7.while … do

while … do 其实与repeat很是类似，都是自带退出条件，不像loop那样需要我们在里面使用条件判断，不过他们还是有一些区别的，while是满足条件才会执行，反过来说其实就是不满足就退出呗，repeat则是满足则退出。不过真正使用时，都是类似的差别不大。
while…do的标准下发如下：

[begin_label:]while condition do ...
end while [end_label];
1
2

一个简单的例子：

drop procedure if exists while_procedure;
delimiter $$
create procedure if not exists while_procedure()
begin 
	declare var_int int default 1;
	label:while var_int <100 do 
		set var_int = var_int + 1;
	end while label;
	set @msg = var_int;
	select @msg;
end $$
delimiter ;
call while_procedure();
1
2
3
4
5
6
7
8
9
10
11
12
13

六、存储过程的光标

流程控制+变量+条件处理，这个程序组织起来你会发现好像就是缺了点啥。回想下使用java写程序时会发现，每次遍历其实我们都是会去遍历一个集合、数组、流等，那procedure里怎么遍历这个，之前好像都没有提到过。那这里就要用到光标了，光标的作用其实就可以类比成流，使用前需要将数据放入光标，这是定义光标的过程；使用时需要打开光标，使用后需要关闭光标，并且光标的数据不受影响。是不是很像一个流呢。

1.光标的定义

下面是光标的标准语法：

declare cursor_name cursor for select_statement; -- 定义光标的数据
open cursor_name; -- 打开光标，只有打开光标才能操作里面的数据
fetch cursor_name into var_name[,...]; -- 将光标中的数据取出来放在变量里进行操作
close cursor_name; -- 关闭光标，光标使用完必须关闭
1
2
3
4

上面已经介绍了，光标的使用其实有这四步，每一步都是不可获取的，声明光标（定义数据），打开光标（操作数据做准备），获取光标（拿到数据操作），关闭光标（使用完关闭，就是操作流时一样）。

2.光标的几个简单例子

我们假设一个场景，然后去体验下这个光标：
有一个room表，有三列，现在想要将列sale_price的价格进行累加，我们可以这么写：

drop procedure if exists cursor_procedure;
delimiter $$
create procedure if not exists cursor_procedure()
begin 
	declare var_price double default 0;
	declare price_cursor cursor for select sale_price from room;
	declare exit handler for not found close price_cursor;
	set @sum_price = 0;
	open price_cursor;
	repeat 
		fetch price_cursor into var_price;
		set @sum_price  =@sum_price + var_price;
	until 0 end repeat;
	close price_cursor;
end $$
delimiter ;

call cursor_procedure();
select @sum_price;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

这样也就正常求出一个和了，其实和我们的sum()函数有些类似。这里使用了repeat对fetch中的数据进行循环取数，同时定义了一旦数据被取完就会关闭光比并退出。

七、总结

存储过程里面其实东西不算多，相比于高级语言里已经少了一些了，不过这些已经能解决大部分问题了，这里总结下，先是参数（in、out、inout）在后面就是条件处理了（condition、handler）在后面又是流程控制（if…else，case…when，leave、iterate、loop、repeat、while）在后面就是光标了（cursor），掌握好这些存储过程也就没有死点了。