The
CALL statement calls a procedure or a foreign procedure.
Invocation
This statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. It is an executable statement that can be dynamically
prepared.
When invoked using the command line processor, there
are some additional rules for specifying arguments of the procedure.
For more information, refer to Using command line SQL statements and XQuery statements.
Authorization
The privileges held by the
authorization ID of the statement must include at least one of the
following authorities:
- EXECUTE privilege on the procedure
- DATAACCESS authority
If a matching procedure exists that the authorization
ID of the statement is not authorized to execute, an error is returned
(SQLSTATE 42501).
Syntax
>>-CALL--procedure-name--+----------------------------+--------><
| .-,----------------. |
| V | |
'-(------| argument |---+--)-'
argument
|--+--------------------+--+-expression-+-----------------------|
'-parameter-name--=>-' +-DEFAULT----+
'-NULL-------'
Description
- procedure-name
- Specifies the procedure that is to be called. It must be a procedure
that is described in the catalog or that is declared in the scope
of the compound SQL (compiled) statement that includes the CALL statement.
The specific procedure to invoke is chosen using procedure resolution.
(For more details, see the "Notes" section of this statement.)
- argument
- parameter-name
- Name of the parameter to which the argument is assigned. When
an argument is assigned to a parameter by name, then all the arguments
that follow it must also be assigned by name (SQLSTATE 4274K).
A
named argument must be specified only once (implicitly or explicitly)
(SQLSTATE 4274K).
Named arguments are not supported on the
call to an uncataloged procedure (SQLSTATE 4274K).
- expression or DEFAULT or NULL
- Each specification of expression, the DEFAULT keyword, or the NULL keyword is
an argument of the CALL. The nth unnamed argument of the CALL statement corresponds
to the nth parameter defined in the CREATE
PROCEDURE statement for the procedure.
Named
arguments correspond to the same named parameter, regardless of the
order in which they are specified.
If the
DEFAULT keyword is specified, the default as defined in the CREATE
PROCEDURE statement is used if it exists; otherwise the null value
is used as the default.
If the NULL keyword
is specified, the null value is passed as the parameter value.
Each
argument of the CALL must be compatible with the corresponding parameter
in the procedure definition as follows:
- IN parameter
- The argument must be assignable to the parameter.
- The assignment of a string argument uses the storage assignment
rules.
- OUT parameter
- The argument must be a single variable or parameter marker (SQLSTATE
42886).
- The argument must be assignable to the parameter.
- The assignment of a string argument uses the retrieval assignment
rules.
- INOUT parameter
- The argument must be a single variable or parameter marker (SQLSTATE
42886).
- The argument must be assignable to the parameter.
- The assignment of a string argument uses the storage assignment
rules on invocation and the retrieval assignment rules on return.
Notes
- Parameter assignments: When the CALL statement is
executed, the value of each of its arguments is assigned (using storage
assignment) to the corresponding parameter of the procedure. A parameter value that is defined to have a default
value can be omitted from the argument list when invoking the procedure.
When the CALL statement is executed, control
is passed to the procedure according to the calling conventions of
the host language. When execution of the procedure is complete, the
value of each parameter of the procedure is assigned (using storage
assignment) to the corresponding argument of the CALL statement defined
as OUT or INOUT. If an error is returned by the procedure, OUT arguments
are undefined and INOUT arguments are unchanged. For details on the
assignment rules, see "Assignments and comparisons".
When
the CALL statement is in an SQL procedure and is calling another SQL
procedure, assignment of XML parameters is done by reference. When
an XML argument is passed by reference, the input node trees, if any,
are used directly from the XML argument, preserving all properties,
including document order, the original node identities, and all parent
properties.
- Procedure signatures: A procedure is identified
by its schema, a procedure name, and the number of parameters. This
is called a procedure signature, which must be unique within the database.
There can be more than one procedure with the same name in a schema,
provided that the number of parameters is different for each procedure.
- SQL path: A procedure can be invoked by referring
to a qualified name (schema and procedure name), followed by an optional
list of arguments enclosed by parentheses. A procedure can also be
invoked without the schema name, resulting in a choice of possible
procedures in different schemas with the same number of parameters.
In this case, the SQL path is used to assist in procedure resolution.
The SQL path is a list of schemas that is searched to identify a procedure
with the same name and number of parameters. For static CALL statements,
SQL path is specified using the FUNCPATH bind option. For dynamic
CALL statements, SQL path is the value of the CURRENT PATH special
register.
- Procedure resolution: Given a procedure invocation,
the database manager must decide which of the possible procedures
with the same name to execute.
Local
scope procedure resolution is used when a procedure is invoked from
within a compound SQL (compiled) statement and either of the following
criteria exist:
- A procedure with the same name as the invoked procedure is declared
in the same compound SQL (compiled) statement
- A procedure with the same name as the invoked procedure is declared
in a compound SQL (compiled) statement within which the compound SQL
(compiled) statement that invoked the procedure is nested
Local scope procedure resolution means that only declared procedures
within the scope of the compound SQL (compiled) statement that invoked
the procedure are considered during procedure resolution regardless
of the existence of possible matching built-in procedures, schema
procedures, or module procedures.
Global scope procedure resolution is
used in all other cases and considers candidates from schemas and
modules depending on the context of the invocation and the qualification
of the procedure name.
- Let A be the number of arguments in a procedure invocation.
- Let P be the number of parameters in a procedure signature.
- Let N be the number of parameters without a default.
Candidate procedures for resolution of
a procedure invocation are selected based on the following criteria:
- Each candidate procedure has a matching name and an applicable
number of parameters. An applicable number of parameters satisfies
the condition N ≤ A ≤ P.
- Each
candidate procedure has parameters such that for each named argument
in the CALL statement there exists a parameter with a matching name
that does not already correspond to a positional (or unnamed) argument.
- Each parameter of a candidate procedure that
does not have a corresponding argument in the CALL statement, specified
by either position or name, is defined with a default.
- Each candidate procedure from a set of one or
more schemas has the EXECUTE privilege associated with the authorization
ID of the statement invoking the function.
In addition, the set of candidate procedures depends on the environment
where the procedure is invoked and how the procedure name is qualified.
- Retrieving the DB2_RETURN_STATUS from an SQL procedure: If
an SQL procedure successfully issues a RETURN statement with a status
value, this value is returned in the first SQLERRD field of the SQLCA.
If the CALL statement is issued in an SQL procedure, use the GET DIAGNOSTICS
statement to retrieve the DB2_RETURN_STATUS value. The value is -1
if the SQLSTATE indicates an error. The values is 0 if no error is
returned and the RETURN statement was not specified in the procedure.
- Returning result sets from procedures: If the calling
program is written using CLI, JDBC, or SQLJ, or the caller is an SQL
procedure, result sets can be returned directly to the caller. The
procedure indicates that a result set is to be returned by declaring
a cursor on that result set, opening a cursor on the result set, and
leaving the cursor open when exiting the procedure.
At the end of
a procedure:
- For every cursor that has been left open, a result set is returned
to the caller or (for WITH RETURN TO CLIENT cursors) directly to the
client.
- Only unread rows are passed back. For example, if the result set
of a cursor has 500 rows, and 150 of those rows have been read by
the procedure at the time the procedure is terminated, rows 151 through
500 will be returned to the caller or application (as appropriate).
If the procedure was invoked from CLI or JDBC, and more than
one cursor is left open, the result sets can only be processed in
the order in which the cursors were opened.
- Improving performance: The values of all arguments
are passed from the application to the procedure. To improve the performance
of this operation, host variables that correspond to OUT parameters
and have lengths of more than a few bytes should be set to the null
value before the CALL statement is executed.
- Nesting CALL statements: Procedures can be called
from routines as well as application programs. When a procedure is
called from a routine, the call is considered to be nested.
If a
procedure returns any query result sets, the result sets are returned
as follows:
- RETURN TO CALLER result sets are visible only to the program that
is at the previous nesting level.
- RETURN TO CLIENT results sets are visible only if the procedure
was invoked from a set of nested procedures. If a function or method
occurs anywhere in the call chain, the result set is not visible.
If the result set is visible, it is only visible to the client application
that made the initial procedure call.
Consider the following example:
Client program:
EXEC SQL CALL PROCA;
PROCA:
EXEC SQL CALL PROCB;
PROCB:
EXEC SQL DECLARE B1 CURSOR WITH RETURN TO CLIENT ...;
EXEC SQL DECLARE B2 CURSOR WITH RETURN TO CALLER ...;
EXEC SQL DECLARE B3 CURSOR FOR SELECT UDFA FROM T1;
UDFA:
EXEC SQL CALL PROCC;
PROCC:
EXEC SQL DECLARE C1 CURSOR WITH RETURN TO CLIENT ...;
EXEC SQL DECLARE C2 CURSOR WITH RETURN TO CALLER ...;
From
procedure PROCB:
- Cursor B1 is visible in the client application, but not visible
in procedure PROCA.
- Cursor B2 is visible in PROCA, but not visible to the client.
From procedure PROCC:
- Cursor C1 is visible to neither UDFA nor to the client application.
(Because UDFA appears in the call chain between the client and PROCC,
the result set is not returned to the client.)
- Cursor C2 is visible in UDFA, but not visible to any of the higher
procedures.
- Nesting procedures within triggers, compound statements,
functions, or methods: When a procedure is called within a
trigger, compound statement, function, or method:
- The procedure must not issue a COMMIT or a ROLLBACK statement.
- Result sets returned from the procedure cannot be accessed.
- If the procedure is defined as READS SQL DATA or MODIFIES SQL
DATA, no statement in the procedure can access a table that is being
modified by the statement that invoked the procedure (SQLSTATE 57053).
If the procedure is defined as MODIFIES SQL DATA, no statement in
the procedure can modify a table that is being read or modified by
the statement that invoked the procedure (SQLSTATE 57053).
When
a procedure is called within a function or method:
- The procedure has the same table access restrictions as the invoking
function or method.
- Savepoints defined before the function or method was invoked will
not be visible to the procedure, and savepoints defined inside the
procedure will not be visible outside the function or method.
- RETURN TO CLIENT result sets returned from the procedure cannot
be accessed from the client.
- Compilation
of CALL statements from DB2® for i and DB2 for z/OS®:The compilation of CALL statements from DB2 for i and DB2 for z/OS implicitly behave
as if CALL_RESOLUTION DEFERRED was specified. When CALL statements are compiled with CALL_RESOLUTION
DEFERRED, all arguments must be provided via host variables, and expressions are not allowed.
- Syntax alternatives: There is an older form of the
CALL statement that can be embedded in an application by precompiling
the application with the CALL_RESOLUTION DEFERRED option. This option
is not available for SQL procedures and federated procedures.
Examples
- Example 1: A Java™ procedure
is defined in the database using the following statement:
CREATE PROCEDURE PARTS_ON_HAND (IN PARTNUM INTEGER,
OUT COST DECIMAL(7,2),
OUT QUANTITY INTEGER)
EXTERNAL NAME 'parts!onhand'
LANGUAGE JAVA
PARAMETER STYLE DB2GENERAL;
A Java application calls this procedure
using the following code fragment:
...
CallableStatement stpCall;
String sql = "CALL PARTS_ON_HAND (?, ?, ?)";
stpCall = con.prepareCall(sql); /*con is the connection */
stpCall.setInt(1, hvPartnum);
stpCall.setBigDecimal(2, hvCost);
stpCall.setInt(3, hvQuantity);
stpCall.registerOutParameter(2, Types.DECIMAL, 2);
stpCall.registerOutParameter(3, Types.INTEGER);
stpCall.execute();
hvCost = stpCall.getBigDecimal(2);
hvQuantity = stpCall.getInt(3);
...
This application code fragment will invoke
the Java method onhand in
class parts, because the procedure name specified
on the CALL statement is found in the database and has the external
name parts!onhand.
- Example 2: There are six FOO procedures, in four different
schemas, registered as follows (note that not all required keywords
appear):
CREATE PROCEDURE AUGUSTUS.FOO (INT) SPECIFIC FOO_1 ...
CREATE PROCEDURE AUGUSTUS.FOO (DOUBLE, DECIMAL(15, 3)) SPECIFIC FOO_2 ...
CREATE PROCEDURE JULIUS.FOO (INT) SPECIFIC FOO_3 ...
CREATE PROCEDURE JULIUS.FOO (INT, INT, INT) SPECIFIC FOO_4 ...
CREATE PROCEDURE CAESAR.FOO (INT, INT) SPECIFIC FOO_5 ...
CREATE PROCEDURE NERO.FOO (INT,INT) SPECIFIC FOO_6 ...
The
procedure reference is as follows (where I1 and I2 are INTEGER values):
CALL FOO(I1, I2)
Assume that
the application making this reference has an SQL path established
as:
"JULIUS", "AUGUSTUS", "CAESAR"
Following
through the algorithm...
The procedure with specific name FOO_6
is eliminated as a candidate, because the schema "NERO" is not included
in the SQL path. FOO_1, FOO_3, and FOO_4 are eliminated as candidates,
because they have the wrong number of parameters. The remaining candidates
are considered in order, as determined by the SQL path. Note that
the types of the arguments and parameters are ignored. The parameters
of FOO_5 exactly match the arguments in the CALL, but FOO_2 is chosen
because "AUGUSTUS" appears before "CAESAR" in the SQL path.
- Example 3: Assume the following procedure exists.
CREATE PROCEDURE update_order(
IN IN_POID BIGINT,
IN IN_CUSTID BIGINT DEFAULT GLOBAL_CUST_ID,
IN NEW_STATUS VARCHAR(10) DEFAULT NULL,
IN NEW_ORDERDATE DATE DEFAULT NULL,
IN NEW_COMMENTS VARCHAR(1000)DEFAULT NULL)...
Also assume that the global variable
GLOBAL_CUST_ID is
set to the value 1002. Call the procedure to change the status of
order 5000 for customer 1002 to 'Shipped'. Leave the rest of the order
data as it is by allowing the rest of the arguments to default to
the null value.
CALL update_order (5000, NEW_STATUS => 'Shipped')
The customer with ID 1001 has
called and indicated that they received their shipment for purchase
order 5002 and are satisfied. Update their order.
CALL update_order (5002,
IN_CUSTID => 1001,
NEW_STATUS => 'Received',
NEW_COMMENTS => 'Customer satisfied with the order.')
- Example
4: The following example illustrates procedure resolution, given
two procedures named p1:
CREATE PROCEDURE p1(i1 INT)...
CREATE PROCEDURE p1(i1 INT DEFAULT 0, i2 INT DEFAULT 0)...
CALL p1(i2=>1)
The argument names are taken into
consideration during the candidate selection process. Therefore, only
the second version of p1 will be considered
a candidate. Furthermore, it can be successfully called because i1 in
this version of p1 is defined with a default,
so only specifying i2 on the call to p1 is
valid.
- Example
5: The following example is another illustration of procedure
resolution, given two procedures named p1:
CREATE PROCEDURE p1(i1 INT, i2 INT DEFAULT 0)...
CREATE PROCEDURE p1(i1 INT DEFAULT 0, i2 INT DEFAULT 0, i3 INT DEFAULT 0)...
CALL p1(i2=>1)
One of the criteria for a procedure
parameter which does not have a corresponding argument in the CALL
statement (specified by either position or name) is that the parameter
is defined with a default value. Therefore, the first version of p1 is
not considered a candidate.