#在存储过程中使用游标DROP TABLE IF EXISTStbl;CREATE TABLEtbl (IDINT not NULLAuto_increment, TitleVARCHAR( -) not NULL, authorVARCHAR( -) not NULL, PRIMARY KEY(ID));DROP PROCEDURE IF EXISTSSP7;CREATE PROCEDURESP7 (INOUT rstVARCHAR( -), Out Rst_codeINT, Out Rst_msgTEXT) COMMENT'using cursors in stored procedures'BEGIN DECLAREErr_staVARCHAR(5)DEFAULT "'; DECLAREErr_msgTEXT DEFAULT "'; DECLAREcsr_idINT; DECLARECsr_titleVARCHAR( -); DECLARECsr_authorVARCHAR( -); DECLARECsr_doneINT DEFAULTFALSE; #声明游标结束标识DECLARECsrCURSOR for SELECTId,title,author fromtbl; #声明游标DECLARE EXITHANDLER forsqlwarning,sqlexceptionBEGINGET Diagnostics CONDITION1Err_sta=Returned_sqlstate,err_msg=Message_text; SETRst_code=FALSE; SETRst_msg=CONCAT ('[ERR]', Err_sta,' - ', err_msg); ROLLBACK; END; DECLARE CONTINUEHANDLER for notFOUNDSETCsr_done=TRUE; #游标NOT found handler #SIGNAL SQLSTATE'42000' SETMessage_text= 'When some value was not allowed'; #自定义异常返回消息 STARTTRANSACTION; OPENCSR; #开启游标 Lp:loopFETCHCsr intoCsr_id,csr_title,csr_author; #按照查询表列的顺序IFCsr_done ThenLEAVE LP; END IF; SETRst=CONCAT (Csr_title,';', RST); ENDLOOP LP; CLOSECSR; #关闭游标 #INSERT intoTBL (Title,author)VALUES('Learn CSharp','James'); #INSERT intoTBL (Title,author)VALUES(NULL,'Jen');#[ERR] 23000 - Column 'title'Cannot beNULL COMMIT; SETRst_code=TRUE, Rst_msg="';ENDSET @rst="'; Call SP7 (@rst,@rst_code,@rst_msg);SELECT @rst asRst@rst_code asRst_code,@rst_msg asrst_msg;
MySQL uses cursors in stored procedures