MySQL 存储过程

1、概念介绍

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程特点：

• 封装，复用。
• 可以接收参数，也可以返回数据。
• 减少网络交互，效率提升。

2、语法格式

调用存储过程：CALL 名称([参数]);

删除存储过程：DROP PROCEDURE [IF EXISTS] 存储过程名称

创建存储过程：

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
	--SQL语句
END;

查看存储过程：

-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;
-- 查询指定数据库的存储过程及状态信息
SELECT * FROM information_schema.routines WHERE routine_schema='数据库名称';

-- 创建存储过程
-- 修改结束符
DELIMITER %
CREATE PROCEDURE p1()
BEGIN
	SELECT COUNT(*) FROM student;
END%
-- 修改结束符
DELIMITER ;

-- 调用存储过程
CALL p1();

-- 查看存储过程创建语句
SHOW CREATE PROCEDURE p1;
-- 查询指定数据库的存储过程及状态信息
SELECT * FROM information_schema.routines WHERE routine_schema='itcast';

-- 删除存储过程
DROP PROCEDURE IF EXISTS p1;

注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter 指定SQL语句的结束符。创建完存储过程之后，再将结束符改为“;”。

3、变量

a. 系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)。

查看系统变量：

-- 查看所有系统变量
show [session|global] variables;
-- 通过like模糊匹配方式查找变量
show [session|global] variables like 'xxx';
-- 查看指定变量的值
select @@[session|global].系统变量名;

设置系统变量：

set [session|global] 系统变量名=值;
set @@[session|global] 系统变量名=值;

-- 查看会话级别的系统变量
SHOW VARIABLES;
SHOW SESSION VARIABLES;
-- 模糊查看
SHOW SESSION VARIABLES LIKE 'auto%';
-- 查看全局系统变量
SHOW GLOBAL VARIABLES;
-- 模糊匹配
SHOW GLOBAL VARIABLES LIKE 'auto%';
-- 准确查看系统变量，默认查看会话级别
SELECT @@autocommit;
-- 准确查看系统变量，默认查看全局级别
SELECT @@global.autocommit;

-- 设置会话系统变量:设置不自动提交
SET SESSION autocommit=0;
SELECT @@session.autocommit;

注意：mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf 中配置。

b. 自定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。

定义变量：

set @var_name = expr [,@var_name = expr…];
set @var_name := expr [,@var_name := expr…];
select @var_name := expr [,@var_name := expr…];
select 字段名 into @var_name from 表名;

使用变量：select @var_name;

-- 赋值
SET @myname = 'hualu';
SET @age := 10;
SET @mygender := 'male',@myhobby='coding';
SELECT @mycolor := 'red';
SELECT COUNT(*) INTO @mycount FROM student;

-- 查询自定义变量
SELECT @myname,@myage,@mygender,@myhobby,@mycolor,@mycount;

c. 局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN...END块。

声明：declare 变量名 变量类型 [DEFAULT …];

赋值：

set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名…;

-- 修改结束符
DELIMITER $
CREATE PROCEDURE p2()
BEGIN	
	-- 声明局部变量
	DECLARE stu_count INT DEFAULT 0;
	-- 赋值
	SELECT COUNT(*) INTO stu_count FROM student;
	-- 查看局部变量
	SELECT stu_count;
END$
-- 将结束符改回来
DELIMITER ;

-- 调用存储过程
CALL p2();

4、参数

CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
	-- SQL语句
END;

-- 更改结束符
DELIMITER $$
-- 创建存储过程
CREATE PROCEDURE p4(IN score INT, OUT result VARCHAR(10))
BEGIN
	IF score >= 85 THEN
		SET result := '优秀';
	ELSEIF score >= 60 THEN
		SET result := '及格';
	ELSE
		SET result := '不及格';
	END IF;
END$$
-- 将存储过程改回
DELIMITER ;

-- 调用存储过程
CALL p4(65, @result);

-- 查询自定义变量
SELECT @result;

5、条件判断

a. if 判断

语法格式：

-- 语法
if 条件 then
	……
elseif 条件2 then
	……
else
	……
end if;

-- 案例1：根据传入参数score，判定当前分数对应的分数等级，并返回。
-- 更改结束符
DELIMITER $$
CREATE PROCEDURE p3()
BEGIN
	DECLARE score INT DEFAULT 65;
	DECLARE slevel VARCHAR(10);
	IF score >= 85 THEN
		SET slevel := '优秀';
	ELSEIF score >= 60 THEN
		SET slevel := '合格';
	ELSE
		SET slevel := '不及格';
	END IF;
	SELECT slevel;		
END$$
-- 将结束符改回
DELIMITER ;

-- 调用存储过程
CALL p3();



-- 案例2：将传入的 200分制的分数，进行换算，换算成百分制，然后返回分数---> inout
DELIMITER $$
-- 创建存储过程
CREATE PROCEDURE p5(INOUT score DOUBLE)
BEGIN
	SET score := score * 0.5;
END$$
DELIMITER ;

-- 为变量赋值
SET @score = 78;
-- 调用存储过程
CALL p5(@score);
-- 查看返回值
SELECT @score;

b. case 分支

 -- 语法一 
 CASE case_value
	WHEN when_value1 THEN statement_list1
	[WHEN when_value2 THEN statement_list2]……
	[ELSE statement_list]
END CASE;

-- 语法二
CASE
	WHEN search_condition1 THEN statement_list1
	[WHEN search_condition2 THEN statement_list2]…
	[ELSE statement_list]
END CASE;



-- 案例1：根据传入的月份，判定月份所属的季节（要求采用case结构)。\
DELIMITER %
CREATE PROCEDURE p6(IN month1 INT)
BEGIN
	-- 声明存储变量
	DECLARE result VARCHAR(10);
	
	-- case 分支
	CASE
		WHEN month1 >= 1 AND month1 <= 3 THEN
			SET result := '第一季度';
		WHEN month1 >= 4 AND month1 <= 6 THEN
			SET result := '第二季度';
		WHEN month1 >= 7 AND month1 <= 9 THEN
			SET result := '第三季度';
		WHEN month1 >= 10 AND month1 <= 12 THEN
			SET result := '第四季度';
		ELSE 
			SET result := '非法参数';
	END CASE;
	SELECT CONCAT('您输入的月份为：',month1,',所属的月份为：',result);
END%
DELIMITER ;

CALL p6(6);

6、循环语句

a. while 循环

while是有条件的循环控制语句,当满足条件的时候进行循环。

-- 先判定条件，如果条件为true，则执行逻辑，否则不执行逻辑
WHILE 条件 DO
	-- SQL 逻辑
END WHILE;


-- 案例1.计算从1累加到n 的值，n为传入的参数值。
DELIMITER $$
CREATE PROCEDURE p7(IN num INT)
BEGIN
	-- 定义和变量
	DECLARE total INT DEFAULT 0;
	WHILE  num > 0 DO
		SET total := total + num;
		SET num := num - 1;
	END WHILE;
	SELECT CONCAT('从1加到',num,'的总和为：',total);
END$$
DELIMITER ;

CALL p7(10);

b. repeat 循环

repeat是有条件的循环控制语句,当满足条件的时候退出循环。

-- 先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
	SQL逻辑……
	UNTIL 条件
END REPEAT;

-- 案例1.计算从1累加到n 的值，n为传入的参数值。
DELIMITER $$
CREATE PROCEDURE p8(IN num INT)
BEGIN
	DECLARE total INT DEFAULT 0;
	REPEAT
		SET total := total + num;
		SET num := num - 1;
		UNTIL num <= 0
	END REPEAT;
	SELECT CONCAT('从1加到',num,'的和为：',total);
END$$
DELIMITER ;

CALL p8(10);

c. loop 循环

Loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。Loop可以配合以下两个语句使用：

• LEAVE：配合循环使用，退出循环。
• ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

[begin_label:] LOOP
	SQL 逻辑……
END LOOP [end_label];

--退出指定标记的循环体
LEAVE label; 
--直接进入下一次循环
ITERATE label; 

-- 案例1：计算从1累加到n的值，n为传入的参数值。
DELIMITER $
CREATE PROCEDURE p9(IN num INT)
BEGIN
	DECLARE total INT DEFAULT 0;
	
	`sum`:LOOP
		IF num <= 0 THEN
			LEAVE `sum`;
		END IF;
		
		SET total := total + num;
		SET num := num -1;
	END LOOP `sum`;
	
	SELECT total;
END$
DELIMITER ;

CALL p9(100);



-- 案例2：计算从1到n之间的偶数累加的值，n为传入的参数值。
DELIMITER $
CREATE PROCEDURE p10(IN num INT)
BEGIN
	DECLARE total INT DEFAULT 0;
	
	`sum`:LOOP
		IF num <= 0 THEN
			LEAVE `sum`;
		END IF;
		
		-- 如果当前num值是奇数
		IF num % 2 = 1 THEN
			SET num := num -1;
			-- 跳过当前循环
			ITERATE `sum`;
		END IF;
		
		SET total := total + num;
		SET num := num -1;
	END LOOP `sum`;
	
	SELECT total;
END$
DELIMITER ;

CALL p10(10);

7、游标

游标（CURSOR）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和 CLOSE，其语法分别如下。

• 声明游标： declare 游标名称 cursor for 查询语句;
• 打开游标： OPEN 游标名称;
• 获取游标记录：fetch 游标名称 into 变量[,变量];
• 关闭游标： close 游标名称;

a. 条件处理程序

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为:

DECLARE handler_action HANDLER FOR condition_value [,condition_value]… statement;

handler_action

• CONTINUE 继续执行当前程序
• EXIT 终止执行当前程序

condition_value

• SQLSTATE sqlstate_value 状态码，如02000

• SQLWARNING 所有以01开头的SQLSTATE代码的简写
• NOT FOUND 所有以02开头的SQLSTATE代码的简写
• SQLEXCEPTION 所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

-- 根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名(name)
-- 和专业(profession)，并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中

-- 更改结束符
DELIMITER $
-- 创建存储过程
CREATE PROCEDURE p11(IN uage INT)
BEGIN
	-- 先声明普通变量，在声明游标
	-- 声明存储游标记录的变量
	DECLARE uname VARCHAR(10);
	DECLARE upro VARCHAR(20);
	-- 1. 声明游标，存储查询的结果集
	DECLARE u_cursor CURSOR FOR SELECT `name`,profession FROM tb_user WHERE age <= uage;
	
	-- 声明条件处理程序，当循环报错之后会执行此处的代码
	DECLARE EXIT HANDLER FOR SQLSTATE '02000' CLOSE u_cursor;
	-- 声明条件处理程序时，也可以不适用状态码
	-- DECLARE EXIT HANDLER FOR NOT FOUND CLOSE u_cursor;
	
	-- 2. 创建表
	DROP TABLE IF EXISTS tb_user_pro;
	CREATE TABLE IF NOT EXISTS tb_user_pro(
		id INT PRIMARY KEY AUTO_INCREMENT,
		`name` VARCHAR(10),
		profession VARCHAR(20)
	);
	
	-- 3. 打开游标
	OPEN u_cursor;
	
	-- 4. 遍历游标中的记录
	-- 此处是死循环，当遍历结束后，会报错[02000][1329] No data - zero rows fetched, selected, or processed
	WHILE TRUE DO
		-- 获取游标记录
		FETCH u_cursor INTO uname,upro;
		-- 5. 将内容插入到新表中
		INSERT INTO tb_user_pro VALUES(NULL, uname, upro);
	END WHILE;
	
	-- 6. 关闭游标
	CLOSE u_cursor;
END$
-- 将结束符改回
DELIMITER ;

-- 调用存储过程
CALL p11(30);

8、存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下:

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS TYPE [characteristic...]
BEGIN
	-- SQL语句
	RETURN …;
END;

characteristic 说明：

• DETERMINISTIC 相同的输入参数总是产生相同的结果
• NO SQL 不包含SQL语句
• READS SQL DATA 包含读取数据的语句，但不包含写入数据的语句。

-- 计算从1累加到num的值，num为传入的参数值。
DELIMITER $
CREATE FUNCTION fun1(num INT)
-- mysql 8.0 之后需要指定 deterministic
RETURNS INT DETERMINISTIC
BEGIN
	-- 声明变量和
	DECLARE total INT DEFAULT 0;
	-- 循环累加
	WHILE num > 0 DO
		SET total := total + num;
		SET num := num - 1;
	END WHILE;
	-- 返回总和
	RETURN total;
END$
DELIMITER ;

SELECT fun1(100)