第十部分 游标+触发器+事务处理

使用游标

游标(cursor)是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。MySQL游标只能用于存储过程和函数。

1.使用游标

使用游标涉及几个明确的步骤

• 在能够使用游标前，必须声明它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
• 一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
• 对于填有数据的游标，根据需要检索各行。在游标打开后，可根据需要频繁地执行取操作。
• 在结束游标使用时，必须关闭游标。存储过程处理完全后，游标就消失，因为它局限于存储过程。

2.创建游标

示例：定义名为ordernumbers的游标，使用可以检索所有订单的SELECT语句

SQL语句：CREATE PROCEDURE processorders()

BEGIN

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

END;

3.打开和关闭游标

SQL语句：OPEN ordernumbers;

SQL语句：CLOSE ordernumbers；

SQL语句：CREATE PROCEDURE processorders()

BEGIN

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

OPEN odernumbers;

CLOSE odernumbers;

END;

4.使用游标数据

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。

示例：循环检索数据，从第一行到最后一行。定义并打开一个游标，重复读取所有行，然后关闭游标。

SQL语句：CREATE PROCEDURE processorders()

BEGIN

DECLARE done BOOLEAN DEFAULT 0;

DECLARE o INT;

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET done=1;

OPEN ordernumbers;

REPEAT

FETCH ordernumbers INTO o;

UNTIL done END REPEAT;

CLOSE ordernumbers;

END;

这个例子使用FETCH检索当前order_num到声明的名为o的变量中。但与前面的区别是，这个例子中的FETCH是在REPEAT内，因此，它反复执行直到done为真（由UNTIL done END REPEAT;规定）。为使它起作用，用一个DEFAULT 0(假，不结束)定义变量done。那么，done怎样才能在结束时被设置为真的呢？答案是用以下语句

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET done=1;

其中，CONTINUE HANDLER它是在条件出现时被执行的代码。 SQLSTATE ‘02000‘ 是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。

示例：增加另一个名为t的变量，用于存储每个订单的合计。此存储过程在运行中创建了一个新表ordertotals，用于保存存储过程生成的结果。FETCH像以前一样取每个order_num，然后用CALL执行另一个存储过程来计算每个订单的带税的合计，存储到t，最后，用INSERT保存每个订单的订单号和合计。

SQL语句：CREATE PROCEDURE processorders()

BEGIN

DECLARE done BOOLEAN DEFAULT 0;

DECLARE o INT;

DECLARE t DECIMAL(8,2);

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET done=1;

CREATE TABLE IF NOT EXISTS ordertotals(order_num INT, total DECIMAL(8,2));

OPEN ordernumbers;

REPEAT

FETCH ordernumbers INTO o;

CALL ordertotal(0,1,t);

INSERT INTO ordertotals(order_num, total) VALUES(o,t);

UNTIL done END REPEAT;

CLOSE ordernumbers;

END;

示例：此存储过程不返回数据，但它能够创建和填充另一个表，使用SELECT查看该表

SQL语句：SELECT * FROM ordertotals；

触发器

MySQL语句在需要时被执行，存储过程亦如此。但是，如果你想要某条语句在事件发生时自动执行，怎么办呢？例如，每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确或者每当订阅一个产品时，都从库存数量中减去订购的数量。这些例子的共同之处是它们都需要在某个表发生更改时自动处理，即触发器。触发器是MySQL响应DELETE、INSERT、UPDATE语句而自动执行的一条MySQL语句或位于BEGIN和END语句之间的一组语句，其它MySQL语句不支持触发器。触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6各触发器（每条INSERT、UPDATE和DELETE之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

1.创建触发器

在创建触发器时，需要给出4条信息：

• 唯一的触发器名，这里的唯一是指触发器名在每个表中唯一，并不是每个数据库中唯一，换言之，同一个数据库中的两个表可具有相同名字的触发器；
• 触发器关联的表；
• 触发器应该响应的活动（DELETE、INSERT、UPDATE)；
• 触发器何时执行（处理之前或之后）。

示例：创建名为newproduct的触发器

SQL语句：CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT ‘Product added‘；

2.删除触发器

示例：删除名为newproduct的触发器

SQL语句：DROP TRIGGER newproduct；

触发器不能更新或覆盖，如果要修改一个触发器，必须先删除它，然后再重新创建。

3.使用触发器

(1) INSERT触发器

INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：

• 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
• 在BEFORE INSERT触发器中，NEW中的值也可以被更新；
• 对于AUTO_INCREMENT列（具有MySQL自动赋值的值），NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

示例：创建名为neworder的触发器，它按照AFTER INSERT ON orders执行，生成一个新订单号并保存到order_num中，触发器从NEW.order_num取得这个值并返回它。

SQL语句：CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

示例：测试这个触发器是否成功，试着插入一下新行，返回order_num列值

SQL语句：INSERT INTO orders(order_rate, cust_id) VALUES(Now(),1001);

(2) DELETE触发器

DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下两点：

• 在DELETE触发器代码内，可引用一个名为OLD的虚拟表，访问被删除的行；
• OLD中的值全部是只读的，不能更新。

示例：使用OLD保存将要删除的行到一个存档表archive_orders中

SQL语句：CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW

BEGIN

INSERT INTO archive_orders(order_num,order_rate,cust_id)

VALUES(OLD.order_num,OLD.order_date, OLD.cust_id);

END;

3.UPDATE触发器

UPDATE触发器在语句执行之前或之后执行，需要知道以下几点：

• 在UPDATE触发器代码中，可以引用一个名为OLD的虚拟表访问（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；
• 在BEFORE UPDATE触发器中，NEW中的值可能被更新（允许更改将要用于UPDATE语句中的值）；
• OLD中的值全部是只读的，不能更新。

示例：保证州名的缩写总是大写

SQL语句：CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state);

事务处理

数据库引擎MyISAM不支持事务处理管理，而数据库引擎InnoDB支持事务处理管理。事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给数据库数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。事务处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。使用事务处理的几个术语：

• 事务（transaction）指一组SQL语句；
• 回退（rollback）指撤销指定SQL语句的过程；
• 提交（commit）指将未存储的SQL语句结果写入数据库表；
• 保留点（savepoint）指事物处理中设置的临时占位符，可以对其发布回退。

控制事务处理

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

(1) 使用ROLLBACK

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

示例：首先执行SELECT以显示该表不为空，然后开始执行一个事务处理，使用DELETE语句删除ordertotals中的所有行，最后使用SELECT语句验证ordertotals确实为空，用一条ROLLBACK语句回退START TRANSACTION之后的所有语句，最后一条SELECT语句显示该表不为空。

SQL语句：SELECT * FROM ordertotals；

START TRANSACTION;

DELETE FROM ordertotals；

SELECT * FROM odertotals；

ROLLBACK;

SELECT * FROM ordertotals；

需要注意的是ROLLBACK只能在一个事务处理内使用，即在执行一条START TRANSACTION命令之后。另外，不能回退SELECT语句和CREATE或DROP操作。

(2) 使用COMMIT

一般的MySQL语句是直接针对数据库表执行和编写的，即提交操作是自动进行的。但是，在事务处理中，提交不会隐含地进行，为进行明确的提交，使用COMMIT语句。

示例：从系统中完全删除订单20010，因为涉及更新两个数据库表orders和orderitems，所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改，即如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它是被自动撤销的）

SQL语句：

START TRANSACTION;

DELETE FROM orderitems WHERE order_num = 20010;

DELETE FROM orders WHERE order_num = 20010;

COMMIT;

(3) 使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是，只是对简单的事务处理才能这样做，更复杂的事物处理可能需要部分提交或回退。例如，添加订单的过程为一个事务处理，如果发生错误，只需要返回到添加orders行之前即可，不需要回退到customers表。为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。这些占位符，我们称之为保留点。使用SAVEPOINT创建占位符。

示例：回退到deletel

SQL语句：SAVEPOINT deletel；

ROLLBACK TO deletel；

(4) 更改默认的提交行为

默认的MySQL行为是自动提交所有更改。换言之，任何时候执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效。为指示MySQL不自动提交更改，需要使用语句SET autocommit=0；其中，autocommit标志决定是否自动提交更改，不管有无COMMIT语句。