Mimer SQL Programmer's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer

Routines

In Mimer SQL, the term routine is used to collectively refer to functions and procedures. Essentially the same constructs may be used in each.

A routine can be created by declaring it in a module definition, see Modules, or be created on its own by executing the CREATE FUNCTION or CREATE PROCEDURE statement. A routine created on its own cannot be subsequently added to a module.

A routine belongs to the schema in which it was created and the routine name may be qualified in the normal way with the name of the schema. Only the ident with the same name as the schema to which a routine belongs may refer to it by its unqualified name, all other idents must use the fully qualified routine name.

A given schema cannot contain more than one function or more than one procedure with the same name, i.e. a function cannot have the same qualified name (i.e. schema_name.function_name) as another function and a procedure cannot have the same qualified name as another procedure.

It is possible for a function to have the same qualified name as a procedure, because the invocation of a function is distinct from that of a procedure.

In order to invoke a routine, the ident invoking it must have been granted EXECUTE privilege on the routine. Routines may be recursively invoked.

Note: When routines and modules are created using BSQL, the create statement must be delimited by the "@" character, see the Mimer SQL User's Manual for details and examples).

The following points should be noted for procedures: 

 SET :name = get_author('1-55860-461.8').

A routine essentially consists of static SQL source which is stored in the data dictionary and which may be invoked by name whenever it is to be executed.

The SQL source for a routine comprises a definition of various routine components, seeSyntactic Components of a Routine Definition for details, followed by the routine body.

The routine body consists of a single executable SQL statement, which may be a compound SQL statement, see Scope in Routines - the Compound SQL Statement, containing local declarations and a number of SQL statements, delimited by a BEGIN and END.

Note: It is recommended that a compound SQL statement always be used for the body of a routine, as this offers the greatest flexibility and results in a consistent structure for all routines.

It is possible to declare exception handlers within a compound SQL statement to handle specific exceptions or classes of conditions, see Declaring Exception Handlers.

Functions

A function is invoked by specifying the function invocation where a value expression would normally be used. The parameters of a function are used to provide input only, values cannot be passed back to the calling environment through the parameters of a function.

A function always returns a single value and the data type of the return value is defined in the returns clause, which is specified after the parameter definition part of the function definition.

The function returns its value when a RETURN statement is executed within the body of the function. The data type of the value expression in the RETURN statement must be assignment-compatible with the data type specified in the returns clause of the function.

A function can be created with the same name as a predefined function (e.g. ABS, SIGN). If such a function also has the same number of parameters as the predefined function and each parameter has a data type that is assignment-compatible with the corresponding parameter of the predefined function, the invocations of the two functions cannot be distinguished. In this situation, any unqualified reference to the function name will be taken to be a reference to the predefined function.

Functions and SQL Statements

The SQL statements that apply to a function are:

Statement
Description
CREATE FUNCTION
creates a function that exists on its own
DROP FUNCTION
drops a function that exists on its own
GRANT EXECUTE
grants the privilege to invoke a function
REVOKE EXECUTE
revokes the privilege to invoke a function
COMMENT ON FUNCTION
defines a comment on a function

Refer to the Mimer SQL Reference Manual for a description of the SQL statements mentioned above.

Examples: 
 CREATE FUNCTION SQUARE_INTEGER(ROOT INTEGER)
 RETURNS INTEGER
 CONTAINS SQL
 BEGIN
   RETURN ROOT*ROOT;
 END
 
 CREATE FUNCTION COUNT_BILL(RESERVATION_NUMBER INTEGER)
 RETURNS INTEGER
 READS SQL DATA
 BEGIN
   DECLARE X INTEGER;
   SELECT COUNT(*) INTO X FROM BILL WHERE RESERVATION = RESERVATION_NUMBER;
   RETURN X;
 END
 
 CREATE FUNCTION TWIST(IN_CURRENCY CHAR(3))
 RETURNS DECIMAL(6,3)
 READS SQL DATA
 BEGIN
    DECLARE CNT INTEGER;
    DECLARE L_RATE DECIMAL(6,3);
    SELECT RATE INTO L_RATE FROM EXCHANGE_RATE
                  WHERE CURRENCY = IN_CURRENCY;
    GET DIAGNOSTICS CNT = ROW_COUNT;
    IF CNT = 0 THEN
        SET L_RATE = 1.0;
    END IF;
    RETURN L_RATE;
 END
 CREATE FUNCTION TRANSLATE_DATE(OLD_DATE CHAR(8))
 RETURNS DATE
 BEGIN
    RETURN CAST(SUBSTRING(OLD_DATE FROM 1 FOR 4) || '-' || 
                    SUBSTRING(OLD_DATE FROM 5 FOR 2) || '-' ||
                    SUBSTRING(OLD_DATE FROM 7 FOR 2) AS DATE);
 END

Procedures

A procedure is normally invoked explicitly by executing the CALL statement and does not return a value. The parameters of a procedure can be used to provide input and may be used to pass values back to the calling environment.

There is a special type of procedure, called a result set procedure, which returns rows of a result set to a cursor when it is invoked by executing the FETCH statement in that context.

A result set procedure is distinguished from a normal procedure by having a values clause specified after the parameter definition part of the procedure definition, see Result Set Procedures for a detailed description of result set procedures.

Procedures and SQL Statements

The SQL statements that apply to a procedure are:

Statement
Description
CREATE PROCEDURE
creates a procedure that exists on its own
DROP PROCEDURE
drops a procedure that exists on its own
GRANT EXECUTE
grants the privilege to invoke a procedure
REVOKE EXECUTE
revokes the privilege to invoke a procedure
CALL
invokes a procedure
COMMENT ON PROCEDURE
defines a comment on a procedure

Refer to the Mimer SQL Reference Manual for a description of the SQL statements mentioned above.

Examples 
 CREATE PROCEDURE MY_PROCEDURE ( IN TEST CHAR(8) )
 NOT DETERMINISTIC
 MODIFIES SQL DATA
 BEGIN
   DECLARE SQLSTATUS CHAR(5) DEFAULT '?????';
   DECLARE ERRCNT INTEGER DEFAULT 0;
   DECLARE CASE_EXCEPTION CONDITION FOR SQLSTATE VALUE '20000';
   DECLARE TEST_SUCCESS   CONDITION FOR SQLSTATE VALUE 'Z0000';
   DECLARE TEST_FAILURE   CONDITION FOR SQLSTATE VALUE 'Z9999';
 
   CASE TEST
     WHEN '0414' THEN
 
       L0414:
       BEGIN
         DECLARE X CURSOR FOR SELECT   EMPNUM, HOURS
                              FROM     WORKS
                              WHERE    PNUM = 'P2'
                              ORDER BY EMPNUM DESC;
         SET CNT = 0;
         SET CNT2 = 0;
         BEGIN
           DECLARE LION CURSOR FOR SELECT   EMPNUM
                                   FROM     STAFF
                                   WHERE    EMPNUM = 'E20';
           P200:
           LOOP
              SET CNT = CNT + 1;
              BEGIN
                DECLARE EXIT HANDLER FOR NOT FOUND
                BEGIN
                END;
 
                OPEN X;
                SET I = 0;
                WHILE I < 20 DO
                   FETCH X INTO EMPNO1, HOURS1;
                   SET I = I + 1;
                END WHILE;
              END;
              CLOSE X;
              IF CNT = 5 THEN LEAVE P200; END IF;
              ...
           END LOOP P200;
        ...
     END L0414;
   WHEN '0415' THEN
      ...
   END CASE;
 ...
 END
 
 CALL MY_PROCEDURE('0415')
 
 COMMENT ON PROCEDURE MY_PROCEDURE IS 'This is my procedure'
 
 DROP PROCEDURE MY_PROCEDURE

Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Programmer's Manual TOC PREV NEXT INDEX