I want to remove redundant call for count in the below mentioned stored procedure and rather want to use a temp table and use the temp table to return in cursor

procedure status(p_order_id in varchar2, p_stat out sys_refcursor) is
  l_count pls_integer;
begin
  select count(*) into l_count
  from order o
  where o.order_id=p_order_id;
  if l_count > 0 then
    -- exists in main table so only query that
    open p_stat for select o.status_code,o.order_id
      from order o
      where o.order_id=p_order_id;
  else -- does not exist in main table so only query archive
    open p_stat for select a.status_code,a.order_id
      from order_archive a where a.order_id=p_order_id;
  end if;
end status;

0 Answers