Write a block in PL/SQL to displays employee name and number of jobs he or she done at past
- pl/sql
- 2021-09-28
- mhanasmh00489829403
الأجوبة
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 ...
أسئلة مشابهة
القوائم الدراسية التي ينتمي لها السؤال
معلومات ذات صلة