Syntactic Components of a Routine Definition
The following sections discuss parameters, language indicators, clauses, scope, variables and data types when working with routines.
A routine may have zero or more parameters and each parameter must have a name and a data type specified.
Each parameter of a procedure can have an optional mode specification (
CREATE PROCEDUREin the Mimer SQL Reference Manual for details). When the mode is not explicitly specified,
INis assumed by default.
It is not possible to specify the mode for the parameters of a function (they always have the default mode,
A parameter name must be unique within the routine. A parameter can have the same name as a routine name, however this is not generally recommended. The parameter name can be up to 128 characters in length, see the Mimer SQL Reference Manual, Naming Objects, for further details about naming SQL objects.
The parameter data type can be any data type supported by Mimer SQL, except any of the large object types, see the Mimer SQL Reference Manual, Data Types in SQL Statements.
Note: A domain name cannot be specified for the data type of a routine parameter.
A parameter name may be referenced in an unqualified manner throughout a routine, at all scope levels - see Scope in Routines - the Compound SQL Statement for a discussion of scope in routines.
Examples:CREATE FUNCTION onefunction(a INTEGER, b DECIMAL(5,2)) RETURNS DECIMAL(5,2) BEGIN ... END CREATE PROCEDURE lookup(IN i INTEGER, OUT retval VARCHAR(20)) BEGIN ... END
Mimer SQL supports the possibility to define multiple functions or procedures with the same name as long as they differ with regard to the number of parameters or the data type of the parameter. It is not possible to have multiple functions that only differ with regard to the return data type.
As an example, it is possible to create two functions likeSQL>create function f(c1 char)returns int return 1; SQL>create function f(c1 integer)returns int return 2;
and these can be used asSQL>set ? = f('1'); ? =========== 1 SQL>set ? = f(1); ? =========== 2
In this case there is no problem deciding which routine that should be invoked in the two cases since there can be no implicit conversion from a character type to integer or vice versa. Some interesting cases arise when there are parameter overloading and where there are implicit conversions between the parameter types. For instance, if we have these functionsSQL>create function f(p char varying(2)) SQL&returns int return 1; SQL>create function f(p nchar varying(2)) SQL&returns int return 2;
Given the statementSQL>set ? = f('a');
which routine should be invoked? The data type of the actual argument is char and there is no routine with a parameter list that matches the invocation exactly. In this case a type precedence list is used to determine the proper subject routine. For
CHARthe type precedence list is
NCHAR VARYINGwhich means that the function returning 1 will be chosen in this case. If there are multiple parameters, the subject routine is determined by evaluating the type precedence list for each parameter, going from left to right.
The type precedence lists are found in Mimer SQL Reference Manual, Appendix H, Type Precedence Lists.
Specific Names and Parameter List
When specifying a routine in a drop, grant or revoke statement the routine must be uniquely identifiable. This is no problem as long as parameter overloading is not used. If there are multiple functions or procedures with the same name there are two ways of specifying a unique routine. The first is by using the specific name for the routine. A specific name can be defined when the routine is created. If no specific name is given, a unique name is generated automatically. This name can be seen in the view
INFORMATION_SCHEMA.ROUTINES. As an example:SQL>CREATE PROCEDURE p(p1 INT, p2 CHAR(20)) SQL&SPECIFIC p_int_char SQL&... SQL>GRANT EXECUTE ON SPECIFIC PROCEDURE p_int_char SQL&TO public WITH GRANT OPTION;
The other way to distinguish between overloaded routines in DDL statements is to use a data type list. Given the above procedure definition, the grant statement can be written asSQL>GRANT EXECUTE ON PROCEDURE p(INT,CHAR) SQL&TO public WITH GRANT OPTION;
To specify a routine without parameters, the syntax isSQL>GRANT EXECUTE ON PROCEDURE p() SQL&TO public WITH GRANT OPTION;
Routine Parameters and Null Values
All parameters accept null values. Use
CASTto invoke a routine with a null value as a parameter, as follows:CALL mimer_store_music.AddTrack(718751799622, CAST(NULL AS int), 'Null Set', '3:53')
Routine Language Indicator
The language indicator specifies the language of the routine. Currently, the only language name supported is SQL.
If no language indicator is specified,
LANGUAGE SQLis assumed by default.
Routine Deterministic Clause
The deterministic clause for a routine can specify
DETERMINISTIC. If a deterministic clause is not specified,
NOT DETERMINISTICis assumed by default.
DETERMINISTICroutine is one that is guaranteed to produce the same result every time it is invoked with the same set of input values.
DETERMINISTICroutine may not contain a reference to:
Specifying a routine to be
DETERMINISTICallows repeated invocations of it to be optimized.
Routine Access Clause
The access clause for a routine specifies which SQL statements are permitted within the routine.
The three different options for the routine access clause (
READS SQL DATAand
MODIFIES SQL DATA) are described under
CREATE PROCEDUREin the Mimer SQL Reference Manual. If no routine access clause is specified, then
CONTAINS SQLis assumed.
If the routine contains a
READS SQL DATAis required. (Or if a
READS SQL DATAroutine is called.)
If the routine contains a
MODIFIES SQL DATAis required. (Or if a
MODIFIES SQL DATAroutine is called.)
Scope in Routines - the Compound SQL Statement
A compound SQL statement allows a sequence of procedural SQL statements to be considered as a single SQL statement, see
COMPOUND STATEMENTin the Mimer SQL Reference Manual for a description of the syntax.
A routine body may contain only one executable SQL statement and the compound SQL statement allows a routine to be defined that can actually contain any number of SQL statements.
A compound SQL statement also defines a local scope in which variables, exception handlers, and cursors can be declared. Compound SQL statements may be nested, one within the other, and thus local scopes may be nested.
A compound SQL statement may be labeled, which effectively names the local scope defined by it. The label name can be used whenever the scope environment needs to be referred to explicitly, e.g. when qualifying the names of objects which have been declared in the compound SQL statement. The label name must not be the same as a routine name.
It is important to understand the effect of scoping on declared items, particularly with respect to: out-of-scope references to variables, see Declaring Variables, the scope within which an exception handler remains in effect and the flow of control effects following the use of different types of exception handler, see Declaring Exception Handlers.
The SQL statement
LEAVEis specifically provided to give the programmer the ability to force the flow of control to exit from a labeled scope.
Example:CREATE PROCEDURE some_procedure(INOUT y INTEGER) CONTAINS SQL s0: BEGIN ... s1: BEGIN IF y < 0 THEN SET y = 0; LEAVE s0; END IF; ... END s1; ... END s0
In the example above, the effect of the
LEAVEstatement is to pass flow of control to the statement
END S0, i.e. flow of control exits from the scope labeled
All open cursors declared in a compound SQL statement are closed whenever flow of control leaves the compound SQL statement for any reason.
Note: A compound SQL statement may be preceded by a label which names the scope delimited by the BEGIN and END (this is called the beginning label). Specifying the label next to the END is optional. However, if a label is specified next to the END, the beginning label must be specified.
The ATOMIC Compound SQL Statement
The execution of any SQL statement, other than a
procedure-control-statement, is atomic. See the Mimer SQL Reference Manual, Procedural SQL Statements, for a definition of a
The execution of a compound SQL statement defined as ATOMIC is also atomic.
When the execution of an SQL statement is atomic, an atomic execution context becomes active while the statement, or any contained sub-query, is executing. While an atomic execution context is active, it is possible for another atomic execution context to become active within it.
While an atomic execution context is active the following is true:
- It is not possible to explicitly terminate a transaction, thus all changes made within the atomic execution context occur within the same transaction.
- If an SQL statement within the atomic execution context fails and no handler has been declared within it to handle the error, all the changes made within the atomic execution context will be cancelled and the associated exception will be propagated to the calling environment.
- If an SQL statement within the atomic execution context fails and an
UNDOhandler has been declared within it to handle the error, all the changes made within the atomic execution context will be cancelled and the handler will be executed.
Note: If the atomic statement contains operations on tables located in a databank defined with work option, these operations will not be part of the atomic statement but will be executed immediately. If the atomic statement is terminated by an sql exception, such operation will not be undone.
An atomic compound SQL statement is defined by specifying the keyword
ATOMICnext to the
ROLLBACKstatements cannot be used within an atomic compound SQL statement.
A compound SQL statement is explicitly defined as not being atomic by specifying
NOT ATOMICnext to the
BEGINdelimiter. If nothing is specified next to the
NOT ATOMICis assumed by default.
If the compound SQL statement contains a declaration for an
UNDOexception handler, see Declaring Exception Handlers, the compound SQL statement must be
Examples:CREATE FUNCTION an_atomic_function(i INTEGER) RETURNS INTEGER BEGIN ATOMIC ... -- All statements executed between this BEGIN -- and END execute within the same active atomic -- execution context. -- UNDO exception handlers are permitted. -- No COMMIT or ROLLBACK allowed! ... END CREATE PROCEDURE a_non_atomic_procedure(i INTEGER) BEGIN NOT ATOMIC ... -- This compound SQL statement is not atomic. -- COMMIT and ROLLBACK statements are permitted. -- No UNDO exception handlers allowed! ... END CREATE FUNCTION a_default_function(i INTEGER) RETURNS INTEGER BEGIN ... -- This compound SQL statement is not atomic, by default. -- COMMIT and ROLLBACK statements are permitted. -- No UNDO exception handlers allowed! ... END
It is possible to declare variables, cursors, condition names and exception handlers at the beginning of a compound SQL statement. These items can, therefore, be declared in a routine when a compound SQL statement is used for the routine body.
This section discusses the declaration of variables. Discussions about declaring the other items mentioned above can be found in the following sections:
- cursors, see Using Cursors
- condition names, see Declaring Condition Names
- exception handlers, see Declaring Exception Handlers.
Variables of any data type supported by Mimer SQL may be declared. The name of a variable must be unique within the scope of its declaration and must not conflict with the name of any of the routine parameters.
Variable names can be a maximum of 128 characters in length and are case insensitive. See the Mimer SQL Reference Manual, Naming Objects, for further details.
Note: The data type for a variable must be specified explicitly, it is not possible to specify a domain.
More than one variable of the same type can be declared in a single variable declaration, see the examples below.
It is possible to specify an optional expression, which may be
NULL, that defines the default expression for a variable declaration. The variable(s) created by the variable declaration are given the initial value derived from the default expression. If a default expression is not specified, the value
Examples:DECLARE z INTEGER; DECLARE x, y INTEGER DEFAULT 9; DECLARE abx VARCHAR(50); DECLARE a INTEGER DEFAULT NULL;
Note: It is possible to declare a variable that has the same name as a column name in a table. All ambiguous references will be interpreted as a reference to a column name rather than a variable. It is therefore recommended that a suitable naming convention be adhered to that clearly distinguishes between the names of table columns and variables.
The name of a variable may be qualified in the normal way with the beginning label of the scope in which it has been declared.
Example:CREATE PROCEDURE some_procedure(IN x INTEGER) s0: BEGIN DECLARE a, b INTEGER; s1: BEGIN DECLARE b, c INTEGER; ... END s1; s2: BEGIN DECLARE y INTEGER; ... END s2; END s0
The qualified names for the variables in the preceding example are as follows:
The ROW Data Type
Mimer SQL supports a data type called the
ROWdata type. It can be used in a compound SQL statement in place of the data type specified when a variable is declared.
A variable that is declared as having the
ROWdata type implicitly defines a row value, which is a single construct that has a value that effectively represents a table row.
A row value is composed of a number of named values, each of which has its own data type and represents a column value in the overall row value.
ROWdata type can be defined either by explicitly specifying a number of field-name/data-type pairs or by specifying a number of table columns from which the unqualified names and data types are inherited.
ROW Data Type Syntax
The syntax for defining a
ROWdata type is:
The following points apply to the specification of a
- A domain cannot be specified for
- The value specified for
data-typecan be a
ROWdata type specification.
- Two fields in the same
ROWdata type specification must not have the same name (this restriction applies equally to fields named by specifying a
field-namevalue and those named by inheriting the unqualified name of a table column).
table-nameis specified without a list of column names, all the columns in the table are used to define fields in the
Using the ROW Data Type
ROWvariable field is referenced like this:
A value may be assigned to one of the fields in a
ROWvariable in the same way as a value would be assigned to a variable declared with the same data type as the field. The data type of the field must be assignment compatible with the value being assigned to it.
If the declaration of a
ROWvariable does not include a
DEFAULTclause, each field in the
ROWvariable is set to
The value of a field in a
ROWvariable may be used in the same way as any value of that type.
ROWdata type is defined by specifying table columns, the names and data types of its fields are inherited from the columns in the table(s). Subsequently assigning values to the ROW variable will not affect the table(s) used to define the
A row value, which may be the value of a
ROWvariable, may be assigned to a
ROWvariable. The row value and the
ROWvariable are assignment-compatible if, and only if, both contain the same number of values and each value in the row value is assignment-compatible with the corresponding field in the
Two row values, one or both of which may be the value of a
ROWvariable, may be compared. The row values are comparison-compatible if, and only if, both contain the same number of values and each value in one is comparison-compatible with the corresponding value in the other.
ROWvariable may be used within a compound SQL statement in the following contexts:
- As the only expression specified in a
RETURNstatement used in a result set procedure. The
ROWvariable must be assignment-compatible with the row value defined by the procedure
- As the only target variable specified in the
INTOclause of a
SELECT INTOstatement. The row value selected must be assignment-compatible with the
ROWvariable and will be assigned to it.
- As the only target variable specified in the
INTOclause of a
FETCHstatement. The row value fetched must be assignment-compatible with the
ROWvariable and will be assigned to it.
- As the procedure-variable or expression in a
SETassignment statement (see the description above of assignment-compatibility involving
- As an argument in a comparison (see the description above of comparison-compatibility involving
Row Value Expression
A row value expression is an expression that specifies a row value. The values that represent the column values of the row value expression are specified as value expressions in a comma-separated list that is delimited by parentheses.
A row value expression can be used in the following contexts:
- As the only expression in a
RETURNstatement used in a result set procedure.
- As the expression following the
DEFAULTkeyword in a
DECLARE VARIABLEstatement for a variable declared to have the
- As a row value in a comparison or assignment operation.
Examples:RETURN (24, 16, 'xyz', 11.3, x+4/9); DECLARE rc ROW (a INTEGER, b INTEGER, s VARCHAR(10)) DEFAULT (14, 27, 'hello'); IF rc = (14, 27, 'hello') THEN SET rc.s = 'bye'; END IF; SET rc = (99, 105, 'new value');
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40