The Pyrrho Database Management System

Malcolm Crowe, University of the West of Scotland

 

Stored Procedures and Functions

Pyrrho supports stored procedures and functions following the SQL2003 syntax (volumes 2 and 4). The programming model offered in this way is computationally complete, so the use of external code written in other programming languages is not supported.

In SQL2003 the syntax :v is not supported for variable references, and instead variables are identified by qualified identifier chains of form a.b.v . The syntax ? for parameters is not supported either.

In SQL2003-2-11.50 we see that procedures never have a returns clause (functions should be used if a value is to be returned), and procedure parameters can be declared IN, OUT or INOUT and can be RESULT parameters. Variables can be ROW types and collection types. For functions, TABLE is a valid RETURNS type (it is strictly speaking a “multiset” in SQL2003 terminology). From SQL2003-2-6.39 we see that RETURN TABLE ( queryexpression ) is a valid return statement.

Here are some outlines of procedure statements specified in SQL2003-4 and supported in Pyrrho. Complete syntax summaries for Pyrrho are given in chapter 7.

Table-valued functions

create table author(id int primary key, aname char)

create table book(id int primary key, authid int, title char)

...

[ create function booksby(auth char) returns table(title char)

  return table(select title from author a inner join book b on a.id = b.authid where aname = booksby.auth ) ]

This example also shows that a routine body is a single procedure statement (possibly a compound BEGIN..END statement). Square brackets are used like this in the Pyrrho client utilities to enclose multi-line SQL statements.

The above function can be referenced by statements such as

select * from table(booksby('Charles Dickens'))

The keyword table in this example is required by SQL2003-2(7.6).

Examples

The following functions perform the same task. The first uses a handler, while the second uses a for statement.

create function gather1() returns char

begin

  declare c cursor for select a2 from a;

  declare done boolean default false;

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

  declare a char default '';

  declare p char;

  open c;

  repeat

    fetch c into p;

    if not done then

      if a = '' then

        set a = p

      else

        set a = a || ', ' || p

      end if

    end if

  until done end repeat;

  close c;

  return a

end

 

create function gather2() returns char

begin

  declare b char default '';

  for select a2 from a do

      if b='' then

            set b = a2

      else

            set b = b || ', ' || a2

      end if

  end for;

  return b

end