MySQL学习总结（错误处理、游标、触发器）

目录

一、错误处理

1、自定义错误名称

2、自定义错误处理程序

 二、游标

1、操作流程

（1）定义游标

（2）打开游标

（3）利用游标检索数据

（4）关闭游标

2、使用游标检索数据

三、触发器

1、 触发器概述

2、触发器的基本操作

（1）创建触发器

（2）查看触发器

（3）触发触发器

（4）删除触发器

---

      学习MySQL的错误处理、游标、触发器

一、错误处理

1、自定义错误名称

       在MySQL中可以使用DECLARE 语句为错误声明一个名称，声明的

基本语法格式

DECLARE 错误名称 CONDITION FOR 错误类型;

       在上述语法格式中，错误名称指自定义的错误名称。错误类型有两种可选值，分别为mysql_error_code 和SQLSTATE [ VALUE ] sqlstate_value,其中 mysq_error_code是MySQL数值类型的错误代码；sqlstate_value是长度为5的字符串类型的错误代码。mysql_error_code和sqlstate_value 都可以表示MySQL错误。

       例如：参考如下错误信息

ERROR 1062 (23000): Duplicate entry '9839' for key emp.PRIMARY'

       上述错误信息是在插入重复的主键值时抛出的错误信息，其中1062是一个mysqloror code类型的错误代码，23000是对应的SQLSTATE类型的错误代码。

      例如：使用DECLARE 语句为上述错误代码声明一个名称

      在上述语句中，DECLARE语句将错误代码SQLSTATE '23000' 命名为duplicate_entry，在处理错误的程序中可以使用该名称表示错误代码SQLSTATE'23000'。

      另外，以上示例DECLARE语句中还可以为mysql_error_code类型的错误代码定义名称

DECLARE duplicate_entry CONDITION FOR 1062;

2、自定义错误处理程序

      自定义错误处理程序的基本语法格式

DECLARE 错误处理方式 HANDLER FOR 错误类型 [,错误类型...] 程序语句段

      在上述语法格式中，MySQL支持的错误处理方式有CONTINUE和EXIT，其中CONTINUE表示遇到错误不进行处理，继续向下执行；EXIT表示遇到错误后马上退出。程序语句段表示在遇到定义的错误时需要执行的一些存储过程或函数。

错误类型有6种可选值，分别如下。

• sqlstate_value：匹配SQLSTATE错误代码。
• condition name：匹配DECLARE定义的错误条件名称。
• SQLWARNING：匹配所有以01开头的SQLSTATE错误代码。
• NOT FOUND：匹配所有以02开头的SQLSTATE错误代码。
• SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码。
• mysql_error_code：匹配mysql_error_code类型的错误代码。

      例如，由于员工表中设有主键，如果在存储过程中往员工表中插入多条数据，当插入的数据中有相同的主键值时会使存储过程执行出现错误，导致程序中断。此时可以通过自定义错误处理程序确保存储过程的执行不被中断

​​​​​​​

       错误处理的语句要定义在BEGIN...END中，并且在程序代码之前。

      例如：创建存储过程proc handler_err2后，调用它并查询当前会话变量num的值

 二、游标

1、操作流程

（1）定义游标

       MySQL中使用DECLARE关键字定义游标，因为游标要操作的是SELECT语句返回的结果集，所以定义游标时需要指定与其关联的SELECT语句。

定义游标的基本语法格式

DECLARE 游标名称 CURSOR FOR SELECT 语句

      在上述语法格式中，游标名称必须唯一，因为在存储过程和函数中可以存在多个游标，而游标名称是唯一用于区分不同游标的标识。需要注意的是，SELECT语句中不能含有INTO关键字。

      使用DECLARE...CURSOR FOR语句定义游标时，因为与游标相关联的SELECT语句并不会立即执行，所以此时MySQL服务器的内存中并没有SELECT语句的查询结果集。

需要注意的是，变量、错误触发条件、错误处理程序和游标都是通过DECLARE定义的，但它们的定义是有先后顺序要求的。变量和错误触发条件必须在最前面声明，然后是游标的声明，最后才是错误处理程序的声明。

（2）打开游标

      声明游标之后，要想从游标中提取数据，需要先打开游标。在MySQL中，打开游标通过OPEN关键字实现，

其语法格式

OPEN 游标名称

      打开游标后，SELECT 语句根据查询条件将查询到的结果存储到MySQL服务器的内存中。

（3）利用游标检索数据

      打开游标之后，就可以通过游标检索SELECT语句返回的结果集中的数据。游标检索数据的

基本语法格式

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

       每执行一次FETCH语句就在结果集中获取一行记录，FETCH语句获取记录后，游标的内部指针就会向前移动一步，指向下一条记录。在上述语法格式中，FETCH语句根据指定的游标名称，将检索出来的数据存放到对应的变量中，变量名的个数需要和SELECT语句查询的结果集的字段个数保持一致。

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

（4）关闭游标

      游标检索完数据后，应该利用MySQL提供的语法关闭游标，释放其占用的MySQL服务器的内存资源。

关闭游标的基本语法格式

CLOSE 游标名称

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

2、使用游标检索数据

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

       首先创建用来存放结果数据的数据表 emp_comm

 

      接着创建存储过程，在存储过程中奖金为NULL的员工信息添加到数据表中emp_comm

 

       在调用存储过程之前，先查看员工表emp中的记录

      查看数据表emp_comm中的记录

三、触发器

1、 触发器概述

      触发器可以看作一种特殊的存储过程，它与存储过程的区别在于，存储过程使用CALL语句调用时才会执行，而触发器会在预先定义好的事件（例如INSERT、DELETE等操作）发生时自动调用。

      创建触发器时需要与数据表相关联，当数据表发生特定事件时，会自动执行触发器中的SQL语句。例如，插入数据前强制检验或转换数据等操作，或是在触发器中的代码执行错误后，撤销已经执行成功的操作，保障数据的安全。

      从上述内容可以知道，触发器具有以下优点。

• 当触发器相关联的数据表中的数据发生修改时，触发器中定义的语句会自动执行。
• 触发器对数据进行安全校验，保障数据安全。
• 通过和触发器相关联的表，可以实现表数据的级联更改，在一定程度上保证数据的完整性。

2、触发器的基本操作

（1）创建触发器

创建触发器时，需要指定其操作的数据表。

基本语法格式

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

      在上述语法格式中，触发器的名称必须在当前数据库中唯一。如果要在指定的数据库中创建触发器，触发器名称前面应该加上数据库的名称。

      触发时机指触发程序执行的时间，可选值有BEFORE和AFTER；其中BEFORE表示在触发事件之前执行触发程序，AFTER表示在触发事件之后执行触发程序。触发事件表示激活触发器的操作类型，可选值有INSERT、UPDATE和DELETE：其中INSERT表示将新记录插入表时激活触发器中的触发程序，UPDATE表示更改表中某一行记录时激活触发器中的触发程序，DELETE 表示删除表中某一行记录时激活触发器中的触发程序。

       触发程序指的是触发器执行的SQL语句，如果要执行多条语句，可使用BEGIN...END作为语句的开始和结束。触发程序中可以使用NEW和OLD分别表示新记录和旧记录，例如，当需要访问新插入记录的字段值时，可以使用“ NEW.字段名 ”方式访问；当修改数据表的某条记录时，可以使用“ OLD.字段名 ”访问修改之前的字段值。

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

      首先创建一张新数据表，用于存放被删除的员工信息

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

（2）查看触发器

      如果想通过语句查看数据库中已经存在的触发器的信息，可以采用两种方法：一种是利用SHOW TRIGGERS语句查看触发器，另一种是利用SELECT语句查看数据库information_schema下数据表triggers中的触发器数据。

      利用SHOW TRIGGERS语句查看触发器信息的语法格式

SHOW TRIGGERS

      例如：下面使用SHOW TRIGGERS语句查看当前数据库中已经存在的触发器（为了让查询出的信息纵向显示，可以使SHOW TRIGGERS语句以\G结尾）

        在上述执行结果中，Trigger表示触发器的名称，Event表示激活触发器的操作类型，Table表示触发器创建在哪个数据表中，Statement表示触发器激活时执行的语句，Timing表示触发器的触发时机。除此之外，SHOW TRIGGERS语句还显示了创建触发器的日期时间、触发器执行时有效的SQL模式及创建触发器的账户信息等。

      在MySQL中，触发器信息都保存在数据库information_schema下的数据表 triggers中，可以通过SELECT 语句查看该数据表获取触发器信息。

      通过 triggers数据表查询触发器的语法格式

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

       在上述语法格式中，可以通过WHERE子句指定触发器的名称，如果不指定触发器名称，则会查询出information_schema数据库中所有已经存在的触发器信息。

      下面使用SELECT语句查询触发器trig_emp的信息

       从上述执行结果可以看出，使用SELECT语句查询出的触发器信息比使用SHOW TRIGGERS语句更丰富。其中TRIGGER_SCHEMA表示触发器所在的数据库名称；ACTION_ORIENTATION的值为ROW，表示操作每条记录都会触发触发器。此外，通过triggers数据表还查询出创建触发器的日期时间、触发器执行时有效的SQL模式等信息。

（3）触发触发器

      触发器 trig_emp创建成功后，会根据触发时机和触发事件触发。

      例如，技术人员需要删除员工表中的一条员工记录，并且想要在删除操作后查看数据表emp_del中的记录，以验证触发器是否触发

       从上述执行结果可以看出，删除员工表emp中员工陈二的记录后，数据表emp_del中新增了一条记录。由此可以得出，对员工表emp执行删除操作后，触发了触发器trig_emp。

（4）删除触发器

        当创建的触发器不再符合当前需求时，可以将它删除。删除触发器的操作很简单，只需要使用MySQL提供的DROP TRIGGER语句即可。

DROP TRIGGER语句的基本语法格式

DROP TRIGGER [IF EXISTS ] [数据库名.] 触发器名;

      在上述语法格式中，利用“ 数据库名.触发器名 ”方式可以删除指定数据库下的触发器，当省略“ 数据库名. ”时，则删除当前选择的数据库下的触发器。

      例如，在一次员工管理系统升级之后，技术人员觉得触发器trig_emp的使用意义不大，想要删除ems中的触发器trig_emp

 查询

​​​​​​​