存储过程之四—游标

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

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

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

目录
相关文章
|
6天前
|
存储 SQL 关系型数据库
MySQL基础-存储过程与函数
MySQL基础-存储过程与函数
|
5月前
|
存储 SQL 关系型数据库
MySQL基础篇——存储过程和函数
MySQL基础篇——存储过程和函数
46 0
|
10月前
|
存储 SQL 算法
MySQL基础篇(04):存储过程和视图,用法和特性详解
MySQL基础篇(04):存储过程和视图,用法和特性详解
85 0
|
存储 SQL 关系型数据库
推荐文章
更多