MySQL 存储过程与游标的使用

老牛浏览 318评论 0发表于 7 年前

1. 游标的作用及属性

游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

游标有下面这些属性：

游标是只读的，也就是不能更新它；
游标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录；
避免在已经打开游标的表上更新数据。

2. 如何使用游标

首先用 DECLARE 语句声明一个游标。
sql
```
DECLARE cursor_name CURSOR FOR SELECT_statement;
```
上面这条语句，对我们执行的 select 返回的记录指定了一个游标。
其次需要使用 OPEN 语句来打开上面你定义的游标。
sql
```
OPEN cursor_name;
```
接下来你可以用 FETCH 语句来获得下一行数据，并且游标也将移动到对应的记录上。
sql
```
FETCH cursor_name INTO variable list;
```
最后当我们所需要进行的操作都结束后我们要把游标释放掉。
sql
```
CLOSE cursor_name;
```

在使用游标时需要注意的是，使用定义一个针对 NOT FOUND 的条件处理函数（condition handler）来避免出现「no data to fetch」这样的错误，条件处理函数就是当某种条件产生时所执行的代码，这里但我们游标指到记录的末尾时，便达到 NOT FOUND 这样条件，这个时候我们希望继续进行后面的操作，所以我们会在下面的代码中看到一个 CONTINUE。

3. 使用实例

先看看我们的表格内容：

下面的游标使用演示获取库存量小于 100 的产品的代码：

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`CursorProc` $$
CREATE PROCEDURE `test`.`CursorProc`()
BEGIN
    DECLARE no_more_products,quantity_in_stock INT DEFAULT 0;
    DECLARE product_code VARCHAR(255);
    DECLARE cur_product CURSOR FOR SELECT code FROM products; -- 定义一个游标
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_products = 1; -- 针对not found处理

    CREATE TEMPORARY TABLE infologs(
      `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
      `msg` VARCHAR(255) NOT NULL
    );

    OPEN cur_product; -- 打开游标
    FETCH cur_product INTO product_code; -- 第一行数据写入变量

    REPEAT
        SELECT quantity INTO quantity_in_stock FROM products WHERE code = product_code;
        IF quantity_in_stock < 100 THEN
            INSERT INTO infologs(msg) VALUES (product_code);
        END IF;
        FETCH cur_product INTO product_code;
    UNTIL no_more_products = 1 END REPEAT ;

    CLOSE cur_product; -- 关闭游标
    SELECT * FROM infologs;
    DROP TABLE infologs;
END $$

DELIMITER ;

调用存储过程：

call test.CursorProc();

下面是最终的结果：

暂无评论，快来发表评论吧~

私信

老牛@ilaoniu

老牛，俗称哞哞。单纯的九零后理工小青年。喜欢折腾，爱玩，爱音乐，爱游戏，爱电影，爱旅游...

最后活跃于 15 小时前