db2自带的whiles.db2例子
CREATE PROCEDURE dept_median
(IN deptNumber SMALLINT, OUT medianSalary DOUBLE)
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INTEGER;
DECLARE SQLSTATE CHAR(5);
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;
DECLARE v_mod INT DEFAULT 0;
DECLARE v_salary1 DOUBLE DEFAULT 0;
DECLARE v_salary2 DOUBLE DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT CAST(salary AS DOUBLE) 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;
SET v_mod = MOD(v_numRecords, 2);
CASE v_mod
WHEN 0 THEN
WHILE v_counter < (v_numRecords / 2 + 1) DO
SET v_salary1 = v_salary2;
FETCH c1 INTO v_salary2;
SET v_counter = v_counter + 1;
END WHILE;
SET medianSalary = (v_salary1 + v_salary2)/2;
WHEN 1 THEN
WHILE v_counter < (v_numRecords / 2 + 1) DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
END CASE;
END @
添加新评论0 条评论