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(' '
|| Rpad(employee_list.first_name, 20)
|| 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
...
Sample Output:
need an explanation for this answer? contact us directly to get an explanation for this answer