In MySQL, inner cursor ( which means Cursor inside another Cursor) does not work properly for stored procedures. So you can use “BLOCK” in such situations. |
For example, if you want to create a stored procedure to iterate through all the records of a Master table and update related records in the Child table, the following code snippet can help you do so |
Declare the Master Cursor here...... ................................................. ................................................. OPEN MasterCursor; REPEAT FETCH .......... BLOCK2: BEGIN Declare the Child Cursor here...... ................................................. ................................................. OPEN ChildCursor; REPEAT FETCH .................................... ............................................... UNTIL .... END REPEAT; CLOSE ChildCursor; END BLOCK2; UNTIL ... END REPEAT; CLOSE MasterCursor; END BLOCK1;