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.
What is the difficulty level of this exercise?
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics