Creating Functions, Procedures, Triggers and Modules

---

Functions and procedures are SQL routines that are stored in the data dictionary.

A module is a collection of SQL routines.

Triggers contain the same constructs as routines but are created on tables or views (depending on the type of trigger) and execute before, after or instead of a specified data manipulation operation.

Refer to the Mimer SQL Reference Manual, SQL Statement Descriptions for the syntax definitions for CREATE FUNCTION, CREATE MODULE, CREATE PROCEDURE and CREATE TRIGGER, and the Mimer SQL Programmer's Manual, Mimer SQL Stored Procedures for a general discussion of the stored procedure functionality in Mimer SQL.

Creating Functions and Procedures

The CREATE FUNCTION statement is used to create a function that does not belong to a module and the CREATE PROCEDURE statement is used to create a procedure that does not belong to a module.

The format of the routine definition is the same in the CREATE FUNCTION and CREATE PROCEDURE statements as it is in a function or procedure declaration in a module.

Creating a Module

A module is created by using the CREATE MODULE statement and all the routines that belong to the module are defined by declaring them within the CREATE MODULE statement.

Routines cannot be added to a module after the module has been created and a routine cannot be removed from the module it belongs to. The routines in a module behave in all respects as single objects (e.g. EXECUTE privilege is applied on individual routines in a module, not the module). If the module is dropped, all the routines in it are dropped.

Creating a Trigger

The CREATE TRIGGER statement is used to define a trigger on a table or view.

Examples

Note: The examples that follow show the `@' character which is used in Mimer BSQL to delimit SQL statements whose syntax involves use of the normal end-of-statement character `;' before the actual end of the statement.

This is the case for many of the SQL/PSM statements. See Mimer BSQL for details.

The `@' character may be used to delimit any statement. This is useful when dealing with large statement as the error reporting facility in BSQL shows more information in such cases.

Create a standalone function FUNC_1 with one input parameter of data type VARCHAR(20) that returns a value of data type INTEGER:

 @
 CREATE FUNCTION func_1(p1 VARCHAR(20)) RETURNS INTEGER
 BEGIN
    ...
 END
 @

Create a standalone procedure PROC_1 with one input parameter of data type INTEGER and one output parameter of VARCHAR(20):

 @
 CREATE PROCEDURE proc_1(IN p_value1 INTEGER,
                         OUT p_value2 VARCHAR(20))
 BEGIN
    ...
 END
 @

Create a module M1 containing 2 procedures, PROC_1 (with no parameters), PROC_2 (one input parameter, X, of data type INTEGER) and 1 function, FUNC_1 (with no parameters, returning an INTEGER):

 @
 CREATE MODULE m1
    DECLARE PROCEDURE proc_1()
    READS SQL DATA
    BEGIN
       ...
    END;
 
    DECLARE PROCEDURE proc_2(IN p_x INTEGER)
    MODIFIES SQL DATA
    BEGIN
       ...
    END;
 
    DECLARE FUNCTION func_1() RETURNS INTEGER
    READS SQL DATA
    BEGIN
       ...
    END;
 END MODULE
 @

Create a trigger that will execute after INSERT operations on table PRODUCTS:

 @
 CREATE TRIGGER products_after_insert AFTER INSERT ON products
 REFERENCING NEW TABLE AS pdt
 FOR EACH STATEMENT
 BEGIN ATOMIC
    ...
 END
 @
 

Note: It is recommended that all functions, procedures and triggers are created by executing a command file so that they may be easily re-created in the event of being unintentionally dropped because of CASCADE effects following a drop.

The effect of CASCADE can be quite far-reaching where routines and modules are concerned, see the Mimer SQL Programmer's Manual.

The use of a command file also facilitates module re-definition by dropping an existing module, altering the CREATE MODULE statement in the command file and creating the new, redefined module.

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