MySQL游标和触发器的操作流程

游标

使用SELECT语句可以返回符合指定条件的结果集（虚拟表），但没有办法对结果集中的数据进行单独的处理。例如，使用SELECT语句查询出多条员工信息的结果集后，无法获取结果集中的单条记录。为此，mysql提供了游标机制，利用游标可以对结果集中的数据进行单独处理。

游标的操作流程

1. 定义游标

DECLARE 游标名称 CURSON FOR SELECT语句

特点：游标名称必须唯一，因为在存储过程和函数中可以存储多个游标，而游标名就是区分不同游标的唯一标志。另外SELECT语句中不能含有INTO关键字。需要注意的是，变量、错误触发条件、错误处理程序和游标都是通过DECLARE定义的，但他们的定义是有先后顺序要求的。变量和错误触发条件必须在最前面声明，然后是游标的声明，最后才是错误处理程序的声明。

2.打开游标

OPEN游标名称

3.利用游标检索数据

FETCH 游标名称 INTO 变量名1 [,变量名2]...

每执行一次FETCH语句就在结果集中获取一行记录，FETCH语句获取记录后，游标的内部指针就会向前移动一步，指向下一条记录。并将获取到的记录存入对应的变量中，其中变量名的个数要和SELECT语句查询出来的结果集一致。

FETCH语句一般和循环语句一起完成数据的检索，它通常和REPEjsAT循环语句一起使用。因为无法直接判断哪条记录是结果集中的最后一条记录，当利用游标从结果集中检索出最后一条记录后，再次执行FETCH语句，将产生ERROR 1329 （02000）:No data to FETCH错误信息。因此，使用游标时通常自定义错误处理程序处理该错误，从而结束游标的循环。

4.关闭游标

CLOSE 游标名称

在程序内，如果使用CLOSE关闭了游标，则不能再通过FETCH使用该游标。如果想要再次利用游标检索数据，只需要使用OPEN打开游标即可，而不用重新定义游标。如果没有使用CLOSE关闭游标，那么它将在被打开的BEGIN...END语句块的末尾关闭。

例题

技术人员想将员工表emp中奖金为NULL的员工信息存放在一个新的数据表emp_comm中，数据表emp_comm的结构和员工表保持一致

原表如下

创建一个存http://www.chinasem.cn储过程，实现将奖金为NULL的员工信息添加到数据表emp_comm

然后定义存储过程如下

DELIMITER // -- 修改MySQL语句默认结束符号为//
CREATE PROCEDURE proc_emp_comm() -- 创建名为proc_emp_comm()的存储过程
BEGIN -- 开始存储过程
DECLARE mark INT DEFAULT 0; -- 定义了变量mark用于存储游标结束循环的标识
# 定义变量用来存储select语句查询出来的8个字段数据
DECLARE emp_no INT;
DECLARE emp_name VARCHAR(20);
DECLARE emp_job VARCHAR(20);
DECLARE emp_mgr INT;
DECLARE emp_hiredate DATE;
DECLARE emp_sal DECIMAL(7,2);
DECLARE emp_comm DECIMAL(7,2);
DECLARE emp_deptno INT;
DECLARE cur CURSOR FOR SELECT * FROM emp WHERE comm IS NULL; -- # 定义游标
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' -- 定义错误程序及处理方式
 SET mark=1;
# 打开游标
OPEN cur;
# 借助repeat循环，移动指针获取虚拟结果集中的数据，存储到定义的变量中
REPEAT -- 开启循环
FETCH cur INTO emp_no,emp_name,emp_job,emp_mgr,emp_hiredate,emp_sal,emp_comm,emp_deptno; -- 使用上面定义的8个字段去接收查询语句查询出来的数据
IF mark!=1 THEN -- 只要mark值不为1，说明结果集中还有数据，就将数据添加到emp_comm表中
 INSERT INTO emp_comm VALUES(emp_no,emp_namandroide,emp_job,emp_mgr,emp_hiredate,emp_sal,emp_comm,emp_deptno);
END IF; -- 结束if语句
UNTIL mark=1 END REPEAT; -- 结束repeat循环语句
CLOSE cur; -- 关闭游标
END // -- 结束存储过程
DELIMITER ; -- 设置MySQL命令结束符号为;
 

调用存储过程

CALL proc_emp_comm();

查看emp_comm表数据

SELECT * FROM emp_comm;

触发器

在实际开发项目时，如果需要在数据表发生更改时自动进行一些处理，这时就可以使用触发器。

例如，删除一条数据时，需要在数据库中保留一个备份副本，这种情况下可以创建一个触发器对象，每当删除一条数据时，就执行一次备份操作。

触发器可以看成一种特殊的存储过程，它不用CALL语句调用，而是在预选定义好的操作自动调用（INSERT,DELETE)等

触发器具有以下优点：

当触发器相关联的数据表中的数据发生修改时，触发器中定义的语句会自动执行。

触发器对数据进行安全校验，保障数据安全。

通过和触发器相关联的表，可以实现表数据的级联更改，在一定程度上保证数据的完整性。

触发器的基本操作

1.创建触发器

CREATE TRIGGER 触发器名称 触发时机 触发事件 ON 数据表名 FOR EACH ROW 触发程序

• 触发器名称：必须在当前数据库中唯一。如果要在指定的数据库中创建触发器，触发器名称前面应该加上数据库的名称。
• 触发时机：指触发程序执行的时间，可选值有BEFORE和AFTER；其中BEFORE表示在触发事件之前执行触发小程序，AFTER表示在触发事件之后执行触发程序。
• 触发事件：表示激活触发器的操作类型，可选值有INSERT、UPDATE和DELETE；其中INSERT表示将新纪录插入表时激活触发器中的触发程序，UPDATE表示更改表中某一条记录时激活触发器中的触发程序，DELETE表示删除表中某一行记录时激活触发器中的触发程序。
• 触发程序：指的是触发器执行的SQL语句，如果要执行多条语句，可使用BEGIN...END作为语句的开始和结束。触发程序中可以使用NEW和OLD分别表示新记录和旧记录。例如，当需要访问数新插入记录的字段值时，可以使用“NEW.字段名”方式访问；当修改数据表的某条记录时，可以使用“OLD.字段名”访问修改之前的字段值。

2.查看触发器

SHOW TRIGGERS;

利用SELECT语句查看数据库information_schema下数据表trigges中的触发器数据

SELECT * FROM information_schema.triggers [WHERE trigger_name = '触发器名称'];

3. 触发触发器

根据定义的触发器知道，执行删除操作时，会触发触发器的执行(下面的例题)
DELETE FROM emp WHERE empno=8888;

4. 删除触发器

DROP TRIGGER [IF EXISTS] [数据库名.]触发器名;-- DELETE一般删除表中数据,其他为DROP

DROP TRIjavascriptGGER IF EXISTS trig_emp;（下面例题）

例题

技术人员想要在删除员工信息后，自动将删除的员工信息添加在其他数据表，以防后续需要查询被删除的员工信息

 首先创建一个新的表，用来存储删除的数据，这个表的字段和emp表的字段一样

CREATE TABLE `emp_del` (
  `empno` INT DEFAULT NULL,
  `ename` VARCHAR(50) DEFAULT NULL,
  `job` VARCHAR(50) DEFAULT NULL,
  `mgr` INT DEFAULT NULL,
  `hiredate` DATE DEFAULT NULL,
  `sal` DECIMAL(7,2) DEFAULT NULL,
  `comm` DECIMAL(7,2) DEFAULT NULL,
  `deptno` INT DEFAULT NULL
);

接着在员工表emp中创建触发器。当
删除员工表的数据后，触发该触发器，
并且在触发器的触发程序中将被删除的员工信息添加到数据表emp_del

# CREATE TRIGGER 触发器名称 触发时机 触发事件 ON 数据表名 FOR EACH ROW 触发程序

DELIMITER //
CREATE TRIGGER trig_emp
AFTER DELETE ON emp
FOR EACH ROW
BEGIN
    INSERT INTO emp_del(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    VALUES(OLD.empno, OLD.ename, OLD.job, androidOLD.mgr, OLD.hiredate, OLD.sal, OLD.comm, OLD.deptno);
END //
DELIMITER ;

接着根据定义的触发器知道，执行删除操作时，会触发触发器的执行

-- 删除员工编号为 8888 的记录
DELETE FROM emp WHERE empno = 8888;
-- 删除员工编号为 7369 的记录
DELETE FROM emp WHERE empno = 7369;

查看触发器

SHOW TRIGGERS;

到此这篇关于MySQL游标和触发器的操作流程的文章就介绍到这了,更多相关mysql游标和触发器内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程China编程(www.chinasem.cn)！