MySQL游标的简单实践

mysql> delimiter $$
mysql> create procedure number_of_players(
    -> 　　out pnumber int)
    -> begin
    -> 　　declare a_playerno int;  
    -> 　　declare found bool default true;　　循环控制变量，其值为false时循环结束   
    ->
    -> 　　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$$
mysql> delimiter ;
mysql> call number_of_players(@pnumber);
mysql> select @pnumber;
+----------+
| @pnumber |
+----------+
|       14 |
+----------+
mysql> select count(*) from PLAYERS;
+----------+
| count(*) |
+----------+
|       14 |
+----------+

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

mysql> delimiter $$
mysql> create procedure number_penalties(
    -> 　　in p_playerno int,
    -> 　　out pnumber int)
    -> begin
    -> 　　declare a_playerno int;
    -> 　　declare found bool default true;  循环控制变量
    -> 
    -> 　　declare c_players cursor for  声明游标
    -> 　　　　select playerno
    -> 　　　　from PENALTIES
    -> 　　　　where playerno = p_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$$
mysql> delimiter ;
mysql> call number_penalties(44,@pnumber);
mysql> select @pnumber;
+----------+
| @pnumber |
+----------+
|        3 |
+----------+

MySQL游标的简单实践

标签：若是   int   procedure   efault   number   创建过程   span   执行   while