Creating Nested and Recursive Procedures
Go Up to The Procedure Body
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 can be restricted to fewer than 1,000 levels by memory and stack limitations of the server.
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;
SUSPEND;
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;
SUSPEND;
END
END ;
The following C code demonstrates how a host-language program would call FACTORIAL
:
. . .
printf('\nCalculate factorial for what value? ');
scanf('%d', &pnum);
EXEC SQL
EXECUTE PROCEDURE FACTORIAL :pnum RETURNING_VALUES
:pfact;
printf('%d factorial is %d.\n', pnum, pfact);
. . .
Recursion nesting restrictions would not allow this procedure to calculate
factorials for numbers greater than 1,001. Arithmetic overflow, however, occurs for much smaller numbers.