游标

  • 需要MySQL 5：MySQL 5添加了对游标的支持。

  • 游标是一个存储在MySQL服务器上的数据库查询。

    它不是一条SELECT语句，而是被该语句检索出来的结果集。

    在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

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

  • 只能用于存储过程：不像多数DBMS，MySQL游标只能用于存储过程（和函数）。

使用游标

  • 使用游标涉及几个明确的步骤：
    1. 在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
    2. 一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
    3. 对于填有数据的游标，根据需要取出（检索）各行。
    4. 在结束游标使用时，必须关闭游标。
  • 在声明游标后，可根据需要频繁的打开和关闭游标。在游标打开后，可根据需要频繁的执行取操作。

创建游标

  • DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。
1
2
3
4
5
6
7
-- 定义游标，检索所有订单
CREATE PROCEDURE processorders()
BEGIN
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
END;
  • 存储过程处理完成后，游标就消失（因为它局限于存储过程）。

打开和关闭游标

1
2
3
4
5
-- 打开游标
OPEN ordernumbers;

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

使用游标

  • 在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。

    FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- 保存每个订单的订单号和合计（带税或不带税）
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;

/*
    FETCH检索当前order_num到声明的名为o的变量中；
    FETCH是在REPEAT内，因此它反复执行直到done为真；
    为使它起作用，用一个DEFAULT 0（假，不结束）定义变量done。
        
    那么，怎样才能在结束时被设置为真呢？答案为第15行；
    此条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码；
    它指出当SQLSTATE '02000'出现时，SET done = 1。
        
    名为t的变量，用来存储每个订单的合计。
        
    此存储过程还创建了一个新表（如果它不存在的话），名为ordertotals；
    这个表将保存存储过程生成的结果。
    FETCH像以前一样取每个order_num，
    然后用CALL执行另一个存储过程（前一章中创建）来计算每个订单的带税的合计（结果存储到t）；
    最后用INSERT保存每个订单的订单号和合计。
*/

-- 此存储过程不返回数据，但它能够创建和填充另一个表，查看该表。
SELECT *
FROM ordertotals;

  • DECLARE语句的次序：DECLARE语句的发布存在特定的次序。

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

  • 重复或循环？除REPEAT语句外，MySQL还支持循环语句，它可用来重复执行代码，直到使用LEAVE语句手动退出为止。通常REPEAT语句的语法使它更适合于对游标进行循环。