MySQL存储过程的学习

MySQL存储过程

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

什么是存储过程

• 存储过程和函数就是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程可以简化开发人员很多过程,减少数据在数据库和服务间的传输,对于提高数据处理的效率是有好处的.
• 存储过程和存储函数区别在于函数必须有返回值,而存储过程没有.
  – 函数:是一个有返回值的过程.
  – 过程:是一个没有返回值的函数.

1. 创建存储过程

CREATE PROCEDURE procedure_name([proc_parameter[....]])
begin
	-- sql语句
end;
示例:
-- delimiter来指定哪个符号作为分隔符(也就是原来';'的作用)
delimiter $
create procedure pro_test1()
begin 
	select 'hello mysql';
end$
delimiter ;

2. 调用存储过程

call 存储过程名称;

示例: call procedure_test1();

3. 查看存储过程

3.1 查询db_name数据库中的所有存储过程

select name from mysql.proc where db='db_name';

3.2 查询存储过程的状态信息

show procedure status;

3.3 查询某个存储过程的定义

show create procedure test.pro_test1 \G;

4. 删除存储过程(存储过程名称不要加’()’)

DROP PROCEDURE [IF EXISTS] sp_name;

5. 语法

5.1 变量

• DECLARE:定义一个局部变量,该变量作用范围只能在BEGIN…END块中.
  declare 变量名 变量类型 [默认值];

declare num int default 10;

• SET:直接使用SET,可以赋常量或者赋表达式,具体语法如下:

set var_name = expr[,var_name = expr]...

示例:

delimiter $
create procedure procedure_test01()
begin 
	declare name varchar(20);
	set name = 'mysql';
	select name;
end$
delimiter ;

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

delimiter	$
create procedure procedure_test02()
begin 
	declare num int default 0;
	select count(*) into num from stu;
	select num;
end$
delimiter	; 

5.2 if判断

if search_condition then statement_list
	[elseif search_condition then statement_list]...
	[else statement_list]
end if;
示例:
	根据定义的身高变量,来判断所属身材类型.
	180及以上:身材高挑,
	170-180:标准身材,
	170及以下,一般身材

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 传递参数

create procedure procedrue_test04([in/out/inout])
输入参数:in(默认的,可不加修饰),输出参数:out
输入输出参数:inout
需求:
	根据传入的身高参数,来显示对应的身材.

-- 有输入,没输出
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 ;

-- 有输入,有输出
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语句

方式一:

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;

/* 
需求:
	给定一个月份,然后计算是哪一季度
*/
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循环

while search_condition do
	statement_list
end while;
需求:
	计算从1到n的累加值

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是满足条件则退出.

repeat
	statement_list
	until search_condition(注意这里没有';')
end repeat;
需求:
	计算从1到n的累加值

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 ;