Let's look at Figure 8.1 and Figure 8.2 for examples of two basic nested SQL procedures.
Figure 8.1. An example of a called procedure.
CREATE PROCEDURE count_projects ( IN p_empno CHAR(6) , OUT p_total INT ) LANGUAGE SQL SPECIFIC count_projects -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries cp: BEGIN -- Procedure logic SELECT COUNT(*) INTO p_total FROM emp_act WHERE empno = p_empno; END cp
Figure 8.2. An example of a caller procedure.
CREATE PROCEDURE bonus ( IN p_empno CHAR(6) , OUT p_bonus CHAR(1) ) LANGUAGE SQL SPECIFIC bonus -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries bn: BEGIN -- Declare variables DECLARE v_min INT DEFAULT 5; DECLARE v_total INT DEFAULT 0; -- Procedure logic CALL count_projects(p_empno, v_total); IF ( v_total >= v_min ) THEN SET p_bonus = 'Y'; ELSE SET p_bonus = 'N'; END IF; END bn
The procedure count_projects in Figure 8.1 returns the number of projects that one employee has completed. Each employee is identified by an employee number. The procedure bonus in Figure 8.2 is used to determine whether an employee should be awarded a bonus based on how many projects he or she has completed. It uses the output of the count_projects procedure in its calculation.
In procedure bonus, which is referred to as the caller, a CALL statement is issued to invoke procedure count_projects, which is referred to as the called procedure.
By using nested SQL procedures, you can encapsulate business logic into smaller separate units. The code becomes more readable, maintainable, and reusable.