SQL stored function

I am to write a stored function to retrieve the number of items of a given product that have been delivered in the last year, and write a SQL Select statement that uses the function to display product no and name + no of items delivered in the last year for all products.

Please help 开发者_StackOverflow中文版me out

Thanks

Presumably your question is prompted by a school or college assignment. At least I hope it is, because using a function for this purpose is bad practice. It performs badly, because of the constant switching between SQL and PL/SQL engines, and because it queries the deliveries on a row-by-row basis instead of using a set operation.

A simple pure SQL statement performs better. I have chosen to use an outer join because you probably want to include products which haven't had any activity in the last year.

select p.product_id
       , p.product_name
       , count(d.delivery_id) as sales_this_year 
from products p
    left outer join deliveries d
         on (p.product_id = d.product_id)
where d.dispatch_date >= add_months(sysdate, -12)
group by p.product_id, p.product_name
/

I have interpreted "in the last year" to mean the last twelve months, hence the use of the ADD_MONTHS() function. A different interpretation would have a WHERE clause like this:

where d.dispatch_date between to_date('01-JAN-2010') and to_date('31-DEC-2010')

that is, last calendar year; or perhaps:

where d.dispatch_date >= trunc(sysdate, 'YYYY')

that is, this calendar year.

I suppose you have a delivery table and a product table.

create or replace function prod_delivered_cnt (p_prod_id in number) as
  v_res number;
begin
  select count(*) into v_res 
  from delivery_table d 
  where d.prod_id = p_prod_id 
  and d.date < to_date('2011', 'yyyy');
  return v_res;
end prod_delivered_cnt;

select p.prod_num, p.prod_name, prod_delivered_cnt(p.id) as cnt 
from product_table p;