Creates a new stored procedure.
procedure-nameshould follow the normal rules for naming database objects, see Naming Objects.
procedure-nameis specified in its unqualified form, the procedure will be created in the schema which has the same name as the current ident.
procedure-nameis 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.
parameter-definitionshould 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
A specific name can be used in
REVOKEstatements. 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.
The following mode values may be specified in a
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
INOUTis specified, then
The permitted data types, specified in
parameter-definition, are pre-defined data types (described in Data Types in SQL Statements), and user-defined types (see CREATE TYPE).
result-set-clauseis 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
NOT DETERMINISTICis specified, then
NOT DETERMINISTICis implicit.
DETERMINISTICis 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.
The following access options may be specified:
READS SQL DATA
procedural-sql-statementsare permitted except those performing updates (i.e.
UPDATE). The procedure may only invoke
READ SQL DATAfunctions, methods and procedures.
MODIFIES SQL DATA
READS SQL DATAnor
MODIFIES SQL DATAis specified, then
CONTAINS SQLis implicit.
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.
ROWdata type cannot be specified in
parameter-definitionor in a
A domain data type cannot be specified in
parameter-definitionor in a
A result set procedure may only have parameters with mode
A result set procedure or a routine invoked from within a result procedure, must not execute a
ROLLBACKstatement because this would interfere with the cursor used when the result set procedure is called.
DETERMINISTICis specified, the
procedural-sql-statementof the procedure may not contain, or be, a reference to:
MODIFIES SQL DATAcannot be used for a result set procedure.
An ident must have
EXECUTEprivilege on the procedure in order to invoke it.
Refer to the Mimer SQL User's Manual, Creating Functions, Procedures, Triggers and Modules, for details on using the
CREATE PROCEDUREstatement in Mimer BSQL where the
@delimiter is required.
ExampleCREATE 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.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40