Introduction
The concept of stored procedures is very useful in a Relational Database Management System (RDBMS). SQL routines are stored in the database just like other schema objects such as tables, domains, etc. Stored routines allow application logic to be moved from the applications to the RDBMS. Routines are cached on the server and the overheads of transmitting and preparing frequently used SQL code is avoided.
Function
An overview of the features supported include:
- Stored routines written entirely in SQL
- 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
- Exception-handling facilities
- Possibility to return result sets
When evaluating Mimer PSM, the Mimer SQL Triggers and Functions should be studied as well. For example, see the article about INSTEAD OF Triggers.
Techniques
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).
Benefits
When using stored procedures, very good and uncomplicated solutions can be implemented in many major areas. Key factors, such as database consistency, performance and security, are all benefiting from a solution using PSM.
