Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


Stored Routines


The concept of stored procedures and stored functions (collectively referred to as stored routines) is very useful in an RDBMS. SQL routines are stored in the database just like other schema objects (tables, domains, etc.). Stored routines allow application logic to be moved from the applications to the RDBMS. Routines are cached on the server and avoid the overheads of transmitting and preparing frequently used SQL code. These features imply a number of important benefits when developing database applications:

Support for stored routines within Mimer SQL is based on the ISO standard for SQL's Persistent Stored Modules (SQL/PSM - ISO/IEC 9075-4:1996(E), December 15, 1996).

The SQL/PSM standard does not include any specification for how a result set should be returned from a stored procedure. But since such functionality is of great importance, a solution for handling of result sets has been included in Mimer SQL/PSM. A special type of procedure, so called result procedures are used to handle result sets. Result procedures make it possible to declare a cursor for a procedure. Such a cursor can be used just like a cursor for a SELECT statement, i.e. it is opened, rows are fetched, and finally the cursor is closed. The result procedure returns one row every time the FETCH statement invokes it, until there are no more rows to return. The rows can be returned from the result procedure either explicitly by a RETURN statement, or implicitly by using a direct SELECT statement.

An overview of the features supported include:

Mimer SQL supports a pseudo data type called ROW. The ROW data type can be used in a compound statement and is defined by explicitly specifying a number of field-name/data-type pairs or by specifying a number of table columns from which the unqualified names and data types are inherited.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL User's Manual TOC PREV NEXT INDEX