Posted on 2008-04-27 17:05
G_G 閱讀(7120)
評論(0) 編輯 收藏 所屬分類:
Database
請使用 mysql 1.5 或以上version;
測試表 level ;
create table
test.level (name varchar(20));
再 insert 些數(shù)據(jù) ;
?/*初始化*/?
?drop?procedure?if?exists??useCursor?//????
?
?/*建立?存儲過程?create?*/?
?CREATE?PROCEDURE?useCursor()
????BEGIN
????/*局部變量的定義?declare*/?
?????????declare?tmpName?varchar(20)?default?''?;
?????????declare?allName?varchar(255)?default?''?;
?????????
?????????declare?cur1?CURSOR?FOR?SELECT?name?FROM?test.level?;
?????????
?????????/*????mysql?不知道為什么用異常加入判斷?？
??????????*????此請參考官方文檔 20.2.11.
光標 光標?
??????????*????????這把?游標?異常后?捕捉?
??????????*????????并設(shè)置?循環(huán)使用?變量?tmpname?為?null?跳出循環(huán)。
??????????*/
?????????declare?CONTINUE?HANDLER?FOR?SQLSTATE?'02000'?SET?tmpname?=?null;
????
????
????/*開游標*/?
?????OPEN?cur1;
?????????/*游標向下走一步*/?
?????????FETCH?cur1?INTO?tmpName;
?????????
?????????/*?循環(huán)體?這很明顯?把游標查詢出的?name?都加起并用?;?號隔開?*/
??????WHILE?(?tmpname?is?not?null)?DO
??????????set?tmpName?=?CONCAT(tmpName?,";")?;
??????? ? set?allName?=?CONCAT(allName?,tmpName)?;
????????/*游標向下走一步*/?
????????FETCH?cur1?INTO?tmpName;
??????END?WHILE;
??
????CLOSE?cur1;
????
????select?allName?;
END;//
call?useCursor()//
? 運行結(jié)果：
mysql>?call?useCursor()//
+--------------------------------------+
|?allName??????????????????????????????|
+--------------------------------------+
|?f1;c3;c6;c5;c2;c4;c1;f1;f3;f4;f2;f5;?|
+--------------------------------------+
1?row?in?set?(0.00?sec)