Detailed usage of the "MySQL" cursor

Test table level;

Create table Test.  Level varchar ());

Insert some data, code, initialize

Drop procedure if exists //

Create a stored procedure create

CREATE PROCEDURE usecursor () BEGIN

Definition of local variables declare

Declare varchar (default"declarevarchar" (255Default "'  declareCURSORforSELECT from Test.  Level

MySQL cursor Catch after exception

and set the loop to use the variable tmpname null to jump out of the loop.

Declare CONTINUE  for ' 02000 ' SET = NULL

Open cursor

OPEN Cur1;

The cursor goes down one step

FETCH  into Tmpname;

Loop body This obviously adds up the name queried by the MySQL cursor; Number separated

 while  is  not NULL   set = set =

The cursor goes down one step

FETCH  into Tmpname;

End Loop Body:

END while;

Close Cursors

CLOSE Cur1;

Select data

Select allname;

End Stored Procedure

END; //

To call a stored procedure:

Call Usecursor ()//

Operation Result:

 mysql>  call Usecursor () // +  -- ------------- -----------------------+  |  allname |  +  -- ------------------------------------+  |  F1;c3;c6;c5;c2;c4;c1;f1;f3;f4;f2;f5; |  +  -- ------------------------------------+  1  row in  set  (0.00  sec) 

Loop loop cursors:

DELIMITER $$DROP PROCEDURE IFEXITS cursor_example$$CREATE PROCEDUREcursor_example () READS SQL DATABEGIN       DECLAREl_employee_idINT; DECLAREL_salary NUMERIC (8,2); DECLAREl_department_idINT; DECLAREDoneINT DEFAULT 0; DECLARECur1CURSOR  for SELECTemployee_id, salary, department_id fromemployees; DECLARE CONTINUEHANDLER for  notFOUNDSETDone=1; OPENCur1; Emp_loop:loopFETCHCur1 intol_employee_id, L_salary, l_department_id; IFDone=1  ThenLEAVE Emp_loop; END IF; ENDLOOP Emp_loop; CLOSECur1; END$$ DELIMITER; 

Repeat Loop cursors:

/*Create a process*/DELIMITER//DROP PROCEDURE IF EXISTSTest//CREATE PROCEDURETest ()BEGIN    DECLAREDoneINT DEFAULT 0; DECLAREAVARCHAR( $)DEFAULT "'; DECLARECVARCHAR( $)DEFAULT "'; DECLAREMyCursorCURSOR  for SELECTFusername fromUchome_friend; DECLARE CONTINUEHANDLER for  notFOUNDSETDone=1; OPENMyCursor; REPEATFETCHMyCursor intoA; IF  notDone Then            SETC=CONCAT (C,a);/*string Addition*/        END IF; UNTIL DoneENDREPEAT; CLOSEMyCursor; SELECTC;END //DELIMITER;

/*******************************************Loop Statement ************************************************//*(1). While Loop*/ [Label:]    whileexpression do statementsEND    while   [label] ;/*(2). Loop Loop*/ [Label:]LOOP StatementsENDLOOP[label] ;/*(3). REPEAT until cycle*/ [Label:]REPEAT statements UNTIL expressionENDREPEAT[label];

Original address: http://www.jb51.net/article/38317.htm

Detailed usage of the "MySQL" cursor