CALL CLPPlus command

The CALL CLPPlus command calls a stored procedure.

Invocation

You can use the CALL command to call a stored procedure with array parameters when the stored procedure is on a Db2® server.

Authorization

None

Required connection

None

Command syntax

argument

Command parameters

procedure_name

Specifies the procedure to call. If multiple stored procedures with the same name are present, the specific procedure to invoke is chosen by using procedure resolution. Procedure resolution involves identifying the target procedure by its schema, the procedure name, and the number of parameters.

Attention: If procedure_name is not qualified with a schema name in the CALL command, the value of the CURRENT SCHEMA special register is used as the procedure name qualifier in the CALL command.

parameter_value

Specifies the argument value. Enclose all text values in single quotation marks.

Specifies the OUT parameter placeholder.

ARRAY[ parameter_value1, parameter_value2, ... ]

Specifies the array parameter values. Enclose all text values in single quotation marks.

Examples

In the following example, the SUM stored procedure with IN and OUT parameters is created:

CREATE PROCEDURE MYSCHEMA.SUM
          (IN int1 integer, IN int2 integer, OUT int3 integer)
           BEGIN
            SET int3 = int1+int2;
           END;
           /

The CURRENT SCHEMA special register is set to the value MYSCHEMA.

SET CURRENT SCHEMA=’MYSCHEMA’;

The SUM stored procedure is invoked by using the CALL command:

call sum(5,10,?);
/

The sample CALL command returns the following output:

Value of output parameters
--------------------------------
INT3 = 15

DB250000I: The command completed successfully.

In the following example, the names user type and the find_student stored procedure with array parameters are created:

CREATE TYPE names AS VARCHAR(20) ARRAY[50];

CREATE PROCEDURE find_student(IN students_in names,IN alphabet VARCHAR(1), OUT students_out names)
          BEGIN
            DECLARE i,j,max INTEGER;
        SET i = 1;
        SET j = 1;
        SET students_out = NULL;
        SET max = CARDINALITY(students_in);
        WHILE i <= max DO
        if substr(students_in[i], 1, 1) = alphabet THEN
            SET students_out[j] = students_in[i];
            SET j = j+1;
        END IF;
            SET i = i+1;
         END WHILE;
           END;
          /

The find_student stored procedure is invoked to find the list of students whose names start with the character A:

CALL find_student(ARRAY['Alice','Bob','Derk','Peter','Alan','Clark'],'A',?);
/

The sample CALL command returns the following output:

Value of output parameters
--------------------------------
STUDENTS_OUT : ARRAY

Values
---------------
'Alice'
'Alan'

DB250000I: The command completed successfully.