DSN8ES2
Accepts a bonus base amount (BONUSBAS) to be awarded to employees who are managers.
-- DSN8.DSN8ES2: SOURCE MODULE FOR 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 bonus base amount (BONUSBAS) to be awarded to 00090000
-- employees who are managers. Determines a bonus premium 00100000
-- (BONUSPRM) for each manager, according to the number of 00110000
-- employees he or she manages. Updates the BONUS column of 00120000
-- the sample EMP table for each manager with the sum of the 00130000
-- bonus base and his or her bonus premium. Returns the 00140000
-- total (BONUSTOT) of all bonuses awarded to managers. 00150000
-- 00160000
-- SQLERRCD is null unless an SQL exception occurs, in which 00170000
-- case SQLERRCD is set to the current SQLCODE 00180000
-- 00190000
-- Notes: 00200000
-- Dependencies: 00210000
-- - Requires DB2 precompiler support for SQL procedures (DSNHPSM) 00220000
-- 00230000
-- Restrictions: 00240000
-- 00250000
-- Module Type: SQL Procedure 00260000
-- Processor: DB2 for OS/390 precompiler and IBM C/C++ for OS/390 00270000
-- or a subsequent release 00280000
-- Attributes: Re-entrant and re-usable 00290000
-- 00300000
-- Entry Point: DSN8ES2 00310000
-- Purpose: See Function, above 00320000
-- 00330000
-- Parameters: 00340000
-- - Input: BONUSBAS DECIMAL(15,2) 00350000
-- - Output: BONUSTOT DECIMAL(15,2) 00360000
-- SQLERRCD INTEGER 00370000
-- 00380000
-- Normal Exit: 00390000
-- Error Exit: 00400000
-- 00410000
-- 00420000
-- External References: 00430000
-- - EMP : DB2 Sample Employee Table 00440000
-- 00450000
-- Pseudocode: 00460000
-- - Open a cursor on sample DEPT and EMP tables, that identifies 00470000
-- department managers and the number of persons in each department 00480000
-- 00490000
-- - For each manager found: 00500000
-- - Determine the bonus premium according to the number of 00510000
-- employees managed: $1000 for more than 10, $500 for 6 to 10, 00520000
-- $100 for 1 to 5 00530000
-- - Update the manager's bonus in the sample EMP table with the 00540000
-- sum of the bonus base and the bonus premium 00550000
-- - Add the manager's bonus to the total bonuses bucket. 00560000
-- - Return total amount of bonuses awarded 00570000
-- 00580000
CREATE PROCEDURE DSN8.DSN8ES2 00590000
( IN BONUSBAS DECIMAL(15,2), 00600000
OUT BONUSTOT DECIMAL(15,2), 00610000
OUT SQLERRCD INTEGER ) 00620000
PARAMETER CCSID EBCDIC 00630000
FENCED 00640000
RESULT SETS 0 00650000
LANGUAGE SQL 00660000
NOT DETERMINISTIC 00670000
MODIFIES SQL DATA 00680000
COLLID DSN8ES!! 00700000
WLM ENVIRONMENT WLMENV 00710000
ASUTIME NO LIMIT 00720000
COMMIT ON RETURN NO 00760000
00770000
P1: BEGIN NOT ATOMIC 00780000
DECLARE MANAGER_ID CHAR(6) CCSID EBCDIC; 00790000
DECLARE NUM_EMPLOYEES INT DEFAULT 0; 00800000
DECLARE BONUSPRM DECIMAL(15,2) DEFAULT 0; 00810000
DECLARE BONUSBKT DECIMAL(15,2) DEFAULT 0; 00820000
DECLARE END_TABLE INT DEFAULT 0; 00830000
DECLARE SQLCODE INT; 00831000
00840000
-- Cursor gets id and no. of direct reports for each manager 00850000
DECLARE C1 CURSOR FOR 00860000
SELECT DEPT.MGRNO, 00870000
COUNT(DISTINCT EMP.EMPNO) 00880000
FROM DEPT DEPT, 00890000
EMP EMP 00900000
WHERE EMP.WORKDEPT = DEPT.DEPTNO 00910000
GROUP BY EMP.WORKDEPT, DEPT.MGRNO; 00920000
00930000
DECLARE CONTINUE HANDLER FOR NOT FOUND 00940000
SET END_TABLE = 1; 00950000
00960000
DECLARE EXIT HANDLER FOR SQLEXCEPTION 00970000
SET SQLERRCD = SQLCODE; 00980000
00990000
SET BONUSTOT = NULL; 01000000
SET SQLERRCD = NULL; 01010000
01020000
OPEN C1; 01030000
01040000
FETCH C1 01050000
INTO MANAGER_ID, 01060000
NUM_EMPLOYEES; 01070000
01080000
WHILE END_TABLE = 0 DO 01090000
CASE 01100000
WHEN( NUM_EMPLOYEES > 10 ) THEN 01110000
SET BONUSPRM = 1000.00; 01120000
WHEN( NUM_EMPLOYEES > 5 ) THEN 01130000
SET BONUSPRM = 500.00; 01140000
WHEN( NUM_EMPLOYEES > 0 ) THEN 01150000
SET BONUSPRM = 100.00; 01160000
ELSE 01170000
SET BONUSPRM = 0.00; 01180000
END CASE; 01190000
01200000
UPDATE EMP 01210000
SET BONUS = BONUSBAS + BONUSPRM 01220000
WHERE EMPNO = MANAGER_ID; 01230000
01240000
SET BONUSBKT = BONUSBKT + BONUSBAS + BONUSPRM; 01250000
01260000
FETCH C1 01270000
INTO MANAGER_ID, 01280000
NUM_EMPLOYEES; 01290000
01300000
END WHILE; 01310000
01320000
CLOSE C1; 01330000
01340000
SET BONUSTOT = BONUSBKT; 01350000
01360000
END P1 01370000