Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    8

    Question Unanswered: Problems with simple procedure

    <deleted>
    Last edited by appoooh; 10-29-03 at 08:57.

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Lightbulb Re: Problems with simple procedure

    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;

  3. #3
    Join Date
    Aug 2003
    Posts
    8
    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');

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •