Syntactic Components of a Routine Definition

Syntactic Components of a Routine Definition

The following sections discuss parameters, language indicators, clauses, scope, variables and data types when working with routines.

Routine Parameters

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 (IN, OUT or INOUT - see CREATE PROCEDURE in the Mimer SQL Reference Manual for details). When the mode is not explicitly specified, IN is assumed by default.

It is not possible to specify the mode for the parameters of a function (they always have the default mode, IN).

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
 
Parameter Overloading

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 like
 SQL>create function f(c1 char)returns int return 1;
 SQL>create function f(c1 integer)returns int return 2;
 
and these can be used as
 SQL>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 functions
 SQL>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 statement
 SQL>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 CHAR the type precedence list is CHAR, CHARACTER VARYING, NCHAR and NCHAR VARYING which 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 as
 SQL>GRANT EXECUTE ON PROCEDURE p(INT,CHAR)
 SQL&TO public WITH GRANT OPTION;
 
To specify a routine without parameters, the syntax is
 SQL>GRANT EXECUTE ON PROCEDURE p()
 SQL&TO public WITH GRANT OPTION;
Routine Parameters and Null Values

All parameters accept null values. Use CAST to 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 SQL is assumed by default.

Routine Deterministic Clause

The deterministic clause for a routine can specify NOT DETERMINISTIC or DETERMINISTIC. If a deterministic clause is not specified, NOT DETERMINISTIC is assumed by default.

A DETERMINISTIC routine is one that is guaranteed to produce the same result every time it is invoked with the same set of input values.

Therefore, a DETERMINISTIC routine may not contain a reference to: CURRENT_DATE, LOCALTIME or LOCALTIMESTAMP.

Specifying a routine to be DETERMINISTIC allows 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 (CONTAINS SQL, READS SQL DATA and MODIFIES SQL DATA) are described under CREATE PROCEDURE in the Mimer SQL Reference Manual. If no routine access clause is specified, then CONTAINS SQL is assumed.

If the routine contains a SELECT statement, READS SQL DATA is required. (Or if a READS SQL DATA routine is called.)

If the routine contains a DELETE or an UPDATE statement, MODIFIES SQL DATA is required. (Or if a MODIFIES SQL DATA routine 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 STATEMENT in 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 LEAVE is 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 LEAVE statement is to pass flow of control to the statement END S0, i.e. flow of control exits from the scope labeled S0.

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 procedure-control-statement.

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 UNDO handler 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 ATOMIC next to the BEGIN delimiter. The COMMIT and ROLLBACK statements cannot be used within an atomic compound SQL statement.

A compound SQL statement is explicitly defined as not being atomic by specifying NOT ATOMIC next to the BEGIN delimiter. If nothing is specified next to the BEGIN delimiter, NOT ATOMIC is assumed by default.

If the compound SQL statement contains a declaration for an UNDO exception handler, see Declaring Exception Handlers, the compound SQL statement must be ATOMIC.

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
Declaring Variables

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 NULL is assumed.

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:

S0.A, S0.B, S1.B, S1.C, and S2.Y.

The ROW Data Type

Mimer SQL supports a data type called the ROW data 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 ROW data 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.

A ROW data 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 ROW data type is:

The following points apply to the specification of a ROW data type:

A domain cannot be specified for data-type.
The value specified for data-type can be a ROW data type specification.
Two fields in the same ROW data type specification must not have the same name (this restriction applies equally to fields named by specifying a field-name value and those named by inheriting the unqualified name of a table column).
If table-name is specified without a list of column names, all the columns in the table are used to define fields in the ROW data type.

Using the ROW Data Type

A ROW variable field is referenced like this: variable-name.field-name.

A value may be assigned to one of the fields in a ROW variable 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 ROW variable does not include a DEFAULT clause, each field in the ROW variable is set to NULL initially.

The value of a field in a ROW variable may be used in the same way as any value of that type.

When a ROW data 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 ROW data type.

A row value, which may be the value of a ROW variable, may be assigned to a ROW variable. The row value and the ROW variable 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 ROW variable.

Two row values, one or both of which may be the value of a ROW variable, 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.

A ROW variable may be used within a compound SQL statement in the following contexts:

As the only expression specified in a RETURN statement used in a result set procedure. The ROW variable must be assignment-compatible with the row value defined by the procedure VALUES clause.
As the only target variable specified in the INTO clause of a SELECT INTO statement. The row value selected must be assignment-compatible with the ROW variable and will be assigned to it.
As the only target variable specified in the INTO clause of a FETCH statement. The row value fetched must be assignment-compatible with the ROW variable and will be assigned to it.
As the procedure-variable or expression in a SET assignment statement (see the description above of assignment-compatibility involving ROW variables).
As an argument in a comparison (see the description above of comparison-compatibility involving ROW variables).

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 RETURN statement used in a result set procedure.
As the expression following the DEFAULT keyword in a DECLARE VARIABLE statement for a variable declared to have the ROW data type.
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
info@mimer.se