存储过程

基本概念

存储过程和存储函数 事先经过编译并存储在数据库中的一段MySQL语句，类似于 Java 当中的方法, 可以对多组 SQL语句,进行封装操作。从 MySQL5.0 版本开始, 支持存储过程和存储函数。

  • 存储函数和存储过程的区别(了解)
  1. 1.存储函数：必须有返回值。
  2. 2.存储过程：可以没有返回值,也可以拥有返回值。
  • 优点和缺点(了解)
  1. 1.优点：
  2.     A.存储过程可以封装 复杂的SQL语句，简化SQL的编写
  3.     B.存储过程可以回传递增，并接受参数
  4.     C.存储过程无法使用select指定来运行，与查看表数据或定义函数不同
  5.     D.存储过程可以用在数据校验，强制实行商业逻辑等。
  7. 2.缺点：
  8.     A.存储过程，往往制定在特定的数据库中，因为支持的编程语言不同，当切换其他厂商数据库时候，需要重写原有的存储过程。
  10.     B.存储过程性能调校与撰写，受限于种种数据库系统。

步骤说明

  1. 1. 准备SQL表数据
  2. 2. 创建存储过程
  3. 3. 调用存储过程
  4. 4. 查看存储过程
  5. 5. 删除存储过程

准备数据

  1. -- 删除数据库 db7
  2. DROP DATABASE IF EXISTS db7;
  4. -- 创建db7数据库
  5. CREATE DATABASE IF NOT EXISTS db7 CHARACTER SET utf8;
  7. -- 使用数据库
  8. USE  db7;
  10. -- 删除表数据
  11. DROP TABLE IF EXISTS student;
  13. -- 创建学生 student 表
  14. CREATE TABLE IF NOT EXISTS student(
  15.     id INT,  -- 学生id
  16.     name VARCHAR(30),    -- 学生姓名
  17.     age INT, -- 学生年龄
  18.     gender VARCHAR(20),  -- 学生性别
  19.     score INT  -- 考试成绩
  20. );
  22. -- 添加数据
  23. INSERT INTO  student VALUES (1,'张三',23,'男',95),(2,'李四',24,'男',98),(3,'王五',24,'女',100),(4,'赵六',26,'女',90);
  25. -- 查看结果
  26. SELECT * FROM student;
  28. -- 按照性别进行分组,查看每组学生的总成绩. 按照总成绩的升序排列
  29. SELECT gender,SUM(score) '总分' FROM student GROUP BY gender ORDER BY '总分' ASC;

创建存储过程

  • 语法
  1. -- 修改结束分隔符
  2. DELIMITER $
  4. -- 创建存储过程
  5. CREATE PROCEDURE  存储过程名称(参数列表)
  6. BEGIN 
  7.     SQL语句列表;
  8. END $
  10. -- 修改结束分隔符(⚠️警告：一定要打空格)
  11. DELIMITER ;
  • 举例
  1. -- 修改结束分隔符
  2. DELIMITER $
  4. -- 创建存储过程
  5. CREATE PROCEDURE stu_group ()
  6. BEGIN
  7.     SELECT gender,SUM(score) FROM student GROUP BY gender;
  8. END $
  10. -- 修改结束分隔符
  11. DELIMITER ;

调用存储过程

  • 语法
  1. -- 调用存储过程
  2. CALL  存储过程名称(实际参数);
  • 举例
  1. -- 案例代码：调用存储过程
  2. call stu_group();

查看存储过程

  • 语法
  1. -- 查看存储过程（语法）
  2. SELECT * FROM mysql.proc WHERE db = '数据库名称';
  • 举例
  1. -- 案例代码: 查看存储过程
  2. SELECT * FROM mysql.proc WHERE db = 'db7';

删除存储过程

  • 语法
  1. -- 删除存储过程（语法）
  2. DROP PROCEDURE IF EXISTS 存储过程名称;
  • 举例
  1. -- 案例代码: 删除存储过程
  2. DROP PROCEDURE IF EXISTS stu_group;

存储过程 变量使用

  • 创建存储过程，并定义变量
  1. -- 修改结束符
  2. delimiter $
  4. -- 创建存储过程
  5. create procedure stu_getsum()
  7. -- 开始
  8. begin
  9.     -- 定义变量total，默认值10（第一种）
  10.     declare total int default 10;
  11.     -- 修改变量total的值（第二种）
  12.     set total=20;
  14.     select 列名 into 变量名 from 表名 where条件；
  15.     -- 查询student表的总分，赋值给变量total（第三种）
  16.     select sum(score) into total from student;
  17.     -- 查询total的结果
  18.     select total;
  19. -- 结束
  20. end $
  22. -- 修改结束符(⚠️警告：一定要打空格)
  23. delimiter ;
  25. 举例:
  26. begin
  27.         declare men,women int;
  28.         select sum(score) into men from student where gender='男'；
  29.         select sum（score） into women from student where='女'；
  30.         select men,women;
  31. end$
  • 调用存储过程
  1. call stu_getsum();

存储过程 if语句

  • 语法
  1. IF  判断条件  THEN  条件满足需要执行的语句;
  2. ELSEIF  判断条件  THEN  条件满足需要执行的语句;
  3. ELSE  其他情况下需要执行的语句;
  4. END IF;
  • 举例
  1. 需求：
  2.     1. 定义一个 int 类型的变量,用于存储班级的总成绩
  3.     2. 定义一个 varchar 类型的变量,用于存储分数的描述
  4.     3. 根据总成绩,进行判断
  5.     380分以上:  学习优秀
  6.     320-380分:  学习不错
  7.     320分以下:  学习一般
  2. -- 创建存储过程
  3. DELIMITER $
  5. CREATE PROCEDURE stu_if()
  6. BEGIN
  7.     -- 定义变量,接收结果,查询总成绩赋值。
  8.     DECLARE message VARCHAR(10); 
  9.     DECLARE total INT;
  11.     SELECT SUM(score) INTO total FROM student;
  12.     IF total > 380 THEN SET message = '学习优秀';
  13.     ELSEIF total >= 320 AND total <= 380 THEN SET message = '学习不错';
  14.     ELSE SET message = '学习一般';
  15.     END IF;
  16.     -- 查询总成绩和描述信息
  17.     SELECT total,message;
  19. END $
  20. DELIMITER ;
  22. -- 第03步：调用存储过程
  23. CALL stu_if();

存储过程 参数传递

  • 语法
  1. CREATE  PROCEDURE  存储过程的名称([IN][OUT][INOUT]  参数名称  参数类型)
  2. BEGIN 
  3.         SQL语句列表;
  4. END $
  • 举例
  1. -- 第01步: 删除存储过程
  2. DROP PROCEDURE IF EXISTS stu_param;
  4. -- 第02步: 创建存储过程,设置为参数传递
  5. DELIMITER $
  6. CREATE PROCEDURE stu_param(IN total INT,INOUT message VARCHAR(20))
  7. BEGIN
  8.     IF total>380 THEN SET message = '学习优秀';
  9.     ELSEIF total>=320 AND total<=380 THEN SET message = '学习不错';
  10.     ELSE SET message = '学习一般';
  11.     END IF;
  12. END $
  13. DELIMITER ;
  15. -- 第03步: 调用存储过程
  16. CALL stu_param(350,@message);
  18. -- 第04步: 查询返回结果
  19. SELECT @message;

存储过程 循环

  • 语法
  1. 初始化语句;
  2. WHILE      条件判断语句 DO
  3.         循环体语句;
  4.         条件控制语句;
  5. END  WHILE;
  • 举例
  1. -- 第01步：删除存储过程
  2. DROP PROCEDURE IF EXISTS stu_loop;
  4. -- 第02步：创建存储过程,计算 1-100 之间的偶数和
  5. DELIMITER $
  7. CREATE PROCEDURE stu_loop()
  8. BEGIN
  9.     DECLARE result INT DEFAULT 0;
  10.     DECLARE num INT DEFAULT 1;
  11.     WHILE num <= 100 DO 
  12.     IF num % 2 = 0 THEN SET result = result + num;
  13.     END IF;
  14.     SET num = num + 1;
  15. END WHILE;
  16. SELECT result;
  17. END $
  19. DELIMITER ;
  21. -- 第03步: 调用存储过程
  22. CALL stu_loop();

存储函数

存储函数和存储过程理解上是一样的，只是存储函数有返回值，必须写return语句。

无参数的存储函数

  • 创建存储函数格式
  1. DELIMITER $
  2. CREATE  FUNCTION  函数名称(参数列表)  RETURNS 返回值类型
  3. BEGIN
  4.     SQL 语句列表;
  5.     RETURN  结果;
  6. END $
  7. DELIMITER ;
  • 创建存储函数举例
  1. -- 查询学生表当中,成绩大于 95分的学生人数。
  3. -- 第01步: 删除存储函数
  4. DROP FUNCTION IF EXISTS stu_hanshu;
  6. -- 第02步: 创建存储函数
  7. DELIMITER $
  8. CREATE FUNCTION  stu_hanshu()  RETURNS INT
  9. BEGIN 
  10.     -- 定义变量,记录查询结果,返回
  11.     DECLARE total INT;
  12.     -- 获取到查询的结果
  13.     SELECT COUNT(*) INTO total FROM student WHERE score>95;
  14.     -- 返回结果
  15.     RETURN total;
  16. END $
  17. DELIMITER ;
  19. -- 第03步: 调用存储函数
  20. SELECT stu_hanshu();

有参数的存储函数

  1. -- 查询学生表当中,成绩大于 95分的学生人数。
  3. -- 第01步: 删除存储函数
  4. DROP FUNCTION IF EXISTS stu_hanshu;
  6. -- 第02步: 创建存储函数
  7. DELIMITER $
  8. CREATE FUNCTION  stu_hanshu()  RETURNS INT
  9. BEGIN 
  10.     -- 定义变量,记录查询结果,返回
  11.     DECLARE total INT;
  12.     -- 获取到查询的结果
  13.     SELECT COUNT(*) INTO total FROM student WHERE score>95;
  14.     -- 返回结果
  15.     RETURN total;
  16. END $
  17. DELIMITER ;
  19. -- 第03步: 调用存储函数
  20. SELECT stu_hanshu();

触发器

触发器就是在表数据发生变化的时候，自动触发的一些 SQL 操作。

这种特性可以协助应用系统在数据库端确保数据的完整性，日志记录，数据校验等操作

使用别名new和old来引用触发器中发生变化的内容记录。

触发器分类

触发器类型 OLD 触发器之前的效果 NEW 触发器之后的效果
INSERT
类型的触发器		 无（因为插入前，没有之前的数据） NEW 表示将要或者已经新增的数据
UPDATE
类型的触发器		 OLD 表示修改之前的数据 NEW 表示将要或者已经修改后的数据
DELETE
类型的触发器		 OLD 表示将要或者已经修改的数据 无（因为删除后，状态无数据）

准备数据

  1. -- 删除数据库
  2. DROP DATABASE IF EXISTS db8;
  4. -- 创建数据库 db8
  5. CREATE DATABASE IF NOT EXISTS db8 CHARSET utf8;
  7. -- 使用数据库
  8. USE db8;
  10. -- 创建账户表
  11. CREATE TABLE IF NOT EXISTS account(
  12.     id INT PRIMARY KEY AUTO_INCREMENT,  -- 账户id
  13.     NAME VARCHAR(20),      -- 姓名
  14.     money DOUBLE        -- 余额
  15. );
  17. -- 添加数据
  18. INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);
  20. -- 创建日志表 account_log
  21. CREATE TABLE IF NOT EXISTS account_log(
  22.     id INT PRIMARY KEY AUTO_INCREMENT,  -- 日志id
  23.     operation VARCHAR(20),      -- 操作的类型(insert update delete)
  24.     operation_time DATETIME,     -- 操作时间
  25.     operation_id INT,        -- 操作表的id
  26.     operation_params VARCHAR(200)    -- 操作参数
  27. );

INSERT 触发器

基础语法

  1. -- 删除触发器
  2. DROP TRIGGER IF EXISTS 触发器的名称;
  4. -- 第02步,创建触发器
  5. DELIMITER $
  7. CREATE TRIGGER  触发器的名称  
  8. AFTER INSERT ON 需要监测的表名称  FOR EACH ROW 
  9. BEGIN
  10.     INSERT INTO 记录结果的表名称 VALUES (值1,值2,值3,值4);
  11. END $
  13. DELIMITER ;

代码举例

  1. -- 创建 INSERT 类型的触发器
  2. -- 第01步,删除触发器
  3. DROP TRIGGER IF EXISTS account_insert;
  5. -- 第02步,创建触发器
  6. DELIMITER $
  8. CREATE TRIGGER account_insert  
  9. AFTER INSERT ON account FOR EACH ROW 
  10. BEGIN
  11.     INSERT INTO account_log VALUES (
  12.         NULL,
  13.         'INSERT',
  14.         NOW(),     -- 使用NOW函数,获取到当前的系统时间
  15.         new.id,
  16.         CONCAT('插入后{id=',new.id,'name=',new.name,'money=',new.money,'}')
  17.     );
  18. END $
  20. DELIMITER ;
  22. -- 第03步,向 account 表当中添加一条记录
  23. INSERT INTO account VALUES (NULL,'王五',2000);
  25. -- 第04步, 查询 account 表记录
  26. SELECT * FROM account;
  28. -- 第05步, 查询 account_log 表记录
  29. SELECT * FROM account_log;

UPDATE 触发器

基础语法

  1. -- 创建触发器, 创建更新的触发器 
  2. DELIMITER $
  4. CREATE TRIGGER  触发器的名称  
  5. AFTER UPDATE ON  需要监测的表名称 FOR EACH ROW 
  6. BEGIN
  7.     INSERT INTO 记录结果的表名称 VALUES (值1,值2,值3,值4);
  8. END $
  10. DELIMITER ;

代码实现

  1. -- 创建 UPDATE 类型的触发器
  3. -- 第01步,删除触发器
  4. DROP TRIGGER IF EXISTS account_update;
  6. -- 第02步,创建触发器
  7. DELIMITER $
  9. CREATE TRIGGER account_update  
  10. AFTER UPDATE ON account FOR EACH ROW 
  11. BEGIN
  12.     INSERT INTO account_log VALUES (
  13.         NULL,
  14.         'UPDATE',
  15.         NOW(),     -- 使用NOW函数,获取到当前的系统时间
  16.         new.id,
  17.         CONCAT('更新前{id=',old.id,'name=',old.name,'money=',old.money,'}',
  18.         '更新后{id=',new.id,'name=',new.name,'money=',new.money,'}')
  19.     );
  20. END $
  22. DELIMITER ;
  24. -- 第03步,向 account 表当中更新一条记录
  25. UPDATE account SET money = money+10000 WHERE id = 1;
  27. -- 第04步, 查询 account 表记录
  28. SELECT * FROM account;
  30. -- 第05步, 查询 account_log 表记录
  31. SELECT * FROM account_log;

DELETE 触发器

基础语法

  1. -- 创建触发器, 创建删除的触发器 
  2. DELIMITER $
  4. CREATE TRIGGER 触发器的名称
  5. AFTER DELETE ON 需要监测的表名称 FOR EACH ROW 
  6. BEGIN
  7.     INSERT INTO 记录结果的表名称 VALUES (值1,值2,值3,值4);
  8. END $
  10. DELIMITER ;

代码实现

  1. -- 创建 DELETE 类型的触发器
  3. -- 第01步,删除触发器
  4. DROP TRIGGER IF EXISTS account_delete;
  6. -- 第02步,创建触发器
  7. DELIMITER $
  9. CREATE TRIGGER account_delete
  10. AFTER DELETE ON account FOR EACH ROW 
  11. BEGIN
  12.     INSERT INTO account_log VALUES (
  13.         NULL,
  14.         'DELETE',
  15.         NOW(),     -- 使用NOW函数,获取到当前的系统时间
  16.         old.id,
  17.         CONCAT('删除前{id=',old.id,'name=',old.name,'money=',old.money,'}')
  18.     );
  19. END $
  21. DELIMITER ;
  23. -- 第03步,向 account 表当中删除一条记录
  24. DELETE FROM account WHERE id = 2;
  26. -- 第04步, 查询 account 表记录
  27. SELECT * FROM account;
  29. -- 第05步, 查询 account_log 表记录
  30. SELECT * FROM account_log;

查看和删除触发器

  1. -- 1. 查看触发器
  2. SHOW  TRIGGERS;
  4. -- 2. 删除触发器
  5. DROP  TRIGGER  触发器的名称;

事务