PL/SQL User's Guide and Reference

Sample 2. Cursors

The next 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

14 records selected.

PL/SQL Block

-- available online in file SAMPLE2

DECLARE
   CURSOR c1 is
      SELECT ename, empno, sal FROM emp
         ORDER BY sal DESC;   -- start with highest paid employee
   my_ename CHAR(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 temp VALUES (my_sal, my_empno, my_ename);
      COMMIT;
   END LOOP;
   CLOSE c1;
END;

Output Table

SQL> SELECT * FROM temp ORDER BY col1 DESC;

 
     COL1     COL2    MESSAGE
--------- --------    -------
     5000     7839    KING
     3000     7902    FORD
     3000     7788    SCOTT
     2975     7566    JONES
     2850     7698    BLAKE