MySQL游标的简单实践

Q：为什么要使用游标？

A：

　　在存储过程(或函数)中，如果某条select语句返回的结果集中只有1行，可以使用select into语句(上几篇博客有介绍到用法)来得到该行进行处理；如果结果集中有多行，简单的select语句成批地进行处理，需要在检索出来的行中前进或后退一行或多行……若是想得到其中的每一行进行处理，就必须使用游标。

Q：什么是游标？

A：

　　游标(cursor)，是一个存储在MySQL服务器上的数据库查询，游标不是一条 SELECT语句，而是被该语句检索出来的结果集；可以看做是指向查询结果集的指针；通过cursor，就可以一次一行的从结果集中把行拿出来处理。

注意：MySQL游标只能用于存储过程和函数。

游标的处理过程：4步

　　①声明游标declare：没有检索数据，只是定义要使用的select语句

　　②打开游标open：打开游标以供使用，用上一步定义的select语句把数据实际检索出来

　　③检索游标fetch：对于填有数据的游标，根据需要取出(检索)各行

　　④关闭游标close：在结束游标使用时，必须关闭游标

1、声明游标

DECLARE cursor_name CURSOR FOR select_statement;

声明一个游标cursor_name，让其指向查询select_statement的结果集。

注意：

　　①游标声明必须出现在变量和条件声明的后面，但是在异常处理声明的前面

　　②一个过程中可以有多个游标声明

2、打开游标

OPEN cursor_name;

cursor_name是声明中定义的名字；打开游标时才执行相应的select_statement。

3、检索游标

FETCH cursor_name INTO var_name [, var_name] …

从游标cursor_name中拿出一行，把该行的各个列值保存到各个变量中。

解析：

　　一次只拿一行，拿完后，自动移动指针到下一行；

　　如果没有拿到行，会抛出异常，其SQLSTATE代码值为‘02000’，此时要检测到该情况，需要声明异常处理程序 (针对条件NOT FOUND也可以)，通常需要在一个循环中来执行fetch语句，通过检测以上异常来结束循环。

4、关闭游标

CLOSE cursor_name;

收回游标占用的内存，别浪费资源嘛。

例1：创建过程，计算players表中行的数量

<span style="font-size: 16px;">mysql><span style="color: rgba(0, 0, 0, 1);"> delimiter $$
mysql</span>><span style="color: rgba(0, 0, 0, 1);"> create procedure number_of_players(
    </span>-> 　　<span style="color: rgba(0, 0, 255, 1);">out</span> pnumber <span style="color: rgba(0, 0, 255, 1);">int</span><span style="color: rgba(0, 0, 0, 1);">)
    </span>-><span style="color: rgba(0, 0, 0, 1);"> begin
    </span>-> 　　declare a_playerno <span style="color: rgba(0, 0, 255, 1);">int</span><span style="color: rgba(0, 0, 0, 1);">;  
    </span>-> 　　declare found <span style="color: rgba(0, 0, 255, 1);">bool</span> <span style="color: rgba(0, 0, 255, 1);">default</span> <span style="color: rgba(0, 0, 255, 1);">true</span><span style="color: rgba(0, 0, 0, 1);">;　　<span style="color: rgba(0, 128, 0, 1);">循环控制变量，其值为false时循环结束   
    </span></span>->
    -> 　　declare c_players cursor <span style="color: rgba(0, 0, 255, 1);">for</span>
    -> 　　　　<span style="color: rgba(0, 0, 255, 1);">select</span> playerno <span style="color: rgba(0, 0, 255, 1);">from</span><span style="color: rgba(0, 0, 0, 1);"> PLAYERS;  <span style="color: rgba(0, 128, 0, 1);">①声明游标
    </span></span>->
    -> 　　declare <span style="color: rgba(0, 0, 255, 1);">continue</span> handler <span style="color: rgba(0, 0, 255, 1);">for</span><span style="color: rgba(0, 0, 0, 1);"> not found
    </span>-> 　　　　<span style="color: rgba(0, 0, 255, 1);">set</span> found=<span style="color: rgba(0, 0, 255, 1);">false</span><span style="color: rgba(0, 0, 0, 1);">;  <span style="color: rgba(0, 128, 0, 1);">声明异常处理程序
    </span></span>->
    -> 　　<span style="color: rgba(0, 0, 255, 1);">set</span> pnumber=<span style="color: rgba(128, 0, 128, 1);">0</span><span style="color: rgba(0, 0, 0, 1);">;
    </span>->
    -><span style="color: rgba(0, 0, 0, 1);"> 　　open c_players;  <span style="color: rgba(0, 128, 0, 1);">②打开游标
    </span></span>->
    -><span style="color: rgba(0, 0, 0, 1);"> 　　fetch c_players into a_playerno;  <span style="color: rgba(0, 128, 0, 1);">③检索游标(检索第一行)
    </span></span>-> 　　<span style="color: rgba(0, 0, 255, 1);">while</span> found <span style="color: rgba(0, 0, 255, 1);">do</span>
    -> 　　　　<span style="color: rgba(0, 0, 255, 1);">set</span> pnumber=pnumber+<span style="color: rgba(128, 0, 128, 1);">1</span><span style="color: rgba(0, 0, 0, 1);">;
    </span>-><span style="color: rgba(0, 0, 0, 1);"> 　　　　fetch c_players into a_playerno;
    </span>-> 　　end <span style="color: rgba(0, 0, 255, 1);">while</span><span style="color: rgba(0, 0, 0, 1);">;  <span style="color: rgba(0, 128, 0, 1);">循环检索其余行
    </span></span>->
    -><span style="color: rgba(0, 0, 0, 1);"> 　　close c_players;  <span style="color: rgba(0, 128, 0, 1);">④关闭游标
    </span></span>-><span style="color: rgba(0, 0, 0, 1);"> end</span><span style="color: rgba(0, 0, 0, 1);">$$
mysql</span>><span style="color: rgba(0, 0, 0, 1);"> delimiter ;
mysql</span>><span style="color: rgba(0, 0, 0, 1);"> call number_of_players(@pnumber);
mysql</span>> <span style="color: rgba(0, 0, 255, 1);">select</span><span style="color: rgba(0, 0, 0, 1);"> @pnumber;
</span>+----------+
| @pnumber |
+----------+
|       <span style="color: rgba(128, 0, 128, 1);">14</span> |
+----------+<span style="color: rgba(0, 0, 0, 1);">
mysql</span>> <span style="color: rgba(0, 0, 255, 1);">select</span> count(*) <span style="color: rgba(0, 0, 255, 1);">from</span><span style="color: rgba(0, 0, 0, 1);"> PLAYERS;
</span>+----------+
| count(*) |
+----------+
|       <span style="color: rgba(128, 0, 128, 1);">14</span> |
+----------+</span>

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：创建过程，计算某个球员的罚款次数–游标声明中可以包含变量

<span style="font-size: 16px;">mysql><span style="color: rgba(0, 0, 0, 1);"> delimiter $$
mysql</span>><span style="color: rgba(0, 0, 0, 1);"> create procedure number_penalties(
    </span>-> 　　<span style="color: rgba(0, 0, 255, 1);">in</span> p_playerno <span style="color: rgba(0, 0, 255, 1);">int</span><span style="color: rgba(0, 0, 0, 1);">,
    </span>-> 　　<span style="color: rgba(0, 0, 255, 1);">out</span> pnumber <span style="color: rgba(0, 0, 255, 1);">int</span><span style="color: rgba(0, 0, 0, 1);">)
    </span>-><span style="color: rgba(0, 0, 0, 1);"> begin
    </span>-> 　　declare a_playerno <span style="color: rgba(0, 0, 255, 1);">int</span><span style="color: rgba(0, 0, 0, 1);">;
    </span>-> 　　declare found <span style="color: rgba(0, 0, 255, 1);">bool</span> <span style="color: rgba(0, 0, 255, 1);">default</span> <span style="color: rgba(0, 0, 255, 1);">true</span><span style="color: rgba(0, 0, 0, 1);">;  <span style="color: rgba(0, 128, 0, 1);">循环控制变量
    </span></span>-> 
    -> 　　declare c_players cursor <span style="color: rgba(0, 0, 255, 1);">for</span><span style="color: rgba(0, 128, 0, 1);">  声明游标
    </span>-> 　　　　<span style="color: rgba(0, 0, 255, 1);">select</span><span style="color: rgba(0, 0, 0, 1);"> playerno
    </span>-> 　　　　<span style="color: rgba(0, 0, 255, 1);">from</span><span style="color: rgba(0, 0, 0, 1);"> PENALTIES
    </span>-> 　　　　<span style="color: rgba(0, 0, 255, 1);">where</span> playerno =<span style="color: rgba(0, 0, 0, 1);"> p_playerno;  <span style="color: rgba(0, 128, 0, 1);">包含变量p_playerno
    </span></span>-> 
    -> 　　declare <span style="color: rgba(0, 0, 255, 1);">continue</span> handler <span style="color: rgba(0, 0, 255, 1);">for</span><span style="color: rgba(0, 0, 0, 1);"> not found
    </span>-> 　　　　<span style="color: rgba(0, 0, 255, 1);">set</span> found=<span style="color: rgba(0, 0, 255, 1);">false</span><span style="color: rgba(0, 0, 0, 1);">;  <span style="color: rgba(0, 128, 0, 1);">声明异常处理程序
    </span></span>-> 
    -> 　　<span style="color: rgba(0, 0, 255, 1);">set</span> pnumber=<span style="color: rgba(128, 0, 128, 1);">0</span><span style="color: rgba(0, 0, 0, 1);">;
    </span>->  
    -><span style="color: rgba(0, 0, 0, 1);"> 　　open c_players;  <span style="color: rgba(0, 128, 0, 1);">打开游标
    </span></span>-> 
    -><span style="color: rgba(0, 0, 0, 1);"> 　　fetch c_players into a_playerno;
    </span>-> 　　<span style="color: rgba(0, 0, 255, 1);">while</span> found <span style="color: rgba(0, 0, 255, 1);">do</span><span style="color: rgba(0, 128, 0, 1);">    循环检索游标每一行
    </span>-> 　　　　<span style="color: rgba(0, 0, 255, 1);">set</span> pnumber=pnumber+<span style="color: rgba(128, 0, 128, 1);">1</span><span style="color: rgba(0, 0, 0, 1);">;
    </span>-><span style="color: rgba(0, 0, 0, 1);"> 　　　　fetch c_players into a_playerno;
    </span>-> 　　end <span style="color: rgba(0, 0, 255, 1);">while</span><span style="color: rgba(0, 0, 0, 1);">;
    </span>->
    -><span style="color: rgba(0, 0, 0, 1);"> 　　close c_players;  <span style="color: rgba(0, 128, 0, 1);">关闭游标
    </span></span>-><span style="color: rgba(0, 0, 0, 1);"> end</span><span style="color: rgba(0, 0, 0, 1);">$$
mysql</span>><span style="color: rgba(0, 0, 0, 1);"> delimiter ;
mysql</span>> call number_penalties(<span style="color: rgba(128, 0, 128, 1);">44</span><span style="color: rgba(0, 0, 0, 1);">,@pnumber);
mysql</span>> <span style="color: rgba(0, 0, 255, 1);">select</span><span style="color: rgba(0, 0, 0, 1);"> @pnumber;
</span>+----------+
| @pnumber |
+----------+
|        <span style="color: rgba(128, 0, 128, 1);">3</span> |
+----------+</span>

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 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游标的简单实践

发表回复取消回复

MySQL游标的简单实践

发表回复 取消回复

发表回复取消回复