第十部分 游标+触发器+事务处理
使用游标
游标(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语句。