The Pyrrho Database Management System
Malcolm Crowe, University of the West of Scotland
Malcolm Crowe, University of the West of Scotland
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.
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).
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
