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
...
Sample Output:
need an explanation for this answer? contact us directly to get an explanation for this answer