|
|
|
|
|
|
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:
- Thin clients are achieved as a result of moving program logic from the applications to the database server.
- Business rules can be stored in one place and do not have to be duplicated in all the applications.
- Performance is improved, since less communication between a client (e.g. an application program) and the server is needed to perform the operations in the stored routine. This is particularly important in client/server environments, including the Internet and the World Wide Web.
- Giving the users execution rights to a set of stored routines instead of giving them access rights to the database objects directly eliminates the risk of accidental damage to data through interactive tools.
- Creating a set of stored procedures by which all database access is performed can standardize DBMS access.
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:
- Stored routines written entirely in SQL.
- Data manipulation statements.
- Procedural programming statements - RETURN, CASE, IF/THEN/ELSE, LOOP, LEAVE, WHILE, REPEAT, and compound statements.
- SQL variables and assignment statements for assigning values to SQL variables and to parameters of stored procedures.
- EXECUTE privilege determines which users can invoke given routines.
- CALL statement for invoking stored procedures.
- Functions may be invoked from within scalar expressions e.g. in SELECT and WHERE clauses in queries.
- Explicit parameter modes (IN, OUT, or IN OUT) for stored procedure parameters. An SQL function can only have input parameters but does return a value.
- Advanced exception-handling facilities.
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 |
|
|
|
|
|
|