dokuwiki

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_year as (
select level-1 + 
(select extract(year from min(hire_date)) from emp)
as yl from dual connect by level <= 
(select extract(year from max(hire_date)) -
        extract(year from min(hire_date)) + 1
from emp) 
)
select yl, count(employee_id) from emp right join
  list_year on yl = extract(year from hire_date)
  group by yl
  order by yl