MySQL游標(biāo)操作指南

    本篇文章是對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;