数据库教程之游标及触发器

¶一、游标

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

注意：只能用于存储过程 不像多数DBMS，MySQL游标只能用于存储过程(和函数)。

¶1、使用游标

在声明游标后，可根据需要频繁地打开和关闭游标。在游标打开后，
可根据需要频繁地执行取操作。

1. 首先声明(定义)游标。这个过程实际上没有检索数据，只是定义要使用的SELECT语句。
2. 声明后，必须打开游标使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
3. 对于填有数据的游标，根据需要取出(检索)各行
4. 结束游标使用时，必须关闭游标

¶2、创建游标

游标创建用DELCARE语句，(和前面的存储过程中声明变量的关键字一样)。

DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。

DECLARE 语句用来定义和命名游标，这里为 ordernumbers 。 存储过程处理完成后，游标就消失（因为它局限于存储过程）。

CREATE PROCEDURE processorders()
BEGIN
    DELCARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
END;

¶3、打开关闭游标

在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动。

CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。

隐含关闭：如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它。

-- 打开游标
OPEN ordernumbers;

-- 关闭游标
CLOSE ordernumbers;

修改版本：

这个存储过程声明、打开和关闭一个游标。但对检索出的数据什么也没有做…

CREATE PROCEDURE processorders()
BEGIN
   -- Delcare the cursor
    DELCARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Open the cursor
    OPEN ordernumbers; 

    -- Close the cursor
    CLOSE ordernumbers;
END;

¶4、使用游标数据

在一个游标被打开后，可以使用 FETCH 语句分别访问它的每一行。

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

¶4.1 FETCH获取一条数据

FETCH用来检索当前行的order_num列(将自动从第一行开始)到一个名为o的局部声明的变量中。

CREATE PROCEDURE processorders()
BEGIN
   -- Declare local variables
   DECLARE o INT;

   -- Delcare the cursor
    DELCARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Open the cursor
    OPEN ordernumbers;

    -- Get order number
   FETCH ordernumbers INTO o;

    -- Close the cursor
    CLOSE ordernumbers;
END;

¶4.2 FETCH循环检索数据

这里的FETCH在REPEAT内，因此反复执行直到done为真（ UNTIL done END REPEAT; ）。

上面这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。指出当SQL STATE '02000'出现时，SET done = 1。

REPEAT循环语句：REPEAT ... UNTIL 变量 END REPEAT;

CREATE PROCEDURE processorders()
BEGIN
   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE o INT;

   -- Delcare the cursor
    DELCARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

     -- Open the cursor
     OPEN ordernumbers;

    -- Loop through all rows
      REPEAT

        -- Get order number
        FETCH ordernumbers INTO o;
    
     -- End of loop
     UNTIL done END REPEAT;

     -- Close the cursor
     CLOSE ordernumbers;
END;

¶4.3 定义DECLARE语句的次序

• DECLARE语句定义的局部变量必须在定义任意游标或句柄(普通语句)之前定义
• 句柄(普通语句)必须在游标之后定义(如循环的条件等)

¶4.4 利用游标处理数据

此存储过程不返回数据，但它能够创建和填充另一个表，可以用一条简单的SELECT语句查看该表。

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

CREATE PROCEDURE processorders()
BEGIN
   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE o INT;
   DECLARE t DECIMAL(8, 2);

   -- Delcare the cursor
    DELCARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    -- Create a table to store the results
    CREATE TABLE IF NOT EXISTS ordertotals
        (order_num INT, total DECIMAL(8,2));

     -- Open the cursor
     OPEN ordernumbers;

    -- Loop through all rows
      REPEAT

        -- Get order number
        FETCH ordernumbers INTO o;

        -- Get the total for this order
        CALL ordertotal(o, 1, t); --前一章的存储过程，主要是计算带税订单的合计，存储到t
   
         -- Insert order and total into ordertotals
         INSERT INTO ordertotals(order_num, totals)
         VALUES(o, t);

     -- End of loop
     UNTIL done END REPEAT;

     -- Close the cursor
     CLOSE ordernumbers;
END;

¶二、触发器

存储过程的执行是需要自己调用的，需要是进行调用！

而触发器则是在触发某个条件自动执行！

触发器，当然就是触发了然后自动执行。。。

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于BEGIN和END语句之间的一组语句)：

• DELETE
• UPDATE
• INSERT

¶1、创建触发器

注意：保持每个数据库的触发器名唯一。在MySQL 5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一。

虽然MySQL允许同一数据库可使用相同触发器名（不同表即可），但是这样很明显不是好的做法，最好保证同一数据库触发器名唯一！

需要的四条信息：

• 唯一的触发器名
• 触发器关联的表
• 触发器应该响应的活动(DELETE、INSERT、UPDATE)
• 触发器何时执行(处理之前后之后)

创建触发器：

CREATE TRIGGER用来创建名为newproduct的新触发器。

触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。

这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。

在这个例子中，文本Product added将对每个插入的行显示一次。

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

注意：

1. 只有表才支持触发器，视图不支持，临时表也不支持。
2. 每个表的每个事件每次只允许一个触发器
3. 每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）
4. 单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

触发器失败：

如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器（如果有的话）。

¶2、删除触发器

注意：触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

DROP TRIGGER 触发器名字;

¶3、使用触发器

¶3.1 INSERT触发器

INSERT触发器在INSERT语句执行之前或之后执行 。

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

会发现下面INSERT语句的执行多了输出结果！就是因为触发了上面的neworder触发器，执行了SELECT NEW.order_num;语句！

• 此代码创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。
• 在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。
• 触发器从NEW. order_num取得这个值并返回它。

此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号。

-- 创建触发器
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

-- 触发开始
INSERT INTO orders(order_date，cust_id)
VALUES (Now()，10001) ;

+-----------+
| order_num |
+-----------+
|	20010	|
+-----------+

BEFORE或AFTER？

通常，将BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。本提示也适用于UPDATE触发器。

¶3.2 DELETE触发器

DELETE触发器在DELETE语句执行之前或之后执行：

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

在任意订单被删除前将执行此触发器。

• 它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_orders的存档表中（为实际使用这个例子，你需要用与orders相同的列创建一个名为archive_orders的表）。

使用BEFORE DELETE触发器的优点（相对于AFTER DELETE触发器 来说），如果由于某种原因，订单不能存档，DELETE本身将被放弃。

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

多语句触发器：

正如所见，触发器deleteorder使用BEGIN和END语句标记触发器体。这在此例子中并不是必需的，不过也没有害处。

使用BEGIN END块的好处是触发器能容纳多条SQL语句（在BEGIN END块中一条挨着一条）。

¶3.3 UPDATE触发器

UPDATE触发器在UPDATE语句执行之前或之后执行

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

显然：数据净化都需要在 UPDATE 语句之前进行，就像这个例子中一样。每次更新一个行时， NEW.vend_state 中的值（将用来更新表行的值）都用 Upper(NEW.vend_state) 替换。

此例子：保证州名缩写总是大写（不管 UPDATE 语句中给出的是大写还是小写）。

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

¶4、触发器补充知识

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