Basic Nested SQL Procedures

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.