MySQL存储过程和存储函数

一、存储过程和存储函数的概念

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

二、存储过程和存储函数的好处

• 存储过程和存储函数可以重复使用，减轻开发人员的工作量。类似于java开发中方法可以多次调用。
• 减少网络流量，存储过程和函数位于服务器上，调用的时候只需要传递名称和参数即可。
• 减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率。
• 将一些业务逻辑在数据库层面来实现，可以减少代码层面的业务处理。

三、存储过程和函数的区别

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

四、存储过程使用

4.1 创建存储过程

• DELIMITER关键字

-- 标准语法
DELIMITER 分隔符

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

• 创建存储过程

- 修改分隔符为$
DELIMITER $

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

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

-- 创建db8数据库
CREATE DATABASE db8;

-- 使用db8数据库
USE db8;

-- 创建学生表
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,    -- 学生id
    NAME VARCHAR(20),                    -- 学生姓名
    age INT,                            -- 学生年龄
    gender VARCHAR(5),                    -- 学生性别
    score INT                           -- 学生成绩
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,'男',95),(NULL,'李四',24,'男',98),
(NULL,'王五',25,'女',100),(NULL,'赵六',26,'女',90);

-- 按照性别进行分组，查询每组学生的总成绩。按照总成绩的升序排序
SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;

4.2 调用存储过程

-- 标准语法
CALL 存储过程名称(实际参数);

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

4.3 查看存储过程

-- 查询数据库中所有的存储过程 标准语法
SELECT * FROM mysql.proc WHERE db='数据库名称';

4.4 删除存储过程

-- 标准语法
DROP PROCEDURE [IF EXISTS] 存储过程名称;

-- 删除stu_group存储过程
DROP PROCEDURE stu_group;

五、存储过程语法

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

5.1 变量的使用

• 定义变量

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

-- 定义一个int类型变量、并赋默认值为10
DELIMITER $

CREATE PROCEDURE pro_test1()
BEGIN
    DECLARE num INT DEFAULT 10;   -- 定义变量
    SELECT num;                   -- 查询变量
END$

DELIMITER ;

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

• 变量的赋值

-- 标准语法
SET 变量名 = 变量值;

-- 定义字符串类型变量，并赋值
DELIMITER $

CREATE PROCEDURE pro_test2()
BEGIN
    DECLARE NAME VARCHAR(10);   -- 定义变量
    SET NAME = '存储过程';       -- 为变量赋值
    SELECT NAME;                -- 查询变量
END$

DELIMITER ;

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

-- 标准语法
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];

-- 定义两个int变量，用于存储男女同学的总分数
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 ;

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

5.2 if语句的使用

标准语法

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

/*
    定义一个int变量，用于存储班级总成绩
    定义一个varchar变量，用于存储分数描述
    根据总成绩判断：
        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;

    -- 查询总成绩和描述信息
    SELECT total,description;
END$

DELIMITER ;

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

5.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 ;

/*
    输入总成绩变量，代表学生总成绩
    输出分数描述变量，代表学生总成绩的描述
    根据总成绩判断：
        380分及以上  学习优秀
        320 ~ 380    学习不错
        320以下      学习一般
*/
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);

-- 查询总成绩描述
SELECT @description;

• 注：

@变量名:  这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。

@@变量名: 这种在变量前加上 "@@" 符号, 叫做系统变量 

5.4 case语句的使用

标准语法

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

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

5.5 while循环

-- 标准语法
初始化语句;
WHILE 条件判断语句 DO
    循环体语句;
    条件控制语句;
END WHILE;

5.6 repeat循环

标准语法

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

-- 注意：repeat循环是条件满足则停止。while循环是条件满足则执行

5.7 loop循环

-- 标准语法
初始化语句;
[循环名称:] LOOP
    条件判断语句
        [LEAVE 循环名称;]
    循环体语句;
    条件控制语句;
END LOOP 循环名称;

-- 注意：loop可以实现简单的循环，但是退出循环需要使用其他的语句来定义。我们可以使用leave语句完成！
--      如果不加退出循环的语句，那么就变成了死循环。

5.8 游标

标准语法

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

六、存储过程总结

• 存储过程是 事先经过编译并存储在数据库中的一段 SQL 语句的集合。可以在数据库层面做一些业务处理
• 说白了存储过程其实就是将sql语句封装为方法，然后可以调用方法执行sql语句而已
• 存储过程的好处
  • 安全
  • 高效
  • 复用性强

七、存储函数

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

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

• 标准语法

  • 创建存储函数

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

  • 调用存储函数

  -- 标准语法
  SELECT 函数名称(实际参数);
  

  • 删除存储函数

  -- 标准语法
  DROP FUNCTION 函数名称;
  

• 案例演示

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

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

DELIMITER ;

-- 调用fun_test1存储函数
SELECT fun_test1();