create or replace procedure my_proc (p_where in varchar2)
is
  type rc is ref cursor;
  v_cursor  rc;
begin
  open v_cursor for 'select name, salary from employee ' || p_where;
  loop
    fetch v_cursor into x, y;
    exit when v_cursor%notfound;
    -- do something
  end loop;
  close v_cursor;
end;