PL/SQL Cursor Exercises: Display a cursor based detail information of employees from employees table

PL/SQL Cursor: Exercise-9 with Solution

Write a program in PL/SQL to display a cursor based detail information of employees from employees table.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE 
    CURSOR z_emp_info IS 
      SELECT employee_id, 
             first_name, 
             last_name, 
             salary 
      FROM   employees; 
    r_emp_info z_emp_info%ROWTYPE; 
BEGIN 
    OPEN z_emp_info; 

    LOOP 
        FETCH z_emp_info INTO r_emp_info; 

        EXIT WHEN z_emp_info%NOTFOUND; 

        dbms_output.Put_line('Employees Information:: ' 
                             ||'  ID: ' 
                             ||r_emp_info.employee_id 
                             ||'  Name: ' 
                             ||r_emp_info.first_name 
                             ||' ' 
                             ||r_emp_info.last_name); 
    END LOOP; 

    dbms_output.Put_line('Total number of rows : ' 
                         ||z_emp_info%rowcount); 

    CLOSE z_emp_info; 
END;
/

Sample Output:

SQL> /
Employees Information::   ID: 100  Name: Steven King
Employees Information::   ID: 101  Name: Neena Kochhar
Employees Information::   ID: 102  Name: Lex De Haan
Employees Information::   ID: 103  Name: Alexander Hunold
Employees Information::   ID: 104  Name: Bruce Ernst
Employees Information::   ID: 105  Name: David Austin
Employees Information::   ID: 106  Name: Valli Pataballa
Employees Information::   ID: 107  Name: Diana Lorentz
Employees Information::   ID: 108  Name: Nancy Greenberg
Employees Information::   ID: 109  Name: Daniel Faviet
Employees Information::   ID: 110  Name: John Chen
Employees Information::   ID: 111  Name: Ismael Sciarra
Employees Information::   ID: 112  Name: Jose Manuel Urman
...

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to display a table based detail information for the employee of ID 149 from the employees table.
Next: Write a program in PL/SQL to retriev the records from the employees table and display them using cursors.