MySQL存储过程学习

MySQL存储过程

作为mysql的初学者，自己看着教程视频，做的笔记，以便日后回顾复习，纯手打，可能有些误差，可指出更正。

什么是存储过程

  • 存储过程和函数就是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化开发人员很多过程，减少数据在数据库和服务间的传输，对于提高数据处理的效率是有好处的。
  • 存储过程和存储函数区别在于函数必须有返回值,而存储过程没有.
    – 函数:是一个有返回值的过程.
    – 过程:是一个没有返回值的函数.
1. 创建存储过程
1
2
3
4
5
6
7
8
9
10
11
12
CREATE PROCEDURE procedure_name([proc_parameter[....]])
begin
	-- sql语句
end;
示例:
-- delimiter来指定哪个符号作为分隔符(也就是原来';'的作用)
delimiter $
create procedure pro_test1()
begin 
	select 'hello mysql';
end$
delimiter ;
2. 调用存储过程

call 存储过程名称;

1
示例: call procedure_test1();
3. 查看存储过程
3.1 查询db_name数据库中的所有存储过程
1
select name from mysql.proc where db='db_name';
3.2 查询存储过程的状态信息
1
show procedure status;
3.3 查询某个存储过程的定义
1
show create procedure test.pro_test1 \G;
4. 删除存储过程(存储过程名称不要加’()’)
1
DROP PROCEDURE [IF EXISTS] sp_name;
5. 语法
5.1 变量
  • DECLARE:定义一个局部变量,该变量作用范围只能在BEGIN…END块中.
    declare 变量名 变量类型 [默认值];
1
declare num int default 10;
  • SET:直接使用SET,可以赋常量或者赋表达式,具体语法如下:
1
set var_name = expr[,var_name = expr]...

示例:

1
2
3
4
5
6
7
8
delimiter $
create procedure procedure_test01()
begin 
	declare name varchar(20);
	set name = 'mysql';
	select name;
end$
delimiter ;

也可以通过select…into方式进行赋值操作;

1
2
3
4
5
6
7
8
delimiter	$
create procedure procedure_test02()
begin 
	declare num int default 0;
	select count(*) into num from stu;
	select num;
end$
delimiter	;
5.2 if判断
1
2
3
4
5
6
7
8
9
if search_condition then statement_list
	[elseif search_condition then statement_list]...
	[else statement_list]
end if;
示例:
	根据定义的身高变量,来判断所属身材类型.
	180及以上:身材高挑,
	170-180:标准身材,
	170及以下,一般身材
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
delimiter $
create procedure procedure_test03()
begin 
	declare hight int default 182;
	declare description varchar(10) default '';
	if hight>=180 then 
		set description = '身材高挑';
	elseif hight>=170 and hight<180 then
		set description = '标准身材';
	else 
		set description = '一般身材';
	end if;
	select concat('身高',hight,'对应的身材类型为',description);
end$
delimiter ;
5.3 传递参数
1
2
3
4
5
create procedure procedrue_test04([in/out/inout])
输入参数:in(默认的,可不加修饰),输出参数:out
输入输出参数:inout
需求:
	根据传入的身高参数,来显示对应的身材.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 有输入,没输出
delimiter $
create procedure procedure_test04(in hight int)
begin 
	declare description varchar(10) default '';
	if hight>=180 then 
		set description = '身材高挑';
	elseif hight>=170 and hight<180 then
		set description = '标准身材';
	else 
		set description = '一般身材';
	end if;
	select concat('身高',hight,'对应的身材类型为',description);
end$
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 有输入,有输出
delimiter $
create procedure procedure_test05(in hight int,out description varchar(10))
begin 
	if hight>=180 then 
		set description = '身材高挑';
	elseif hight>=170 and hight<180 then
		set description = '标准身材';
	else 
		set description = '一般身材';
	end if;
end$
delimiter ;
-- 调用该存储过程并获取返回值
call procedure_test05(178,@desc);
select @desc;

小知识
@description:这种变量要在变量名称前加‘@’符号,叫做用户会话变量,代表整个会话都是有效的,只要不关闭连接,类似于全局变量.
@@global.sort_buffer_size:这种变量加上‘@@’符号,叫做系统变量.

5.4 case语句

方式一:

1
2
3
4
5
CASE	case_value
	WHEN	when_value THEN statement_list
	[WHEN	when_value THEN statement_list]...
	[ELSE statement_list]
END CASE;

方式二:

1
2
3
4
5
CASE	
	WHEN	search_condition THEN statement_list
	[WHEN	search_condition THEN statement_list]...
	[ELSE statement_list]
END CASE;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/* 
需求:
	给定一个月份,然后计算是哪一季度
*/
delimiter $
create procedure procedure_test06(mon int,out jidu varchar(10))
begin
	case mon
		when mon>=1 and mon<=3 then 
			set jidu='第一季度';
		when mon>=4 and mon<=6 then 
			set jidu='第二季度';
		when mon>=7 and mon<=9 then 
			set jidu='第三季度';
		else
			set jidu='第四季度';
	end case;
	select concat(mon,'是',jidu);
end$
delimiter ;
5.5 while循环
1
2
3
4
5
while search_condition do
	statement_list
end while;
需求:
	计算从1到n的累加值
1
2
3
4
5
6
7
8
9
10
11
12
delimiter $
create procedure procedure_test07(n int)
begin
	declare total int default 0;
	declare start int default 1;
	while start<=n do
		set total=total+start;
		set start=start+1;
	end while;
	select total;
end$
delimiter ;
5.6 repeat循环

有条件的循环控制语句,当满足条件时退出循环.
while循环是满足条件才执行,repeat是满足条件则退出.

1
2
3
4
5
6
repeat
	statement_list
	until search_condition(注意这里没有';')
end repeat;
需求:
	计算从1到n的累加值
1
2
3
4
5
6
7
8
9
10
11
12
delimiter $
create procedure procedure_test08(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 ;