mysql 游标的简单_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

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://www.cnblogs.com/n276973988/p/6817894.html