CREATE OR REPLACE
PROCEDURE test_proc(
    p_type  IN VARCHAR2,
    p_value IN VARCHAR2)
IS
TYPE c1 IS REF CURSOR;
    cur_emp c1;
    rec_emp emp%rowtype;
    l_str VARCHAR2(1000):='select * from emp where ';
  BEGIN
    IF p_type   ='JOB' THEN
      l_str    :=l_str|| 'JOB = :p_value ';
    elsif p_type='DEPTNO' THEN
      l_str    :=l_str|| 'DEPTNO = :p_value ';
    elsif p_type='MGR' THEN
      l_str    :=l_str|| 'MGR = :p_value ';
    ELSE
      NULL;
    END IF;
    dbms_output.put_line(l_str);
    OPEN cur_emp FOR l_str USING p_value;
    loop
      fetch cur_emp INTO rec_emp;
    exit
  WHEN cur_emp%notfound;
    dbms_output.put_line(rec_emp.ename);
  END loop;
  CLOSE cur_emp;
END; 


exec test_proc ('DEPTNO','10');

select * from emp 
 where (p_type = 'JOB'    and job    = p_value)
    or (p_type = 'DEPTNO' and debtno = p_value)
    or (p_type = 'MGR'    and mgr    = p_value)

CREATE OR REPLACE
PROCEDURE test_proc(
    p_type  IN VARCHAR2,
    p_value IN VARCHAR2)
IS
cur_emp sys_refcursor;
rec_emp emp%rowtype;
  BEGIN
  decide_type(p_type ,p_value ,cur_emp);      
    loop
      fetch cur_emp INTO rec_emp;
    exit
  WHEN cur_emp%notfound;
    dbms_output.put_line(rec_emp.ename);
  END loop;
  CLOSE cur_emp;
END; 


CREATE OR REPLACE PROCEDURE decide_type(p_type IN VARCHAR2,p_value in varchar2,rec_emp out  sys_refcursor)
is
BEGIN
  IF p_type   ='JOB' THEN
      OPEN rec_emp for select * from emp where JOB = p_value ;
    elsif p_type='DEPTNO' THEN
      OPEN rec_emp for select * from emp where deptno = p_value ;
    elsif p_type='MGR' THEN
      OPEN rec_emp for select * from emp where mgr = p_value ;
    ELSE
      OPEN rec_emp for select * from emp where 1=2;
    END IF;
end;

exec test_proc ('DEPTNO','10');

select * from emp 
 where (p_type = 'JOB'    and job    = p_value)
    or (p_type = 'DEPTNO' and debtno = p_value)
    or (p_type = 'MGR'    and mgr    = p_value)

select * from emp 
 where (p_type = 'JOB'    and job    = p_value)
union all
select * from emp 
 where (p_type = 'DEPTNO' and debtno = p_value)
union all
select * from emp 
 where (p_type = 'MGR'    and mgr    = p_value)