1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
| CREATE FUNCTION RANK(N INTEGER)
RETURNS TABLE(
POSITION INTEGER,
EMPNO CHAR(6),
FIRSTNME CHAR(20),
LASTNAME CHAR(20),
SALARY DECIMAL(13,2)
)
LANGUAGE SQL
DISALLOW PARALLEL
MODIFIES SQL DATA
NOT FENCED
BEGIN
DECLARE LAST_SALARY DEC(13,2) DEFAULT 0;
DECLARE I INTEGER DEFAULT 1;
DECLARE STMT VARCHAR(255);
DECLARE TABLE_ALREADY_EXISTS CONDITION FOR '42710';
DECLARE CONTINUE HANDLER FOR TABLE_ALREADY_EXISTS
DELETE FROM SESSION.RETURN_TBL;
DECLARE GLOBAL TEMPORARY TABLE SESSION.RETURN_TBL (
POSITION INTEGER NOT NULL,
EMPNO CHAR(6) NOT NULL,
FIRSTNME CHAR(20) NOT NULL,
LASTNAME CHAR(20) NOT NULL,
SALARY DECIMAL(13,2) NOT NULL);
FOR_LOOP: FOR EACH_ROW AS C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
FROM SAMPLEDB01.EMPLOYEE ORDER BY SALARY DESC DO
IF (I > N) AND (EACH_ROW.SALARY < LAST_SALARY) THEN
LEAVE FOR_LOOP;
ELSE
SET LAST_SALARY = EACH_ROW.SALARY;
END IF;
INSERT INTO SESSION.RETURN_TBL
VALUES ( I, EACH_ROW.EMPNO, EACH_ROW.FIRSTNME,
EACH_ROW.LASTNAME, EACH_ROW.SALARY);
SET I = I + 1;
END FOR;
RETURN SELECT POSITION, EMPNO, FIRSTNME, LASTNAME, SALARY
FROM SESSION.RETURN_TBL;
END; |
Partager