Go Use of MySQL Cursors

Transferred from: http://www.cnblogs.com/sk-net/archive/2011/09/07/2170224.html

The following article mainly introduces the use of MySQL cursor notes, which can be used in the stored procedure SQL statement, its main types are mainly the following, the following is a detailed description of it, I believe that if you master the technology, will be in the future study or work to bring great help.

1, no return result statements, such as: Insert,update,drop, delete, etc.

2. The SELECT statement returns a single-line variable and can be passed to the local variable (select: Into

3. Returns the SELECT statement for the multiline result set, and can be used to cycle through the MySQL cursor

Note that the multi-row result set returned by the stored procedure can be received by the client program (such as PHP), but it is not possible to receive a result set of another stored procedure in one stored procedure, and the general workaround is to deposit the temporary table for other processes to share

4. Prepare statement

The following mainly describes cursors and prepare parts

Cursor

Defined

1. DECLARE cursor_name cursor for select_statement;

Cursor operations

Open cursor

1. OPEN cursor_name;

Fetch gets a record of the cursor's current pointer and passes it to the specified variable list, noting that the number of variables must match the number of fields returned by the MySQL cursor, to obtain multiple rows of data, to use a loop statement to execute FETCH

1. FETCH cursor_name into variable list;

Close cursor

1. CLOSE cursor_name;

Note: The MySQL cursor is read forward only, that is, you can only sequentially read the result set from the beginning, cannot move forward, or jump directly to the middle record.

A complete example:

Defining local Variables

1. DECLARE o varchar (128);

Defining cursors

1. DECLARE ordernumbers CURSOR
2. For
3. SELECT callee_name from Account_tbl where acct_timeduration=10800;
4. DECLARE CONTINUE HANDLER for not FOUND SET no_more_departments=1;
5. SET no_more_departments=0;

Open cursor

1. OPEN ordernumbers;

Loop all the rows

1. REPEAT
2. --Get Order number
3. FETCH ordernumbers into O;
4. Update account set Allmoneyallmoney=allmoney+72,lastmonthconsumelastmonthconsume=  lastMonthConsume-72 where [email protected];

End of cycle

1. UNTIL no_more_departments
2. END REPEAT;

Close Cursors

1. CLOSE ordernumbers;

DELIMITER $$

Use ' Kubauser ' $$

DROP PROCEDURE IF EXISTS ' cursortest ' $$

CREATE definer= ' coo8new ' @ '% ' PROCEDURE ' cursortest ' (out a varchar (+), out B varchar (50))
BEGIN
DECLARE _outuserid VARCHAR (50);
DECLARE _kubauserid VARCHAR (50);
DECLARE flag INT;
DECLARE update_cursor Cursor
For
SELECT Outuserid,kubauserid from Ecuser_cooperationuser;
DECLARE CONTINUE HANDLER for not FOUND SET flag=1;
SET flag=0;
OPEN Update_cursor;
REPEAT/* Loop */
FETCH update_cursor into _outuserid,_kubauserid;
SET A=_outuserid;
SET B=_kubauserid;
/*update Set where*/
UNTIL Flag
END REPEAT;
CLOSE Update_cursor;

end$$

DELIMITER;

Go Use of MySQL Cursors