MySQL必知必会——第二十四章使用游标


使用游标

本章将讲授什么是游标以及如何使用游标。

游标

MySQL检索操作返回一组称为结果集的行。这组行都是与SQL语句相匹配的行。使用简单的SELECT语句,没有办法得到第一行、下一行或前十行,也不存在每次仅修改结果集中某行的办法。

有时,需要在检索出来的行中前进或后退一行或多行。我们可以使用游标(cursor),它是一个存储在MySQL服务器上的数据库查询,并非一条SELECT语句,而是一个SELECT语句检索出的结果集。在存储了游标后,应用程序可以根据需要滚动或浏览其中的数据。

只能用于存储过程 MySQL游标只能用于存储过程(和函数)。


使用游标

使用游标需要几个步骤:

  1. 使用游标前,必须声明(定义)它。 此时并未检索数据,只是定义使用的SELECT语句。
  2. 声明后,必须打开游标以供使用。 此时使用定义的SELECT语句进行实际检索。
  3. 对于填有数据的游标,根据需要取出(检索)各行。
  4. 结束游标使用时,必须关闭游标。

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

创建游标

游标用DECLARE语句创建()。DECLARE命名游标,并定义相应的SELECT语句。

定义名为ordernumbers的游标:

mysql> DELIMITER //
mysql> CREATE PROCEDURE processorders()
    -> BEGIN
    ->     DECLARE ordernumbers CURSOR
    ->     FOR
    ->     SELECT order_num FROM orders;
    -> END //
Query OK, 0 rows affected (0.04 sec)

mysql> DELIMITER ;

DECLARE语句用来定义和命名游标。存储过程处理完成后,游标就消失。

DELIMITER DELIMITER修改了命令行的默认语句结束符,若非命令行客户端,需将DELIMITER行删除,并且END后//改;,详情见。

打开和关闭游标

游标用OPEN CURSOR语句来打开:

OPEN ordernumbers;

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

游标处理完成后可使用CLOSE语句关闭游标:

CLOSE ordernumbers;

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

游标关闭后,需要重新打开才可使用。但声明过的游标,无需再次声明。

  • 隐式关闭 如果不明确关闭游标,MySQL将在END语句时自动关闭游标。

使用游标数据

游标打开后,可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据,检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针,使下一条FETCH语句检索下一行。

从游标中检索 单个行 :

CREATE PROCEDURE processorders()
BEGIN

    -- Declare local variables
    DECLARE o INT;

    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Open the cursor
    OPEN ordernumbers;

    -- Get order number
    FETCH ordernumbers INTO o;

    -- Close the cursor
    CLOSE ordernumbers;

END;

此语句中,FETCH用来检索当前行的order_num列(自动从第一行开始)到名为o的局部声明的变量中。

从游标中 循环检索 所有行:

CREATE PROCEDURE processorders()
BEGIN

    -- Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;

    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Declare continue handler
    DELCARE CONTINUE HANDLER FOR SQLSTATE 02000 SET done=1;

    -- Open the cursor
    OPEN ordernumbers;

    -- Loop through all rows
    REPEAT

        -- Get order number
        FETCH ordernumbers INTO o;

    -- End of loop
    UNTIL done END REPEAT;

    -- Close the cursor
    CLOSE ordernumbers;

END;

此语句与前一个不同的是,FETCH是在REPEAT内,因此它反复执行直到done为真(由UNTIL done END REPEAT;规定)。done用DEFAULT 0语句默认为假,并且在结束时使用以下语句设置为真:

DELCARE CONTINUE HANDLER FOR SQLSTATE 02000 SET done=1;

此语句定义了一个CONTINUE HANDLER,它是在条件出现时被执行的代码。SQLSTATE 02000’是一个未找到条件,但REPEAT由于没有更多行供循环而继续时,出现这个条件。

  • DECLARE语句的次序 DECLARE语句存在特定的次序。局部变量必须在定义任意游标或句柄前,而句柄必须在游标后定义。

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

  • 重复或循环? 除了REPEAT语句外,MySQL还支持循环语句,可用来重复执行代码,直到使用LEAVE语句手动退出。

综合样例:

CREATE PROCEDURE processorders()
BEGIN

    -- Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);

    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE 02000 SET done=1;

    -- Create a table to store the results
    CREATE TABLE IF NOT EXISTS ordertotals
        (order_num INT, total DECIMAL(8,2));

    -- Open the cursor
    OPEN ordernumbers;

    -- Loop through all rows
    REPEAT

        -- Get order number
        FETCH ordernumbers INTO o;

        -- Get the total for this order
        CALL ordertotal(0, 1, t);

        -- Insert order and total into ordertotals
        INSERT INTO ordertotals(order_num, total)
        VALUES(o, t);

    -- End of loop
    UNTIL done END REPEAT;

    -- Close the cursor
    CLOSE ordernumbers;

END;

此样例,我们使用变量t来存储每个订单的合计。此存储过程还在运行中创建了一个新表,名为ordertotals,用来存储结果。FETCH取每个order_num,然后用CALL执行另一个存储过程(上章的样例)来计算每个订单带税的合计。最后用INSERT保存每个订单的订单号和合计。

原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/tech/aiops/290966.html

(0)
上一篇 2022年10月13日 00:01
下一篇 2022年10月13日 00:08

相关推荐

发表回复

登录后才能评论