存储过程

存储过程

MySQL

发布日期: 2021-06-02

文章字数: 2.4k

阅读时长: 9 分

基本介绍

存储过程和函数：存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合

存储过程和函数的好处：

提高代码的复用性
减少数据在数据库和应用服务器之间的传输，提高传输效率
减少代码层面的业务处理
一次编译永久有效

存储过程和函数的区别：

存储函数必须有返回值
存储过程可以没有返回值

基本操作

DELIMITER：

DELIMITER 关键字用来声明 sql 语句的分隔符，告诉 MySQL 该段命令已经结束
MySQL 语句默认的分隔符是分号，但是有时需要一条功能 sql 语句中包含分号，但是并不作为结束标识，这时使用 DELIMITER 来指定分隔符：
```
DELIMITER 分隔符
```

存储过程的创建调用查看和删除：

创建存储过程

-- 修改分隔符为$
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
	sql语句;
END$

-- 修改分隔符为分号
DELIMITER ;

调用存储过程
```
CALL 存储过程名称(实际参数);
```

查看存储过程

SELECT * FROM mysql.proc WHERE db='数据库名称';

删除存储过程

DROP PROCEDURE [IF EXISTS] 存储过程名称;

一个简单示例：

数据准备————student表

id	NAME	age		gender	score
1	张三		23		男		95
2	李四		24		男		98
3	王五		25		女		100
4	赵六		26		女		90

创建 stu_group() 存储过程，封装分组查询总成绩，并按照总成绩升序排序的功能

DELIMITER $

CREATE PROCEDURE stu_group()
BEGIN
	SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC; 
END$

DELIMITER ;

-- 调用存储过程
CALL stu_group();
-- 删除存储过程
DROP PROCEDURE IF EXISTS stu_group;

存储语法

变量使用

存储过程是可以进行编程的，意味着可以使用变量、表达式、条件控制语句等，来完成比较复杂的功能

定义变量：DECLARE 定义的是局部变量，只能用在 BEGIN END 范围之内
```
DECLARE 变量名 数据类型 [DEFAULT 默认值];
```

变量的赋值

SET 变量名 = 变量值;
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];

定义两个 int 变量，用于存储男女同学的总分数

DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
	-- 定义两个变量
	DECLARE men,women INT;
	-- 查询男同学的总分数，为men赋值
	SELECT SUM(score) INTO men FROM student WHERE gender='男';
	-- 查询女同学的总分数，为women赋值
	SELECT SUM(score) INTO women FROM student WHERE gender='女'; 
	-- 使用变量
	SELECT men,women;
END$
DELIMITER ;
-- 调用存储过程
CALL pro_test3();

参数传递

参数传递的语法

IN：代表输入参数，需要由调用者传递实际数据，默认的
OUT：代表输出参数，该参数可以作为返回值
INOUT：代表既可以作为输入参数，也可以作为输出参数
```
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
	执行的sql语句;
END$

DELIMITER ;
```

输入总成绩变量，代表学生总成绩，输出分数描述变量，代表学生总成绩的描述

DELIMITER $

CREATE PROCEDURE pro_test6(IN total INT, OUT description VARCHAR(10))
BEGIN
	-- 判断总分数
	IF total >= 380 THEN 
		SET description = '学习优秀';
	ELSEIF total >= 320 AND total < 380 THEN 
		SET description = '学习不错';
	ELSE 
		SET description = '学习一般';
	END IF;
END$

DELIMITER ;
-- 调用pro_test6存储过程
CALL pro_test6(310,@description);
CALL pro_test6((SELECT SUM(score) FROM student), @description);
-- 查询总成绩描述
SELECT @description;

查看参数方法
- @变量名 : 用户会话变量，代表整个会话过程他都是有作用的，类似于全局变量
- @@变量名 : 系统变量

IF语句

if 语句标准语法

IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;

根据总成绩判断：全班 380 分及以上学习优秀、320 ~ 380 学习良好、320 以下学习一般

DELIMITER $
CREATE PROCEDURE pro_test4()
BEGIN
	DECLARE total INT;							-- 定义总分数变量
	DECLARE description VARCHAR(10);			-- 定义分数描述变量
	SELECT SUM(score) INTO total FROM student; 	-- 为总分数变量赋值
	-- 判断总分数
	IF total >= 380 THEN
		SET description = '学习优秀';
	ELSEIF total >=320 AND total < 380 THEN
		SET description = '学习良好';
	ELSE
		SET description = '学习一般';
	END IF;
END$
DELIMITER ;
-- 调用pro_test4存储过程
CALL pro_test4();

CASE

标准语法 1

CASE 表达式
    WHEN 值1 THEN 执行sql语句1;
    [WHEN 值2 THEN 执行sql语句2;]
    ...
    [ELSE 执行sql语句n;]
END CASE;

标准语法 2

CASE
    WHEN 判断条件1 THEN 执行sql语句1;
    [WHEN 判断条件2 THEN 执行sql语句2;]
    ...
    [ELSE 执行sql语句n;]
END CASE;

演示

DELIMITER $
CREATE PROCEDURE pro_test7(IN total INT)
BEGIN
	-- 定义变量
	DECLARE description VARCHAR(10);
	-- 使用case判断
	CASE
	WHEN total >= 380 THEN
		SET description = '学习优秀';
	WHEN total >= 320 AND total < 380 THEN
		SET description = '学习不错';
	ELSE 
		SET description = '学习一般';
	END CASE;
	
	-- 查询分数描述信息
	SELECT description;
END$
DELIMITER ;
-- 调用pro_test7存储过程
CALL pro_test7(390);
CALL pro_test7((SELECT SUM(score) FROM student));

WHILE

while 循环语法

WHILE 条件判断语句 DO
	循环体语句;
	条件控制语句;
END WHILE;

计算 1~100 之间的偶数和

DELIMITER $
CREATE PROCEDURE pro_test6()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- while循环
	WHILE num <= 100 DO
		IF num % 2 = 0 THEN
			SET result = result + num;
		END IF;
		SET num = num + 1;
	END WHILE;
	-- 查询求和结果
	SELECT result;
END$
DELIMITER ;

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

REPEAT

repeat 循环标准语法

初始化语句;
REPEAT
	循环体语句;
	条件控制语句;
	UNTIL 条件判断语句
END REPEAT;

计算 1~10 之间的和

DELIMITER $
CREATE PROCEDURE pro_test9()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- repeat循环
	REPEAT
		-- 累加
		SET result = result + num;
		-- 让num+1
		SET num = num + 1;
		-- 停止循环
		UNTIL num > 10
	END REPEAT;
	-- 查询求和结果
	SELECT result;
END$

DELIMITER ;
-- 调用pro_test9存储过程
CALL pro_test9();

LOOP

LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现，如果不加退出循环的语句，那么就变成了死循环

loop 循环标准语法

[循环名称:] LOOP
	条件判断语句
		[LEAVE 循环名称;]
	循环体语句;
	条件控制语句;
END LOOP 循环名称;

计算 1~10 之间的和

DELIMITER $
CREATE PROCEDURE pro_test10()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- loop循环
	l:LOOP
		-- 条件成立，停止循环
		IF num > 10 THEN
			LEAVE l;
		END IF;
		-- 累加
		SET result = result + num;
		-- 让num+1
		SET num = num + 1;
	END LOOP l;
	-- 查询求和结果
	SELECT result;
END$
DELIMITER ;
-- 调用pro_test10存储过程
CALL pro_test10();

游标

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理

游标可以遍历返回的多行结果，每次拿到一整行数据
简单来说游标就类似于集合的迭代器遍历
MySQL 中的游标只能用在存储过程和函数中

相关语法：

创建游标

DECLARE 游标名称 CURSOR FOR 查询sql语句;

打开游标
```
OPEN 游标名称;
```

使用游标获取数据

FETCH 游标名称 INTO 变量名1,变量名2,...;

关闭游标
```
CLOSE 游标名称;
```
Mysql 通过一个 Error handler 声明来判断指针是否到尾部，并且必须和创建游标的 SQL 语句声明在一起：
```
DECLARE EXIT HANDLER FOR NOT FOUND (do some action，一般是设置标志变量)
```

游标的基本使用：

创建 stu_score 表

CREATE TABLE stu_score(
	id INT PRIMARY KEY AUTO_INCREMENT,
	score INT
);

将student表中所有的成绩保存到stu_score表中

DELIMITER $

CREATE PROCEDURE pro_test12()
BEGIN
	-- 定义成绩变量
	DECLARE s_score INT;
	-- 定义标记变量
	DECLARE flag INT DEFAULT 0;
	
	-- 创建游标，查询所有学生成绩数据
	DECLARE stu_result CURSOR FOR SELECT score FROM student;
	-- 游标结束后，将标记变量改为1  这两个必须声明在一起
	DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
	
	-- 开启游标
	OPEN stu_result;
	-- 循环使用游标
	REPEAT
		-- 使用游标，遍历结果,拿到数据
		FETCH stu_result INTO s_score;
		-- 将数据保存到stu_score表中
		INSERT INTO stu_score VALUES (NULL,s_score);
	UNTIL flag=1
	END REPEAT;
	-- 关闭游标
	CLOSE stu_result;
END$

DELIMITER ;

-- 调用pro_test12存储过程
CALL pro_test12();
-- 查询stu_score表
SELECT * FROM stu_score;

存储函数

存储函数和存储过程是非常相似的，存储函数可以做的事情，存储过程也可以做到

存储函数有返回值，存储过程没有返回值（参数的 out 其实也相当于是返回数据了）

创建存储函数

DELIMITER $
-- 标准语法
CREATE FUNCTION 函数名称(参数 数据类型)
RETURNS 返回值类型
BEGIN
	执行的sql语句;
	RETURN 结果;
END$

DELIMITER ;

调用存储函数，因为有返回值，所以使用 SELECT 调用
```
SELECT 函数名称(实际参数);
```
删除存储函数
```
DROP FUNCTION 函数名称;
```

定义存储函数，获取学生表中成绩大于95分的学生数量

DELIMITER $
CREATE FUNCTION fun_test()
RETURN INT
BEGIN
	-- 定义统计变量
	DECLARE result INT;
	-- 查询成绩大于95分的学生数量，给统计变量赋值
	SELECT COUNT(score) INTO result FROM student WHERE score > 95;
	-- 返回统计结果
	SELECT result;
END
DELIMITER ;
-- 调用fun_test存储函数
SELECT fun_test();

DestiNation

https://li-zhenhua.github.io/2021/06/02/cun-chu-guo-cheng/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 DestiNation !

存储过程

MySQL的MRR优化

2021-06-14 MySQL

MRR

MySQL执行计划解析

2021-05-25 MySQL

MySql 执行计划 Explain