next up previous contents index
Next: 19. Triggers Up: Ovrimos version 3.0 On-line Previous: 17. Ovrimos SQL

Subsections

   
18. Persistent Stored Modules

18.1 Overview

Persistent Stored Module (SQL/PSM) is a feature which provides users the ability to add their own extensions to the standard SQL. The most important reasons for providing such a capability are:

Ovrimos support for this feature includes the following:

1.
User-defined functions which can be invoked from within scalar expressions.
2.
User-defined procedures which can be invoked by means of a new SQL statement (CALL).
3.
A new procedural language which adds the power of a traditional programming language to SQL through flow control statements and other programming constructs (e.g. variables). It should be pointed out that this language follows the standard SQL with its new control statements (this term refers to the procedural language introduced into SQL by PSM).

Flow Control Statements Programming Constructs
If-then-else Variable declarations
Looping statements Set statements for value assignment
Exception handling  
Compound statements (Begin-End blocks)  

18.2 Routines and Modules

In this section we review briefly the concept of routine and module, we provide some examples and finally we describe the syntax and semantics of their creation and destruction.

PSM uses the term routine to cover both functions and procedures. A function is a routine whose invocation returns a value while the invocation of a procedure doesn't return a value so the latter is invoked by means of an explicit CALL statement.

PSM modules (server modules) belong to schemas and are stored at the server site. From this point forward, we will refer to them by the term ``module''. Modules can contain functions as well as procedures.

18.2.1 Routine Definition and Examples

Ovrimos routines are written in SQL and are SQL-invoked. Not all routines belong to a module, though they are all stored at the server site. The body of such a routine (it is said to be an SQL routine) consists of either a single or compound SQL statement, where the name SQL refers to SQL/92 augmented by the facilities of PSM. Routine SQLRTABS in Example 1 is an SQL routine and SQL-invoked. Compound SQL statements are illustrated in Example 2.

Routine Definition 

 ::=   [CREATE | DECLARE] {PROCEDURE | FUNCTION}
         routine ([parameter-definition-commalist])
	   [RETURNS data-type]
	   [LANGUAGE language]
	   [SPECIFIC routine]
	     SQL-statement

parameter-definition
 ::=    [IN | OUT | INOUT] parameter data-type

Notes on the routine definition:

1.
The keyword CREATE is required if the routine definition is contained within a schema. The keyword DECLARE is permitted (though not required) if the routine definition is contained within a module definition.

2.
The syntax element routine appearing after the PROCEDURE or FUNCTION keyword is the name by which the routine in question will be invoked. In order to support what is commonly called ``overloading'', distinct routines are allowed to have the same invocation name. However, every routine is required to have a unique specific name, defined by means of the SPECIFIC clause, that is unique with respect to all such names within the schema that directly or indirectly contains the definition of the routine in question. If the SPECIFIC clause is omitted, a default name -derived from the invocation name and the data types of the corresponding parameters- is provided. More specifically, the specific name is used in operations that are intended to drop the routine or grant or revoke authorization on it.

3.
Assuming that a routine has invocation name I and that it belongs to schema S, then the name I can be qualified by the schema name S in the usual way. For example, if the routine happened to be a procedure, then CALL S.I would be a legal procedure invocation. If this procedure additionally belongs to a specific module, then the name I can instead be qualified by the keyword MODULE. Thus, CALL MODULE.I would be a legal invocation-though only from within the specific module.

4.
The data type specifications within parameter definitions and the RETURNS clause must be SQL data types (or domains), not host language data types.

5.
The LANGUAGE specification indicates the language in which the routine is written.

6.
The routine body consists of any single or compound SQL statement supported by Ovrimos (including the PSM extensions). You can refer to Section B.4 to read about the SQL statements supported by Ovrimos.

Example 1   The following PSM statement defines a function called SQRTABS, which calculates the square root of the absolute value of a number. 
CREATE FUNCTION 
    SQRTABS (:N DOUBLE)
	RETURNS DOUBLE
	RETURN
	  CASE
	   WHEN :N>0 THEN SQRT(N)
	    ELSE SQRT(-N)
	  END

The above example illustrates just those syntax elements that are minimally required to define a PSM routine:

Here is an example of an SQL expression that includes an invocation of SQRTABS: 

SELECT location, owner
FROM booths
WHERE SQRTABS (surface)>2.0

In connection with the above function invocation, it is worth pointing out:

Example 2   The following is an example of a procedure definition. The procedure name is UPDSTATUS. 
CREATE PROCEDURE
  UPDSTATUS (:STATUS INTEGER,
             OUT :NUM INTEGER)
             
BEGIN
  SET :NUM = 0;
   WHILE EXISTS (SELECT * FROM ORDERS WHERE ORDER_STATUS = :STATUS) DO
     UPDATE ORDERS 
       SET ORDER_STATUS = ORDER_STATUS + 1
       WHERE ORDER_ID = (SELECT MIN(ORDER_ID) FROM ORDERS 
            WHERE ORDER_STATUS = :STATUS);
	 SET :NUM = :NUM + 1;
   END WHILE;
END

Notes on the procedure definition:

Here is an example of an invocation of procedure UPDSTATUS. Even if the program containing this invocation is written in some host language (e.g. Pascal), the invoked procedure is still an SQL-invoked routine, because the CALL statement that performs the actual invocation is an SQL statement. 

BEGIN
DECLARE X INTEGER;
CALL UPDSTATUS ('1', X);
SELECT order_id, X FROM orders WHERE order_status = 2;
END

  
18.2.2 Module Definition and Examples

Every routine belongs either to a schema or to a module that in turn belongs to a schema. ** Modules (server modules) are created and destroyed by means of new CREATE MODULE and DROP MODULE statements and are stored at the server site.

Module Definition 

::= CREATE MODULE [module]
      [NAMES ARE character-set]
        LANGUAGE language
	  [SCHEMA schema]  [AUTHORIZATION user]
	  [PATH schema-commalist]
	  [temporary-table-definition-list]
	    module-element-list
	 END MODULE

Notes on the module definition:

1.
The CREATE and END MODULE keywords must be specified.
2.
The LANGUAGE specification indicates the language in which routines in this module are written (not the language from which they are invoked). This specification can be optionally defined at the individual routine level.
3.
PATH ****
4.
The legal module elements are routine definitions.

Example 3   The following example illustrates the packaging of routines into a module. 
CREATE MODULE STRING_FUNCS
	NAMES ARE IOS9221
	LANGUAGE SQL
	SCHEMA APPSCHEMA
	AUTHORIZATION ADMIN
	PATH LOCAL_SCHEMA, ADMIN
	
	DECLARE FUNCTION
		REVERSE (:X VARCHAR (1000) )
		RETURNS VARCHAR (1000)
		RETURN
			CASE CHAR_LENGTH( :X )
			WHEN 0 THEN :X
			WHEN 1 THEN :X
			ELSE SUBSTRING (:X FROM CHAR_LENGTH(:X) ) ||
				 REVERSE (SUBSTRING(:X FROM 1 FOR CHAR_LENGTH(:X) - 1))
			END;

	FUNCTION FIRST_WORD (:X CHAR (1000))
		RETURNS CHAR (40)
		RETURN TRIM (SUBSTRING( (:X || ' ') FROM 1 
						FOR POSITION (' ' IN (:X || ' '))));
	DECLARE PROCEDURE
		LAST_WORD ( :X VARCHAR (1000), OUT :LW VARCHAR(40) )
		BEGIN
			SET LW = REVERSE( FIRST_WORD( REVERSE( :X ) ) );
		END;

END MODULE

Notes:

18.2.3 Routine and Module Destruction

Routines and modules can be destroyed by means of a DROP statement: 
DROP MODULE module {RESTRICT | CASCADE}
DROP [SPECIFIC]  {ROUTINE | FUNCTION | PROCEDURE} 
      routine {RESTRICT | CASCADE}

Destroying a module, automatically destroys all routines belonging to that module. In case of ``DROP ... routine'', routine must be a specific name if SPECIFIC is specified, an invocation otherwise (further, that invocation name can be followed, if necessary, by a parenthesized commalist of parameter data type specifications --as shown in Example 4.

Example 4   The effect of the following statement is the destruction of the procedure UPDSTATUS (defined in Example 2).  
DROP routine UPDSTATUS (INTEGER, INTEGER)

If the dropped routine is also invoked by another routine, then in case RESTRICT is specified (it is also the default option), DROP will not succeed. On the contrary, if CASCADE has been specified, all routines (the one to be dropped and all the others that invoke it) will be dropped.

18.2.4 The EXECUTE Privilege

This privilege is required on a certain routine in order to be allowed to invoke it. More specifically, it only applies to routines either within schemas or within server modules. In the latter case, EXECUTE is granted or revoked at the level of the overall module, not that of the individual routine. 
GRANT EXECUTE ON object TO users [WITH GRANT OPTION]

REVOKE [GRANT OPTION FOR] EXECUTE ON object FROM users 
       {RESTRICT | CASCADE}
where ``object'' is either an expression of the form ``MODULE module'' or an expression of the following form. ``routine'' is possibly followed by a parenthesized commalist of parameter data type specifications, as for DROP: 
[SPECIFIC] {ROUTINE | FUNCTION | PROCEDURE} routine
Destroying a module or routine automatically causes all EXECUTE privileges on them to be revoked. Let us now examine an example of the EXECUTE privilege on a routine R which belongs to some schema. We also suppose that within this schema there are several routines which all have the same invocation name R but different specific names, e.g. R1, R2, R3, etc. As long as the user holds the EXECUTE privilege on at least one of R1, R2, R3, etc. an invocation of R by that user is permissible.

18.3 SQL Control Statements

18.3.1 Flow Control Statements

CALL
The specified routine must be a procedure, not a function. 
CALL routine-invocation
The effects of this statement are precisely the effects of the procedure invocation, e.g. updates to the database, assignments to SQL variables, etc. For more information on routine invocation names you can refer to Section C.2.1.

RETURN
It is permitted only within the body of a function. Its effect is to establish the result to be returned to the invoking routine and to pass control back to that routine. The keyword NULL can appear in place of the scalar expression. 

RETURN scalar-expression

IF 

IF conditional-expression
   THEN nested-SQL-statement-list
        [elseif-clause-list]
   [ELSE nested-SQL-statement-list]
END IF
It is the familiar IF-THEN-ELSE construct found in other programming languages, except that it can include a list of ``elseif clauses'' of the form: 
ELSEIF conditional-expression
    THEN nested-SQL-statement-list
Here ELSEIF is short for ELSE IF, with an implied END IF appearing immediately before the explicit one. For example, the following statements are equivalent: 
IF x1 THEN A
      ELSEIF x2 THEN B
	            ELSE C
END IF
is equivalent to this one: 
IF x1 THEN A
      ELSE IF x2 THEN B
	             ELSE C
           END IF
END IF

Example 5   The following is an example of the PSM IF statement: 
IF EXISTS(SELECT * FROM ORDER_CONS WHERE ORDER_ID >= 100) THEN
    SELECT * FROM ORDER_CONS WHERE ORDER_ID >= 100;
ELSEIF EXISTS(SELECT * FROM ORDER_CONS WHERE ORDER_ID >= 50) THEN
    SELECT * FROM ORDER_CONS WHERE ORDER_ID >= 50;
ELSEIF EXISTS(SELECT * FROM ORDER_CONS WHERE ORDER_ID >= 5) THEN
    SELECT * FROM ORDER_CONS WHERE ORDER_ID >= 5;
ELSE
	SELECT * FROM ORDER_CONS WHERE ORDER_ID >= 2;
END IF

LOOP 

[label:] LOOP
         nested-SQL-statement-list
END LOOP [label]
To stop the execution of the ``nested SQL statement list'', the LEAVE statement is provided which permits exit from the loop.

Example 6   The execution of loop LABEL1 will be stopped as soon as the parameter XX gets equal to 10. 
BEGIN
 DECLARE XX INTEGER;
 SET XX = 1;
 LABEL1: LOOP
	UPDATE ORDERS SET ORDER_STATUS = ORDER_STATUS + 1 WHERE ORDER_ID = XX;
	SET XX = XX+1;
	IF XX >= 10 THEN
			LEAVE LABEL1;
	END IF;
 END LOOP;
END

LEAVE 

LEAVE label
The specified label must be the beginning-label of some looping or compound SQL statement that contains the LEAVE statement. For example, if statement L1 contains statement L2 (at any level of nesting), statement L2 can contain the statement LEAVE L1.

WHILE 

[label] WHILE conditional-expression DO
             nested-SQL-statement-list
END WHILE [label]

Example 7   The following SQL statements will be executed as many times as the conditional-expression is true. 
WHILE EXISTS(SELECT * FROM ORDERS WHERE ORDER_STATUS = 1) DO
  UPDATE ORDERS SET ORDER_STATUS = ORDER_STATUS + 1 
   WHERE ORDER_ID = (SELECT MIN(ORDER_ID) FROM ORDERS WHERE ORDER_STATUS = 1);
END WHILE

REPEAT 

[label] REPEAT
         nested-SQL-statement-list
UNTIL conditional-expression
END REPEAT [label]

Example 8   The following SQL statements will be executed at least once and as soon as the conditional-expression is found false, the control is transferred out of the loop: 
LREP: REPEAT 
 UPDATE ORDERS SET ORDER_STATUS = ORDER_STATUS + 1 
 WHERE ORDER_ID = (SELECT MIN(ORDER_ID) FROM ORDERS 
  WHERE ORDER_STATUS = 4);
 UNTIL EXISTS(SELECT * FROM ORDERS WHERE ORDER_STATUS = 1)
END REPEAT LREP

18.3.2 Compound Statements

A sequence of one or more SQL statements can be given semicolon terminators and bracketed by BEGIN and END, so they can be treated as a unit. A compound statement can include definitions of SQL variables, conditions and handlers whose scope is the compound statement in question, overriding definitions of items that have the same name but are outside the specific scope. 
[label] BEGIN [[NOT] ATOMIC]
 [SQL-variable-definition-list]
 [condition-definition-list]
 [handler-definition-list]
 [nested-SQL-statement-list]
END [label]
The keyword ATOMIC specifies that the begin-end block is executed within an ``atomic execution context'' -- the default is NOT ATOMIC. Example 6 illustrates a compound statement.

18.3.2.1 Exception Handling

Compound statements within PSM routines can make their own arrangements for handling exceptions that might arise during execution of the compound statement in question.

Condition Definition The purpose of this statement is to introduce a condition that can be used in subsequent statements. 

DECLARE condition CONDITION [FOR string];
This statement must be written before any DECLARE HANDLER statement that refers to the condition in question. If the FOR option is specified, the condition name stands for the particular condition identified by the specified SQLSTATE value; ``string'' must be a character string literal representing one of the standard SQLSTATE values (not 00000). If the FOR option is omitted, the condition name stands for some condition whose meaning is not understood by SQL but, presumably, by the user. The scope of a condition is the compound statement in which it is declared, excluding any inner compound statements in which another DECLARE CONDITION appears specifying the same condition name. No two condition names in the same scope can refer to the same SQLSTATE value.

Handler Definition This statement is used to define exception handlers for specific conditions. 

DECLARE [CONTINUE | EXIT | UNDO] HANDLER
   FOR exception-commalist
       action;
The ``action'' is a single or compound SQL statement (exactly as in a routine body) which will be executed if any of the specified ``exceptions'' occur. Legal ``exceptions'' are as follows:

The optional handler type specifies what the system is to do before and after execution of the specified action:

SIGNAL
This statement is used to force the raising of a specified condition. 

SIGNAL {condition | string}
where ``condition'' and ``string'' are as for DECLARE CONDITION.

RESIGNAL
It is also used to force the raising of a specified condition but only within an exception handler execution. 

RESIGNAL {condition | string}
where ``condition'' and ``string'' are as for DECLARE CONDITION.

Some important points to note about SIGNAL and RESIGNAL:

18.3.3 Programming Constructs

18.3.3.1 Variable declarations

An SQL variable definition takes the following form: 
DECLARE SQL-variable-commalist
  {data-type | domain} [DEFAULT default];

Any number of SQL variables can be defined within a single DECLARE, all of them having exactly the same data type and the same initial value (specified by ``default'', which can be any legal SQL/92 default, or an invocation of a user-defined function). The DEFAULT specification can be either LITERAL or NULL.

18.3.3.2 Statements for value assignment

SET
The target can be any parameter or an SQL or host variable. The keyword NULL can appear in place of the scalar expression. 
SET target = scalar-expression

18.4 Miscellaneous Topics

18.4.1 Default SPECIFIC names

next up previous contents index
Next: 19. Triggers Up: Ovrimos version 3.0 On-line Previous: 17. Ovrimos SQL