Knowledge Walls
John Peter
Pune, Maharashtra, India
Nested Cursor in Mysql of Code Template
7629 Views
Nested cursor template
DECLARE c_rno INT;
DECLARE c_name VARCHAR(100);
DECLARE cursor_var CURSOR FOR SELECT rno,name FROM students;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cursor_var;

cursor_var_loop: LOOP
     FETCH cursor_var INTO c_rno,c_name;


     -- cursor loop statements
     INNER_BLOCK: BEGIN
        DECLARE inner_cursor_var CURSOR FOR SELECT id,name FROM teachers;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET inner_done = TRUE;

        OPEN inner_cursor_var;
        
        inner_cursor_var_loop: LOOP
            FETCH inner_ cursor_var INTO c_inner_id,c_inner_name;

            -- inner cursor statements

            IF inner_done THEN
                LEAVE inner_cursor_var_loop;
            END IF;
        END LOOP inner_cursor_var_loop;
        
        CLOSE inner_ cursor_var;
     END INNER_BLOCK;   
    

     IF done THEN
            LEAVE cursor_var_loop;
     END IF;
END LOOP;

CLOSE cursor_var;
Hints 
For nested cursor have to include more block of statement in stored procedures.

In mysql stored procedures blocks using BEGIN and END like below.
BEGIN
    DECLARATION

    STATEMENTS
END

IN nested CURSOR statements
BEGIN
    CURSOR 1 DECLARATION

    CURSOR 1 STATEMENTS
    BEGIN
       CURSOR 2 DECLARATION

       CURSOR 2 STATEMENTS
    END
END
Sub Lessons
  Copyright © 2014 Knowledge walls, All rights reserved
KnowledgeWalls
keep your tutorials and learnings with KnowledgeWalls. Don't lose your learnings hereafter. Save and revise it whenever required.
Click here for more details