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 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;
