dokuwiki

– die laengste Zeitspanne die zwischen zwei hire_dates liegt

procedure longest_time_wo_hire
(
  p_time_wo_hire out number
) 
as
  v_longest_span number := 0;
  v_span number;
  v_hire_date_prev employees.hire_date%type := null;
begin
  for r_employee in c_employees loop
     if v_hire_date_prev is not null then
        v_span := r_employee.hire_date - v_hire_date_prev;
     end if;
     if  v_span > v_longest_span then
         v_longest_span := v_span;
         p_time_wo_hire := v_longest_span;
     end if;
     dbms_output.put_line(v_longest_span);
     v_hire_date_prev := r_employee.hire_date;     
  end loop;
end;

SQL

with ord_hd as (
select last_name,
       hire_date
  from employees
  order by hire_date asc
  ),
ord_hd_rn as (
select rownum rn, last_name, hire_date from ord_hd
)
select rn, 
       hire_date, 
       last_name,
       prior hire_date,
       prior last_name,
       hire_date - prior hire_date span
       from ord_hd_rn connect by prior rn = rn - 1
       start with rn = 1
order by span desc