FOR Loop Examples

The following example shows a procedure called bio_match which returns the first row from the author table which has an author row whose short_bio contains the bio_string argument value.

create procedure bio_match(in bio_string char)
 begin
     declare author_name char(30) default null;
     declare author_bio char(216) default null;
     fetch_author: for author_row as authors cursor for
         select * from author
     do
         if locate(bio_string, author_row.short_bio, 1) != 0 then
             set author_name = author_row.full_name;
             set author_bio = author_row.short_bio;
             leave fetch_author;
         end if;
     end for;
     if author_name is null then
         select "No author found with short_bio containing:", bio_string;
     else
         select author_name, author_bio;
     end if;
 end;

Note that the FOR statement specifies author_row which automatically generates a begin statement labeled "author_row" so that the variables declared within that block that will contain the result column values from the generated FETCH for the cursor named authors and which is only visible within the DO … END FOR block can be referenced by qualifying the variable name with author_row.

The above procedure returns just the first matching row. Procedure bio_match_all below returns all of the matching rows.

create procedure bio_match_all(in bio_string char)
begin
    declare none_found boolean default true;
    for select * from author
    do
        if locate(bio_string, short_bio, 1) != 0 then
            select full_name, short_bio;
            set none_found = false;
        end if;
    end for;
    if none_found = true then
        select "No author found with short_bio containing:", bio_string;
    end if;
end;

This version does not specify the author_row for loop variable which is not really needed as the system implicitly assigns variables with the same name as the SELECT statement result column names to contain each fetched row’s column result values. An rdm-sql script which compiles and executes the bio_match_all procedure is shown below.

rdm-sql: use bookshop;
rdm-sql: .r bio_match_all.sql
rdm-sql: call bio_match_all("science");
full_name                        short_bio
Verne, Jules                     French author who helped pioneer the science-fiction genre...
 
full_name                        short_bio
Wells, H. G. (Herbert George)    English author, now best known for his work in the science fiction...