Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

About Routines

A routine is either defined as a function or as a procedure. Essentially the same constructs may be used in both functions, methods and procedures.

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.

It is possible to have multiple functions and procedures with the same name within a schema as long as they differ with regard to either the number of parameters or the data type for the parameters. This is called parameter overloading.

To distinguish between routines with the same name it is possible to give a specific name when creating a routine. This specific name can used when granting or revoking execute privilege for the routine or when dropping the routine.

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, the CREATE statement must be executed as one single statement. For example, using BSQL, the create statement must be delimited by the @ character, see the Mimer SQL User's Manual, Creating Functions, Procedures, Triggers and Modules, for details and examples.

The following points should be noted for procedures: 

 SET :isbn = mimer_store_book.format_isbn('1558604618');

A routine essentially consists of static SQL source that 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, see Syntactic Components of a Routine Definition for details, followed by the routine body.

The routine body consists of a single executable SQL statement - typically a compound SQL statement, i.e. local declarations and a number of SQL statements delimited by a BEGIN and END. See Scope in Routines - the Compound SQL Statement.

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. A function can return user-defined types.

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.

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, see the Mimer SQL Reference Manual, CREATE FUNCTION
 
 DROP FUNCTION
drops a function that exists on its own, see the Mimer SQL Reference Manual, DROP
 
 GRANT EXECUTE
grants the privilege to invoke a function, see the Mimer SQL Reference Manual, GRANT OBJECT PRIVILEGE
 
 REVOKE EXECUTE
revokes the privilege to invoke a function, see the Mimer SQL Reference Manual, REVOKE OBJECT PRIVILEGE
 
 COMMENT ON FUNCTION
defines a comment on a function, see the Mimer SQL Reference Manual, COMMENT.

Example 1 
 CREATE FUNCTION SQUARE_INTEGER(p_root INTEGER) RETURNS INTEGER
 CONTAINS SQL
 BEGIN
    RETURN p_root * p_root;
 END
Example 2 
 CREATE FUNCTION mimer_store_web.session_expiration_period()
    RETURNS INTERVAL HOUR TO MINUTE
 -- Defines the period that a session can be unused
 DETERMINISTIC
 RETURN INTERVAL '10' MINUTE(3);  -- Intentionally very short
Example 3 
 CREATE FUNCTION mimer_store_book.keyword_id(p_keyword VARCHAR(48))
    RETURNS INTEGER
 -- Inserts a word in the KEYWORDS table
 -- and returns the identifier with which the keyword is associated
 MODIFIES SQL DATA
 BEGIN
    DECLARE v_keyword_id INTEGER;
 
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    BEGIN
       INSERT INTO mimer_store_book.keywords(keyword)
          VALUES (UPPER(TRIM(p_keyword)));
 
       SET v_keyword_id = CURRENT VALUE FOR mimer_store_book.keyword_id_seq;
    END;  -- of not found handler
 
    SELECT keyword_id
       INTO v_keyword_id
       FROM mimer_store_book.keywords
       WHERE keyword = TRIM(p_keyword);
 
    RETURN v_keyword_id;
 END  -- of routine mimer_store_book.keyword_id

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 returns 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, see the Mimer SQL Reference Manual, CREATE PROCEDURE
 
 DROP PROCEDURE
drops a procedure that exists on its own, see the Mimer SQL Reference Manual, DROP
 
 GRANT EXECUTE
grants the privilege to invoke a procedure, see the Mimer SQL Reference Manual, GRANT OBJECT PRIVILEGE
 
 REVOKE EXECUTE
revokes the privilege to invoke a procedure, see the Mimer SQL Reference Manual, REVOKE OBJECT PRIVILEGE
 
 CALL
invokes a procedure, see the Mimer SQL Reference Manual, CALL
 
 COMMENT ON PROCEDURE
defines a comment on a procedure, see the Mimer SQL Reference Manual, COMMENT.

Example 1 
 CREATE PROCEDURE mimer_store_web.delete_basket(p_session_no VARCHAR(16))
 -- Deletes expired baskets
 MODIFIES SQL DATA
 BEGIN
    IF p_session_no = '*' THEN
       -- '*' indicates that all expired sessions should be deleted
       DELETE
          FROM mimer_store.orders
          WHERE order_id IN (SELECT order_id
                                FROM mimer_store_web.sessions
                                WHERE last_accessed < LOCALTIMESTAMP -
                                      mimer_store_web.session_expiration_period());
    ELSE
       -- Delete the specified session
       DELETE
          FROM mimer_store.orders
          WHERE order_id = (SELECT order_id
                               FROM mimer_store_web.sessions
                               WHERE session_no = p_session_no);
    END IF;
 END  -- of routine mimer_store_web.delete_basket
 
 
 CALL mimer_store_web.delete_basket( '*' );
 
 
 COMMENT ON PROCEDURE mimer_store_web.delete_basket
         IS 'Deletes expired baskets';
 
 
 DROP PROCEDURE mimer_store_web.delete_basket;
Example 2 
 CREATE PROCEDURE mimer_store_book.catalogue_authors(IN p_item_id INTEGER,
                                                  IN p_authors_list VARCHAR(128))
 -- Stores author names as keywords and forms a link between a book
 -- and the keywords
 MODIFIES SQL DATA
 BEGIN
    DECLARE v_author VARCHAR(50);
    DECLARE v_authors VARCHAR(130);
    DECLARE v_offset, v_length INTEGER;
 
    SET v_authors = REPLACE(' ' || p_authors_list || ' ', ' and ', ';');
    SET v_authors = REPLACE(v_authors, ' & ', ';');
    SET v_authors = TRIM(v_authors);
 
 extract_authors:
    LOOP
       IF v_authors = '' THEN LEAVE extract_authors; END IF;
 
       SET v_offset = POSITION(';' IN v_authors);
 
       IF v_offset <> 1 THEN
          IF v_offset = 0
          OR v_offset > 49 THEN
             SET v_length = 48;
          ELSE
             SET v_length = v_offset - 1;
          END IF;
 
          SET v_author = mimer_store_book.authors_name(
                            SUBSTRING(v_authors FROM 1 FOR v_length));
 
          BEGIN
             DECLARE v_keyword_id INTEGER;
 
             DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
             BEGIN
                -- Ignore all SQL errors
             END;  -- of sqlexception handler
 
             SET v_keyword_id = mimer_store_book.keyword_id(v_author);
 
             INSERT INTO mimer_store_book.authors(keyword_id, item_id)
                VALUES (v_keyword_id, p_item_id);
          END;
       END IF;
 
       IF v_offset = 0 THEN LEAVE extract_authors; END IF;
 
       SET v_authors = TRIM(SUBSTRING(v_authors FROM v_offset+1));
    END LOOP extract_authors;
 END  -- of routine mimer_store_book.catalogue_authors
Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX