SQL Constructs in Routines

SQL Constructs in Routines

The following SQL constructs are specifically provided for use in the body of a routine.

Assignment Using SET

The SET statement is used to assign a value to a variable declared in a routine or an output parameter of a procedure (i.e. a parameter with mode OUT or INOUT).

Examples:
 SET a = 5;
 SET x = NULL;
 SET y = 11 + a;
 SET d = CURRENT_DATE;
 SET z = NEXT_VALUE OF Z_SEQUENCE;
 SET x = CASE y WHEN 1 THEN y ELSE 0 END;
Conditional Execution Using IF

The IF statement provides a mechanism for conditional execution of SQL statements based on the truth value of a conditional expression.

Note: If the conditional expression includes (or equals) NULL, the conditional expression evaluates to false. Testing for the NULL value must be done by using IS NULL, see the Mimer SQL Reference Manual, The NULL Predicate.

A basic IF statement consists of a conditional expression followed by a list of one or more SQL statements in a THEN clause, which are executed if the conditional expression evaluates to true and, optionally, a list of one or more SQL statements in an ELSE clause which are executed if the conditional expression evaluates to false.

All of the predicates supported by Mimer SQL are allowed in the conditional expression of an IF statement - see the Mimer SQL Reference Manual, Predicates.

One or more IF statements can be nested, one within the other, by using an ELSEIF clause in place of the ELSE clause in the IF statement containing another.

The IF statement does not in any sense define a local scope, it is simply a mechanism for conditionally executing a sequence of SQL statements.

Once the SQL statements to be executed have been selected, they execute in the same way as any ordinary sequence of SQL statements. This point is particularly important when considering exception condition handling behavior, see Managing Exception Conditions.

Examples:
 IF x > 50 THEN
    SET x = 50;
    SET y = 1;
 ELSE
    SET y = 0;
 END IF;
 
 IF y IN (2,3,4) THEN
    ...
 ELSE
    ...
 END IF;
 
 IF x > 50 THEN
    SET x = 50;
    SET y = 2;
 ELSEIF x > 25 THEN
    SET y = 1;
 ELSE
    SET y = 0;
 END IF;
 
 IF NOT EXISTS (SELECT *
                   FROM table_1) THEN
    ...
 ELSE
    ...
 END IF;
 
 IF X > (SELECT c1
            FROM t1
            WHERE ... ) THEN
    ...
 ELSE
    ...
 END IF;
Conditional Execution - the CASE Statement

The CASE statement provides another mechanism for conditional execution of SQL statements. The CASE statement comes in two forms, a simple case and a searched case.

Simple Case

A simple case works by evaluating equality between one value expression and one or more alternatives of a second value expression. For example:
 DECLARE y INTEGER;
 
 CASE y
    WHEN 1 THEN ...
    WHEN 2 THEN ...
    WHEN 3 THEN ...
    ELSE ...
 END CASE;
Searched Case

A searched case works by evaluating, for truth, a number of alternative search conditions. For example:
 CASE
    WHEN EXISTS (SELECT *
                    FROM BILL) THEN ...
    WHEN x > 0 OR y = 1 THEN ...
    ELSE ...
 END CASE;
About Case Statements

For both forms of the CASE statement the following is true:

A sequence of one or more SQL statements can follow the THEN clause for each of the conditional alternatives, in the same way as for an IF statement, even though only a single implied SQL statement is shown in the examples above.
Each alternative sequence of SQL statements in a CASE statement is treated in the same way, with respect to the behavior of exception handlers etc., as has already been described for sequences of SQL statements in an IF statement, see Conditional Execution Using IF.
Like the IF statement, the CASE statement simply provides a mechanism for selecting a sequence of SQL statements to execute. The CASE statement as a whole is not considered, in any sense, to be a single statement.
The conditional part of each WHEN clause is evaluated, working from the top of the CASE statement down. The SQL statements that are actually executed are those following the THEN clause of the first WHEN condition to evaluate to true. If none of the WHEN conditions evaluate to true, the SQL statements following the CASE statement ELSE clause are executed.

The presence of an ELSE clause in the CASE statement is optional and if it is not present (and none of the WHEN conditions evaluate to true) an exception condition is raised to indicate that a case was not found for the CASE statement.

Note: If it is desired that there be no operation performed and no exception condition raised if none of the WHEN conditions evaluate to true, then an ELSE clause should be specified as an empty compound SQL statement.

Only the single selected sequence of SQL statements that follow a THEN or the ELSE is executed before the CASE statement terminates. There is no potential fall-through to subsequent THEN sequences as is found in case statements in some other programming environments.

Note: The CASE statement is distinct from the CASE expression - see the Mimer SQL Reference Manual, CASE and Mimer SQL Reference Manual, CASE Expression.

Iteration

The following sections describe how you can use iteration.

Iterating through a result set - FOR loop

A for loop can be used to iterate through all records in a result set and perform some operations for each record. This is a vast simplification compared to using a cursor.

A simple example of a for loop is
 FOR SELECT surname, forname FROM customers
     WHERE customer_id IN
     (SELECT customer_id FROM orders 
      WHERE datetime BETWEEN DATE '2006-01-01' AND DATE '2006-06-31') DO
      CALL orderStat(surname,forname);
 END FOR
 
I.e. call the orderStat routine for each record in the customers table that fulfil the where criteria. Within the body of the for statement it is possible to reference the columns values as ordinary variables. This also means that each item in the select list must have a name and that name must be unique within the select list.

The body of the for statement is an atomic statement, which means that it cannot contain statements such as start, commit and rollback.

It is possible to use a result procedure in a for loop
 FOR CALL coming_soon('Blues') DO
     IF producer IN ('Bill Vernon','Bill Ham') THEN
        INSERT INTO stats(format,release_date,...)
            VALUES (format,release_date,...);
     END IF;
 END FOR
 
In this case the correlation names in the as clause of the result procedure definition can be used as variable names in the body of the for loop.

The select or call statement in the for loop can be labelled and this label can be used to qualify variable references.
 l1: BEGIN
     DECLARE forname CHAR(12);
     ...
     FOR l2 AS SELECT forname FROM customers DO
         IF l1.forname <> l2.forname THEN
             ...
         END if;
     END FOR;
 END
 
The label used cannot be the same as any label of a compound statement enclosing the for loop.

Iteration Using LOOP

The LOOP statement may be preceded by a label that can be used as an argument to LEAVE in order to terminate the loop. The LOOP statement can contain a sequence of one or more SQL statements that are executed, in order, repeatedly.

The iteration is terminated by executing the LEAVE statement, or if an exception condition is raised.

Example:
 s1:
 LOOP
    IF ... LEAVE s1;
    ...
 END LOOP s1;
Iteration Using WHILE

The WHILE statement may be preceded by a label that can be used as an argument to LEAVE in order to terminate the while loop. The WHILE statement can contain a sequence of one or more SQL statements that are executed, in order, repeatedly.

The WHILE statement includes a conditional expression and iteration continues as long as this expression evaluates to true. Iteration may also be terminated by executing the LEAVE statement, or if an exception condition is raised.

Example:
 SET i = 0;
 s1:
 WHILE i <= 10 DO
    ...
    SET i = i + 1;
 END WHILE s1;
Iteration Using REPEAT

The REPEAT statement may be preceded by a label that can be used as an argument to LEAVE in order to terminate the repeat loop. The REPEAT statement can contain a sequence of one or more SQL statements which are executed, in order, repeatedly.

The REPEAT statement includes an UNTIL clause, which specifies a conditional expression, and iteration continues until this expression evaluates to true. Iteration may also be terminated by executing the LEAVE statement, or if an exception condition is raised.

Example:
 SET i = 0;
 s1:
 REPEAT
    ...
    SET i = i + 1;
 UNTIL i > 10
 END REPEAT s1;
Using ITERATE to Skip Statements

You can use an ITERATE statement to skip the remaining statements in an iteration as shown in the following examples:
 SET x = 0:
 s1:
 REPEAT
    SET x = x + 1;
    ...
    IF x < 10 THEN
       ITERATE s1; -- execution continues at the beginning
                   -- of the repeat statement
    END IF;
    ...
 UNTIL x = 20 END REPEAT s1;
Using ITERATE in all Iteration Statements

You can use ITERATE in all iteration statements in stored procedures. ITERATE is not restricted to the innermost statement. For example:
    SET x = 0;
 s1:
    REPEAT
       SET x = x + 1;
 s2:
       BEGIN
 s3:
          LOOP
             ...
             IF x < 10 THEN
                ITERATE s1;
             ELSEIF x < 20 THEN
                ITERATE s3;
             END IF;
             ...
          END LOOP s3;
       END s2;
    UNTIL x = 20
    END REPEAT s1;
 
Note: The statement ITERATE S1 will cause an implicit leave of the compound statement labeled S2.

Invoking Procedures and Functions

The following sections discuss invoking procedures and functions.

Invoking Procedures - CALL

The CALL statement is used to invoke a procedure. The name of the procedure may be qualified with the name of the schema to which it belongs. A value expression or target variable must be specified for each of the procedure's parameters, see the Mimer SQL Reference Manual, Target Variables, for the definition.

If the procedure parameter has mode OUT or INOUT, a target variable must be specified. For procedure parameters with mode IN, a value expression may be specified.

SQL/PSM is not strongly typed, so the expression specified for each procedure parameter need not have exactly the same data type as the parameter, however the expression must be assignment-compatible with the procedure parameter for which it is supplied, see the Mimer SQL Reference Manual, Assignments, for a discussion of assignment and implicit data type conversions.

Examples:
 CALL PROC1( );
 
 CALL PROC2(x, y);
 
 CALL IDENT1.PROC7(CURRENT_DATE, x+3, z);
Invoking Functions

Functions are not invoked by calling them explicitly. A function is invoked, and it returns its value, when it is used in a procedure-control-statement or in an assignment where a value-expression would normally be used.

The name of the function may be qualified with the name of the schema to which it belongs.

The following restrictions apply:

If the function is defined as NOT DETERMINISTIC, it must not be used in the expression that follows the CASE keyword or the WHEN keyword in the simple case form of a CASE statement, see Conditional Execution - the CASE Statement.
If MODIFIES SQL DATA has been specified for the access-clause of the function, it must not be used in the expression following the DEFAULT keyword in a DECLARE VARIABLE statement.

Examples:
 IF fn(x) > 70 THEN
    ...
 ELSE
    ...
 END IF;
 
 SET v_Artist = Mimer_Store_Music.ArtistName(p_RecordedBy) || '%';
 
 IF Mimer_Store.Index_Text(Data.Title) LIKE v_Title THEN
    ...
 END IF;
Comments in Routines

Any text that occurs after -- and before end-of-line in a routine is taken to be a comment.

Example:
 CREATE PROCEDURE tstproc(y INTEGER)
 -- This is a comment: Note that Y has mode IN (default)
 READS SQL DATA
 BEGIN
    DECLARE b INTEGER;
    -- Here is another comment
    SET b = y + 22;  -- Y is input to the procedure
    ...
 END
Restrictions

The following groups of SQL statements may not be used in a routine:

Access Control statements
Data Definition statements
Connection statements
ESQL Control statements
Security Control statements
Dynamic SQL statements
System Administration statements.

Refer to the Mimer SQL Reference Manual, SQL Statement Descriptions for a definition of the statement groups mentioned above.

Note: Any SQL statements used in a routine must be executable, so the usual restriction on the use of SELECT versus SELECT INTO applies (only the latter being considered executable - the former may, however, be used in a conditional expression, e.g. in an IF statement or a cursor declaration).

The following restrictions apply to result set procedures:

A COMMIT or ROLLBACK statement must not be executed in a result set procedure because it will interfere with the open cursor that will exist in the context from where the result set procedure is called.
A function or procedure that executes a COMMIT or ROLLBACK statement must not be invoked from within a result set procedure.
A function or procedure that has MODIFIES SQL DATA specified for its access clause must not be invoked from within a result set procedure.

Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se