﻿ PL/SQL Cursor: Displays employee name and number of jobs he or she done at past. Displays employee name and job count by job - w3resource

# PL/SQL Cursor Exercises: Displays employee name and number of jobs he or she done at past. Displays employee name and job count by job

## PL/SQL Cursor: Exercise-30 with Solution

Write a block in PL/SQL to displays employee name and number of jobs he or she done at past.

Displays employee name and job count by job.

Sample Solution:

PL/SQL Code:

DECLARE
CURSOR emp_job_cur IS
SELECT job_id,
job_title
FROM   jobs
WHERE  job_id IN (SELECT job_id
FROM   employees)
ORDER  BY 2;
CURSOR employees_cur (
emp_jobid VARCHAR) IS
SELECT first_name,
Count(start_date) no_of_jobs
FROM   employees e
left outer join job_history jh USING (employee_id)
WHERE  e.job_id = emp_jobid
GROUP  BY first_name
ORDER  BY 1;
BEGIN
FOR emp_job_rec  IN emp_job_cur LOOP
dbms_output.Put_line('---------------------------------------');
dbms_output.Put_line('Job Title: '||emp_job_rec.job_title);
dbms_output.Put_line('---------------------------------------');

FOR employee_list IN employees_cur( emp_job_rec.job_id) LOOP
dbms_output.Put_line('    '
|| employee_list.no_of_jobs);
END LOOP;
END LOOP;
END;
/

Sample Output:

SQL> /
---------------------------------------
Job Title: Accountant
---------------------------------------
Daniel              0
Ismael              0
John                0
Jose Manuel         0
Luis                0
---------------------------------------
Job Title: Accounting Manager
---------------------------------------
Shelley             0
---------------------------------------
Job Title: Administration Assistant
---------------------------------------
Jennifer            2
---------------------------------------
Job Title: Administration Vice President
---------------------------------------
Lex                 1
Neena               2
...

Flowchart:

Improve this sample solution and post your code through Disqus

What is the difficulty level of this exercise?

﻿

Inviting useful, relevant, well-written and unique guest posts