INOUT parameters

You can define the INOUT parameter for a procedure to have a default value, by using the DEFAULT keyword. This capability does not apply to the Entry plan of the Db2® Warehouse on Cloud managed service.

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.

Examples

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);