In Mimer SQL you can define functions, procedures and modules, collectively known as stored procedures.
Mimer SQL stored procedures enable you to define and use powerful functionality through the creation and execution of routines. By using stored procedures, you can move application logic from the client to the server, thereby reducing network traffic.
Stored procedures are stored in the data dictionary and you can invoke them when needed.
For a complete and detailed discussion of stored procedures, see Functions and the Mimer SQL Programmer's Manual, chapter 12, Mimer SQL Stored Procedures.
Mimer SQL stored procedures are based on the ISO standard for Persistent Stored Modules (PSM),
Routines - Functions and Procedures
The term routine is a collective term for functions and procedures. Functions are distinguished from procedures in that they return a single value and the parameters of a function are used for input only. A function is invoked by using it where a value expression would normally be used.
Mimer SQL supports standard procedures and also result set procedures, which are procedures capable of returning the row value(s) of a result-set.
Standard procedures are invoked directly by using the CALL statement and can pass values back to the calling environment through the procedure parameters.
In embedded SQL, result set procedures are invoked by declaring a cursor which includes the procedure call specification and by then using the FETCH statement to execute the procedure and return the row(s) of the result-set.
In interactive SQL, a result set procedure is invoked by using the CALL statement directly and the result-set values are presented in the same way as for a select returning more than one row.
The ident invoking a routine must have EXECUTE rights on it.
The creator of a routine must hold the appropriate access rights on any database objects referenced from within the routine. These access rights must be held for the life of the routine.
Routine names, like those of other private objects in the database, are qualified with the name of the schema to which they belong.
A module is simply a collection of routines. All the routines in a module are created when the module is created and belong to the same schema.
EXECUTE rights on the routines contained in a module are held on a per-routine basis, not on the module.
If a module is dropped, all the routines contained in the module are dropped.
Under certain circumstances a routine may be dropped because of the cascade effect of dropping some other database object. If such a routine is contained in a module, it is implicitly removed from the module and dropped. The other routines contained in the module remain unaffected.
In general, care should be taken when using DROP or REVOKE in connection with routines, modules or objects referenced from within routines because the cascade effects can often affect many other objects.
For more information, see Mimer SQL User's Manual, chapter 9, Dropping Objects from the Database and the Mimer SQL User's Manual, chapter 10, Revoking Privileges.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40