SQL笔记21：使用游标

内容要点

1. 什么是游标
2. 如何使用游标

游标

游标（Cursor）是一个存储在DBMS服务器上的数据库查询结果集，在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

常见选项和特性：

1. 能够标记游标为只读
2. 能控制可以执行的定向操作
3. 能标记某些列为可编辑的，某些列为不可编辑的
4. 规定范围，使游标对创建它的特定请求或所有请求可访问
5. 指示DBMS对检索出的数据进行复制，使数据正在游标打开和访问期间不变化

游标主要用于交互式应用

使用游标

几个步骤

1. 必须声明它，实际上没有检索数据，只是定义要使用的SELECT语句和游标选项
2. 声明后必须打开游标，用前面定义的SELECT语句把数据实际检索出来
3. 对于填有数据的游标，根据需要取出各行
4. 在结束游标使用，必须关闭游标，可以的话，释放游标

声明游标后，可根据情况频繁打开或关闭。
打开时，可根据情况频繁执行取操作。

创建游标

使用DECLARE语句

输入

mysql游标只能用于存储过程

DELIMITER //
CREATE PROCEDURE processsorders()
BEGIN
	DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
END//
DELIMITER ;

打开和关闭游标

OPEN ordernumbers;

完整实例

DELIMITER //
CREATE PROCEDURE processsorders()
BEGIN
	DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    OPEN ordernumbers;
    
    CLOSE ordernumbers;
END//
DELIMITER ;

使用游标数据

使用fetch语句分别访问它的每一行。

1. 检索什么数据
2. 检索出来的数据存储在什么地方
3. 向前移动游标内的内部行指针，使下一条fetch检索下一行

例1

DELIMITER //
CREATE PROCEDURE processsorders()
BEGIN
	DECLARE o INT;
    DECLARE ordernumbers CURSOR FOR
    SELECT order_num FROM orders;
    
    OPEN ordernumbers;
    FETCH ordernumbers INTO o;
    
    CLOSE ordernumbers;
END//

例2

DELIMITER //
CREATE PROCEDURE processsorders()
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//

例3

DELIMITER //
CREATE PROCEDURE processsorders()
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;

输出

例3讹误说明

使用repeat循环读取游标中的数，实际上会重复取最后一个数

    REPEAT 
		FETCH ordernumbers INTO o;
        CALL ordertotal(o,1,t);
        INSERT INTO ordertotals(order_num,total)
        VALUES(o,t);
	UNTIL done END REPEAT;

在repeat循环中，当FETCH取最后一个数时，done!=1，这时循环还没有马上结束，但是游标中的数已经取完，当进入下一次循环，执行FETCH语句时，SQL STATUS才抛出02000，此时将重复插入最后一个游标值作为参数得到的order_num和total值，最后才执行检查done的值，推出循环。
这样，我们会发现，repeat的执行逻辑与do{}while()完全一致。

小结

1. 什么是游标
2. 为什么要使用游标
3. 基本游标使用的例子
4. 对游标结果进行循环以及逐行处理的技术