Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


CREATE PROCEDURE


Creates a new stored procedure.

where procedure-definition is:

and parameter-definition is:

and values-clause is:

Usage

Embedded/Interactive/ODBC/JDBC.

Description

The procedure-name should follow the normal rules for naming database objects, see Naming Objects.

If procedure-name is specified in its unqualified form, the procedure will be created in the schema which has the same name as the current ident.

If procedure-name is specified in its fully qualified form (i.e. schema-name.procedure-name) the procedure will be created in the named schema (in this case, the current ident must be the creator of the specified schema).

The fully qualified procedure name must be used by all idents except the ident that has the same name as the schema to which the procedure belongs.

The parameter-name in the parameter-definition should follow the normal rules for naming SQL identifiers, see Naming Objects.

Parameter Definitions

The following mode values may be specified in a parameter-definition:

Data Type

The permitted values for data-type, specified in parameter-definition, are described in Data Types in SQL Statements.

Values Clause

If a values-clause is specified, the procedure is created as a result set procedure. A result set procedure is a special type of procedure which returns a result-set and is called by being specified in a cursor declaration, see DECLARE CURSOR, rather than by using the CALL statement.

If neither DETERMINISTIC nor NOT DETERMINISTIC is specified, then NOT DETERMINISTIC is implicit.

If DETERMINISTIC is specified, then the procedure is guaranteed to produce the same result every time it is invoked with the same set of input values and repeated invocations of it can, therefore, be optimized.

Access Options

The following access options may be specified:

If neither CONTAINS SQL, READS SQL DATA nor MODIFIES SQL DATA is specified, then CONTAINS SQL is implicit.

Restrictions

A procedure created this way cannot be added to a module.

Two procedures with the same name cannot belong to the same schema.

It is not possible to create a synonym for a procedure name.

A parameter name must be unique within the procedure.

The ROW data type cannot be specified in parameter-definition or in a values-clause.

If the procedure contains a COMMIT or ROLLBACK statement, it must not be invoked from within a result set procedure.

A result set procedure may only have parameters with mode IN.

A result set procedure must not execute a COMMIT or ROLLBACK statement because this will interfere with the cursor used when the result set procedure is called.

A result set procedure must not invoke a function that executes a COMMIT or ROLLBACK statement.

If DETERMINISTIC is specified, the procedural-sql-statement of the procedure may not contain, or be, a reference to: SESSION_USER, CURRENT_DATE, CURRENT_PROGRAM, LOCALTIME or LOCALTIMESTAMP.

The option MODIFIES SQL DATA cannot be used for a result set procedure.

An ident must have EXECUTE privilege on the procedure in order to invoke it.

Notes

If neither IN, OUT nor INOUT is specified in a parameter-definition then IN is implicit.

Refer to the Mimer SQL User's Manual, chapter 9, Creating Modules, Functions, Procedures and Triggers, for details on using the CREATE PROCEDURE statement in Mimer BSQL where the @ delimiter is required.

Example

 CREATE PROCEDURE MY_RESULT_PROC(IN A INTEGER, IN B INTEGER)
                  VALUES ( VARCHAR(32), INTEGER(10) )
                  AS     ( CLIENT_NAME, CLIENT_ID )
 READS SQL DATA
 BEGIN
 ...
 END;
 

For more information, see the Mimer SQL User's Manual, chapter 9, Creating Modules, Functions, Procedures and Triggers.

Standard Compliance

Standard
Compliance
Comments

SQL/PSM

EXTENDED

Support for the VALUES clause, i.e. the concept of a procedure returning a result-set, is a Mimer SQL extension.



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