爱程序网

存储过程之四—游标

来源: 阅读:

 游标在存储过程和函数中使用。语法如同在嵌入的SQL中。游标是只读的及不滚动的,只能在一个方向上进行遍历,不能在记录之间随意进退,不能跳过某些记录,所以每次读完之后就应该移动到下一个记录。游标必须在声明处理程序之前被声明,并且变量和条件必须在声明光标或处理程序之前被声明。

一、游标

  1、定义

   DECLARE 游标名称 CURSOR FOR 查询语句

   这个语句声明一个光标。也可以在子程序中定义多个光标,但是一个块中的每一个光标必须有唯一的名字。

   2、OPEN语句

    OPEN 游标名称

    这个语句打开先前声明的游标。

  3、FETCH语句

    FETCH 游标名称 INTO 变量[,  变量2] ...

    这个语句用指定的打开游标读取下一行(如果有下一行的话),并且前进游标指针。

   4、CLOSE语句

    CLOSE 游标名称

    这个语句关闭先前打开的游标。如果未被明确地关闭,游标在它被声明的复合语句的末尾被关闭。

二、实例

  表结构如下:

-- ------------------------------ Table structure for person-- ----------------------------DROP TABLE IF EXISTS `person`;CREATE TABLE `person` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `username` varchar(255) DEFAULT NULL,  `age` int(11) DEFAULT NULL,  `password` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;-- ------------------------------ Records of person-- ----------------------------INSERT INTO `person` VALUES ('1', '张三', '21', null);INSERT INTO `person` VALUES ('2', '李四', '23', null);INSERT INTO `person` VALUES ('3', '王五', '22', null);INSERT INTO `person` VALUES ('4', 'zhangsan', '22', 'fdsafds');INSERT INTO `person` VALUES ('8', 'zhangsan', '22', 'fdsafds');INSERT INTO `person` VALUES ('9', 'zhangsan', '22', 'fdsafds');INSERT INTO `person` VALUES ('10', 'wangwu', '23', 'password123');

  1、游标使用REPEAT 

DROP PROCEDURE IF EXISTS proc_test_cursor;-- 所有人的年龄和CREATE PROCEDURE proc_test_cursor(    OUT total INT(11))BEGIN    DECLARE t INT DEFAULT 0;    DECLARE done INT DEFAULT 0;    DECLARE pcursor CURSOR FOR SELECT age FROM person;    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 在 FETCH 语句中引用的游标位置处于结果表最后一行之后。    SET total = 0;    OPEN pcursor;    REPEAT        FETCH pcursor INTO t;         IF NOT done THEN -- 还有记录            SET total = total + t;        END IF;    UNTIL done END REPEAT;    CLOSE pcursor; END;CALL proc_test_cursor(@total);SELECT @total; SELECT SUM(age) FROM person; 

 

   两次查询的结果一样,则游标执行正常。

  2、游标使用while

DROP PROCEDURE IF EXISTS proc_test_cursor_while;-- id小于某个值的年龄和CREATE PROCEDURE proc_test_cursor_while(    IN uid INT(11),    OUT total INT(11))BEGIN    DECLARE t INT DEFAULT 0;    DECLARE done INT DEFAULT 0;    DECLARE pcursor CURSOR FOR SELECT age FROM person WHERE id < uid;    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 在 FETCH 语句中引用的游标位置处于结果表最后一行之后。    SET total = 0;    OPEN pcursor;     WHILE(NOT done) DO        FETCH pcursor INTO t;        IF(NOT done) THEN            SET total = total + t;            END IF;  end WHILE;    CLOSE pcursor; END;CALL proc_test_cursor_while(3,@total);SELECT @total;SELECT SUM(age) FROM person where id < 3;

    两次查询的结果一样,则游标执行正常。

  3、游标中使用update语句

DROP PROCEDURE IF EXISTS proc_test_cursor_update;-- 年龄大于多少的年龄加某个数CREATE PROCEDURE proc_test_cursor_update(     IN avgage INT(11))BEGIN    DECLARE num INT DEFAULT 0;    DECLARE t INT DEFAULT 0;    DECLARE done INT DEFAULT 0;    DECLARE pcursor CURSOR FOR SELECT id, age FROM person;    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 在 FETCH 语句中引用的游标位置处于结果表最后一行之后。    OPEN pcursor;     REPEAT            FETCH pcursor INTO num, t;             IF NOT done THEN -- 还有记录                    IF t > avgage THEN -- 年龄大于传入的年龄值                    UPDATE person SET age = age + 5 where id = num;                END IF;            END IF;    UNTIL done END REPEAT;    CLOSE pcursor; END;SET @uage = 20;SELECT id, username, age FROM person where age > @uage;CALL proc_test_cursor_update(@uage); SELECT id, username, age FROM person where age > @uage;

    两次查询的结果一样,则游标执行正常。

相关文章列表:
关于爱程序网 - 联系我们 - 广告服务 - 友情链接 - 网站地图 - 版权声明 - 人才招聘 - 帮助