mysql 游标循环使用

测试数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `stu_id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `stu_sex` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `stu_age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`stu_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 71 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

INSERT INTO `student` VALUES (1, '李明', '男', 18);
INSERT INTO `student` VALUES (2, '李花', '女', 19);
INSERT INTO `student` VALUES (3, '关明', '男', 17);
INSERT INTO `student` VALUES (4, '高花', '女', 19);

SET FOREIGN_KEY_CHECKS = 1;

demo

CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
begin
    declare id int;
    declare realname varchar(100) character set utf8;
    declare sex varchar(1) character set utf8;
		declare age int;
    declare done int default 0;
    -- 声明游标
    declare mc cursor for select stu_id,stu_name,stu_age,stu_sex from student where stu_age>10;
     declare continue handler for not found set done = 1; 
-- 打开游标
    open mc;
    	-- 循环
		read_loop: LOOP
			-- 获取结果
			fetch mc into id,realname,age,sex;
			-- 声明结束
			IF done THEN
				LEAVE read_loop;
			END IF;
			-- 循环打印显示获取结果
			select id,realname,age,sex;
		END LOOP;
    -- 关闭游标
    close mc;
end