Using procedures to number rows

Introduction

A frequently asked feature is the ability to number rows within a result set. Database products solve this problem in different ways. Up until SQL-2003 there has not been such a feature defined in the standard, but in SQL-2003 it is part of the extension T611 “Elementary OLAP operations” — see the chart Optional Features in SQL-2003. (This optional feature is not yet available in Mimer SQL).

This article presents an example on how to solve this problem using a result set procedure. At the same time, the example below shows how to use the SQL ROW data type.

Description

The solution is to embed the query in a result set procedure, and simply use an integer to count returned rows. The below procedure produces a list of the collations installed into the database server. For simplicity, it returns only two columns, the row number and a combination of the collation schema and its name.

 create procedure NUMBER_ROWS_QUERY_1 ()
   values (integer,varchar(258)) as (NR,COLLATION)
   reads sql data
 begin
   declare CURS cursor for select * from INFORMATION_SCHEMA.COLLATIONS;
   declare ROWNUM integer;
   declare exit handler for not found close CURS;
   declare rc ROW as (INFORMATION_SCHEMA.COLLATIONS);

   set ROWNUM=0;

   open CURS;
   loop
     set ROWNUM=ROWNUM+1;
     fetch CURS into rc;
     return (ROWNUM,rc.COLLATION_SCHEMA||'.'||rc.COLLATION_NAME);
   end loop;
   close CURS;
 end

The above procedure returns a result set like the following. Only the first 14 rows are shown.

 SQL>call number_rows_query_1();
          NR COLLATION
 =========== =========================================================
           1 INFORMATION_SCHEMA.ISO8BIT
           2 INFORMATION_SCHEMA.SQL_TEXT
           3 INFORMATION_SCHEMA.UCS_BASIC
           4 INFORMATION_SCHEMA.EOR
           5 INFORMATION_SCHEMA.EOR_3
           6 INFORMATION_SCHEMA.EOR_1
           7 INFORMATION_SCHEMA.UNICODE_3
           8 INFORMATION_SCHEMA.UNICODE_1
           9 INFORMATION_SCHEMA.AFRIKAANS_3
          10 INFORMATION_SCHEMA.AFRIKAANS_1
          11 INFORMATION_SCHEMA.ALBANIAN_3
          12 INFORMATION_SCHEMA.ALBANIAN_1
          13 INFORMATION_SCHEMA.ARABIC_3
          14 INFORMATION_SCHEMA.ARABIC_1
 Continue ?

Links

Read more about the ROW data type and result set procedures in the Reference Manual of the Mimer SQL Documentation set.