declare
cursor employees_cur is
select employee_id, first_name, last_name, job_title , hire_date
from employees natural join jobs;
emp_st_date date;
begin
dbms_output.put_line( rpad('Employee Name',30) || rpad('Job Title',35)|| 'Starting Date');
dbms_output.put_line('-----------------------------------------------------------------------------------------');
for employee_rec in employees_cur
loop
-- find out most recent end_date in job_history
select max(end_date) + 1 into emp_st_date
from job_history
where employee_id = employee_rec.employee_id;
if emp_st_date is null then
emp_st_date := employee_rec.hire_date;
end if;
dbms_output.put_line(rpad((employee_rec.first_name||' '||employee_rec.first_name),30) || rpad(employee_rec.job_title,35)
|| to_char(emp_st_date,'dd-mon-yyyy'));
end loop;
end;
Sample Output:
SQL> /
Employee Name Job Title Starting Date
-------------------------------------------------------------------------------
William William Public Accountant 07-jun-2002
Shelley Shelley Accounting Manager 07-jun-2002
Jennifer Jennifer Administration Assistant 01-jan-2007
Steven Steven President 17-jun-2003
Lex Lex Administration Vice President 25-jul-2006
Neena Neena Administration Vice President 16-mar-2005
John John Accountant 28-sep-2005
Daniel Daniel Accountant 16-aug-2002
Luis Luis Accountant 07-dec-2007
Ismael Ismael Accountant 30-sep-2005
Jose Manuel Jose Manuel Accountant 07-mar-2006
Nancy Nancy Finance Manager 17-aug-2002
Susan 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