Differences

This shows you the differences between two versions of the page.

@@ Line 1: / Line 1: @@
+-- die laengste Zeitspanne die zwischen zwei hire_dates liegt
+<code>
+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;
+</code>
+SQL
+<code>
+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
+</code>

dokuwiki