Create a function that fetches the data

create or replace function find_emps ( 
   p_deptno in scott.emp.deptno%type 
) return sys_refcursor  
is  
   l_rc sys_refcursor;  
begin  
   open l_rc for  
      select empno, ename, job, hiredate, sal, comm, deptno  
        from scott.emp  
       where deptno = p_deptno;  
   return l_rc;  
end;  

Try to SELECT from it

select find_emps(10) from dual

Use PL/SQL to retrieve the data for deptno 10

declare 
  l_rc        sys_refcursor;  
  l_empno     scott.emp.empno%type; 
  l_ename     scott.emp.ename%type; 
  l_job       scott.emp.job%type; 
  l_hiredate  scott.emp.hiredate%type;  
  l_sal       scott.emp.sal%type; 
  l_comm      scott.emp.comm%type; 
  l_deptno    scott.emp.deptno%type; 
begin 
 
  l_rc := find_emps(10);  
              
  loop 
    fetch l_rc  
     into l_empno, l_ename, l_job, l_hiredate, l_sal, l_comm, l_deptno;  
    exit when l_rc%NOTFOUND;  
    dbms_output.put_line(l_empno || ' | ' || l_ename || ' | ' || l_job || ' | ' || l_hiredate || ' | ' || l_sal || ' | ' || l_comm || ' | ' || l_deptno);  
  end loop;  
 
  close l_rc;  
 
END;