Intelligent Enterprises of the 21st Century

If you implement SQL Procedure A which calls SQL Procedure B, which in turn calls Procedure C, you have three levels of nested calls (A->B->C). DB2 LUW and DB2 for zSeries support up to 16 levels of SQL procedure nesting. iSeries has no limit to the number of nesting levels for SQL procedures and user-defined functions.

Recursion

Not only can SQL procedures call other SQL procedures, they can also call themselves. This concept is known as recursion. Recursive SQL procedures are a special case of nested SQL procedures.

Recursive procedures are sometimes the simplest solution to certain problems, even though they may be rewritten in a non-recursive way with the use of loops. Figure 8.24 shows an example of a recursive procedure.

Figure 8.24. An example of a recursive procedure.

CREATE PROCEDURE managers ( IN p_deptno CHAR(3) , OUT p_report_chain VARCHAR(100) ) LANGUAGE SQL SPECIFIC managers -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries mn: BEGIN -- Declare variables DECLARE v_manager_name VARCHAR(15); DECLARE v_admrdept CHAR(3); DECLARE v_report_chain VARCHAR(100); DECLARE v_stmt VARCHAR(100) DEFAULT 'CALL managers(?,?)'; --(1) applies to LUW or iSeries -- Procedure logic SELECT admrdept --(2) INTO v_admrdept FROM department WHERE deptno=p_deptno; SELECT lastname --(3) INTO v_manager_name FROM employee, department WHERE empno=mgrno AND deptno=p_deptno; IF (v_manager_name IS NULL) THEN SET v_manager_name = 'VACANT'; END IF; IF ( v_admrdept IS NULL ) THEN SET p_report_chain = 'Department ' || p_deptno || ' not found.'; --(4) ELSEIF ( v_admrdept = p_deptno ) THEN --(5) SET p_report_chain = v_manager_name; ELSE --applies to LUW or iSeries PREPARE v_st from v_stmt; --(6) --applies to LUW EXECUTE v_st INTO v_report_chain USING v_admrdept; --(7) --applies to iSeries --EXECUTE v_st USING v_admrdept, v_report_chain; --(8) --applies to zSeries or iSeries --CALL managers (v_admrdept, v_report_chain); --(9) SET p_report_chain = v_manager_name || ' -> ' || v_report_chain; END IF; END mn

The procedure managers find the report chain of a department, which is indicated by a department number. For any department, there is an administrative department. The manager of a department is not considered a member of this department but rather a member of the administrative department, except at the highest executive level.

The basic logic is to use a department number to find the administrative department number on Line (2), then to use the new department number obtained as the input in turn to find the next level administrative department number on Line (7). The process will continue until the highest executive level is reached.

As with all recursive procedures, you need a terminating condition. In this case, the condition is the highest executive level where the department number is the same as the administrative department number, or if the department number provided to the procedure is incorrect. See Lines (2), (4), and (5).

The dynamic SQL on Line (1) is required on LUW in order to compile the procedure because the procedure is making a recursive call to call itself, and deferred validation is not supported. If static SQL is used, the procedure name will not be resolved at compile time and will generate a compile error. On zSeries and iSeries, however, deferred object validation is supported, and a static CALL statement can be used as shown on Line (9). In fact, on zSeries you have to use the static CALL statement because it cannot be prepared dynamically. On iSeries, either form of the CALL statement can be used. So the previous example is valid if Lines (6) and (8) are used in conjunction for the dynamic CALL, or just Line (9) for the static CALL.

The limit on the number of recursions is the same as the limit for nesting levels. You need to know how many levels of recursion your recursive SQL procedure will require. You might have to rewrite the code using an iterative solution if the limit is reached, or as a user-defined function.