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

  • pl/sql

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.

الأجوبة

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
...
هل كان المحتوى مفيد؟

معلومات ذات صلة

تبحث عن مدرس اونلاين؟

محتاج مساعدة باختيار المدرس الافضل؟ تواصل مع فريقنا الان لمساعدتك بتأمين افضل مدرس
ماهو التخصص الذي تبحث عنه؟
اكتب هنا...