EXECUTE PROCEDURE (Procedures)
Go Up to Procedures and Triggers
Executes a stored procedure. Available in triggers and stored procedures.
EXECUTE PROCEDURE name [:<param> [, :<param> …]]
[RETURNING_VALUES :<param> [, :<param> …]];
Argument | Description |
---|---|
<name> |
Name of the procedure being executed. Must have been previously defined to the database with |
[<param> [, <param> …]] |
List of input parameters, if the procedure requires them
|
[ |
List of output parameters, if the procedure returns values; precede each with a colon, except |
Description: A stored procedure can itself execute a stored procedure. Each time a stored procedure calls another procedure, the call is said to be nested because it occurs in the context of a previous and still active call to the first procedure. A stored procedure called by another stored procedure is known as a nested procedure.
If a procedure calls itself, it is recursive. Recursive procedures are useful for tasks that involve repetitive steps. Each invocation of a procedure is referred to as an instance, since each procedure call is a separate entity that performs as if called from an application, reserving memory and stack space as required to perform its tasks.
Stored procedures can be nested up to 1,000 levels deep. This limitation helps to prevent infinite loops that can occur when a recursive procedure provides no absolute terminating condition. Nested procedure calls may be restricted to fewer than 1,000 levels by memory and stack limitations of the server.
Example: The following example illustrates a recursive procedure, FACTORIAL
, which calculates factorials. The procedure calls itself recursively to calculate the
factorial of NUM
, the input parameter.
CREATE PROCEDURE FACTORIAL (NUM INT)
RETURNS (N_FACTORIAL DOUBLE PRECISION)
AS
DECLARE VARIABLE NUM_LESS_ONE INT;
BEGIN
IF (NUM = 1) THEN
BEGIN /**** Base case: 1 factorial is 1 ****/
N_FACTORIAL = 1;
EXIT;
END
ELSE
BEGIN
/**** Recursion: num factorial = num * (num-1) factorial ****/
NUM_LESS_ONE = NUM - 1;
EXECUTE PROCEDURE FACTORIAL NUM_LESS_ONE
RETURNING_VALUES N_FACTORIAL;
N_FACTORIAL = N_FACTORIAL * NUM;
EXIT;
END
END;
See Also
For more information on executing procedures, see EXECUTE PROCEDURE.