Calling procedures from the command line processor (CLP)

Example 1.

In the whiles.db2 file, the CREATE PROCEDURE statement for the DEPT_MEDIAN procedure signature is as follows:

   CREATE PROCEDURE DEPT_MEDIAN 
   (IN deptNumber SMALLINT, OUT medianSalary DOUBLE)

The DEPT_MEDIAN procedure selects the STAFF table for the specified deptNumber value. You can call the DEPT_MEDIAN procedure with the following CALL statement:

   db2 call dept_median (51, ?)

On Linux® and UNIX operating systems, the parentheses have special meaning to the command shell, so they must be preceded with a "\" character or surrounded with quotation marks, as follows:

   db2 "call dept_median (51, ?)"

You do not use quotation marks if you are using the interactive mode of the command line processor. The following results are returned from the DEPT_MEDIAN procedure:

   Value of output parameters
   --------------------------
   Parameter Name  : MEDIANSALARY
   Parameter Value : +1.76545000000000E+004 

   Return Status = 0

Example 2.

The example 2 illustrates how to call a procedure with array parameters. The user-defined data type phonenumbers is defined as follows:

CREATE TYPE phonenumbers AS VARCHAR(12) ARRAY[1000]

In the following example, the find_customers procedure contains parameters of type phonenumbers. The find_customers procedure searches for the area_code value in the numbers_in parameter and reports them in the numbers_out parameter.

CREATE PROCEDURE find_customers(
IN numbers_in phonenumbers,
IN area_code CHAR(3),
OUT numbers_out phonenumbers)
BEGIN
DECLARE i, j, max INTEGER;

SET i = 1;
SET j = 1;
SET numbers_out = NULL;
SET max = CARDINALITY(numbers_in);

WHILE i <= max DO
IF substr(numbers_in[i], 1, 3) = area_code THEN
SET numbers_out[j] = numbers_in[i];
SET j = j + 1;
END IF;
SET i = i + 1;
END WHILE;
END

To call the find_customers procedure, you can use the following CALL statement:

db2 CALL find_customers(ARRAY['416-305-3745',
                              '905-414-4565',     
                              '416-305-3746'],
                              '416',
                               ?)

As shown in the CALL statement example, when a procedure has an input parameter of an array data type, the input argument can be specified with an array constructor that contains a list of literal values.

The following results are returned from the find_customers procedure:

Value of output parameters
  --------------------------
  Parameter Name  : OUT_PHONENUMBERS
  Parameter Value : ['416-305-3745',
                     '416-305-3746']

  Return Status = 0

You can also call stored procedures that are created from supported host languages with the Command Line Processor. In the c subdirectory under the sample directory contains files that contain sample stored procedures. The spserver shared library contains a number of stored procedures that can be created from the spserver.sqc file. The spcreate.db2 file registers the stored procedures.

CREATE PROCEDURE MAIN_EXAMPLE (IN job CHAR(8), 
                               OUT salary DOUBLE, 
                               OUT errorcode INTEGER)

   db2 "call MAIN_EXAMPLE ('DESIGNER', ?, ?)"

   Value of output parameters
   --------------------------
   Parameter Name  : SALARY
   Parameter Value : +2.37312500000000E+004 

   Parameter Name  : ERRORCODE
   Parameter Value : 0

   Return Status = 0