Create an SQL procedure that returns the median staff salary. Return a result set containing the name, position, and salary of all employees who earn more than the median salary.
CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DECIMAL(7,2)) LANGUAGE SQL DYNAMIC RESULT SETS 1 BEGIN DECLARE v_numRecords INTEGER DEFAULT 1; DECLARE v_counter INTEGER DEFAULT 0; DECLARE c1 CURSOR FOR SELECT salary FROM staff ORDER BY salary; DECLARE c2 CURSOR WITH RETURN FOR SELECT name, job, salary FROM staff WHERE salary > medianSalary ORDER BY salary; DECLARE EXIT HANDLER FOR NOT FOUND SET medianSalary = 6666; SET medianSalary = 0; SELECT COUNT(*) INTO v_numRecords FROM STAFF; OPEN c1; WHILE v_counter < (v_numRecords / 2 + 1) DO FETCH c1 INTO medianSalary; SET v_counter = v_counter + 1; END WHILE; CLOSE c1; OPEN c2; END
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.