DB2 Version 9.7 for Linux, UNIX, and Windows

INOUT parameter

Starting with Version 9.7 Fix Pack 2 and later fix packs, a procedure can have INOUT parameters defined with a default and the procedure can subsequently be invoked without an argument for those parameters.

Procedure declarations allow the specification of DEFAULT expressions for INOUT parameters in addition to IN parameters. A procedure with INOUT parameters defined with defaults can be invoked without specifying arguments corresponding to those parameters. If an argument corresponding to an INOUT parameter is not specified, or the argument is the DEFAULT keyword, then the provided default expression (or NULL if none was specified) is used to initialize the parameter within the procedure and no value is returned for this parameter when the procedure exits.

INOUT parameter support is enabled by setting bit position number 14 (0x2000) of the DB2_COMPATIBILITY_VECTOR registry variable. A new setting for the registry variable does not take effect until after the instance has been stopped and then restarted.

Restrictions

The DEFAULT keyword is supported for INOUT parameters in procedures, but not in functions.

Example

Creating a procedure with optional INOUT parameters.

   CREATE OR REPLACE PROCEDURE paybonus
     (IN empid INTEGER, 
     IN percentbonus DECIMAL(2, 2), 
     INOUT budget DECFLOAT DEFAULT NULL)
     ...

The procedure computes the amount of bonus from the employee's salary, issues the bonus, and then deducts the bonus from the departmental budget. If no budget is given, then that part is ignored. The procedure can be invoked such as:

   CALL paybonus(12, 0.05, 50000);
   CALL paybonus(12, 0.05, DEFAULT);
   CALL paybonus(12, 0.05);