游标前言:
**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;