Using Inner Cursor in MySQL

Using Inner Cursor in MySQL

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;