w3resource

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:

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

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.

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/plsql-exercises/cursor/plsql-cursor-exercise-9.php