Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
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;
/* Enter one or more SQL statements separated by semicolons */
CREATE PROCEDURE GetCusName()
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
SELECT cusnam FROM customer ORDER BY cusnam;
OPEN c1;
END
CREATE FUNCTION SAMPLEDB01.EMPBYPRJ (
PRJNBR VARCHAR(6) )
RETURNS TABLE (
EMPNO CHAR(6) ,
FIRSTNME CHAR(20) ,
LASTNAME CHAR(20) ,
BIRTHDATE DATE )
LANGUAGE SQL
SPECIFIC SAMPLEDB01.EMPBYPRJ
RETURN
SELECT EMPNO , FIRSTNME , LASTNAME , BIRTHDATE
FROM SAMPLEDB01 . EMPLOYEE WHERE EMPNO IN (
SELECT EMPNO FROM SAMPLEDB01 . EMPPROJACT
WHERE PROJNO = PRJNBR ) ;