MySQL基础（9）——游标、触发器和事务处理

秒速五厘米 2022-06-13 14:41 421阅读 0赞

本篇主要整理如何使用游标、触发器以及管理事务处理的相关内容。

本篇内容 适用于MySQL 5及以后的版本。

### **一、游标** ###

**游标：**是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的**结果集**。游标主要用于**交互式应用**，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

> 不像多数DBMS，MySQL游标只能用于存储过程（和函数）。

关键词：DECLARE、OPEN CURSOR、CLOSE

1.  使用游标：  
    （1）在能够使用游标前，必须声明（定义）它。这个过程只是定义要使用的SELECT语句。  
    （2）一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。  
    （3）对于填有数据的游标，根据需要取出（检索）各行。（4）在结束游标使用时，必须关闭游标。

实例1：创建、打开、关闭游标

![这里写图片描述][SouthEast]

> **declare**定义和命名游标，命名游标为ordernumbers，存储过程处理完成后，游标就消失（因为它局限于存储过程）
> 
> **open**用来打开游标，在处理open语句时执行查询，存储检索出的数据以供浏览和滚动。
> 
> 游标处理完成后，应当使用**close**关闭游标。close释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。如果你不明确关闭游标，MySQL将会在到达END语句时**自动关闭**它。

实例2：循环使用游标

![这里写图片描述][SouthEast 1]

> FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）。

![这里写图片描述][SouthEast 2]

> CONTINUE HANDLER是在条件出现时被执行的代码。它指出当SQLSTATE ‘02000’出现时，SET done=1。SQLSTATE ‘02000’是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。
> 
> DECLARE语句的发布存在特定的次序。用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。不遵守此顺序将产生错误消息。

实例3：一个完整的存储过程、游标、逐行处理以及存储过程调用其他存储过程的工作样例

![这里写图片描述][SouthEast 3]

### **二、触发器** ###

**触发器**：MySQL响应DELETE、INSERT、UPDATE等语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）

1.  使用触发器

关键词：CREATE TRIGGER、

**触发器必要信息**：唯一的触发器名； 触发器关联的表； 触发器应该响应的活动（DELETE、INSERT或UPDATE）； 触发器何时执行（处理之前或之后）。

实例1：创建触发器

![这里写图片描述][SouthEast 4]

> 触发器可在一个操作发生之前或之后执行，这里AFTER INSERT表明此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product added将对每个插入的行显示一次。
> 
> **只有表才支持触发器，视图不支持（临时表也不支持）**  
> 触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，**每个表最多支持6个触发器**（每条INSERT、UPDATE 和DELETE的之前和之后）。**单一触发器不能与多个事件或多个表关联**，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。
> 
> 如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL 将不执行AFTER触发器（如果有的话）。

实例2：删除触发器

![这里写图片描述][SouthEast 5]

不同类型的触发器以及其差别

<table> 
 <tbody> 
  <tr> 
   <th></th> 
   <th>INSERT触发器</th> 
   <th>DELETE触发器</th> 
   <th>UPDATE触发器</th> 
  </tr> 
  <tr> 
   <th>重点</th> 
   <td> 1. 在INSERT触发器代码内，可引用一个名为<font>NEW</font>的虚拟表，访问被插入的行； <br>2. 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）； <br>3. 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。 </td> 
   <td> 1. 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行； <br>2. OLD中的值全都是只读的，不能更新。 </td> 
   <td> 1. 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值； <br>2. 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）； <br>3. OLD中的值全都是只读的，不能更新。 </td> 
  </tr> 
  <tr> 
   <th>示例</th> 
   <td>create trigger neworder after insert on orders <br>-&gt;for each row select new.order_num; </td> 
   <td>使用OLD保存将要被删除的行到一个存档表中： <br>create trigger deleteorder before delete on orders <br>for each row begin insert into <br>archive_orders(order_num,order_date,cust_id)<br>values(OLD.order_num,OLD.order_date,OLD.cust_id); <br>end;</td> 
   <td>create trigger updatevendor before update on vendors <br>for each row <br>set new.vend_state=Upper(new.vend_state);</td> 
  </tr> 
  <tr> 
   <th>说明</th> 
   <td>此代码创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。触发器从NEW. order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新order_num还没有生成。对于orders的每次插 入使用这个触发器将总是返回新的订单号。 </td> 
   <td>在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_ orders的存档表中。使用BEFORE DELETE触发器的优点（相对于AFTER DELETE触发器来说）为，如果由于某种原因，订单不能存档，DELETE本身将被放弃。 </td> 
   <td> 显然，任何数据净化都需要在UPDATE语句之前进行。每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper(NEW.vend_state)替换。 </td> 
  </tr> 
 </tbody> 
</table>

注意：  
（1）与其他DBMS相比，MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。  
（2）创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。  
（3）应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。  
（4）触发器的一种非常有意义的使用是创建审计跟踪。使用触发器， 把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。  
（5）遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

### **三、事务处理** ###

MyISAM不支持明确的事务处理管理，InnoDB支持

**事务处理（transaction processing）**：用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。  
**事务(transaction)**：指一组SQL语句。  
**回退(rollback)**：指撤销指定SQL语句的过程。  
**提交(commit)**：指将未存储的SQL语句结果写入到数据库表。  
**保留点(savepoint)**：指事务处理中设置的临时占位符，可以对它发布回退（与回退整个事务处理不同）。

**管理事务处理的关键**：将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

1.  控制事务处理：

关键词：START TRANSACTION、ROLLBACK、COMMIT、SAVEPOINT

**MySQL的ROLLBACK命令用来回退（撤销）MySQL语句**

> ROLLBACK只能在一个事务处理内使用.  
> 事务处理用来管理INSERT、UPDATE和 DELETE语句：其中，**不能回退SELECT语句**，也**不能回退CREATE或DROP操作**。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

**隐含提交**：提交操作是自动进行的。但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交， 使用**COMMIT**语句。  
实例1：从系统中完全删除订单20010

![这里写图片描述][SouthEast 6]

> 当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）。

为了**支持回退部分事务处理**，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。  
实例2：  
savepoint delete1;  
rollback to delete1;

> 每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。  
> 保留点在事务处理完成（执行一条ROLLBACK或 COMMIT）后自动释放。自MySQL 5以来，也可以用RELEASE SAVEPOINT明确地**释放保留点**。

实例3：更改默认的提交行为  
set autocommit=0;

> autocommit标志决定是否自动提交更改，不管有没有COMMIT 语句。默认的MySQL行为是自动提交所有更改。设置autocommit为0（假）指示MySQL不自动提交更改（直到autocommit被设置为真为止）
> 
> **autocommit标志是针对每个连接而不是服务器的。**

[SouthEast]: /images/20220613/bf6f81c45e924e4aa9262e7e8bdf5e89.png
[SouthEast 1]: /images/20220613/b64775f8fc7e4537b3467f817c6e6e05.png
[SouthEast 2]: /images/20220613/742dc56130dc493ab0c0dc53d3a6c431.png
[SouthEast 3]: /images/20220613/f07dfce470ff424583d483a278f7cc6f.png
[SouthEast 4]: /images/20220613/954a6883710348e48bdd15f32f85f55c.png
[SouthEast 5]: /images/20220613/168c924e4b5f4837b0b1369966832ac0.png
[SouthEast 6]: /images/20220613/ff7d6b4876cc456d99d1da642e95b817.png