5.MYSQL存储过程-练习5 游标2024-05-15

6.MYSQL存储过程-练习62024-05-17

MYSQL存储过程-练习5 游标

 1 DELIMITER $
 2 CREATE PROCEDURE sp_cur()
 3 BEGIN
 4   DECLARE bkname VARCHAR(200);
 5   DECLARE done INT DEFAULT 0; #游标结束标志
 6   
 7   DECLARE cur CURSOR FOR SELECT bookname FROM book;     #声明游标
 8   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  #指定游标结束时的返回值
 9   
10   OPEN cur;  #打开游标
11     lp1:LOOP
12       FETCH cur INTO bkname;   #获取结果
13       IF done = 1 THEN         #当游标的返回值为 1 时 退出循环
14         leavr lp1;
15       END IF;
16       SELECT bkname;
17       END LOOP;
18         
19    CLOSE cur;  #关闭游标
20    
21 END$
22 DELIMITER;

执行存储过程，检查游标运行结果

 1 mysql> call sp_cur();
 2 +------------------------+
 3 | bkname                 |
 4 +------------------------+
 5 | HARRY POTER-魔法石1    |
 6 +------------------------+
 7 1 row in set (0.02 sec)
 8 
 9 +---------------------+
10 | bkname              |
11 +---------------------+
12 | HARRY POTER-密室2   |
13 +---------------------+
14 1 row in set (0.02 sec)
15 
16 +------------------------+
17 | bkname                 |
18 +------------------------+
19 | HARRY POTER-凤凰社3    |
20 +------------------------+
21 1 row in set (0.03 sec)
22 
23 +------------------------+
24 | bkname                 |
25 +------------------------+
26 | HARRY POTER-火焰杯4    |
27 +------------------------+
28 1 row in set (0.06 sec)
29 
30 +---------------------------+
31 | bkname                    |
32 +---------------------------+
33 | HARRY POTER-死亡圣器5     |
34 +---------------------------+
35 1 row in set (0.09 sec)
36 
37 Query OK, 0 rows affected (0.13 sec)