mysql交互式使用游标_数据库学习笔记——20 使用游标

1 游标

SQL检索操作返回一组称为结果集的行。这组返回的行都是与SQL语句相匹配的行(零行或多行)。

有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标(cursor)是一个存储在SQL服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。只能用于存储过程

不像多数DBMS，MySQL游标只能用于存储过程(和函数)。

2 使用游标

使用游标涉及几个明确的步骤。在能够使用游标前，必须声明(定义)它。这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句。

一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT 语句把数据实际检索出来。

对于填有数据的游标，根据需要取出(检索)各行。

在结束游标使用时，必须关闭游标。

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

2.1 创建游标

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

例如，下面的语句定义了名为 ordernumbers 的游标，使用了可以检索所有订单的 SELECT 语句。CREATE PROCEDURE processorders ()BEGIN

DECLARE ordernumbers CURSOR FOR

SELECT order_num FROM orders;END;

DECLARE 语句用来定义和命名游标，这里为 ordernumbers 。 存储过程处理完成后，游标就消失(因为它局限于存储过程)。

2.2 打开和关闭游标CREATE PROCEDURE processorders ()

BEGIN

-- 定义游标DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;

-- 打开游标

OPEN ordernumbers;

-- 关闭游标

CLOSE ordernumbers;

END;

游标用 OPEN CURSOR 语句来打开：

OPEN ordernumbers;

在处理 OPEN 语句时执行查询，存储检索出的数据以供浏览和滚动。

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

CLOSE ordernumbers;

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

在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明，用 OPEN 语句打开它就可以了。隐含关闭

如果你不明确关闭游标，MySQL将会在到达 END 语句时自动关闭它。

2.3 使用游标数据

在一个游标被打开后，可以使用 FETCH 语句分别访问它的每一行。FETCH 指定检索什么数据(所需的列)，检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条 FETCH 语句检索下一行(不重复读取同一行)。CREATE PROCEDURE processorders ()BEGIN-- 定义变量DECLARE o INT;-- 定义游标DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;-- 打开游标OPEN ordernumbers;-- 取订单号赋值给oFETCH ordernumbers INTO o;-- 关闭游标CLOSE ordernumbers;END;

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

下面的例子，循环检索数据，从第一行到最后一行：CREATE PROCEDURE processorders ()BEGIN-- 定义变量DECLARE done TINYINT(1) DEFAULT 0;DECLARE o INT;-- 定义游标DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;-- 定义CONTINUE HANDLERDECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;-- 打开游标OPEN ordernumbers;-- 循环开始REPEAT-- 取订单号赋值给oFETCH ordernumbers INTO o;-- 结束循环UNTIL done END REPEAT;-- 关闭游标CLOSE ordernumbers;END;

与前一个例子一样，这个例子使用 FETCH 检索当前 order_num到声明的名为 o 的变量中。但与前一个例子不一样的是，这个例子中的 FETCH 是在 REPEAT 内，因此它反复执行直到 done 为真(由 UNTILdone END REPEAT; 规定)。为使它起作用，用一个 DEFAULT 0 (假，不结束)定义变量 done 。那么， done 怎样才能在结束时被设置为真呢？答案是用以下语句：

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

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

MySQL的错误代码：错误码说明'00'成功

'01'警告

'02'未找到

> '02'异常

'02'——这在游标的上下文中是相关的，用于控制当游标到达数据集末尾时发生的情况。对于不检索任何行的语句，也会出现这种情况 。DECLARE 语句的次序

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

Variable or condition declaration after cursor or handler declaration

Cursor declaration after handler declaration

如果调用这个存储过程，它将定义几个变量和一个 CONTINUE HANDLER ，定义并打开一个游标，重复读取所有行，然后关闭游标。

如果一切正常，你可以在循环内放入任意需要的处理(在 FETCH 语句之后，循环结束之前)。

为了把这些内容组织起来，下面给出我们的游标存储过程样例的更进一步修改的版本，这次对取出的数据进行某种实际的处理：CREATE PROCEDURE processorders ()BEGIN-- 定义变量DECLARE done TINYINT(1) DEFAULT 0;DECLARE o INT;DECLARE t DECIMAL(8,2);-- 定义游标DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;-- 定义CONTINUE HANDLERDECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;-- 创建一个表存储结果CREATE TABLE IF NOT EXISTS ordertotals(

order_num INT,

total DECIMAL(8,2)

);-- 打开游标OPEN ordernumbers;-- 循环开始REPEAT-- 取订单号赋值给oFETCH ordernumbers INTO o;-- 执行ordertotalmye存储过程CALL ordertotalmye(o,1,t);-- 往ordertotals表中插入数据INSERT INTO ordertotals(order_num,total)VALUES(o,t);-- 结束循环UNTIL done END REPEAT;-- 关闭游标CLOSE ordernumbers;END;

在这个例子中，我们增加了另一个名为 t 的变量(存储每个订单的合计)。此存储过程还在运行中创建了一个新表(如果它不存在的话)，名为 ordertotals 。这个表将保存存储过程生成的结果。 FETCH像以前一样取每个 order_num ，然后用 CALL 执行另一个存储过程(ordertotalmye)来计算每个订单的带税的合计(结果存储到 t )。最后，用 INSERT 保存每个订单的订单号和合计。

此存储过程不返回数据，但它能够创建和填充另一个表，可以用一条简单的 SELECT 语句查看该表：CALL processorders();SELECT * FROM ordertotals ORDER BY order_num;

cursor.jpg

这样，我们就得到了存储过程、游标、逐行处理以及存储过程调用其他存储过程的一个完整的工作样例。

作者：Love零O

链接：https://www.jianshu.com/p/8cb49da4047e