Mysql必知必会：游标的使用

SQL 检索操作返回结果集，如果简单地使用 SELECT 语句，没有办法得到第一行、下一行或前 10 行。有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标（cursor）是一个存储在 DBMS 服务器上的数据库查询， 它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了 游标之后，应用程序可以根据需要滚动或浏览其中的数据。

使用游标

游标使用的步骤：

• 在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据， 它只是定义要使用的 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 查询得到的每一行可以进行分开的独立的相同或不同的操作，是一种分离的思想。可以满足对某个结果行进行特殊的操作。如基于游标位置的增删改查能力。

缺点：

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