游标在存储过程和函数中使用。语法如同在嵌入的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;
两次查询的结果一样,则游标执行正常。