江哥MySQL视频学习笔记--第12章 MySQL游标和触发器

1、游标的使用

在MySQL数据库查询中，可以使用SELECT语句将满足条件的数据一起查询出来，但是没有办法一行一行地获取数据并做一些复杂的处理。如满足条件的有10条语句，那么使用SELECT语句就一起查询出10条，而获取一行之后接着处理一行数据SELECT语句是做不到的，这时游标就有了用武之地。游标是一个存储在MySQL数据库里用来查询的对象，它可以每次从结果集里获取一行数据，进行相应的处理。有了游标，就可以滚动查询满足条件的数据，可以做一些复杂的处理。

MySQL游标只能用于存储过程或者函数中。

 

（1）创建游标。

declare 游标名 cursor for 查询语句;

（2）打开游标。

open 游标名

（3）使用游标。

FETCH 游标名 INTO 变量名1,变量名2,...；

（4）关闭游标。游标处理完成后需要关闭，使用CLOSE CURSOR语句来关闭游标。

CLOSE 游标名;

举例：

DROP PROCEDURE IF EXISTS pro_cursor1;

CREATE PROCEDURE pro_cursor1()
BEGIN
    DECLARE em_name VARCHAR(255) DEFAULT '';
    
    DECLARE cursor1 CURSOR FOR SELECT name FROM employee;
    
    OPEN cursor1;
    
    FETCH cursor1 INTO em_name;
    SELECT em_name FROM DUAL;
    FETCH cursor1 INTO em_name;
    SELECT em_name FROM DUAL;
    FETCH cursor1 INTO em_name;
    SELECT em_name FROM DUAL;
    FETCH cursor1 INTO em_name;
    SELECT em_name FROM DUAL;
    
    CLOSE cursor1;
END;

CALL pro_cursor1();

2、游标的循环执行

DROP PROCEDURE IF EXISTS pro_cursor2;

CREATE PROCEDURE pro_cursor2()
BEGIN
    DECLARE em_name VARCHAR(255) DEFAULT '';
    DECLARE em_salary INT DEFAULT 0;
    DECLARE sum INT DEFAULT 0;
    
    DECLARE cursor1 CURSOR FOR SELECT name,salary FROM employee;
    SELECT COUNT(*) INTO sum FROM employee;
    OPEN cursor1;
    
    WHILE sum>0 DO
        FETCH cursor1 INTO em_name,em_salary;
        SELECT em_name,em_salary FROM DUAL;
        SET sum=sum-1;
    END WHILE;

    
    
    CLOSE cursor1;
END;

CALL pro_cursor2();

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS pro_cursor3;

CREATE PROCEDURE pro_cursor3()
BEGIN
    DECLARE em_name varchar(255) default '';
    DECLARE em_salary varchar(255) default '';
    DECLARE sum INT default 3;
    DECLARE flag INT default 0;
    
    
    DECLARE cursor1 CURSOR FOR SELECT name,salary FROM employee;
    DECLARE EXIT HANDLER FOR NOT found SET flag=1;
    
    OPEN cursor1;    
    WHILE flag=0 DO
        FETCH cursor1 INTO em_name,em_salary;
        SELECT em_name,em_salary FROM dual;
        SET sum=sum-1;
    END WHILE;
    CLOSE cursor1;
END;

CALL pro_cursor3();

 

3、触发器

触发器是一种与表有关的操作对象，当表上出现指定事件（INSERT、UPDATE、DELETE）时，会调用触发器对象，执行触发器的操作，如在主表上执行删除语句，可以调用触发器执行删除子表语句，以达到级联删除的效果。

4、创建触发器

 

创建触发器和创建存储过程类似，基本语法如下。

CREATE

      [DEFINER= { user | CURRENT_USER }]

      TRIGGER trigger_name

      trigger_time trigger_event

      ON tbl_name FOR EACH ROW

      trigger_body

例如：

CREATE

      TRIGGER add_data

      AFTER INSERT

      ON t1 FOR EACH ROW

  BEGIN

       INSERT INTO t2 VALUES('你好');

END

（1）TRIGGER是触发器的关键字，用来标识触发器。

（2）trigger_name：触发器名称，不能与已有触发器的名称重复。

（3）trigger_time：触发器触发的时机，只有两个值，即BEFORE 和AFTER。

（4）trigger_event：触发器触发的事件，只有三个值，即INSERT、UPDATE、DELETE。

（5）tbl_name：建立触发器的表名，就是在哪张表添加触发器。

（6）FOR EACH ROW：在表的每一行操作。

（7）trigger_body：触发器程序体，可以是一句SQL语句，或者用BEGIN和END包含的多条语句。

（8）根据触发的时机和事件，可以建立6种类型的触发器：插入之前（BEFOREINSERT）触发器、更新之前（BEFORE UPDATE）触发器、删除之前（BEFORE DELETE）触发器、插入之后（AFTERINSERT）触发器、更新之后（AFTER UPDATE）触发器、删除之后（AFTER DELETE）触发器。

（9）一个表上不能同时建立两个类型相同的触发器，如建立一个插入之前触发器后，再建立一个插入之前触发器是不允许的。

CREATE TABLE IF NOT EXISTS user(
    id INT auto_increment PRIMARY KEY,
    name VARCHAR(255),
    age TINYINT
);

CREATE TABLE IF NOT EXISTS user_num(
    id INT auto_increment PRIMARY KEY,
    num int
);

INSERT INTO user_num VALUES(1,0);

CREATE TRIGGER tri_num AFTER INSERT ON user FOR EACH ROW
BEGIN
    UPDATE user_num SET num=num+1 WHERE id=1;
END;

INSERT INTO user values(NULL,'wangwu',28);
SELECT * FROM user_num;

CREATE TRIGGER tri_num2 AFTER DELETE ON user FOR EACH ROW
BEGIN
    UPDATE user_num SET num=num-1 WHERE id=1;
END;
 

 

 

6.3.2　NEW和OLD关键字

   触发器用于一张表的某一行数据，如果想在触发器里使用到这行数据，怎么办呢？MySQL数据库定义了NEW和OLD关键字，用来记录触发器的那一行数据。

（1）对于INSERT型触发器，NEW关键字用来表示将要（BEFORE）或已经（AFTER）插入的新数据。

（2）对于UPDATE型触发器，OLD关键字用来表示将要（BEFORE）或已经（AFTER）被修改的原数据，NEW用来表示将要（BEFORE）或已经（AFTER）修改为的新数据。

（3）对于DELETE 型触发器，OLD 用来表示将要（BEFORE）或已经（AFTER）被删除的原数据。

（4）OLD 是只读的，而 NEW 则可以在触发器中使用 SET 赋值。

（5）使用方法：NEW.columnName （columnName 列名）。

 

CREATE TRIGGER tri_del_employ AFTER DELETE ON department FOR EACH ROW
BEGIN
    DELETE FROM employee WHERE depart_id=old.id;
END;

DELETE FROM department WHERE id=5;

SELECT * FROM employee;

 

6.3.3　查看和删除触发器

（1）查看触发器。查看触发器和查看数据库表一样，通过SHOW TRIGGERS就可以查看触发器。

（2）删除触发器。删除触发器就像删除数据库表一样简单，使用关键字TRIGGER来标识触发器，然后使用DROP操作删除指定名称的触发器。

6.3.4　INSERT型触发器

   INSERT型触发器分为插入之前（BEFORE）触发器和插入之后（AFTER）触发器，NEW关键字用来表示将要（BEFORE）或已经（AFTER）插入的新数据。

   下面创建一个员工表employee，包含员工id、姓名name、工资salary，再创建一个记录表record，包含员工id、姓名name、工资salary，如果员工工资低于3500元，则记录到低工资record表中，否则就不记录。

CREATE TRIGGER addRecord AFTER INSERT ON employee FOR EACH ROW
BEGIN
    if new.salary<=30000 THEN
    INSERT INTO record values(new.id,new.name,new.depart_id,new.salary);
    end if;
END;
INSERT INTO employee VALUES(14,'韦小宝',4,20000);

 

6.3.5　UPDATE型触发器

   UPDATE型触发器分为更新之前（BEFORE）触发器和更新之后（AFTER）触发器。对于UPDATE型触发器，OLD关键字用来表示将要（BEFORE）或已经（AFTER）被修改的原数据，NEW用来表示将要（BEFORE）或已经（AFTER）修改为的新数据。

在插入型触发器里已经建好了两个表：员工表employee、记录表record，工资低于3500元的员工会被记录到record表里，如果员工涨工资后工资超过了3 500元，那么就得从记录表record中删除这名员工，如果员工更改工资后工资低于3500元，那么就要更改记录表record中的数据，这时就需要使用UPDATE型触发器。

 

CREATE TRIGGER updateRecord AFTER UPDATE ON employee FOR EACH ROW
BEGIN
    IF NEW.salary>30000 THEN
        DELETE FROM record WHERE id=new.id;
    ELSE
        REPLACE INTO record VALUES(new.id,new.name,new.depart_id,new.salary);
    END IF;
END;
UPDATE employee SET salary=10000 WHERE id=4;

 

6.3.6　DELETE型触发器

   DELETE型触发器分为删除之前（BEFORE）触发器和删除之后（AFTER）触发器，对于DELETE型触发器，OLD用来表示将要（BEFORE）或已经（AFTER）被删除的原数据。

   在插入型触发器里已经建好了两个表：员工表employee、记录表record，工资低于3500元的员工会被记录到record表里。如果在员工表中进行了删除员工的操作，那么在记录表中若记录了该员工，则应该一起删除，这时就需要使用DELETE型触发器。

 

CREATE TRIGGER delRecord AFTER DELETE ON employee FOR EACH ROW
BEGIN
    DELETE FROM record WHERE id=old.id;
END;
DELETE FROM employee WHERE id=14;