MySQL遊標的簡單實踐

標籤：若是   int   procedure   efault   number   建立過程   span   執行   while   

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表中行的數量

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遊標的簡單實踐