helpinghand
search
needassistance
 
Features
Stored Procedures in Mimer SQL
Category: SQL
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

In combination with Mimer PSM, the Mimer SQL Triggers and Functions should be studied.
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:1996(E), December 15, 1996).
Benefits

When using stored procedures, very good solutions can be implemented in many major areas, such as database consistency, performance and security.
Links

Mimer SQL Documentation Set, PDF-format. (See the Reference Manual.)

Last updated: 2002-08-19

 

Powered by Mimer SQL

Powered by Mimer SQL