DB2 Version 10.1 for Linux, UNIX, and Windows

Adding implementations for routine prototypes in a module

Implementing routine-bodies for defined routine prototype in a module is a task that you can do after defining one or more routine prototypes.

Before you begin

Before proceeding with this task:

About this task

Module SQL procedures are allowed to be declared as prototypes first. SQL procedure prototypes allow you to define the procedure and its parameters without, at the same time, having to specify the logic of the routine-body.

Defining routine prototypes before completing the routine implementation is useful and allows you to not worry about the order of creation of routines that cross-reference other routines. For example, without using prototypes if you had 3 routines named, A, B, and C and programmatically you wanted A to call B, and have B call C, then the routines would had to be created in the following order: C, B, and then A. If routine prototypes are created first, this allows you to implement the routines in any order, reference the other routines by name and implement them later.

Routines without routine-bodies cannot be invoked. Implementing the routine-bodies for routines that have been defined as prototypes is a prerequisite to successfully invoking the routine.

Restrictions

For restrictions on modules see:Restrictions on modules

Procedure

  1. Formulate an ALTER MODULE statement:
    1. Specify the ADD clause to implement a function or procedure for which a prototype is already defined in the module.
    2. Specify one of: a module-function-defintion or a module-procedure-definition. The overriding routine must be specified in the same way as the original prototype was previously specified - the parameters must be specified exactly as they were in the existing routine, including the parameter names, data types, and the IN, OUT, or INOUT attribute of each parameter.
    3. Specify any attribute values that are to be changed. If none are specified, the attribute values for the original prototype definition are maintained.
  2. Execute the ALTER MODULE statement from a supported DB2® interface.
  3. Repeat steps 1 - 2 to complete additional routine definitions in the module.

Results

If the ALTER MODULE statement executes successfully, the routines will be fully defined in the module and can be invoked. The attributes of the routine are not changed unless explicitly specified and different from the existing routine. The overridden routine retains the original published flag and the routine remains available for use outside the module.

Example

The following is another example of a script in which a module is created and more complex procedure prototypes are published, and the procedure definitions for the published prototypes are added:
CREATE MODULE moo@

ALTER MODULE moo PUBLISH PROCEDURE prot 
(IN a INT, IN b BIGINT, IN c VARCHAR(20))
LANGUAGE SQL@

ALTER MODULE moo ADD PROCEDURE prot
(IN a INT, IN b BIGINT, IN c VARCHAR(20))
BEGIN
  DECLARE stmt VARCHAR(1000);
  DECLARE c1 CURSOR WITH RETURN FOR sl;

  SET stmt = 'SELECT 1 FROM (values(1))';
  PREPARE sl FROM stmt;
  OPEN c1;
END@


ALTER MODULE moo PUBLISH FUNCTION fprot (r1 INT)
RETURNS int@

ALTER MODULE moo ADD FUNCTION fprot (r1 INT)
RETURNS int
BEGIN
   RETURN r1-1;
END
@

What to do next

Once you have added the full routine definitions to a module, you will be able to successfully invoke the routines.