Looping over a cursor in a stored procedure in MySQL

Looping over a cursor in a stored procedure in MySQL

On this Wiki page we discuss how to loop over a cursor in a MySQL stored procedure.

You can find many examples and variations on the internet and in the book MySQL Stored Procedure Programming about this subject. This page gives you the must robust solution for creating a loop over a cursor.

Stickily speaking this wiki page is not about PhpStratum, but we think that this page is useful for many developers using PhpStratum.

Background

The syntax of MySQL stored routines isn't as sophisticated as the syntax of stored procedures of some other RDBMS. In some other RDBMS you can declare a cursor and just loop over this cursor as in many other high level languages. Inside this loop you can access all selected columns as fields in a record.

for r_table in c_tables loop
  -- Do something useful with r_table.table_schema and r_table.table_name.
end loop

When the cursor is exhausted the loop terminates and the next statement after the loop will be executed.

In MySQL we have to do with a less powerful syntax. We have to create an infinite loop, open the cursor, for each row in the cursor fetch the selected columns into local variables, terminated the loop by some exception, and close the cursor. nevertheless, we can make powerful stored procedures just as with other RDBMS.

An example store procedure with a loop over a cursor.

In section we give discuss an example stored procedure with a loop over a cursor and discuss each line of this stored procedure in detail.

Below the source of the example store procedure.

/**
 * Sample store routine with a cursor.
 *
 * @param p_schema_name The (like) pattern for matching schemata.
 * @param p_table_name  The (like) pattern for matching tables.
 */
create procedure set_loop_example(in p_schema_name @information_schema.tables.table_schema%type@,
                                  in p_table_name  @information_schema.tables.table_name%type@)
modifies sql data
-- type: log
begin
  declare l_done         boolean;
  declare l_table_schema @information_schema.tables.table_schema%type@;
  declare l_table_name   @information_schema.tables.table_name%type@;

  declare c_tables cursor
  for
  select TABLE_SCHEMA
  ,      TABLE_NAME
  from information_schema.TABLES
  where TABLE_SCHEMA like p_schema_name
  and   TABLE_NAME   like p_table_name
  order by TABLE_SCHEMA
  ,        TABLE_NAME
  ;

  declare continue handler for sqlstate '02000' set l_done = true;

  open c_tables;
  loop1: loop
    set l_done = false;
    fetch c_tables
    into  l_table_schema
    ,     l_table_name
    ;
    if (l_done) then
      close c_tables;
      leave loop1;
    end if;

    -- Do something useful with l_table_schema and l_table_name;
  end loop;
end

Lets break up this stored procedure line by line and discuss all each line.

The following lines are the PHPDoc like documentation of the example stored procedure.

/**
 * Sample store routine with a cursor.
 *
 * @param p_schema_name The (like) pattern for matching schemata.
 * @param p_table_name  The (like) pattern for matching tables.
 */

The following lines instruct MySQL to create a stored procedure named set_loop_example.

create procedure set_loop_example(in p_schema_name @information_schema.tables.table_schema%type@,
                                  in p_table_name  @information_schema.tables.table_name%type@)
modifies sql data

We assume that the payload if this stored procedure modifies some data. (Currently the statement modifies sql data has no effect in MySQL.)

The following line is the designation type of the stored procedure for PhpStratum.

-- type: log

The following line is the declaration of a local variable which indicates that all the rows in the cursor have been fetched.

declare l_done         boolean;

The following lines are the declarations of local variables for storing the selected columns in the cursor.

  declare l_table_schema @information_schema.tables.table_schema%type@;
  declare l_table_name   @information_schema.tables.table_name%type@;

The following lines are the declaration of the cursor over which we want to loop.

  declare c_tables cursor
  for
  select TABLE_SCHEMA
  ,      TABLE_NAME
  from information_schema.TABLES
  where TABLE_SCHEMA like p_schema_name
  and   TABLE_NAME   like p_table_name
  order by TABLE_SCHEMA
  ,        TABLE_NAME
  ;

The following line is the declaration of the exception handler for status '02000' (No data - zero rows fetched, selected, or processed).

declare continue handler for sqlstate '02000' set l_done = true;

This exception handler will be invoked when we try to fetch the values of the cursor after we have read all rows in the cursor. Unfortunately, this exception handler will be called for all cases with status '02000'. Such as select statements for which no rows match or other exhausted cursors in the stored procedure either directly in the stored procedure body or via nested stored procedure calls.

The following line opens the cursor.

  open c_tables;

The following line is the start of an infinite loop named loop1.

  loop1: loop

The following line resets your flag which indicates we have exhausted the cursor.

  set l_done = false;

It is important to reset the flag for each iteration and just before the fetch statement. Remember, the exception handler will be called whenever a state '02000' occurs even deeper in the loop in some nested stored procedure call. We advice you to do use this statement as a habit even in trivial loops (without other SQL statement and stored routines class).

The following lines fetches the next row from the cursor into local variables.

    fetch c_tables
    into  l_table_schema
    ,     l_table_name
    ;

When the last row has been fetched in the previous iteration MySQL will throw an exception with state '02000' which will caught by our exception handler which will set l_done to true.

The following lines closes the cursor and escapes from the infinite loop one iteration after the last row from the cursor has been fetched.

    if (l_done) then
      close c_tables;
      leave loop1;
    end if;

The following line is the actual payload of the loop and must be replaced with some useful code.

    -- Do something useful with l_table_schema and l_table_name;