MySQL命令学习笔记（七）

1 使用游标

1.1 游标

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

1.2 使用游标

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

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

1.2.1 创建游标

游标用DECLARE创建，DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。示例如下：

DELIMITER //
CREATE PROCEDURE productorders()
BEGIN
    DECLARE ordernumbers CURSOR
    FOR
    SELECT quantity FROM table1;
END //
DELIMITER;

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

1.2.2 打开和关闭游标

游标用OPEN CURSOR语句来打开：

OPEN ordernumbers;

在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动
游标处理完成后，应当使用如下语句关闭游标：

CLOSE ordernumbers;

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

隐含关闭 若不明确关闭游标，MySQL将会在到达END语句时自动关闭它
修改前面的示例：

DELIMITER //        #重新定义分隔符
CREATE PROCEDURE productorders()
BEGIN
    -- declare the cursor
    DECLARE ordernumbers CURSOR FOR SELECT quantity FROM table1;
    
    -- open the cursor
    OPEN ordernumbers;
    
    -- close the cursor
    CLOSE ordernumbers;
END //
DELIMITER;       #重新定义分隔符

1.2.3 使用游标数据

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH句检索下一行（不重复读取同一行）。

示例1：从游标中检索单个行（第一行）

DELIMITER //        #重新定义分隔符
CREATE PROCEDURE productorders()
BEGIN
    -- declare local variables
    DECLARE o INT;
    
    -- declare the cursor
    DECLARE ordernumbers CURSOR FOR SELECT quantity FROM table1;
    
    -- open the cursor
    OPEN ordernumbers;
    
     -- get order number
    FETCH ordernumbers INTO o;
    
    -- close the cursor
    CLOSE ordernumbers;
END //
DELIMITER;       #重新定义分隔符

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

2 使用触发器

2.1 触发器

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

2.2 创建触发器

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

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

触发器用CREATE TRIGGER语句创建，示例如下：

CREATE TRIGGER newid AFTER INSERT ON table1 FOR EACH ROW SELECT 'id added';

CREATE TRIGGER用来创建名为newid的新触发器,触发器可在一个操作发生之前或之后执行，这里是AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。同时还指定了FOR EACH ROW，因此代码对每个插入行执行。操作结果如下：

图中出现错误：

Not allowed to return a result set from a trigger

表示触发器不能执行SELECT返回结果，解决办法用SELECT INTO来设置变量，修改后的命令如下：

CREATE TRIGGER newid AFTER INSERT ON table1 FOR EACH ROW SELECT 'id added' INTO @msg;

正确执行，然后查看变量的内容：

SELECT @msg;

上图显示结果为NULL，接下来执行一条INSERT语句，然后再查看变量，显示刚才的’id added’字符。

2.3 删除触发器

删除一个触发器，可使用DROP TRIGGER语句，如下所示：

DROP TRIGGER newmsg;

2.4 使用触发器

2.4.1 INSERT触发器

INSERT触发器在INSERT语句执行之前或之后执行，其要点如下：

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

CREATE TRIGGER newtable AFTER INSERT ON table1 FOR EACH ROW SELECT NEW.quantity;

测试该触发器：

INSERT INTO table1(name,id,quantity) VALUES('TN10', 10, 9);

结果如下：

和前面一样也需要用SELECT INTO来设置变量

2.4.2 DELETE触发器

DELETE触发器在DELETE语句执行之前或之后执行，其要点如下：

• 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行
• OLD中的值全都是只读的，不能更新使用OLD保存将要被删除的行到一个存档表中，示例如下：

CREATE TGIGGER deletetable BEFORE DELETE ON table1 FOR EACH ROW
BEGIN
    INSERT INTO del_orders(name, id, home)
    VALUES(OLD.name, OLD.id, OLD.home)
END;

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

2.4.3 UPDATE触发器

UPDATE触发器在UPDATE语句执行之前或之后执行，其要点如下：

• 在UPDATE触发器代码中，可以引用一个名为OLD的虚拟表访问以前（UPDATE）的值，引用一个名为NEW的虚拟表访问新更新的值
• 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
• OLD中的值全部是只读的，不能更新
  以下示例保证home总是大写（不管UPDATE语句中给出的是大写还是小写）：

CREATE TRIGGER updatehome BEEFORE UPDATE ON table1 FOR EACH ROW SET NEW.home = Upper(NEW.home);