Search code examples
sqloracle-databaseplsqldbms-output

PL/SQL Program to display the salaries of top 'a' employees

SET SERVEROUTPUT ON;

DECLARE
    CURSOR cemp
    IS
          SELECT esal
            FROM emp
        ORDER BY esal DESC;

    a     NUMBER (10) := &a;
    sal   emp.esal%TYPE;
BEGIN
    OPEN cemp;

    LOOP
        FETCH cemp INTO sal;

        DBMS_OUTPUT.put_line (sal);
        EXIT WHEN cemp%ROWCOUNT = a OR cemp%NOTFOUND;
    END LOOP;

    CLOSE cemp;
END;

I have written the above pl/sql program to display the salaries of top 'a' employees.I have executed the code in sql developer.But Iam not getting any output.Is there something that is wrong in the code?

Solution

  • I figured out the solution myself.

    Apparently there was some problem with the usage of the cursor.

    The query can be simplified as :

    SELECT * 
FROM 
 (
     SELECT EMPLOYEE, LAST_NAME, SALARY,
     RANK() OVER (ORDER BY SALARY DESC) emprank
     FROM emp
 )
WHERE emprank <= 3;