Bringing It All Together | Intelligent Enterprises of the 21st Century

To summarize the concepts that have been introduced in this chapter, this section presents an example that incorporates many of these concepts.

The sample procedure in Figure 6.23 shows an example of a procedure that is used to delete a row from the department table. It takes the department number to be deleted as an input parameter and returns a message as an output parameter. The row is only deleted if none of the child tables beneath it contain the department number that is to be deleted. A return value of -1 is returned if an error is encountered, 0 upon successful completion, and 1 if successful but no rows were deleted because none existed.

Figure 6.23. An example for the Delete Department for LUW and iSeries.

 CREATE PROCEDURE delete_dept ( IN p_deptno CHAR(3)                               ,OUT p_message VARCHAR(100) )     LANGUAGE SQL     SPECIFIC delete_dept                        -- applies to LUW and iSeries ------------------------------------------------------------------------ -- Procedure Description -- -- Deletes a department, as long as there are no rows with the input --    department number in any child tables (EMPLOYEE and PROJECT). -- -- RETURNS:  1 if successful, but now rows existed for deletion --           0 on successful completion --          -1 on un-successful complete --    SETS: Appropriate message in output parameter 'p_message' ------------------------------------------------------------------------ dd: BEGIN     -- Declare variables     DECLARE SQLCODE INT DEFAULT 0;                                     -- (1)     DECLARE SQLSTATE CHAR(5) DEFAULT '00000';     DECLARE v_ret_value INT DEFAULT 0;     -- In order to return a value to the calling program, the value     --   to be returned is set in the compound statement, and possibly     --   in a handler within the compound statement.     body:BEGIN                                                         -- (2)         -- Declare variables within compound statement         DECLARE v_num_rows INT DEFAULT 0;         -- Declare conditions                                          -- (3)         DECLARE c_EMP_child_rows_exist  CONDITION FOR SQLSTATE '99001';         DECLARE c_PROJ_child_rows_exist CONDITION FOR SQLSTATE '99002';         -- Declare handlers         DECLARE EXIT HANDLER FOR SQLEXCEPTION         BEGIN             SET p_message = 'Unknown error, SQLSTATE: "' || SQLSTATE ||                             '", SQLCODE=' || CHAR(SQLCODE);            -- (4)             SET v_ret_value = -1;         END;         -- Declare handlers for custom conditions                      -- (5)         DECLARE EXIT HANDLER FOR c_EMP_child_rows_exist         BEGIN             SET p_message = 'Cannot delete, child EMPLOYEE rows exist.';             SET v_ret_value = -1;         END;         DECLARE EXIT HANDLER FOR c_PROJ_child_rows_exist         BEGIN             SET p_message = 'Cannot delete, child PROJECT rows exist.';             SET v_ret_value = -1;         END;         -- Child table: EMPLOYEE         SELECT COUNT(1)           INTO v_num_rows           FROM employee          WHERE workdept = p_deptno;         IF v_num_rows <> 0 THEN             SIGNAL c_EMP_child_rows_exist;                             -- (6)         END IF;         --Child table: PROJECT         SELECT COUNT(1)           INTO v_num_rows           FROM project          WHERE deptno = p_deptno;         IF v_num_rows <> 0 THEN             SIGNAL c_PROJ_child_rows_exist;                            -- (7)         END IF;         -- No rows in dependant tables, delete department         DELETE FROM department          WHERE deptno = p_deptno;         GET DIAGNOSTICS v_num_rows = ROW_COUNT;                        -- (8)         -- Set the appropriate return message         IF v_num_rows = 0 THEN         BEGIN             SET v_ret_value = 1;             SET p_message = 'No rows exist for deletion of department '             || p_deptno || '.';         END;         ELSE             SET p_message = 'Department ' || p_deptno             || ' successfully deleted.';         END IF;     END body;     RETURN v_ret_value;                                                -- (9) END dd

Note

Because DB2 UDB for zSeries does not support nested compound statements, the example shown in Figure 6.23 is rewritten in Figure 6.32 for zSeries.

The following notes correspond to the location numbers shown in Figure 6.23:

SQLCODE and SQLSTATE need to be declared, because they will be accessed in the EXIT handler on Line (4).
Because the SQL procedure returns a return value (in addition to an output parameter), the main logic of the procedure lies within a nested compound statement. This way, the nested statement can set the appropriate return value in an EXIT handler. The EXIT handler will continue execution at the end of the compound statement. This will then execute the RETURN statement with the appropriate value.
Conditions are named for customized SQLSTATES. These conditions will be used to set the appropriate error message within the handler declared on Line (5).
The message for non-handled conditions is constructed using SQLCODE and SQLSTATE, so the calling program can have access to it.
Handlers for the named conditions SET the appropriate message for the output parameter.
The SIGNAL statement is used to invoke the handler if child rows in the employee table are found.
The SIGNAL statement is used to invoke the handler if child rows in the project table are found.
The GET DIAGNOSTICS statement is used to determine if any rows were deleted. If no rows were deleted, then an appropriate message is sent back to the calling application.
The RETURN statement is used to send the return value to the calling application. At this point, the appropriate return value has been set, along with the output parameter, so all that is required is to execute the RETURN statement.

On iSeries, you can have a RETURN statement from within the handler. The example in Figure 6.23 can be changed to replace all the

 SET v_ret_value = -1;

in the handlers to

 RETURN -1;

Hence, the body of the procedure does not need to be nested in a BEGIN and END block as described in the second point.