Procedure -Pl/SQL

I have to write a PROCEDURE called DISCOUNT_ORDERS which gives X percent discount for all Y orders whose shipping date is more than Y days from the order date. The procedure takes X% and Ydays as input paramters...I am asked to display only orders who get a discount....It should display order id, order date, ship date, and discount amount and update total of all orders...
I tried to create a function first and then thought of calling the function frlom preocdure...doesnt look like it works....can u help here?

---------------------------------------------------------------
> SQL> Create or replace function DISCOUNT(p_diff IN number)
>
> 2 RETURN number IS
> 3 p_disamt number(2,2);
> 4 p_newamt number(5,2);
> 5 max_day :=15;
> 6 min_day :=5;
> 7 max_dis :=10;
> 8 min_dis :=5;
> 9 BEGIN
> 10 SELECT total INTO p_total from sales_order;
> 11 If(p_diff=min_day)
> 12 THEN
> 13 p_disamt :=(p_total * (min_dis/100));
> 14 p_newamt :=(p_total - p_disamt);
> 15 ELSIF(p_diff>=max_day)
> 16 THEN p_disamt :=(p_total *(max_dis/100));
> 17 p_newamt :=(p_total - p_disamt);
> 18 ELS p_disamt :=0;
> 19 END IF;
> 20 /
>
:confused:

TABLE USED:
CREATE TABLE sales_order (
order_id NUMBER(4) NOT NULL,
order_date DATE,
customer_id NUMBER(6),
ship_date DATE,
total NUMBER(8,2));

1) max_day etc do not seem to be declared with a data type.
2) "SELECT total INTO p_total from sales_order" will return multiple rows and cause the procedure to crash. You can only return one row with this method.
3) You don't have any way of returning the output.

Beyond that, your whole approach is wrong. You're passing in a value of "diff" (which presumably you get from sales_order) and then doing another select from the same table to get the total. But the two selects aren't correlated in any way. You need to be passing the key values retrieved from the first select into your function and including those in the query that gets the total.

In any case, it seems pretty silly to be querying the sales_order table twice. You should be able to do it all in one go. All you should need is something like;

Code:

---

set serveroutput on

create procedure discount_orders (p_disc_percent in number,
p_diff_days in number) is
cursor c_find_candidates (p_diff_days in number) is
select order_id, order_date, ship_date, amount
  from sales_order
  where shipping_date - order_date >= p_diff_days;
v_discount_amount number;
v_order_total        number := 0;
begin
  for rec_cand in c_find_candidates(p_diff_days) loop
      -- compute v_discount_amount (not sure exactly how you want to do this)
      v_discount_amount := rec_cand.amount * (p_disc_percent/100);
      dbms_output.put_line(rec_cand.order_id||'/'||rec_cand.order_date||'/'||rec_cand.ship_date||'/'||v_discount_amount);
      v_order_total := v_order_total + v_discount_amount;
  end loop;
  dbms_output.put_line('Total is '||v_order_total);
end;

---

thanks!! :)