1

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;
1

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;
| improve this answer | |
  • Wow!! Thank you so much.. I have an exam coming up and you've been a godsend!! Thank you so much man.. Appreciate it!! – Akash Wijesinghe May 21 '19 at 11:48
  • In this case please accept the answer. – Wernfried Domscheit May 21 '19 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.