Can someone help me with this PL/SQL block

oracleplsql

Write a PL/SQL block to display the details of five employees who are getting the highest salary (table name is Employee)
Is this how you do it??

DECLARE
Cursor C is select * from Employee ORDER BY salary DESC;
Edet C%ROWTYPE; 

BEGIN
 Open C;
 Loop
 fetch C into Edet;
 EXIT WHEN C%ROWCOUNT == 5;
 DBMS_OUTPUT.PUT_LINE(Edet); 
 end loop;
 Close c;
 END;

Best Answer

Your code has a few syntax issues, it should be like this:

DECLARE
  Cursor C is 
  select * from Employee ORDER BY salary DESC;
  Edet C%ROWTYPE; 

BEGIN
 Open C;
 Loop
    fetch C into Edet;
    EXIT WHEN C%ROWCOUNT = 5;
    DBMS_OUTPUT.PUT_LINE(c.salary); 
 end loop;
 Close c;
END;

However, there are some design issues.

You will get an error if table Employee has less than 5 records because C%ROWCOUNT = 5 does never become true.

If you have Oracle 12 running then you can simple do

select * 
from Employee 
ORDER BY salary DESC
fetch first 5 rows only;

For earlier releases you can do

select *
from (select e.*, ROWNUM as RN from Employee e ORDER BY salary DESC)
where rn <= 5;
Related Question