MySQL cursor usage

SQL statements that can be used in Stored Procedures mainly include the following types:

1. No return result statements, such as insert, update, drop, and delete.
2. The SELECT statement returns a single-row variable and can be passed to the local variable (select ..)
3. Select statements that return multi-row result sets and can be processed cyclically using cursors.

Note: The multi-row result set returned by a stored procedure can be received by a client program (such as PHP), but it is impossible to receive the result set of another stored procedure in one stored procedure, the general solution is to store a temporary table for sharing in other processes.

4. Prepare statement

The following describes the cursor and prepare

Cursor

Definition

Declare cursor_name cursor for select_statement;

Cursor operation
OpenOpen cursor

Open cursor_name;

FetchObtain the record of the current pointer of the cursor and pass it to the specified Variable list. Note that the number of variables must be consistent with the number of fields returned by the cursor. To obtain multiple rows of data, run the fetch statement in a loop.

Fetch cursor_name into Variable list;

CloseClose cursor

Close cursor_name;

Note: The MySQL cursor is read-only, that is, you can only read the result set sequentially from the beginning to the end, not from the back to the front, or directly jump to the middle of the record.

A complete example:
-- Define local variables
Declare o varchar (128 );

-- Define a cursor
Declare ordernumbers cursor
For
Select callee_name from account_tbl where acct_timedurl = 10800;
Declare continue handler for not found set no_more_attributes = 1;
Set no_more_departments = 0;

-- Open the cursor
Open ordernumbers;

-- Loop all rows
Repeat
-- Get Order Number
Fetch ordernumbers into O;
Update account set allmoney = allmoney + 72, lastmonthconsume = lastMonthConsume-72 where numtg = @ O;


-- Loop ends
Until no_more_administrative ments
End repeat;
-- Close the cursor
Close ordernumbers;

Example:
Use MySQL 5.1 or above;
Test Table level;
Create Table Test. Level (name varchar (20 ));
Insert some data;

/* Initialize */
Drop
Procedure
If
Exists usecursor //
/* Create a stored procedure */
Create
Procedure usecursor ()
Begin
/* Declare */
Declare tmpnameVarchar(20) Default
'';
Declare allnameVarchar(255) Default
'';

Declare cur1 cursor
For
Select name from test.Level;

/* MySQL does not know why to add an exception to the judgment?
* For more information, see the official documentation.
2.11. cursor
* The cursor is caught after an exception.
* Set the loop to jump out of the loop with the variable tmpname being null.
*/
Declare
Continue handler for sqlstate '20140901'
Set tmpname =
NULL;


/* Open cursor */
Open cur1;
/* Move the cursor down */
Fetch cur1 into tmpname;

/* Loop body, which obviously adds the names queried by the cursor and separates them with; signs */
While (tmpname is
Not
Null) Do
Set tmpname = Concat (tmpname ,";");
Set allname = Concat (allname, tmpname );
/* Move the cursor down */
Fetch cur1 into tmpname;
End
While;

Close cur1;

Select allname;
End ;//
Call usecursor ()//

Running result:
Mysql> call usecursor ()//
+ -------------------------------------- +
| Allname |
+ -------------------------------------- +
| F1; C3; C6; C5; C2; C4; C1; F1; F3; F4; F2; F5; |
+ -------------------------------------- +
1Row in
Set (0.00Sec)