Problems with simple procedure

<deleted>Hello,

the price field in the table on_sale is missing ..

11 UPDATE on_sale
12 SET price = price * 0.9
13 WHERE CURRENT OF item_cur;

you update the table on_sale and multilpe the field in on_sale with 9 ?

Hope that helps ?

Best regards
Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com

Originally posted by appoooh
I'm having problems getting a simple procedure to compile, could someone possibly show me where I've gone wrong?

TIA

SQL> CREATE OR REPLACE PROCEDURE update_price
2 (p_cutoff IN CHAR)
3 IS
4 CURSOR item_cur IS
5 SELECT items.price, on_sale.start_date
6 FROM items, on_sale
7 WHERE on_sale.start_date = TO_DATE(p_cutoff)
8 FOR UPDATE;
9 BEGIN
10 FOR item_rec IN item_cur LOOP
11 UPDATE on_sale
12 SET price = price * 0.9
13 WHERE CURRENT OF item_cur;
14 END LOOP;
15 COMMIT;
16 END;
17 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE UPDATE_PRICE:

LINE/COL ERROR
--- -------------------
11/9 PL/SQL: SQL Statement ignored
12/17 PLS-00417: unable to resolve "PRICE" as a column

These are the tables I'm working with.

CREATE TABLE ITEMS
(id NUMBER,
name VARCHAR2 (10),
price NUMBER (5,2),
CONSTRAINT items_id_pk PRIMARY KEY (Id));

CREATE TABLE ON_SALE
(item_id NUMBER,
start_date DATE,
end_date DATE,
CONSTRAINT on_sale_item_id_fk FOREIGN KEY (item_id)
REFERENCES items (id));

CREATE SEQUENCE items_id_seq
START WITH 1001
INCREMENT by 1
NOMAXVALUE
NOCACHE;|||The procedure compiles now, so I'm making some progress, but when I call it with EXEC update_price('30-AUG-03'), price doesn't actually get updated. Could someone tell me why?

CREATE OR REPLACE PROCEDURE update_price
(p_cutoff IN CHAR)
IS
CURSOR item_cur IS
SELECT price, start_date
FROM items, on_sale
WHERE items.id = on_sale.item_id
AND p_cutoff = on_sale.start_date
FOR UPDATE of price;
BEGIN
FOR item_rec IN item_cur LOOP
UPDATE items
SET price = price * 0.9
WHERE CURRENT OF item_cur;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(item_rec.price, '$99.99') || ' ' || TO_CHAR(item_rec.start_date));
END LOOP;
COMMIT;

END;
/

EXEC update_price('30-AUG-03');|||Originally posted by appoooh
The procedure compiles now, so I'm making some progress, but when I call it with EXEC update_price('30-AUG-03'), price doesn't actually get updated. Could someone tell me why?

CREATE OR REPLACE PROCEDURE update_price
(p_cutoff IN CHAR)
IS
CURSOR item_cur IS
SELECT price, start_date
FROM items, on_sale
WHERE items.id = on_sale.item_id
AND p_cutoff = on_sale.start_date
FOR UPDATE of price;
BEGIN
FOR item_rec IN item_cur LOOP
UPDATE items
SET price = price * 0.9
WHERE CURRENT OF item_cur;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(item_rec.price, '$99.99') || ' ' || TO_CHAR(item_rec.start_date));
END LOOP;
COMMIT;

END;
/

EXEC update_price('30-AUG-03');
Seems OK to me:

SQL> select * from items;

ID NAME PRICE
---- ---- ----
1 thing 10

SQL> select * from on_sale;

ITEM_ID START_DATE END_DATE
---- ---- ----
1 30-AUG-2003 31-DEC-2003

SQL> EXEC update_price('30-AUG-03');
$10.00 30-AUG-2003

PL/SQL procedure successfully completed.

SQL> select * from items;

ID NAME PRICE
---- ---- ----
1 thing 9

Are you being confused by the fact that the output from PUT_LINE still shows the old price?

BTW, it could more efficiently be written without a cursor like this:

CREATE OR REPLACE PROCEDURE update_price
(p_cutoff IN CHAR)
IS
BEGIN
update items
set price = price*0.9
where id in
( select item_id
from on_sale
where start_date = to_date(p_cutoff,'DD-MON-RR')
);
END;
/

(I would not perform the commit in the procedure - prefer to leave that decision to the caller).