MySQL必知必会 第24章 使用游标

24.1 游标

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

24.2 使用游标

明确步骤

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

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

24.2.1 创建游标

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

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

24.2.2 打开和关闭游标

游标用OPEN CURSOR语句来打开

OPEN ordernumbers;

游标处理完成后，应当使用如下语句关闭游标：

CLOSE ordernumbers;

在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用一个声明过的游标不需要再次声明，用OPEN语法打开就可以了。
隐含关闭：如果你不明确关闭指标，MySQL将会到达END语句时自动关闭它。

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

	OPEN ordernumbers;

	CLOSE ordernumbers;
END;

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

24.2.3 使用游标数据

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

第一个例子从游标中检索单个行（第一行）

CREATE PROCEDURE processorders()
BEGIN
	DECLARE o INT;
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;

	OPEN ordernumbers;

	FETCH ordernumbers INTO o;
	
	CLOSE ordernumbers;
END;

检索当前行的order_num列到一个名为o的局部声明的变量中。

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

CREATE PROCEDURE processorders()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	OPEN ordernumbers;

	REPEAT

		FETCH ordernumbers INTO o;

	UNTIL done END REPEAT;
	
	CLOSE ordernumbers;
END;

CONTINUE HANDLER是条件出现时被执行的代码。SQLSTATE '02000’是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。

DECLARE语句的次序：局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义，不遵守此顺序将产生错误消息。

游标存储过程样例的更进一步修改的版本（对取出的数据进行某种实际的处理）：

CREATE PROCEDURE processorders()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	--存储每个订单的合计
	DECLARE t DECIMAL(8,2);
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	--创建一个新表（如果它不存在的话），保存存储过程的结果
	CREATE TABLE IF NOT EXISTS ordertotals
		(order_num INT,total DECIMAL(8,2));
		
	OPEN ordernumbers;

	REPEAT

		FETCH ordernumbers INTO o;
		
		--执行另一个存储过程，计算每个订单的带税合计
		CALL ordertotal(o,1,t);

		--保存每个订单的订单号的合计
		INSERT INTO ordertotals(order_num,total)
		VALUES(o,t);

	UNTIL done END REPEAT;
	
	CLOSE ordernumbers;
END;

SELECT *
FROM ordertotals;

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