DB2 Version 10.1 for Linux, UNIX, and Windows

INOUT parameters

You can define the INOUT parameter for a procedure to have a default value, by using the DEFAULT keyword.

Enablement

You enable INOUT parameter support by setting the DB2_COMPATIBILITY_VECTOR registry variable to hexadecimal value 0x2000 (bit position 14), and then stop and restart the instance to have the new setting take effect.

db2set DB2_COMPATIBILITY_VECTOR=2000
db2stop
db2start

To take full advantage of the DB2 compatibility features for Oracle applications, the recommended setting for the DB2_COMPATIBILITY_VECTOR is ORA, which sets all of the compatibility bits.

An INOUT parameter is both an input and an output parameter. You can use the DEFAULT keyword to define the default value for an INOUT parameter as either an expression or NULL. If you then invoke the procedure by specifying DEFAULT or no argument for the parameter, the default value that you defined for the parameter is used to initialize it. No value is returned for this parameter when the procedure exits.

Restrictions

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

Example

The following code creates 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 specified for the procedure, then the deduction portion is ignored. Examples of how to invoke the procedure follow:

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