MySQL存储过程，触发器，游标实例

存储过程实例

1.创建表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `one_data`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '名称',
  `sort_number` int(11) NULL DEFAULT NULL COMMENT '序号',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 472 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci COMMENT = '创建事件每秒添加一条数据' ROW_FORMAT = Dynamic;
 
SET FOREIGN_KEY_CHECKS = 1;

2. 创建存储过程(批量插入数据)

-- 创建存储过程delimiter
-- 这里的delimiter后面一定要加一个空格不然会报错
create procedure doinsert()
begin
 declare i int;set i = 0;
while(i<=200) do
  INSERT INTO `one_data`( `name`, `sort_number`, `create_time`) VALUES (CONCAT('名称',i), i, NOW());
  set i = i+1;
end while;end;


-- 调用存储过程
call doinsert();
select * from `one_data`

MySQl命令行下存储过程

例 1
创建名称为 ShowStuScore 的存储过程，存储过程是从学生成绩信息表中查询学生的成绩信息

mysql> DELIMITER //
     > CREATE PROCEDURE ShowStuScore()
    -> BEGIN
    -> SELECT * FROM tb_students_score;
    -> END //

例 2
创建名称为 GetScoreByStu 的存储过程，输入参数是学生姓名。存储过程的作用是通过输入的学生姓名从学生成绩信息表中查询指定学生的成绩信息

mysql> DELIMITER //
mysql> CREATE PROCEDURE GetScoreByStu
    -> (IN name VARCHAR(30))
    -> BEGIN
    -> SELECT student_score FROM tb_students_score
    -> WHERE student_name=name;
    -> END //

触发器实例

before类型触发器

1. 向数据表 tb_emp8 中插入数据之前，对新插入的 salary 字段值进行求和计算。

mysql> CREATE TRIGGER SumOfSalary
   -> BEFORE INSERT ON tb_emp8
   -> FOR EACH ROW
   -> SET @sum=@sum+NEW.salary;

SET @sum=0;


mysql> INSERT INTO tb_emp8
    -> VALUES(1,'A',1,1000),(2,'B',1,500);    


mysql> SELECT @sum;
+------+
| @sum |
+------+
| 1500 |
+------+

after类型触发器

2. 创建一个名为 double_salary 的触发器，触发的条件是向数据表 tb_emp6 中插入数据之后，再向数据表 tb_emp7 中插入相同的数据，并且 salary 为 tb_emp6 中新插入的 salary 字段值的 2 倍。

mysql> CREATE TRIGGER double_salary
    -> AFTER INSERT ON tb_emp6
    -> FOR EACH ROW
    -> INSERT INTO tb_emp7
    -> VALUES (NEW.id,NEW.name,deptId,2*NEW.salary);
    

mysql> INSERT INTO tb_emp6
    -> VALUES (1,'A',1,1000),(2,'B',1,500);


mysql> SELECT * FROM tb_emp6;
+----+------+--------+--------+
| id | name | deptId | salary |
+----+------+--------+--------+
|  1 | A    |      1 |   1000 |
|  2 | B    |      1 |    500 |
+----+------+--------+--------+

mysql> SELECT * FROM tb_emp7;
+----+------+--------+--------+
| id | name | deptId | salary |
+----+------+--------+--------+
|  1 | A    |      1 |   2000 |
|  2 | B    |      1 |   1000 |
+----+------+--------+--------+

游标

编写两个表 sys_user和 user, 编写存储过程，当 两个表的 id 相同时 将 user 表中的 name 更新为 sys_user 中的 user_name

1. sys_user 和 user 表的创建

-- sys_user 表的创建
CREATE TABLE `sys_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
 
-- user 表的创建
CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. 存储过程的编写

delimiter $$
create procedure user_test()
begin
  --定义变量
  declare sys_user_id bigint;
  declare sys_user_name varchar(11);
  declare done int;
  
-- 创建游标，并存储数据
  DECLARE cur_test CURSOR FOR 
      SELECT id AS user_id,user_name AS sys_user_name  FROM `sys_user`;
  -- 游标中的内容执行完后将 done 设置为 1 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
  -- 打开游标
  OPEN cur_test;
  -- 执行循环
  posLoop:LOOP
  -- 判断是否结束循环
        IF done=1 THEN
        LEAVE posLoop;
        END IF;
  -- 取游标中的值
  FETCH cur_test INTO sys_user_id,sys_user_name;
  -- 执行更新操作
    UPDATE `user` SET NAME=sys_user_name WHERE id=sys_user_id;
  END LOOP posLoop;
  -- 释放游标 
  CLOSE cur_test; 
END $$
DELIMITER
 
 
DROP PROCEDURE user_test;
-- 调用存储过程
CALL user_test;

利用存储过程和游标我们将 user 表的 name 字段进行了 更新。

统计 tb_user 表中 香港演员的数量

1. tb_user 表的创建

CREATE TABLE `tb_user` (
  `user_id` int(12) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `gender` varchar(2) DEFAULT NULL,
  `address` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8

2. 存储过程的创建

-- 统计 香港的演员有多少个
SELECT  COUNT(*) AS username_cnt FROM mybatisdb.`tb_user` WHERE address='香港';
 
DELIMITER $$
   CREATE PROCEDURE statisticUser()
   BEGIN
     --  创建接收游标数据的变量
     DECLARE c INT;
     
     -- 创建总数变量
     DECLARE total INT DEFAULT 0;
     
     -- 创建结束标志变量
     DECLARE done INT DEFAULT 0;
     
     -- 创建游标
     DECLARE cur_test CURSOR FOR
                SELECT  COUNT(*) AS username_cnt FROM mybatisdb.`tb_user` WHERE address='香港';
     -- 指定游标循环结束时的返回值
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     
     -- 设置初始值
     SET total = 0;
     -- 打开游标
     OPEN cur_test;
     -- 开始循环游标里的数据
     read_loop:LOOP
     -- 根据游标当前指向的一条数据
     FETCH cur_test INTO c;
     -- 判断游标的循环是否结束
     IF done THEN
       LEAVE read_loop; -- 跳出游标循环
     END IF;
     -- 获取一条数据时，将 count 值进行累加操作
     SET total = total + c;
     
     -- 结束游标循环
     END LOOP;
     CLOSE cur_test;
     
     -- 输出结果
     SELECT total;
      
   END $$
DELIMITER
 
DROP PROCEDURE statisticUser;
 
CALL statisticUser();