PL/SQL Cursor Exercises: Display the employee ID, first name, job title and the start date of present job

PL/SQL Cursor: Exercise-35 with Solution

Write a PL/SQL block to display the employee ID, first name, job title and the start date of present job.

Displays first name, job title, start date

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

job_id				varchar(25)
job_title			varchar(50)
min_salary			integer
max_salary			integer

employee_id			integer
start_date			date
end_date			date
job_id				varchar(25)
department_id			integer

PL/SQL Code:

DECLARE
    CURSOR employees_cur IS
      SELECT employee_id,
             first_name,
             job_title,
             hire_date
      FROM   employees
             NATURAL join jobs;
    emp_first_date DATE;
BEGIN
    dbms_output.Put_line(Rpad('Employee ID', 15)
                         ||Rpad('First Name', 25)
                         ||Rpad('Job Title', 35)
                         ||'First Date');

dbms_output.Put_line('-----------------------------------------------------------------------------------------');

FOR emp_sal_rec IN employees_cur LOOP
    -- find out most recent end_date in job_history
    SELECT Max(end_date) + 1
    INTO   emp_first_date
    FROM   job_history
    WHERE  employee_id = emp_sal_rec.employee_id;

    IF emp_first_date IS NULL THEN
      emp_first_date := emp_sal_rec.hire_date;
    END IF;

    dbms_output.Put_line(Rpad(emp_sal_rec.employee_id, 15)
                         ||Rpad(emp_sal_rec.first_name, 25)
                         || Rpad(emp_sal_rec.job_title, 35)
                         || To_char(emp_first_date, 'dd-mon-yyyy'));
END LOOP;
END; 
/

Sample Output:

SQL> /
Employee ID    First Name               Job Title                          First Date
-----------------------------------------------------------------------------------------
206            William                  Public Accountant                  07-jun-2002
205            Shelley                  Accounting Manager                 07-jun-2002
200            Jennifer                 Administration Assistant           01-jan-2007
100            Steven                   President                          17-jun-2003
102            Lex                      Administration Vice President      25-jul-2006
101            Neena                    Administration Vice President      16-mar-2005
110            John                     Accountant                         28-sep-2005
109            Daniel                   Accountant                         16-aug-2002
113            Luis                     Accountant                         07-dec-2007
111            Ismael                   Accountant                         30-sep-2005
112            Jose Manuel              Accountant                         07-mar-2006
108            Nancy                    Finance Manager                    17-aug-2002
203            Susan                    Human Resources Representative     07-jun-2002
...

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to show the uses of a virtual column in an explicit cursor query.
Next: Write a PL/SQL block to display the name of department the their costliest employee.