腳本之家服務器常用軟件

快捷導航

MySQL游標概念與用法詳解

更新時間：2019年07月05日 11:48:01 作者：webbc

這篇文章主要介紹了MySQL游標概念與用法,簡單描述了mysql游標的概念、原理并結合實例形式詳細分析了游標的各種常見用法與相關操作注意事項,需要的朋友可以參考下

本文實例講述了MySQL游標概念與用法。分享給大家供大家參考，具體如下：

1、游標的概念（Cursor）

一條sql，對應N條資源，取出資源的接口，就是游標，沿著游標，可以一次取出1行。如果開發(fā)過安卓的同學應該知道有一個Api是Cursor，也是讀取SQLite數(shù)據庫用的，和這個有點類似。

2、使用游標的步驟

（1）聲明

使用declare進行聲明

declare 游標名 cursor for select_statement

（2）打開游標

使用open進行打開

open 游標名

（3）從游標中取值

使用fetch進行取值

fetch 游標名 into var1,var2[,...] --將取到的一行賦值給多個變量

（4）關閉游標

使用close關閉游標

close 游標名

3、創(chuàng)建一個簡單的游標

需求：從商品表中讀取第一行數(shù)據

商品表（goods）數(shù)據：

這里寫圖片描述

注意：我這里已經將MySQL的結束標識符改為 $，如果要知道怎么設置為$，請參考前面一篇文章：MySQL觸發(fā)器。

定義：

create procedure p12()
begin
  /*定義三個變量用于存放商品id,商品名稱，商品庫存量*/
  declare row_gid int ; 
  declare row_name varchar(20);
  declare row_num int;
  declare getgoods cursor for select gid,name,num from goods;  --定義游標
  open getgoods; --打開游標
  fetch getgoods into row_gid,row_name,row_num;--從游標中取值
  select row_name,row_num; --顯示操作
  close getgoods; --關閉游標
end$

輸出結果：

這里寫圖片描述

4、多次取值操作

create procedure p13()
begin
  declare row_gid int ;
  declare row_name varchar(20);
  declare row_num int;
  declare getgoods cursor for select gid,name,num from goods;  
  open getgoods;
  fetch getgoods into row_gid,row_name,row_num;
  select row_name,row_num;
  fetch getgoods into row_gid,row_name,row_num;
  select row_name,row_num;
  fetch getgoods into row_gid,row_name,row_num;
  select row_name,row_num;
  fetch getgoods into row_gid,row_name,row_num;
  select row_name,row_num;
  close getgoods;
end$

輸出：

這里寫圖片描述

注意：當游標讀到末尾，如果繼續(xù)進行取值操作會發(fā)生報錯

5、游標循環(huán)表中的所有數(shù)據

（1）使用計數(shù)器來循環(huán)

create procedure p14()
begin 
  declare cnt int default 0;
  declare i int default 0;
  declare row_gid int ;
  declare row_name varchar(20);
  declare row_num int;
  declare getgoods cursor for select gid,name,num from goods;
  select count(*) into cnt from goods;
  open getgoods;
  repeat 
    fetch getgoods into row_gid,row_name,row_num;
  select row_name,row_num;
  set i:= i+1;
  until i >= cnt end repeat;
  close getgoods;
end$

輸出結果：

這里寫圖片描述

（2）使用越界標志來控制循環(huán)

在mysql cursor中，可以聲明declare continue handler來操作1個越界標志

語法：

declare continue handler for NOT FOUND statement;

使用：

create procedure p15()
begin
  declare row_gid int ;
  declare row_name varchar(20);
  declare row_num int;
  declare have int default 1;
  declare getgoods cursor for select gid,name,num from goods;
  declare continue handler for NOT FOUND set have:= 0;
  open getgoods;
  repeat 
    fetch getgoods into row_gid,row_name,row_num;
  select row_name,row_num;
  until have = 0 end repeat;
  close getgoods;
end$

輸出結果：

這里寫圖片描述

注意：這里發(fā)生了錯誤，這里輸出了4行數(shù)據，而表中只有3行數(shù)據，而且還爆出了警告，后面會說怎么結果這個問題。

程序執(zhí)行邏輯：

循環(huán)游標->fetch第三條數(shù)據->顯示->fetch第四條數(shù)據->沒有數(shù)據->設置have=0操作->執(zhí)行continue Handler->程序不退出，執(zhí)行顯示操作->還是顯示第三條數(shù)據

6、continue和exit的區(qū)別

continue：若沒有數(shù)據返回，程序繼續(xù)，并將變量IS_FOUND設為0，這種情況是出現(xiàn)在select XX into XXX from tablename的時候發(fā)生的。
exit：若沒有數(shù)據返回，退出程序，并將變量IS_FOUND設為0，這種情況是出現(xiàn)在select XX into XXX from tablename的時候發(fā)生的。

使用exit來替換continue：
使用exit就不會出現(xiàn)上面的那種情況了，程序執(zhí)行邏輯：

循環(huán)游標->fetch到第三條數(shù)據->顯示->第四次fetch操作->沒有數(shù)據->設置 have=0操作->程序直接退出exit

所以就沒有顯示出第四條數(shù)據。

create procedure p16()
begin
  declare row_gid int ;
  declare row_name varchar(20);
  declare row_num int;
  declare have int default 1;
  declare getgoods cursor for select gid,name,num from goods;
  declare exit handler for NOT FOUND set have:= 0;
  open getgoods;
  repeat 
    fetch getgoods into row_gid,row_name,row_num;
  select row_name,row_num;
  until have = 0 end repeat;
  close getgoods;
end$

輸出結果：

這里寫圖片描述

7、正確的游標循環(huán)

在一些特殊的情況中，我們可以讀到的數(shù)據為空，或者壓根sql語句就有錯誤，我們不能避免出現(xiàn)這種情況，所以我們要正確的使用游標循環(huán)操作。

首先應該創(chuàng)建游標，然后打開游標后，應先手動進行fetch操作獲取到一行數(shù)據，然后再通過循環(huán)，在循環(huán)里先做處理內容，后進行fetch操作。這樣如果在手動獲取數(shù)據的期間就沒有獲得到數(shù)據的話，就會執(zhí)行have = 0，如果是repeat循環(huán)，然后進入repeat循環(huán)，先輸出null數(shù)據，最后又進行獲取，這樣運行到until時就會退出循環(huán)；如果是while循環(huán)，壓根就不進去while循環(huán)里，就不會有任何1行輸出。

（1）repeat循環(huán)：

create procedure p17()
begin
  declare row_gid int;
  declare row_name varchar(20);
  declare row_num int;
  declare have int default 1;
  declare getgoods cursor for select gid,name,num from goods where 0;
  declare continue handler for NOT FOUND set have:= 0;
  open getgoods;
  fetch getgoods into row_gid,row_name,row_num;
  repeat 
  select row_name,row_num;
    fetch getgoods into row_gid,row_name,row_num;
  until have = 0 end repeat;
  close getgoods;
end$

輸出結果：

這里寫圖片描述

（2）while循環(huán)：

create procedure p18()
begin
  declare row_gid int;
  declare row_name varchar(20);
  declare row_num int;
  declare have int default 1;
  declare getgoods cursor for select gid,name,num from goods where 0;
  declare continue handler for NOT FOUND set have:= 0;
  open getgoods;
  fetch getgoods into row_gid,row_name,row_num;
  while have = 1 do 
  select row_name,row_num;
    fetch getgoods into row_gid,row_name,row_num;
  end while;
  close getgoods;
end$

輸出結果：

這里寫圖片描述