CREATE PROCEDURE

Creates a new stored procedure.

 

where procedure-definition is:

 

and parameter-definition is:

 

and result-set-clause is:

 

Usage

Embedded, Interactive, Module, 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.

It is possible to create multiple procedures with the same name if they differ with regard to either the number of parameters, or the data type for the parameters. See Mimer SQL Programmer's Manual, Parameter Overloading for more information. Type precedence lists are found in Type Precedence Lists.

Each routine can be given a specific name, which must be unique within a schema. If no specific name is given, the system will generate a unique name. The specific name for a procedure can be retrieved by using the INFORMATION_SCHEMA views.

A specific name can be used in DROP, GRANT and REVOKE statements. It is particularly useful when dealing with procedures with parameter overloading. Instead of having to specify a list of data types, in order to distinguish the procedure, the specific name can be used.

Parameter Definitions

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

•IN

The parameter is effectively read-only, i.e. it cannot be used as the target in an assignment, fetch or select into statement in the procedure

•OUT

The parameter is effectively write-only, i.e. it can only be used as the target for an assignment and cannot be used in a value expression in the procedure. This type of parameter must be a variable in the procedure CALL statement

•INOUT

The parameter can be used both as an IN and OUT parameter, this type of parameter must be a variable in the procedure CALL statement.

If neither IN, OUT nor INOUT is specified, then IN is implicit.

The permitted data types, specified in parameter-definition, are pre-defined data types (described in Data Types in SQL Statements).

Result-Set-Clause

If a result-set-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:

•CONTAINS SQL

The procedure may not contain any data-manipulation-statements. All other procedural-sql-statements are permitted. The procedure may only invoke CONTAINS SQL functions and procedures.

This option effectively prevents a routine from performing read or write operations on data in the database.

•READS SQL DATA

All procedural-sql-statements are permitted except those performing updates (i.e. DELETE, INSERT and UPDATE). The procedure may only invoke CONTAINS SQL or READ SQL DATA functions and procedures.

This option effectively prevents a routine from performing write operations on data in the database.

•MODIFIES SQL DATA

All procedural-sql-statements are permitted and any function or procedure may be invoked from this type of procedure.

This option allows a routine to perform read and write operations on data in the database.

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.

It is possible to create multiple procedures with the same name in a schema if the procedures have a different number of parameters or parameters with different data types.

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 result-set-clause.

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

A result set procedure or a routine invoked from within a result set procedure, must not execute a COMMIT or ROLLBACK statement because this would interfere with the cursor used when the result set procedure is called.

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, LOCALTIMESTAMP or BUILTIN.UTC_TIMESTAMP.

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

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

If an in parameter is defined as using a domain, any input value for this parameter will be verified to ensue that any check constraint is not violated. If an out parameter is defined as using a domain, the parameter will be initialized with the default value for the domain.

Example

CREATE PROCEDURE res_proc (IN A INTEGER, IN B INTEGER)

                RETURNS TABLE (CLIENT_NAME VARCHAR(32), CLIENT_ID INTEGER)

READS SQL DATA

BEGIN

...

END;

 

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

Standard Compliance