《MySQL必知必会》之快速入门游标和触发器

第二十四章 使用游标

本章将介绍什么是游标以及如何使用游标
游标

之前的select语句检索出来的数据，没有办法得到第一行或者下一行
有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。
游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。
在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
MySQL游标只能用于存储过程（和函数）
使用游标

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

1. CREATE PROCEDURE processorders()
2. BEGIN
3.         DECLARE ordernumbers CURSOR
4.     FOR
5.     SELECT order_num FROM orders;
6. END;

复制代码

这个存储过程并没有做很多事情，DECLARE语句用来定义和命名游标，这里为ordernumbers。存储过程处理完成后，游标就消失（因为它局限于存储过程）。
打开和关闭游标

1. --打开游标
2. OPEN ordernumbers;
3. --处理完成后，应当使用下句关闭游标
4. CLOSE ordernumbers;

复制代码

CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭
MySQL会在达到END语句时自动关闭它
使用游标数据

例：从游标中检索单个行

1. CREATE PROCEDURE processorders()
2. BEGIN
3.         -- Declare local variables
4.     DECLARE o INT;
6.     -- Declare the cursor
7.     DECLARE orderumbers CURSOR
8.     FRO
9.     SELECT order_num FROM orders;
11.     -- Open the cursor
12.     OPEN ordernumbers;
14.         -- Get order number
15.     FETCH ordernumbers INTO o;
17.         -- Close the cursor
18.     CLOSE ordernumbers;
20. END;

复制代码

其中FETCH用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明的变量中。对检索出的数据不做任何处理。
例：循环检索数据，从第一行到最后一行

1. CREATE PROCEDURE processorders()
2. BEGIN
3.         --Declare local variables
4.     DECLARE done BOOLEAN DEFAULT 0;
5.     DECLARE o INT;
7.     --Declare the cursor
8.     DECLARE ordernumbers CURSOR
9.     FOR
10.     SELECT order_num FROM orders;
12.         --Declare continue handler
13.     DECLARE CONTINUE HANDLER FRO SQLSTATE '02000' SET done=1;
15.         --Open the cursor
16.     OPEN ordernumbers;
18.         --Loop through all rows
19.     REPEAT
20.             --Get order numbers
21.         FETCH ordernumbers INTO o;
22.     --End of loop
23.     UNTIL done END REPEAT;
24.     --CLose the cursor
25.     CLOSE ordernumbers;
26. END;

复制代码

这个例子使用FETCH检索当前order_num到声明的名为o的变量中。但与前一个例子不一样的是，这个例子中的FETCH是在REPEAT内，因此它反复执行直到done为真（由UNTIL done END REPEAT;规定）。为使它起作用，用一个DEFAULT 0（假，不结束）定义变量done。
使用以下语句将done在结束时设置为真：

1. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1

复制代码

这条语句定义(DECLARE)了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。
这里，它指出当SQLSTATE '02000’出现时，SET done=1。
SQLSTATE '02000’是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。
注意：
DECLARE语句的次序，定义局部变量时必须在游标或者句柄前定义，定义句柄必须在游标之后定义

1. CREATE PROCEDURE processorders()
2. BEGIN
3.         --Declare local variables
4.     DECLARE done BOOLEAN DEFAULT 0;
5.     DECLARE o INT;
6.     DECLARE t DECIMAL(8,2);
7.     --Declare the cursor
8.     DECLARE ordernumbers CURSOR
9.     FOR SELECT order_num FROM orders;
10.     --Declare continue handler
11.     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
12.     --Create a table to store the results
13.     CREATE TABLE IF NOT EXISTS ordertotals
14.             (order_num INT, total DECIMAL(8,2));
15.     --Open the cursor
16.     OPEN ordernumbers;
17.     --Loop through all rows
18.     REPEAT
19.             --Get order number
20.         FETCH ordernumbers INTO o;
21.         --Get the total for this order
22.         CALL ordertoal(o, 1, t);
23.         --Insert order and total into ordertotals
24.         INSERT INTO ordertotals(order_num, total)
25.         VALUES(o,t);
26.     --End of loop
27.     UNTIL done END REPEAT;
28.     --Close the cursor
29.     CLOSE ordernumbers;
30. END;

复制代码

在这个例子中，增加了另一个名为t的变量（存储每个订单的合计）。此存储过程还在运行中创建了一个新表（如果它不存在的话），名为ordertotals。这个表将保存存储过程生成的结果。FETCH像以前一样取每个order_num，然后用CALL执行另一个存储过程来计算每个订单的带税的合计（结果存储到t）。最后，用INSERT保存每个订单的订单号和合计。
次存储过程不返回数据
第二十五章 使用触发器

本章介绍什么是触发器，为什么要使用以及如何使用触发器，还有创建和使用触发器的语法
触发器

想要某条语句或者某些语句在事件发生时执行就需要使用触发器
需求：往表中新增一条员工数据，都需要检查其电话号格式是否正确，每当订购一个产品时，都从库存数量中减去订购的数量
其实也就是在某个表发生更改时自动处理
触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句

• DELETE
  
• INSERT
  
• UPDATE
  其他MySQL语句不支持触发器
  

创建触发器

创建触发器，需要给出以下4个数据

• 唯一的触发器名
  
• 触发器关联的表
  
• 触发器应该响应的活动
  
• 触发器合何时执行
  

使用CREATE TRIGGER语句创建。例：

1. CREATE TRIGGER newproduct AFTER INSERT ON products
2. FOR EACH ROW SELECT 'Product added';

复制代码

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product added将对每个插入的行显示一次。
注意：
只有表支持触发器，视图不支持
触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。
删除触发器

1. DROP TRIGGER newproduct；

复制代码

触发器不能更新或者覆盖，想要修改，只能删除并重建
INSERT触发器

用在insert语句之前或者之后，需要知道以下几点：

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

1. CREATE TRIGGER neworder AFTER INSERT ON orders
2. FOR EACH ROW SELECT NEW.order_num;

复制代码

此代码创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。触发器从NEW. order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号。

1. INSERT INTO orders(order_date,cust_id)
2. VALUES(Now(),10001);

复制代码

将返回一个order_num，orders表包含三列，date、id必须给出，num自动生成并返回
DELETE触发器

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

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

1. CREATE TRIGGER deleteorder BEFORE DELETE ON orders
2. FOR EACH ROW
3. BEGIN
4.         INSERT INTO archive_orders(order_num, order_date, cust_id)
5.     VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
6. END;

复制代码

在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_orders的存档表中（为实际使用这个例子，你需要用与orders相同的列创建一个名为archive_orders的表）。
说白了就是在删除前备份数据
UPDATE触发器

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

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

1. CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
2. FOR EACH ROW SET NEW.vend_state = Upper(New.vend_state);

复制代码

该sql保证州名缩写总是大写（不管UPDATE语句中给出的是大写还是小写）
关于触发器的进一步介绍

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！