Use of MySQL Cursors

What is a cursor??

A cursor is a database query stored on a MySQL server that is not a SELECT statement but a result set retrieved by the statement.

Using cursors

Before describing how to create a cursor, let's explain how to use the cursor.

There are several explicit steps involved in using cursors.

1. Before you can use a cursor, you must define it. This process actually does not retrieve the data, it simply defines the SELECT statement to use.

2. Once you have defined a cursor, you must open the cursor for use. This process actually retrieves the data from the previously defined SELECT statement. That is, after this step, we can manipulate the data in the cursor.

3, for the data cursor, according to the need to take out the data of the rows to carry out certain operations.

4. When you are finished using the cursor, be sure to close the cursor.

Creating cursors

The syntax for creating a cursor is as follows:

Use declare to define, as follows:

The above is a general form of defining a cursor, and once we have defined a cursor, we can open it, use it, and close it.

To open a cursor :

Open cursor_name;

To close a cursor :

Close cursor_name;

using Cursors :
Fetch data using a cursor, for example: Fetch cursor_name in variable;//remove the data indicated by the cursor to the local variable variable

The following example demonstrates creating a cursor, opening the cursor, and closing the cursor, without data for the data indicated by the cursor.

Example: Manipulating data in a cursor

The demand is like this, we have a student2 table in hand, the structure and data in the table are as follows:

Now we need to synthesize one line of the average score (half of Mathscore+englishscore) for all students in the Student2 table, separated by commas ', '.

For such a requirement, let's try to implement it with a cursor.

1 DELIMITER $$2 3 CREATE4     PROCEDURE' test '. ' Procedure_student2 ' ()5     BEGIN6     --declare some variable, you must precede the declaration of the cursor and handle, and the declaration handle must be after the cursor is declared. 7     DECLAREValDOUBLE DEFAULT 0;8     DECLARETempresVARCHAR(Ten)DEFAULT "';9     DECLAREResVARCHAR( -)DEFAULT "' ;Ten     --Declare a cursor One     DECLARECursor_avgscoreCURSOR A      for -     SELECT(Mathscore+Englishscore)/2   asStudent_avgscore fromStudent2; -     --Declare a continue handler, use finish and loop the     DECLARE CONTINUEHANDLER forSQLSTATE'02000'  SETVal= -1.0 ;  -     --Open Cursor -     OPENCursor_avgscore; -     FETCHCursor_avgscore intoVal; +     --FETCH CURSOR -      whileVal!=-1 Do +         SETTempres=CONCAT (Val,', '); A         SETRes=CONCAT (res,tempres); at         FETCHCursor_avgscore intoVal; -     END  while;  -     --Close Cursor -     CLOSECursor_avgscore; -     --Show Results -     SELECTRes; in     END$$ -  toDELIMITER;

Call this stored procedure

Call Procedure_student2 ();

The results of the operation are as follows:

The above is the use of a while loop to get the data in a cursor, in MySQL we can also use repeat to do.

1 DELIMITER $$2 3 CREATE4     PROCEDURE' test '. ' Procedure_student_v1 ' ()5     BEGIN6     --declare some variable, you must precede the declaration of the cursor and handle, and the declaration handle must be after the cursor is declared. 7     DECLAREValDOUBLE DEFAULT 0;8     DECLARETempresVARCHAR(Ten)DEFAULT "';9     DECLAREResVARCHAR( -)DEFAULT "' ;Ten     --Declare a cursor One     DECLARECursor_avgscoreCURSOR A      for -     SELECT(Mathscore+Englishscore)/2   asStudent_avgscore fromStudent2; -     --Declare a continue handler, use finish and loop the     DECLARE CONTINUEHANDLER forSQLSTATE'02000'  SETVal= -1.0 ;  -     --Open Cursor -     OPENCursor_avgscore;  -     --FETCH CURSOR + REPEAT -         FETCHCursor_avgscore intoVal; +         IFVal!=-1  Then A             SETTempres=CONCAT (Val,', '); at             SETRes=CONCAT (res,tempres); -         END IF; -UNTIL Val=-1 ENDREPEAT;--actually MySQL with val=-1 to end the loop, originally thought should and Java, c similar, with Val==-1 to end.  -     --Close Cursor -     CLOSECursor_avgscore; -     --Show Results in     SELECTRes; -     END$$ to  +DELIMITER;

Transferred from: http://blog.csdn.net/u010412719/article/details/51125496

Use of MySQL Cursors