w3resource
PL/SQL Cursor Exercises

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('    '
                                 || 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
...

Flowchart:

Flowchart: 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

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to display the department name, name of the manager, number of employees in each department, and number of employees listed in job_history.
Next: Write a block in PL/SQL to display the name of department and their head.

What is the difficulty level of this exercise?