DSN8ES1
Accepts a department number from the caller and returns parameters containing the total earnings (salaries and bonuses) for employees in that department, as well as the number of employees who got a bonus.
-- DSN8ES1: SOURCE MODULE FOR THE SAMPLE SQL PROCEDURE 00010000
-- 00020000
-- LICENSED MATERIALS - PROPERTY OF IBM 00030000
-- 5635-DB2 00040000
-- (C) COPYRIGHT 2000, 2006 IBM CORP. ALL RIGHTS RESERVED. 00050000
-- 00060000
-- STATUS = VERSION 9 00070000
-- 00080000
-- Function: Accepts a department number from the caller and returns 00090000
-- parameters containing the total earnings (salaries and 00100000
-- bonuses) for employees in that department, as well as the 00110000
-- number of employees who got a bonus. 00120000
-- 00130000
-- In addition, DSN8ES1 generates a result set that contains 00140000
-- the serial no, first and last name, salary, and bonus for 00150000
-- each employee in the department who got a bonus. The 00160000
-- result set also contains a sequence number so that it can 00170000
-- be read in the order it was generated. 00180000
-- 00190000
-- Notes: 00200000
-- Dependencies: 00210000
-- - Requires DB2 precompiler support for SQL procedures (DSNHPSM) 00220000
-- - Requires a global temporary table (created in sample job 00230000
-- DSNTEJ63) for returning the result. 00240000
-- 00250000
-- Restrictions: 00260000
-- 00270000
-- Module Type: SQL Procedure 00280000
-- Processor: DB2 for OS/390 precompiler and IBM C/C++ for OS/390 00290000
-- or a subsequent release 00300000
-- Attributes: Re-entrant and re-usable 00310000
-- 00320000
-- Entry Point: DSN8ES1 00330000
-- Purpose: See Function, above 00340000
-- 00350000
-- Parameters: 00360000
-- - Input: DEPTNO CHAR(3) 00370000
-- - Output: DEPTSAL DECIMAL(15,2) 00380000
-- BONUSCNT INTEGER 00390000
-- 00400000
-- Normal Exit: 00410000
-- Error Exit: 00420000
-- 00430000
-- 00440000
-- External References: 00450000
-- - EMP : DB2 Sample Employee Table 00460000
-- - DSN8.DSN8ES1_RS_TBL: Global Temporary Table for result set 00470000
-- 00480000
-- Pseudocode: 00490000
-- - Clear any residual from result set table 00500000
-- - Open cursor on EMP table for employees in department DEPTNO 00510000
-- - While more rows: 00520000
-- - Add current employee's salary and bonus to total department 00530000
-- earnings 00540000
-- - If current employee's bonus is greater than zero 00550000
-- - increment the department bonus counter 00560000
-- - add the employee's serial, first and last name, salary and 00570000
-- bonus to the result set table, using the bonus counter as 00580000
-- a result set sequence number 00590000
-- - If no errors, open the cursor to the result set 00600000
-- 00610000
-- 00620000
CREATE PROCEDURE DSN8.DSN8ES1 00630000
( IN DEPTNO CHAR(3), 00640000
OUT DEPTSAL DECIMAL(15,2), 00650000
OUT BONUSCNT INT ) 00660000
PARAMETER CCSID EBCDIC 00670000
FENCED 00680000
RESULT SET 1 00690000
LANGUAGE SQL 00700000
NOT DETERMINISTIC 00710000
MODIFIES SQL DATA 00720000
COLLID DSN8ES!! 00740000
WLM ENVIRONMENT WLMENV 00750000
ASUTIME NO LIMIT 00760000
COMMIT ON RETURN NO 00800000
00810000
P1: BEGIN NOT ATOMIC 00820000
DECLARE EMPLOYEE_NUMBER CHAR(6) CCSID EBCDIC; 00830000
DECLARE EMPLOYEE_FIRSTNME CHAR(12) CCSID EBCDIC; 00840000
DECLARE EMPLOYEE_LASTNAME CHAR(15) CCSID EBCDIC; 00850000
DECLARE EMPLOYEE_SALARY DECIMAL(9,2) DEFAULT 0; 00860000
DECLARE EMPLOYEE_BONUS DECIMAL(9,2) DEFAULT 0; 00870000
DECLARE TOTAL_SALARY DECIMAL(15,2) DEFAULT 0; 00880000
DECLARE BONUS_COUNTER INT DEFAULT 0; 00890000
DECLARE END_TABLE INT DEFAULT 0; 00900000
00910000
-- Cursor for result set of employees who got a bonus 00920000
DECLARE DSN8ES1_RS_CSR CURSOR WITH RETURN WITH HOLD FOR 00930000
SELECT RS_SEQUENCE, 00940000
RS_EMPNO, 00950000
RS_FIRSTNME, 00960000
RS_LASTNAME, 00970000
RS_SALARY, 00980000
RS_BONUS 00990000
FROM DSN8.DSN8ES1_RS_TBL 01000000
ORDER BY RS_SEQUENCE; 01010000
01020000
-- Cursor to fetch department employees 01030000
DECLARE C1 CURSOR FOR 01040000
SELECT EMPNO, 01050000
FIRSTNME, 01060000
LASTNAME, 01070000
SALARY, 01080000
BONUS 01090000
FROM EMP 01100000
WHERE WORKDEPT = DEPTNO; 01110000
01120000
DECLARE CONTINUE HANDLER FOR NOT FOUND 01130000
SET END_TABLE = 1; 01140000
01150000
DECLARE EXIT HANDLER FOR SQLEXCEPTION 01160000
SET DEPTSAL = NULL; 01170000
01180000
-- Clean residual from the result set table 01190000
DELETE FROM DSN8.DSN8ES1_RS_TBL; 01200000
01210000
OPEN C1; 01220000
01230000
FETCH C1 01240000
INTO EMPLOYEE_NUMBER, 01250000
EMPLOYEE_FIRSTNME, 01260000
EMPLOYEE_LASTNAME, 01270000
EMPLOYEE_SALARY, 01280000
EMPLOYEE_BONUS; 01290000
01300000
-- Process each employee in the department 01310000
WHILE END_TABLE = 0 DO 01320000
-- Update department total salary 01330000
SET TOTAL_SALARY = TOTAL_SALARY 01340000
+ EMPLOYEE_SALARY 01350000
+ EMPLOYEE_BONUS; 01360000
01370000
-- If the current employee received a bonus 01380000
IF EMPLOYEE_BONUS > 0.00 THEN 01390000
-- Update department bonus count 01400000
SET BONUS_COUNTER = BONUS_COUNTER + 1; 01410000
01420000
-- Add the employee's data to the result set 01430000
INSERT INTO DSN8.DSN8ES1_RS_TBL 01440000
( RS_SEQUENCE, 01450000
RS_EMPNO, 01460000
RS_FIRSTNME, 01470000
RS_LASTNAME, 01480000
RS_SALARY, 01490000
RS_BONUS ) 01500000
VALUES( P1.BONUS_COUNTER, 01510000
P1.EMPLOYEE_NUMBER, 01520000
P1.EMPLOYEE_FIRSTNME, 01530000
P1.EMPLOYEE_LASTNAME, 01540000
P1.EMPLOYEE_SALARY, 01550000
P1.EMPLOYEE_BONUS ); 01560000
END IF; 01570000
01580000
FETCH C1 01590000
INTO EMPLOYEE_NUMBER, 01600000
EMPLOYEE_FIRSTNME, 01610000
EMPLOYEE_LASTNAME, 01620000
EMPLOYEE_SALARY, 01630000
EMPLOYEE_BONUS; 01640000
01650000
END WHILE; 01660000
01670000
CLOSE C1; 01680000
-- Set return parameters 01690000
SET DEPTSAL = TOTAL_SALARY; 01700000
SET BONUSCNT = BONUS_COUNTER; 01710000
01720000
-- Open the cursor to the result set 01730000
OPEN DSN8ES1_RS_CSR; 01740000
END P1 01750000