MySQL cursor usage notes

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. You can use cursors to process them cyclically. The multi-row result sets returned by stored procedures can be received by client programs (such as PHP, however, 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 among other processes. 4. The prepare statement mainly 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;