## Speeding up SQL queries using inline stored functions

select m.dept_code, 
       nvl(d.description,’?’),
       sum(m.retail_amount)
from   departments d,
       weekly_sales m
where  sales_date between ‘01-JAN-96’ and ‘31-JAN-96’
and    m.dept_code = d.dept_code (+)
group
    by m.dept_code, d.description;


function Lookup_Desc (Dept IN varchar2) return varchar2 AS
  Dsc    varchar2(30);
  cursor C is
  select description
  from   departments
  where  dept_code = Dept;
begin
  open  C;
  fetch C in Dsc;
  close C;
  return(nvl(Dsc,’?’));
end;


select m.dept_code, 
       Lookup_Desc(m.dept_code),
       sum(m.retail_amount)
from   weekly_sales m
where  sales_date between ‘01-JAN-96’ and ‘31-JAN-96’
group
    by m.dept_code;