Procedure n_oldest_authors

The first example shows a stored procedure that returns a list of the n oldest authors in the author table based on their age at death, where n is specified through an integer argument to the procedure. The procedure declaration is contained in a text file which can have any name but our convention is to give the file the same name as the procedure with a ".sql" extension. Hence, the contents of file "n_oldest_authors.sql" is shown below.

 1 create procedure n_oldest_authors(in nrows smallint)
 2 begin
 3    declare rowno smallint default 1;
 4    get_authors: for
 5         select full_name, if(yr_died = 0, year(current_date()), yr_died)-yr_born author_age
 6         from author order by 2 desc
 7    do
 8         select rowno, full_name, author_age;
 9         if rowno = nrows then
10             leave get_authors;
11         end if;
12         set rowno = rowno + 1;
13    end for;
14 end;
15 ;

In line 1 the procedure named n_oldest_authors is declared with one input (specified by the IN attribute) argument named nrows of type SMALLINT which is used to specify the number of result rows to be returned from the procedure. The body of the stored procedure is coded between the BEGIN and END statements.

A declaration of a SMALLINT variable named rowno is given on line 3. It has an DEFAULT initial value of 1.

The for statement on line 4 is used to specify a SELECT statement through which its results rows will be iterated and processed by the statements that follow the DO on line 7 through the END FOR on line 13. Note that the for statement's SELECT statement returns two columns: full_name and author_age. The latter is an alias for the specified expression and must be specified because the result column names are effectively used as variables to contain each row’s column values.

Line 8 has a SELECT statement without a FROM clause which always returns a single row. This one returns the rowno value and the values for the two columns of the current row from the FOR statement’s SELECT. A SELECT without a FROM clause functions much like a simple print statement.

Line 9 is an IF statement to test to see if the desired number of result rows have been returned. If so, the LEAVE statement will exit the block formed between the DO and the END FOR. Note that if nrows is larger than the number of rows in the author table, the FOR will terminate after the last row has been read.

Line 12 contains an assignment statement that increments rowno.

The final ";" in line 15 is used to inform the rdm-sql tool that all of the text from the beginning of the file (or the last double semicolon) should be submitted to RaimaDB SQL as a single statement.

The rdm-sql script below uses the .r command to read the file contents and send it to RaimaDB SQL to be compiled and then calls this procedure to return the 3 oldest authors with the results as shown.

rdm-sql: open database bookshop;
rdm-sql: .r n_oldest_authors.sql
rdm-sql: call n_oldest_authors(3);
 rowno full_name                            author_age 
     1 Hobbes, Thomas                               91 

 rowno full_name                            author_age 
     2 Sinclair, Upton                              90 

 rowno full_name                            author_age 
     3 Hardy, Thomas                                88 

What change(s) need to be made to change this procedure to return the n youngest authors at the time of their death?