《MySQL必知必会》第二十四章-使用游标

最新推荐文章于 2021-02-05 11:02:37 发布

原创最新推荐文章于 2021-02-05 11:02:37 发布 · 355 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL

《MySQL必知必会》学习笔记同时被 2 个专栏收录

20 篇文章

订阅专栏

《MySQL必知必会》读书笔记

20 篇文章

订阅专栏

本文介绍了如何在数据库存储过程中创建和使用游标来处理查询结果集。包括创建游标、打开和关闭游标的过程，以及如何利用游标遍历并处理每一行数据。还展示了如何在循环中使用游标获取数据，并将数据插入到另一个表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建游标

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

打开和关闭游标

DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    -- Open the cursor
    OPEN ordernumbers;
    
    -- Close the cursor
    CLOSE ordernumbers;
END//
DELIMITER ;

使用游标数据

DECLARE语句的次序：DECLARE语句的发布存在特定的次序。用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。即，局部变量->游标->句柄。

DELIMITER //
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//
DELIMITER ;

DELIMITER //
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
    DECLARE 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//
DELIMITER ;

DELIMITER //
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(o, 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//
DELIMITER ;
-- CALL processorders();
-- SELECT *
-- FROM ordertotals;