CREATE OR REPLACE PROCEDURE p_updateorders (decimal, date)
AS '
DECLARE
orderno ALIAS FOR $1;
orderdate ALIAS FOR $2;
--defining variables
v_subtotal decimal;
v_taxstatus varchar(1);
v_shipping varchar(12);
v_shippingrate decimal;
V_shippingcharge decimal := 0;
v_taxrate decimal := 0;
v_taxamt decimal;
v_totalamt decimal;
BEGIN
--taking the subtotal by calcualting with right price and qty of products in an order
SELECT SUM( product_price(orderdate,product_no, qty) * qty) INTO v_subtotal
FROM orderline
WHERE order_no = orderno
GROUP BY order_no;
--finding if tax applicable or not
SELECT tax_status INTO v_taxstatus
FROM orders
WHERE order_no = orderno; --finding the shipping method
SELECT shipping_method INTO v_shipping
FROM orders
WHERE order_no = orderno; --get the tax rate
IF upper(v_taxstatus) = ''Y'' THEN
SELECT tax_rate INTO v_taxrate
FROM tax
WHERE state = (SELECT state
FROM customer WHERE customer_no =
(SELECT distinct customer_no
FROM orders
WHERE order_no = orderno));
END IF;v_taxamt := v_taxrate * v_subtotal;
--get shipping cost
IF upper(v_shipping) = ''2DAY-AIR'' THEN
v_shippingrate := .08;
ELSIF upper(v_shipping) = ''1DAY-AIR'' THEN
v_shippingrate := .1;
ELSIF upper(v_shipping) = ''GROUND'' THEN
v_shippingrate := .05;
ELSE
v_shippingrate := 0;
END IF;v_shippingcharge := v_shippingrate * v_subtotal;
--calculating the total amount
v_totalamt := v_subtotal + v_taxamt + v_shippingcharge; --now update the ORDERS table with new values
UPDATE orders
SET subtotal = v_subtotal,
tax_amt = v_taxamt,
shipping_charge = v_shippingcharge,
total_amt = v_totalamt
WHERE order_no = orderno;END; ' LANGUAGE 'plpgsql';
I know I have to relpace the word PROCEDURE with FUNCTION but then it wants me to put RETURNS <datatype> but I don't want to return anything. I was thinking that I could just have it return integer and then after the last update statement put return 0. I'd rather not have it return a junk value though. What should I do?
Thanks,
Clint
_________________________________________________________________
Never get a busy signal because you are always connected with high-speed Internet access. Click here to comparison-shop providers. https://broadband.msn.com
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
