MySQL之游标

• 游标的概念

  • 游标可以遍历返回的多行结果，每次拿到一整行数据

  • 在存储过程和函数中可以使用游标对结果集进行循环的处理

  • 简单来说游标就类似于集合的迭代器遍历

  • MySQL中的游标只能用在存储过程和函数中

• 游标的语法
  • 创建游标

    -- 标准语法
    DECLARE 游标名称 CURSOR FOR 查询sql语句;

  •  打开游标

    -- 标准语法
    OPEN 游标名称;

  • 使用游标获取数据

    -- 标准语法
    FETCH 游标名称 INTO 变量名1,变量名2,...;

  • 关闭游标

    -- 标准语法
    CLOSE 游标名称;

  • 游标的基本使用

    -- 创建stu_score表
    CREATE TABLE stu_score(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	score INT
    );
    
    /*
    	将student表中所有的成绩保存到stu_score表中
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test11()
    BEGIN
    	-- 定义成绩变量
    	DECLARE s_score INT;
    	-- 创建游标,查询所有学生成绩数据
    	DECLARE stu_result CURSOR FOR SELECT score FROM student;
    	
    	-- 开启游标
    	OPEN stu_result;
    	
    	-- 使用游标，遍历结果,拿到第1行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 使用游标，遍历结果,拿到第2行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 使用游标，遍历结果,拿到第3行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 使用游标，遍历结果,拿到第4行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 关闭游标
    	CLOSE stu_result;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test11存储过程
    CALL pro_test11();
    
    -- 查询stu_score表
    SELECT * FROM stu_score;
    
    
    -- ===========================================================
    /*
    	出现的问题：
    		student表中一共有4条数据，我们在游标遍历了4次，没有问题！
    		但是在游标中多遍历几次呢？就会出现问题
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test11()
    BEGIN
    	-- 定义成绩变量
    	DECLARE s_score INT;
    	-- 创建游标,查询所有学生成绩数据
    	DECLARE stu_result CURSOR FOR SELECT score FROM student;
    	
    	-- 开启游标
    	OPEN stu_result;
    	
    	-- 使用游标，遍历结果,拿到第1行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 使用游标，遍历结果,拿到第2行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 使用游标，遍历结果,拿到第3行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 使用游标，遍历结果,拿到第4行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 使用游标，遍历结果,拿到第5行数据
    	FETCH stu_result INTO s_score;
    	-- 将数据保存到stu_score表中
    	INSERT INTO stu_score VALUES (NULL,s_score);
    	
    	-- 关闭游标
    	CLOSE stu_result;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test11存储过程
    CALL pro_test11();
    
    -- 查询stu_score表,虽然数据正确，但是在执行存储过程时会报错
    SELECT * FROM stu_score;

  • 游标的优化使用（配合循环使用）

    /*
        当游标结束后，会触发游标结束事件。我们可以通过这一特性来完成循环操作
        加标记思想：
            1.定义一个变量，默认值为0(意味着有数据)
            2.当游标结束后，将变量值改为1(意味着没有数据了)
    */
    -- 1.定义一个变量，默认值为0(意味着有数据)
    DECLARE flag INT DEFAULT 0;
    -- 2.当游标结束后，将变量值改为1(意味着没有数据了)
    DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;

    /*
    	将student表中所有的成绩保存到stu_score表中
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test12()
    BEGIN
    	-- 定义成绩变量
    	DECLARE s_score INT;
    	-- 定义标记变量
    	DECLARE flag INT DEFAULT 0;
    	-- 创建游标，查询所有学生成绩数据
    	DECLARE stu_result CURSOR FOR SELECT score FROM student;
    	-- 游标结束后，将标记变量改为1
    	DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
    	
    	-- 开启游标
    	OPEN stu_result;
    	
    	-- 循环使用游标
    	REPEAT
    		-- 使用游标，遍历结果,拿到数据
    		FETCH stu_result INTO s_score;
    		-- 将数据保存到stu_score表中
    		INSERT INTO stu_score VALUES (NULL,s_score);
    	UNTIL flag=1
    	END REPEAT;
    	
    	-- 关闭游标
    	CLOSE stu_result;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test12存储过程
    CALL pro_test12();
    
    -- 查询stu_score表
    SELECT * FROM stu_score;