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.
- Note: 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
. . . 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.