## Speeding Up SQL queries using inline stored function caches

select prod_code, locn_code, sales_date, sum(retail_amount)
from   weekly_sales
where  sales_date between ‘01-JAN-96’ and ‘31-JAN-96’
and    SALES.Chk_Range(locn_code) = ‘TRUE’;



Package Sales 
AS
  function Chk_Range(locn_code IN varchar2) return varchar2;
  --
  pragma restrict_referneces (Chk_Range, WNDS, WNPS);
End;


Package Body Sales
AS
Max_Locn    constant integer := 400;
--
TYPE TAB_9  is TABLE of number(5)   index by binary_integer;
TYPE TAB_x  is TABLE of varchar2(5) index by binary_integer;
--
TAB_Loc     TAB_9;
TAB_Fnd     TAB_x;
--
--
function Chk_Range (locn_code IN number  ) return varchar2 AS
Cursor C1 is
select ‘TRUE’
from   --table1--,
       --table2--.
       --table3--
where  ......
and    ...... ;
--
BEGIN
  if (TAB_loc(locn_code) != locn_code) then
      TAB_loc(locn_code) := locn_code;
      --
      Open  C1;
      Fetch C1 into TAB_Fnd(locn_code);
      Close C1;
  end if;
  --
  return (TAB_Fnd(locn_code));
END Chk_Range;
--
--
for i in 1..Max_Loc Loop     -- Package Initialization Section
    TAB_loc(i) := 0;
    TAB_Fnd(i) := ‘FALSE’;
end loop;
END;