本篇文章是對MySQL游標(biāo)的具體使用進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下

測試表level

代碼如下:

createtabletest.level(namevarchar(20));

再insert些數(shù)據(jù);

初始化

代碼如下:

dropprocedureifexistsuseCursor//

建立存儲過程create

代碼如下:

CREATEPROCEDUREuseCursor()

BEGIN

局部變量的定義declare

代碼如下:

declaretmpNamevarchar(20)default'';

declareallNamevarchar(255)default'';

declarecur1CURSORFORSELECTnameFROMtest.level;

MySQL游標(biāo)異常后捕捉

并設(shè)置循環(huán)使用變量tmpname為null跳出循環(huán)。

代碼如下:

declareCONTINUEHANDLERFORSQLSTATE'02000'SETtmpname=null;

開游標(biāo)

代碼如下:

OPENcur1;

游標(biāo)向下走一步

代碼如下:

FETCHcur1INTOtmpName;

循環(huán)體這很明顯把MySQL游標(biāo)查詢出的name都加起并用;號隔開

代碼如下:

WHILE(tmpnameisnotnull)DO

settmpName=CONCAT(tmpName,";");

setallName=CONCAT(allName,tmpName);

游標(biāo)向下走一步

代碼如下:

FETCHcur1INTOtmpName;

結(jié)束循環(huán)體:

代碼如下:

ENDWHILE;

關(guān)閉游標(biāo)

代碼如下:

CLOSEcur1;

選擇數(shù)據(jù)

代碼如下:

selectallName;

結(jié)束存儲過程

代碼如下:

END;//

調(diào)用存儲過程：

代碼如下:

calluseCursor()//

loop循環(huán)游標(biāo)：

代碼如下:

DELIMITER$$

DROPPROCEDUREIFEXITScursor_example$$

CREATEPROCEDUREcursor_example()

READSSQLDATA

BEGIN

DECLAREl_employee_idINT;

DECLAREl_salaryNUMERIC(8,2);

DECLAREl_department_idINT;

DECLAREdoneINTDEFAULT0;

DECLAREcur1CURSORFORSELECTemployee_id,salary,department_idFROMemployees;

DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;

OPENcur1;

emp_loop:LOOP

FETCHcur1INTOl_employee_id,l_salary,l_department_id;

IFdone=1THEN

LEAVEemp_loop;

ENDIF;

ENDLOOPemp_loop;

CLOSEcur1;

END$$

DELIMITER;

repeat循環(huán)游標(biāo)：

代碼如下:

/*創(chuàng)建過程*/

DELIMITER//

DROPPROCEDUREIFEXISTStest//

CREATEPROCEDUREtest()

BEGIN

DECLAREdoneINTDEFAULT0;

DECLAREaVARCHAR(200)DEFAULT'';

DECLAREcVARCHAR(200)DEFAULT'';

DECLAREmycursorCURSORFORSELECTfusernameFROMuchome_friend;

DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;

OPENmycursor;

REPEAT

FETCHmycursorINTOa;

IFNOTdoneTHEN

SETc=CONCAT(c,a);/*字符串相加*/

ENDIF;

UNTILdoneENDREPEAT;

CLOSEmycursor;

SELECTc;

END//

DELIMITER;

代碼如下:

/*創(chuàng)建過程*/

DELIMITER//

DROPPROCEDUREIFEXISTStest//

CREATEPROCEDUREtest()

BEGIN

DECLAREdoneINTDEFAULT0;

DECLAREaVARCHAR(200)DEFAULT'';

DECLAREcVARCHAR(200)DEFAULT'';

DECLAREmycursorCURSORFORSELECTfusernameFROMuchome_friend;

DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;

OPENmycursor;

REPEAT

FETCHmycursorINTOa;

IFNOTdoneTHEN

SETc=CONCAT(c,a);/*字符串相加*/

ENDIF;

UNTILdoneENDREPEAT;

CLOSEmycursor;

SELECTc;

END//

DELIMITER;

更多信息請查看IT技術(shù)專欄