mysql之游标

游标前言：
**1.**select into 单行多列数据（单行单列）
游标 多行多列数据
2.游标
定义游标
将一个游标和一个select语句进行关联
打开游标
将游标和select结果集关联，执行select语句
获取游标，获取结果集（fetch，等同于select into）
需要使用循环进行数据的获取
当获取到最后一个结果以后，再次执行循环的时候，会报错，这个错误以2开头，这个时候，我们需要定义一个对2开头的错误的捕获（not found）。
关闭游标，结果集消失
资源释放

游标经典案例：
1.

DELIMITER $$

CREATE  PROCEDURE number_of_players(
  OUT pnumber INTEGER)   
 BEGIN 
   DECLARE a_playerno INTEGER;
   DECLARE FOUND BOOLEAN DEFAULT TRUE;
   DECLARE c_players CURSOR FOR---定义游标
   					SELECT playerno FROM PLAYERS;
   DECLARE CONTINUE HANDLER FOR NOT FOUND  
   						SET FOUND = FALSE; 
SET pnumber = 0;   
OPEN c_players;----打开游标   
FETCH c_players INTO a_playerno;--获取游标，获取结果集（fetch，等同于select into）
   WHILE FOUND DO
     SET pnumber = pnumber + 1;
     FETCH c_players INTO a_playerno;
   END WHILE;    
CLOSE c_players;关闭游标
 END$$
DELIMITER ;
mysql> call number_of_players(@pnumber);
mysql> select @pnumber;
| @pnumber |
+----------+
|       14 |

**注意：**游标执行过程中，第一次fetch时，将第一行结果给变量，进入循环，然后再fetch，游标将第一行数据给了变量后紧接着就指向了第二行数据。依次循环，直至游标指向最后一行数据时运行完此次循环，报错（2000错误），错误被异常处理not found捕获，改变条件，循环条件不满足，跳出循环，结束。
2.

DELIMITER $$

CREATE  PROCEDURE number_penalties(
  IN p_playerno INTEGER,
  OUT pnumber INTEGER)   
 BEGIN 
   DECLARE a_playerno INTEGER;
   DECLARE FOUND BOOLEAN DEFAULT TRUE;
   DECLARE c_players CURSOR FOR
     SELECT playerno 
       FROM PENALTIES
      WHERE playerno = p_playerno;
   DECLARE CONTINUE HANDLER FOR NOT FOUND  
     SET FOUND = FALSE;   
SET pnumber = 0;   
   OPEN c_players;
   FETCH c_players INTO a_playerno;
   WHILE FOUND DO
     SET pnumber = pnumber + 1;
     FETCH c_players INTO a_playerno;
   END WHILE;    
   
   CLOSE c_players;
 END$$

DELIMITER ;
mysql> CALL number_penalties(44,@pnumber);
mysql> SELECT @pnumber;
| @pnumber |
+----------+
|        3 |

游标

在存储过程中，如果某条select语句返回的结果集中只有1行，可以使用select into语句来得到该行进行处理。如果结果集中有多行，又该如何得到其中的每一行进行处理呢？这必须使用游标
游标cursor：
可以看做是指向查询结果集的指针。通过它，就可以一次一行的从结果集中把行拿出来处理
游标的处理过程：4步
声明游标
打开游标
检索游标
关闭游标
声明游标

DECLARE cursor_name CURSOR FOR select_statement;

语义：声明一个游标cursor_name，让它指向查询select_statement的结果集
游标声明必须出现在变量和条件声明的后面，但是在异常处理声明前面
一个过程中可以有多个游标声明
打开游标

OPEN cursor_name;

打开游标时才执行相应的select_statement
检索游标

FETCH  cursor_name INTO var_name [, var_name] ...

从游标cursor_name中拿出一行，把该行的各个列值保存到各个变量中，一次只拿一行。拿完后，自动移动指针到下一行。
如果没有拿到行，会抛出异常，其SQLSTATE代码值为 ‘02000‘。要检测到该情况，需要声明异常处理程序 (针对条件 NOT FOUND 也可以)
通常需要在一个循环中来执行fetch语句，通过检测以上异常来结束循环
关闭游标

CLOSE cursor_name;

收回游标占用的内存
例21：创建过程，计算players表中行的数量

DELIMITER $$

CREATE  PROCEDURE number_of_players(
  OUT pnumber INTEGER)   
 BEGIN 
   DECLARE a_playerno INTEGER;
   -- 循环控制变量，其值为false时循环结束   
   DECLARE FOUND BOOLEAN DEFAULT TRUE;
    
   -- 声明游标  
   DECLARE c_players CURSOR FOR
     SELECT playerno FROM players;
     
   -- 声明异常处理程序   
   DECLARE CONTINUE HANDLER FOR NOT FOUND  
     SET FOUND = FALSE; 
 SET pnumber = 0;   
   -- 打开游标 
OPEN c_players;
  
   -- 游标检索。检索第一行
   FETCH c_players INTO a_playerno;
   -- 循环检索其余的行
   WHILE FOUND DO
     SET pnumber = pnumber + 1;
     FETCH c_players INTO a_playerno;
   END WHILE;    
   
   -- 关闭游标
   CLOSE c_players;
 END$$
DELIMITER ;

CALL number_of_players(@pnumber);
SELECT @pnumber;

游标声明中可以包含变量
例22：创建过程，计算某个球员的罚款次数

DELIMITER $$

CREATE  PROCEDURE number_penalties(
  IN p_playerno INTEGER,
  OUT pnumber INTEGER)   
 BEGIN 
   DECLARE a_playerno INTEGER;
   -- 循环控制变量，其值为false时循环结束   
   DECLARE FOUND BOOLEAN DEFAULT TRUE;
    
   -- 声明游标 。包含变量p_playerno 
   DECLARE c_players CURSOR FOR
     SELECT playerno 
       FROM penalties
      WHERE playerno = p_playerno;
     
   -- 声明异常处理程序   
   DECLARE CONTINUE HANDLER FOR NOT FOUND  
     SET FOUND = FALSE;   
 SET pnumber = 0;   
-- 打开游标
   OPEN c_players;
   
   -- 游标检索。检索第一行
   FETCH c_players INTO a_playerno;
   -- 循环检索其余的行
   WHILE FOUND DO
     SET pnumber = pnumber + 1;
     FETCH c_players INTO a_playerno;
   END WHILE;    
   
   -- 关闭游标
   CLOSE c_players;
 END$$

DELIMITER ;
CALL number_penalties(44,@pnumber);
SELECT @pnumber;

游标案例：

DROP PROCEDURE IF EXISTS proc_cursor1;
DELIMITER //
CREATE PROCEDURE proc_cursor1(OUT cnt INT,OUT cc_name VARCHAR(20))
BEGIN  
 /**声明变量**/
 DECLARE c_name VARCHAR(20) DEFAULT 'none';
 DECLARE c_id INT(11) ;
 DECLARE FOUND BOOLEAN DEFAULT TRUE;
 
 -- 声明游标
 DECLARE test2_cursor CURSOR FOR SELECT id,NAME FROM test2;
 
 -- 声明异常处理程序
 DECLARE CONTINUE HANDLER FOR NOT FOUND 
	SET FOUND=FALSE;

SET cnt =0;
OPEN test2_cursor;  -- 打开游标
FETCH test2_cursor INTO c_id,c_name;  -- 取到游标中的第一行数据赋值给变量c_name
SET cc_name = c_name;
WHILE FOUND DO 
	FETCH test2_cursor INTO c_id,c_name;
	-- select concat('123',c_name);
	IF c_name LIKE 'a%' THEN 
		SET cnt = cnt+1;
	END IF;
END WHILE;

CLOSE test2_cursor;
END //
DELIMITER ;

CALL proc_cursor1(@cnt,@cc_name);
SELECT @cnt,@cc_name;

#游标练习：
1.查询某个专业的学生
2.如果学生的总学分小于50，就修改该学生的年级，自动降级；

CREATE TABLE IF NOT EXISTS  test_cursor1(major VARCHAR(20),credit INT,class INT);
INSERT INTO test_cursor1 VALUES('yunji',50,6),('yunji',5,6),('yunji',60,6),('yunji',35,6),('yunji',25,6),('yunji',75,6);

DROP PROCEDURE IF EXISTS proc_cursor1;

DELIMITER //
CREATE PROCEDURE proc_cursor1(IN major_name VARCHAR(20))
BEGIN  
 /**声明变量**/
 DECLARE c_major VARCHAR(20);
 DECLARE c_credit INT;
 DECLARE c_class INT;
 DECLARE FOUND BOOLEAN DEFAULT TRUE;
 -- 声明游标
 DECLARE test2_cursor CURSOR FOR SELECT major,credit,class FROM test_cursor1;
 -- 声明异常处理程序
 DECLARE CONTINUE HANDLER FOR NOT FOUND 
	SET FOUND=FALSE;
OPEN test2_cursor;  -- 打开游标
WHILE FOUND DO -- 遍历游标
	FETCH test2_cursor INTO c_major,c_credit,c_class;
	IF (c_major = major_name ) AND (c_credit < 50) THEN 
		UPDATE  test_cursor1 
		SET class=c_class-1 
		WHERE (major=c_major) AND (credit=c_credit);
	END IF;
END WHILE;
CLOSE test2_cursor;
END //
DELIMITER ;

CALL proc_cursor1('yunji');
SELECT * FROM test_cursor1;