存储过程之四—游标

游标在存储过程和函数中使用。语法如同在嵌入的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;

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