Create a procedure body with a compound statement that performs the following actions.
CREATE PROCEDURE DEPT_MEDIAN (IN deptNumber SMALLINT, OUT medianSalary DOUBLE) LANGUAGE SQL BEGIN DECLARE v_numRecords INTEGER DEFAULT 1; DECLARE v_counter INTEGER DEFAULT 0; DECLARE c1 CURSOR FOR SELECT salary FROM staff WHERE DEPT = deptNumber ORDER BY salary; DECLARE EXIT HANDLER FOR NOT FOUND SET medianSalary = 6666; /* initialize OUT parameter */ SET medianSalary = 0; SELECT COUNT(*) INTO v_numRecords FROM staff WHERE DEPT = deptNumber; OPEN c1; WHILE v_counter < (v_numRecords / 2 + 1) DO FETCH c1 INTO medianSalary; SET v_counter = v_counter + 1; END WHILE; CLOSE c1; END
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.