Mysql必知必会：游标的使用

使用游标

游标使用的步骤：

• 在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据， 它只是定义要使用的 SELECT 语句和游标选项。
• 一旦声明，就必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来。
• 对于填有数据的游标，根据需要取出（检索）各行。
• 在结束游标使用时，必须关闭游标，可能的话，释放游标。

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

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

创建游标

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

下面的语句定义了名为 ordernumbers 的游标，使用了可以检索所有订单的 SELECT 语句。

Order表中的信息：

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
  -- 定义游标
  DECLARE ordernumbers CURSOR
  FOR
  SELECT order_num FROM orders;
END;
复制代码

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

使用游标数据

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

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
    -- 定义局部变量
    DECLARE num INT;
    -- 定义游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    -- 打开游标
    OPEN ordernumbers;
    -- 获取第一行数据
    FETCH ordernumbers INTO num;
    -- 查询结果
    SELECT num;
    -- 关闭游标
    CLOSE ordernumbers;
END;
CALL processorder();
复制代码

其中 FETCH 用来检索当前行的 order_num 列（将自动从第一行开始）到一个名为 num 的局部变量中，并将查询 num 的结果。由于只检索到第一行，所以 num 的值为 '20005'。

下面，循环检索数据，从第一行到最后一行。

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
  -- 定义局部变量
  DECLARE done BOOLEAN DEFAULT false;
  DECLARE num INT;
  -- 定义游标
  DECLARE ordernumbers CURSOR
  FOR
  SELECT order_num FROM orders;
  -- 定义CONTINUE HANDLER
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=true;
  -- 打开游标
  OPEN ordernumbers;
  -- 循环所有行
  REPEAT
    -- 获取第一行数据
    FETCH ordernumbers INTO num;
  -- 结束循环
  UNTIL done END REPEAT;
  -- 查询结果
  SELECT num;
  -- 关闭游标
  CLOSE ordernumbers;
END;
CALL processorder();
复制代码

循环了结果集的所有行，所以 num 的值是最后一行的数据。

与上一个例子不同之处是，这个例子的 FETCH 是在 REPEAT 内，因此它反复执行直到 done 为真。

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=true;

这条语句定义了一个 CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当 SQLSTATE '02000' 出现时，SET done=true。SQLSTATE '02000'是一个未找到条件，当 REPEAT 由于没有更多的行供循环而不能继续时，出现这个条件。

游标的优缺点

优点：游标是面向集合与面向行的设计思想之间的一种桥梁，因为游标是针对行操作的，所以对从数据库中 SELECT 查询得到的每一行可以进行分开的独立的相同或不同的操作，是一种分离的思想。可以满足对某个结果行进行特殊的操作。如基于游标位置的增删改查能力。

缺点：

• 速度较慢
• 会产生死锁现象
• 内存大