SET SERVEROUTPUT ON
variable r refcursor
DECLARE 
  P_EMPNO NUMBER;
  r_out sys_refcursor;   

BEGIN 
P_EMPNO := 12345; 
P_RET_VAL ( P_EMPNO,r_out);
  :r := r_out; 
END;
print r

CREATE OR REPLACE PROCEDURE P_RET_VAL
(P_EMPNO NUMBER,
p_dataset OUT SYS_REFCURSOR
)

IS
v_query VARCHAR2(100);
tcount NUMBER :=0;
BEGIN

SELECT COUNT(*) INTO tcount from emp WHERE empno=P_EMPNO;

IF tcount=0 THEN 
 RETURN;
END IF;
v_query :='SELECT * FROM emp';
OPEN p_dataset for v_query;
END;
/

IF tcount=0 THEN 
 RETURN;
END IF;

DECLARE
    v_empid hr.employees.employee_id%TYPE;
BEGIN
    SELECT t1.employee_id INTO v_empid
    FROM   hr.employees t1
    WHERE  t1.employee_id = p_empno;
. . .
EXCEPTION
   WHEN NO_DATA_FOUND THEN
. . .

. . .
v_query :='SELECT * FROM emp';
OPEN p_dataset for v_query;