create or replace procedure get_count
     (p_deptno in number,
      p_job    in char,
      c1       out sys_refcursor
      )
    is
    begin
      open c1 for
        select e.deptno, count(*)
         from emp e
         where e.deptno = p_deptno
           and e.job = NVL(p_job, e.job)       --> JOB is optional
         group by e.deptno;
   exception
   when NO_DATA_FOUND then
   null;
   when OTHERS then
   null; 
   end;
   /