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