Nesting MySQL Cursor Loops

Handling cursor loops in MySQL keeps puzzling people.

Single Cursor Loops

The common case is to have a simple cursor loop. Each record is fetched from the cursor until there are now more records, and for each record some work is performed.
Let's take a look at this snippet:

01    begin
02        declare v_col1 int;                     -- define the data to grab from the cursor
03        declare no_more_rows boolean := FALSE;  -- define the loop control variable
04        declare cursor1 cursor for              -- define the set to iterate through
05            select col1
06            from   MyTable;
07        declare continue handler for not found  -- Capture cursor exhaustion event
08            set no_more_rows := TRUE;           -- modify the loop control variable, then return
09
10        open cursor1;                           -- execute query, and save the result so we can iterate it
11        LOOP1: loop                             -- start repeatable region of code
12            fetch cursor1                       -- grab data from current cursor record
13            into  v_col1                        -- this will raise NOT FOUND if the cursor is exhausted
14            ;
15            if no_more_rows then                -- check the loop control variable, the handler might've modified it
16                close cursor1;                  -- free resources
17                leave LOOP1;                    -- stop iterations 
18            end if;
19            -- 
20            -- Process data here
21            -- 
22        end loop LOOP1;
23    end;

This is a fragment of a MySPL Procedure.
To summarize, the CURSOR is processed by iteration through an ordinary LOOP that happens to FETCH rows from the cursor. When the cursor's exhausted, the NOT FOUND condition is raised, transferring control to a HANDLER that sets a flag. This flag is checked inside the loop, right after the fetch, and when it is set, iteration is terminated.

Handling cursor exhaustion

An important point to make right away is that, unlike the OPEN (line 10), FETCH (line 12) and CLOSE (line 16) statements, there is no formal relationship between the HANDLER declaration (lines 07..08) and the CURSOR declaration (lines 04..06). For instance, the HANDLER declaration never refers to cursor1, the name of the cursor. It just 'happens' to be the case that FETCH-ing eventually leads to a situation in wich the cursor will have reached it's last record. Such an exhausted cursor will raise the NOT FOUND condition on attempt to FETCH from it again. This condition can then be captured by the, scopewise, nearest appropriate HANDLER.

Nested Cursor Loops

Sometimes you need to nest two cursors. I don't want to discuss what problems are appropriately solved with this device right now. Let me just say that most of the time someone is using this device, they are doing that because they don't know how to write a join properly.
The problem with nesting curors is this: because we can't associate a HANDLER with a particular CURSOR we have to think of a way to know wich one of the cursors is exhausted once the NOT FOUND condition is raised. We really need to know that, because our loop controls depend on it. We definitely want to terminate the corrsponding loop when one of the cursors will be exhausted, don't we?

Using a separate block

Perhaps one of cleanest ways to do this is to rely on the scopewise propagation of the condition. By putting the inner LOOP into it's own BEGIN..END block, we have created the possibility to give that block it's own NOT FOUND handler. A NOT FOUND condition raised by a FETCH in the inner LOOP will thus be captured by the appropriate HANDLER declared in the inner BEGIN..END block. That's because that inner block's handler is nearer than the handler declared in the outer block. From there, the inner loop can easily be terminated:

01    BLOCK1: begin
02        declare v_col1 int;                     
03        declare no_more_rows boolean1 := FALSE;  
04        declare cursor1 cursor for              
05            select col1
06            from   MyTable;
07        declare continue handler for not found  
08            set no_more_rows1 := TRUE;           
09        open cursor1;
10        LOOP1: loop
11            fetch cursor1
12            into  v_col1;
13            if no_more_rows1 then
14                close cursor1;
15                leave LOOP1;
16            end if;
17            BLOCK2: begin
18                declare v_col2 int;
19                declare no_more_rows2 boolean := FALSE;
20                declare cursor2 cursor for
21                    select col2
22                    from   MyOtherTable
23                    where  ref_id = v_col1;
24                declare continue handler for not found
25                    set no_more_rows2 := TRUE;
26                open cursor2;
27                LOOP2: loop
28                    fetch cursor2
29                    into  v_col2;
30                    if no_more_rows then
31                        close cursor2;
32                        leave LOOP2;
33                    end if;
34                end loop LOOP2;
35            end BLOCK2;
36        end loop LOOP1;
37    end BLOCK1;

Note that the inner BEGIN..END block's handler can never capture the NOT FOUND condition raised by the FETCH in the outer block, because conditions are propagated from the inside to the outside.
Another advantage of this approach is that the inner loop is almost entirely self-contained. The only thing that binds the inner BEGIN..END block to the outer one is a reference to the variable v_col1 in the decalaration of cursor2. Everything that has to do with the inner loop is defined in just one place, BLOCK2.

Resetting the loop control variable

The disadvantage of the previous method is of course the duplication of code. Apart from the cursor itself, we have to declare the loop control variable and the handler twice too. Sometimes, this is way too much trouble. Lucky for us, there's a way to evade all that, by resetting the loop control variable just before the inner loop terminates. Check it out:

00    begin
01        declare no_more_rows boolean default false;
02        declare v_col1       int;
03        declare v_col2       int;
04        declare cursor1 cursor for
05            select col1
06            from   MyTable
07            ;
08        declare cursor2 cursor for
09            select col2
10            from   MyOtherTable
11            where  ref_id = v_col1;
12        declare continue handler for not found 
13            set no_more_rows := true;
14    
15        open cursor1;
16        LOOP1: loop
17            fetch cursor1 into v_col1;
18            if no_more_rows then
19                close cursor1;
20                leave LOOP1;
21            end if;
22            open cursor2;
23            LOOP2: loop
24                fetch cursor2 into v_col2;
25                if no_more_rows then
26                    set no_more_rows := false;
27                    close cursor2;
28                    leave LOOP2;
29                end if;
30            end loop LOOP2;
31        end loop LOOP1;
32    end;

Observe te difference between the handling of the loop termination. For the outer loop, we see the usual sequence of statements to close the cursor and to leave the loop (lines 18..21). For the inner loop, we use an additional assignment to reset the loop control variable (line 26). This ensures the outer loop gets a chance to roll off untill it's exhausted too.

Packing cursor loops into stored procedures

Another approach would be to pack all the code for the dependant, inner cursor loop into a separate stored procedure and have the outer loop CALL that. This approach resembles the first one where we put the inner loop in it's own BEGIN..END block:

01    create procedure p_inner_loop(
02        IN p_col1 int
03    )
04    begin
05        declare v_col int;
06        declare no_more_rows boolean := FALSE;
07        declare cursor cursor1 for
08            select col2
09            from   MyOtherTable
10            where  ref_id = p_col1;
11        declare continue handler for not found
12            set no_more_rows := TRUE;
13        open cursor1;
14        LOOP1: loop
15            fetch cursor1
16            into  v_col;
17            if no_more_rows then
18                close cursor1;
19                leave LOOP1;
20            end if;
21        end loop LOOP1;
22    end;

And

01    BLOCK1: begin
02        declare v_col1 int;                     
03        declare no_more_rows boolean1 := FALSE;  
04        declare cursor1 cursor for              
05            select col1
06            from   MyTable;
07        declare continue handler for not found  
08            set no_more_rows1 := TRUE;           
09        open cursor1;
10        LOOP1: loop
11            fetch cursor1
12            into  v_col1;
13            if no_more_rows1 then
14                close cursor1;
15                leave LOOP1;
16            end if;
17            call p_inner_loop(v_col1);
18        end loop LOOP1;
19    end BLOCK1;

This approach is especially useful when you want to be able to use that inner loop from other contexts as well. When cursor processing is becoming complex, it's probably a good idea to use this technique as a "divide and conquer" strategy to keep development managable. For example, this technique allows you test the different cursor loops separate from each other, wich can be a great aid in debugging.