.jpg)
數(shù)據(jù)庫中，對(duì)數(shù)據(jù)的處理分為兩種方式：?
??????一種是基于數(shù)據(jù)行集合的整體處理方式，直接使用select、update、delete等語句來操作（select語句直接查詢某一整列）；?
?????? 一種是逐行處理數(shù)據(jù)行的方式，游標(biāo)就是這種數(shù)據(jù)訪問機(jī)制，允許用戶一次訪問單個(gè)數(shù)據(jù)行，而非整個(gè)數(shù)據(jù)行集（游標(biāo)在某一列中進(jìn)行一行一行查詢）。
一、創(chuàng)建數(shù)據(jù)表
mysql>?select?*?from?person; +----+------+------+------+-----------+|?id?|?name?|?sex??|?age??|?addr??????| +----+------+------+------+-----------+|??1?|?Jone?|?fema?|???27?|?xianggang?| |??2?|?Lily?|?fema?|???25?|?taiwan????| |??3?|?Bobe?|?male?|???25?|?ximan?????||??4?|?Kity?|?fama?|???20?|?beijing???| +----+------+------+------+-----------+
查詢person數(shù)據(jù)表中的addr列，使得結(jié)果以這種形式輸出：?
xianggang;taiwan;ximan;beijing;
二、查詢
方式1：
drop?procedure?if?exists?useCursor?;delimiter?//CREATE?PROCEDURE?useCursor()?#?創(chuàng)建一個(gè)存儲(chǔ)過程??BEGIN ????DECLARE?oneAddr?varchar(20)?default?'';?? #?定義一個(gè)變量oneAddr ????DECLARE?allAddr?varchar(80)?default?'';?? #?定義一個(gè)變量allAddr ????DECLARE?curl?CURSOR?FOR?SELECT?addr?FROM?person.person;?#?定義一個(gè)游標(biāo)curl ????DECLARE?CONTINUE?HANDLER?FOR?SQLSTATE?'02000'?SET?oneAddr?=?null;? #?如果沒有數(shù)據(jù)返回,就將變量oneAddr設(shè)置為null ????#?也可以這么寫 ????#?DECLARE?CONTINUE?HANDLER?FOR?NOT?FOUND?SET?oneAddr?=?null; ????OPEN?curl;??#?打開游標(biāo) ????FETCH?curl?INTO?oneAddr;? #?通過游標(biāo)讀取數(shù)據(jù)????WHILE(oneAddr?is?not?null)?DO?? #?使用?while...do?循環(huán)來遍歷?addr?列?????? set?oneAddr?=?CONCAT(oneAddr,?';');?????? set?allAddr?=?CONCAT(allAddr,?oneAddr); ??????FETCH?curl?into?oneAddr;????END?WHILE; ????CLOSE?curl;?#?關(guān)閉游標(biāo)????SELECT?allAddr;?? ??END;//call?useCursor();
方式2：
drop?procedure?if?exists?useCursor;delimiter?//CREATE?PROCEDURE?useCursor() ??BEGIN ????DECLARE?oneAddr?varchar(20)?default?''; ????DECLARE?allAddr?varchar(80)?default?''; ????DECLARE?done?INT?DEFAULT?0;?#?定義一個(gè)默認(rèn)值0 ????DECLARE?curl?CURSOR?FOR?SELECT?addr?FROM?person.person; ????DECLARE?CONTINUE?HANDLER?FOR?NOT?FOUND?SET?done?=?1; ????OPEN?curl;????REPEAT??#?使用repeat循環(huán)來遍歷addr列 ??????FETCH?curl?INTO?oneAddr;??????IF?NOT?done?THEN ????????set?oneAddr?=?CONCAT(oneAddr,?';');???????? set?allAddr?=?CONCAT(allAddr,?oneAddr);??????END?IF;????UNTIL?done?END?REPEAT;?#直到為0才結(jié)束循環(huán) ????CLOSE?curl;????select?allAddr;??END;//call?useCursor();
方式3：
drop?procedure?if?exists?useCursor;delimiter?//CREATE?PROCEDURE?useCursor() ??BEGIN ????DECLARE?oneAddr?varchar(20)?default?''; ????DECLARE?allAddr?varchar(80)?default?''; ????DECLARE?done?bool?DEFAULT?false;?#?定義布爾變量,默認(rèn)值為false ????DECLARE?curl?CURSOR?FOR?SELECT?addr?FROM?person.person; ????DECLARE?CONTINUE?HANDLER?FOR?NOT?FOUND?SET?done?=?true; ????OPEN?curl; ????personLoop:?LOOP??#使用loop循環(huán)來遍歷addr列 ??????FETCH?curl?INTO?oneAddr;??????IF?done?THEN ????????LEAVE?personLoop;??????ELSE ????????set?oneAddr?=?CONCAT(oneAddr,?';');???????? set?allAddr?=?CONCAT(allAddr,?oneAddr);??????END?IF;????END?LOOP?personLoop; ????CLOSE?curl;????select?allAddr;??END;//call?useCursor();
三、輸出結(jié)果
mysql>?call?useCursor();// +---------------------------------+|?allAddr?????????????????????????| +---------------------------------+|?xianggang;taiwan;ximan;beijing;?| +---------------------------------+
以上就是?【MySQL 10】游標(biāo)的內(nèi)容，更多相關(guān)內(nèi)容請(qǐng)關(guān)注PHP中文網(wǎng)（www.php.cn）！
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載。
THE END
.png)
