【Mysql系列】存储过程和函数

概述

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

创建存储过程

存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。

在存储过程的创建中，经常会用到一个十分重要的 MySQL 命令，即 DELIMITER 命令，特别是对于通过命令行的方式来操作 MySQL 数据库的使用者，更是要学会使用该命令。

在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。
在 MySQL 命令行客户端输入如下 SQL 语句。

mysql > DELIMITER $

CREATE PROCEDURE procedure_name ([proc_parameter])
begin
	-- SQL语句
end;

create PROCEDURE pro_test()
BEGIN
SELECT * FROM base_region;
END$

调用存储过程

call pro_test()

查看存储过程

select name from mysql.proc where db='gmall'

show procedure status;

show PROCEDURE status\G;

删除存储过程

drop PROCEDURE pro_test

存储过程语法介绍

变量

create PROCEDURE pro_test()
begin 
DECLARE num int DEFAULT 10;
select CONCAT('num的值为',num);
end

call pro_test()

create PROCEDURE pro_test01()
begin 
DECLARE num int DEFAULT 10;
set num = num + 10;
select num;
end

call pro_test01()

create PROCEDURE pro_test02()
begin
DECLARE num INT;
select count(*) into num from base_trademark;
select CONCAT('表的数量为: ',num);
end

call pro_test02()

if条件判断

create PROCEDURE pro_test03()
BEGIN
	DECLARE height int default 175;
	DECLARE description varchar(50) DEFAULT '';
	if height >= 180 then
		set description = '身材高挑';
	ELSEIF height >= 170 and height < 180 then
		set description = '标准身材';
	ELSE
		set description = '一般身材';
		end if;
	select concat('身高 ',height,' 对应的身材类型',description);
END

call pro_test03()

传递参数

create procedure procedure_name([in/out/inout] 参数名 参数类型)

IN:该参数可以作为输入、也就是需要调用方法传入值、默认
OUT:该参数作为输出、也就是该参数可以作为返回值
INOUT:既可以作为参数、也可以作为输出参数

IN-输入

根据定义的身高变量、判断当前身高所属的身材类型
示例：

create PROCEDURE pro_test03(in height int)
BEGIN
	DECLARE description varchar(50) DEFAULT '';
	if height >= 180 then
		set description = '身材高挑';
	ELSEIF height >= 170 and height < 180 then
		set description = '标准身材';
	ELSE
		set description = '一般身材';
		end if;
	select concat('身高 ',height,' 对应的身材类型',description);
END

call pro_test03(181)

OUT-输出

create PROCEDURE pro_test04(in height int,out description VARCHAR(10))
BEGIN
	if height >= 180 then
		set description = '身材高挑';
	ELSEIF height >= 170 and height < 180 then
		set description = '标准身材';
	ELSE
		set description = '一般身材';
		end if;
END

call pro_test04(181,@description)

select @description

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:

需求

给定一个月份、然后计算出所在的季度

create PROCEDURE pro_test05(in mon int)
BEGIN
	DECLARE result varchar(10);
	case 
		when mon >= 1 and mon <= 3 THEN 
			set result = '第一季度';
		when mon >= 4 and mon <= 6 THEN
			set result = '第二季度';
		when mon >= 7 and mon <= 9 THEN
			set result = '第三季度';
		ELSE
			set result = '第四季度';
	end case;
	select CONCAT('传递的月份是 ',mon,'季度属于 ',result);
END

call pro_test05(6)

While循环

while search_condition do
	statement_list
end while;

需求

计算从1加到n的值

示例

CREATE PROCEDURE pro_test08(n int)
BEGIN
	DECLARE total int DEFAULT 0;
	DECLARE num int default 1;
	while num <= n DO
		set total = total + num;
		set num = num + 1;
	end while;
	select total;
end

call pro_test08(3)

Repeat结构

有条件的循环控制语句、当满足条件时退出循环、While是满足条件才执行、repeat是满足条件就退出循环
语法结构：

REPEAT

	statement_list
	
	UNTIL search_condition

END REPEAT;

需求：

计算从1加到n的值

示例:

CREATE PROCEDURE pro_test09(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

Loop循环_Leave退出

LOOP循环实现简单的循环、退出循环的条件需要使用其他的语句条件定义、通常可以使用LEAVE语句实现、具体语法如下：

create PROCEDURE pro_test10(n int)
BEGIN
	declare total int default 0;
	
	c:loop
		set total = total + n;
		set n = n - 1;
		
		if n <= 0 THEN
			leave c;
		end if;
	
	end loop c;
	select total;
END