1.概念
- 存储过程和函数是: 事先经过编译并存储在数据库中的一段SQL语句的集合
2.好处
- 可以重复使用,减轻开发人员的工作量
- 减少数据在数据库和应用服务器之间的传输,可以提高数据处理的效率
- 将一些业务逻辑在数据库层面来实现,可以减少代码层面的业务处理
3.存储过程和函数的区别
- 函数必须有返回值
- 存储过程没有返回值
4.创建存储过程
-- *创建空参存储过程
DELIMITER 分割符
CREATE PROCEDURE 存储过程名称()
BEGIN
sql语句;
-- 两处分隔符要保持一致
END 分割符
DELIMITER;
5.调用存储过程
-- *调用存储过程
CALL 存储过程名称();
6.查看存储过程
-- *查看存储过程
SELECT * FROM mysql.proc WHERE db='数据库名称';
-- proc 指 procedure 的缩写
-- 相当于存储过程是存储在mysql.proc这个表中,这个表中有一个字段db,是当前存储过程所属的数据库
7.删除存储过程
-- *删除存储过程
DROP PROCEDURE [IF EXISTS] 存储过程名称;
-- [IF EXISTS]或写或不写
8. 存储过程语法
8.1变量的使用
-- *定义变量
DECLARE 变量名称 数据类型 [DEFAULT 默认值]
-- DECLARE定义的是局部变量 只能用在BEGIN END 范围之内
-- *变量赋值
SET 变量名称 = 值;
-- *将查询结果赋值给变量
DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
DECLARE men,women INT; -- 定义变量
SELECT SUM(score) INTO men FROM student WHERE gender='男'; -- 计算男同学总分数赋值给men
SELECT SUM(score) INTO women FROM student WHERE gender='女'; -- 计算女同学总分数赋值给women
SELECT men,women; -- 查询变量
END$
DELIMITER ;
8.2 if语句的使用
-- *if语句
IF 判断条件1 THEN 执行SQL语句1;
[ELSEIF 判断条件2 THEN 执行SQL语句2;]
...
[ELSE 执行的sql语句n;]
END IF;
8.3 参数的传递
- 参数传递的语法
DELIMITER $
-- 标准语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
执行的sql语句;
END$
/*
IN:代表输入参数,需要由调用者传递实际数据。默认的
OUT:代表输出参数,该参数可以作为返回值
INOUT:代表既可以作为输入参数,也可以作为输出参数
*/
DELIMITER ;
- 输入参数
- 标准语法
DELIMITER $
-- 标准语法
CREATE PROCEDURE 存储过程名称(IN 参数名 数据类型)
BEGIN
执行的sql语句;
END$
DELIMITER ;
- 输出参数
- 标准语法
DELIMITER $
-- 标准语法
CREATE PROCEDURE 存储过程名称(OUT 参数名 数据类型)
BEGIN
执行的sql语句;
END$
DELIMITER ;
- 小知识
@变量名: 这种变量要在变量名称前面加上“@”符号,叫做用户会话变量,代表整个会话过程他都是有作用的,这个类似于全局变量一样。
@@变量名: 这种在变量前加上 "@@" 符号, 叫做系统变量
8.4 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;
8.5 while循环
- 标准语法
-- 标准语法
初始化语句;
WHILE 条件判断语句 DO
循环体语句;
条件控制语句;
END WHILE;
8.6 repeat循环
- 标准语法
-- 标准语法
初始化语句;
REPEAT
循环体语句;
条件控制语句;
UNTIL 条件判断语句
END REPEAT;
-- 注意:repeat循环是条件满足则停止。while循环是条件满足则执行
8.7 loop循环
- 标准语法
-- 标准语法
初始化语句;
[循环名称:] LOOP
条件判断语句
[LEAVE 循环名称;]
循环体语句;
条件控制语句;
END LOOP 循环名称;
-- 注意:loop可以实现简单的循环,但是退出循环需要使用其他的语句来定义。我们可以使用leave语句完成!
-- 如果不加退出循环的语句,那么就变成了死循环。
8.8 游标
- 游标的概念
- 游标可以遍历返回的多行结果,每次拿到一整行数据
- 在存储过程和函数中可以使用游标对结果集进行循环的处理
- 简单来说游标就类似于集合的迭代器遍历
- MySQL中的游标只能用在存储过程和函数中
- 游标的语法
- 创建游标
-- 标准语法
DECLARE 游标名称 CURSOR FOR 查询sql语句;
- 打开游标
-- 标准语法
OPEN 游标名称;
- 使用游标获取数据
-- 标准语法
FETCH 游标名称 INTO 变量名1,变量名2,...;
- 关闭游标
-- 标准语法
CLOSE 游标名称;
- 游标的优化使用(配合循环使用)
/*
当游标结束后,会触发游标结束事件。我们可以通过这一特性来完成循环操作
加标记思想:
1.定义一个变量,默认值为0(意味着有数据)
2.当游标结束后,将变量值改为1(意味着没有数据了)
*/
-- 1.定义一个变量,默认值为0(意味着有数据)
DECLARE flag INT DEFAULT 0;
-- 2.当游标结束后,将变量值改为1(意味着没有数据了)
DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
9. 存储函数
- 存储函数和存储过程是非常相似的。存储函数可以做的事情,存储过程也可以做到!
- 存储函数有返回值,存储过程没有返回值(参数的out其实也相当于是返回数据了)
- 标准语法
- 创建存储函数
DELIMITER $
-- 标准语法
CREATE FUNCTION 函数名称([参数 数据类型])
RETURNS 返回值类型
BEGIN
执行的sql语句;
RETURN 结果;
END$
DELIMITER ;
- 调用存储函数
-- 标准语法
SELECT 函数名称(实际参数); -- 调用存储函数,使用的是select,不是call
- 删除存储函数
-- 标准语法
DROP FUNCTION 函数名称;