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?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics