dokuwiki

This is an old revision of the document!

---

PROCEDURE emp_per_year
   IS
  sy number;
  ey number;
  c  number;
BEGIN
  select min(EXTRACT(year from hire_date)), max(EXTRACT(year from hire_date)) into
     sy, ey from employees;
     
/*  select min(y), max(y) into sy, ey from
    (select extract(year from hire_date) y from employees);     */

  for i in sy..ey loop
    select count(*) into c from employees where EXTRACT(year from hire_date) = i;
    dbms_output.put_line(i|| ' : ' || c);
  end loop;
END; -- Procedure

with list_years as (
select level-1 + 1985 as ya from dual connect by level<20
)
select ya, count(hire_date) from employees right join list_years
on ya = (extract(year from hire_date))
group by ya
order by ya