Problems with simple procedure

appoooh · #1 (**permalink**) 08-30-03, 11:23

alligatorsql.com · #2 (**permalink**) 08-31-03, 06:10

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

Quote:

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;

appoooh · #3 (**permalink**) 09-01-03, 07:07

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

andrewst · #4 (**permalink**) 09-01-03, 07:48

Quote:

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:

Code:

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:

Code:

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).

Internet Services

Web Development

Digital Marketing

Consumer Tech