Cursor in PL/SQL.

Experiment-13

Object: Write a program in PL/SQL to display the employee number and name of top 5 highest paid.

Requirements: Windows XP, Oracle 9i.

Program Code:

SQL> create table empdb

  2  (empname char(25),

  3  empsal number(10));

Table created.

SQL> insert into empdb values('Abhishek',25000);

1 row created.

SQL>  insert into empdb values('Abhinav',5000);

1 row created.

SQL>  insert into empdb values('Brijesh',7500);

1 row created.

SQL>  insert into empdb values('Anand',15000);

1 row created.

SQL>  insert into empdb values('Rahul',2500);

1 row created.

SQL> select * from empdb;

EMPNAME                      EMPSAL

------------------------- ---------

Abhishek                      25000

Abhinav                        5000

Brijesh                        7500

Anand                         15000

Rahul                          2500

SQL> declare

  2  empname empdb.empname%type;

  3  empsal empdb.empsal%type;

  4  cursor temp1 is select empname,empsal from empdb order by empsal desc;

  5  begin open temp1;

  6  loop

  7  fetch temp1 into empname,empsal;

  8  exit when temp1%rowcount>5 or temp1%notfound;

  9  dbms_output.put_line(empname || empsal);

 10  end loop;

 11  close temp1;

 12  end;

 13  .

SQL> /

PL/SQL procedure successfully completed.

Output:

SQL> select * from empdb;

EMPNAME                      EMPSAL

------------------------- ---------

Abhishek                      25000

Anand                         15000

Brijesh                        7500

Abhinav                        5000

Rahul                          2500

Result: Thus the above program in PL/SQL to display the employee number and name of top 5 highest paid is successfully executed.