Classroom-Example-Cursors.doc

Let?s solve this Cursors program. Use emptbl you created last class. Note: You have to create the testtbl Table; The following example uses a cursor to select the five highest paid employees from the emp table. Input Table SQL> SELECT ename, empno, sal FROM emp ORDER BY sal DESC; ENAME EMPNO SAL ---------- ----------- -------- KING 7839 5000 SCOTT 7788 3000 FORD 7902 3000 JONES 7566 2975 BLAKE 7698 2850 CLARK 7782 2450 ALLEN 7499 1600 TURNER 7844 1500 MILLER 7934 1300 WARD 7521 1250 MARTIN 7654 1250 ADAMS 7876 1100 JAMES 7900 950 SMITH 7369 800 PL/SQL Block DECLARE CURSOR c1 is SELECT ename, empno, sal FROM emptbl ORDER BY sal DESC; -- start with highest paid employee my_ename VARCHAR2(10); my_empno NUMBER(4); my_sal NUMBER(7,2); BEGIN OPEN c1; FOR i IN 1..5 LOOP FETCH c1 INTO my_ename, my_empno, my_sal; EXIT WHEN c1%NOTFOUND; /* in case the number requested */ /* is more than the total */ /* number of employees */ INSERT INTO testtbl VALUES (my_sal, my_empno, my_ename); COMMIT; END LOOP; CLOSE c1; END; Output Table SQL> SELECT * FROM testtbl ORDER BY col1 DESC; COL1 COL2 MESSAGE --------- -------- ------- 5000 7839 KING 3000 7902 FORD 3000 7788 SCOTT 2975 7566 JONES 2850 7698 BLAKE