create or replace procedure p(p_where in varchar2)
is
  v_sql := 'select prod_name ,prod_price,prod_qty*1000
from products';
  v_rc  sys_refcursor;  -- 9i
begin
  open v_rc for v_sql || p_where;
  loop
    fetch v_rc into ...;
    exit when v_rc%notfound;
    -- do something
  end loop;
  close v_rc;
end;