Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2005
    Posts
    13

    Unanswered: procedure problem

    Hey guys, the following procedure isn't updating the records it should be and I was just wondering what I have done wrong? Thanks


    CREATE OR REPLACE PROCEDURE HOURS IS
    HOURVAR NUMBER;
    cursor C_CHECK IS
    SELECT HOURS
    INTO HOURVAR
    FROM ADMIN;
    BEGIN
    FOR COUNT IN C_CHECK LOOP
    If HOURS > 250
    UPDATE SECTION
    SET RATE = 'Rcc';
    END IF;

    END LOOP;
    END IF;
    END;

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    why don't you update your table using one update statement?

  3. #3
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    I don't see a commit however I agree if you can update with one statment

  4. #4
    Join Date
    Dec 2005
    Posts
    13
    I am currently trying to learn sql from a book and just want to try out a procedure rather than just an update but I cant see whats wrong with this one.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    maybe

    Code:
    CREATE OR REPLACE PROCEDURE HOURS IS
    
    cursor C_CHECK IS
    SELECT HOURS
    FROM ADMIN 
    FOR UPDATE;
    
    BEGIN
    FOR COUNT IN C_CHECK 
    LOOP
      If COUNT.HOURS > 250
        UPDATE SECTION
        SET RATE = 'Rcc'
        WHERE CURRENT OF C_CHECK;
      END IF;
    END LOOP;
    
    END IF;
    END;
    You might want to even move the HOURS>250 into the cursor declaration.

    -cf

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    How can that work, your cursor is using the admin table and your update is using the section table. and you have NO relationship defined between the two tables. Your update will update the entire section table everytime the loop runs. Also do NOT use an oracle reserved word (COUNT) for your pointer. You might try something like...


    CREATE OR REPLACE PROCEDURE HOURS IS

    cursor C_CHECK IS
    SELECT some_common_column
    FROM ADMIN
    where HOURS > 250
    FOR UPDATE;

    BEGIN
    FOR pnt IN C_CHECK
    LOOP
    UPDATE SECTION a
    SET RATE = 'Rcc'
    WHERE a.some_common_column = pnt.some_common_column;
    END LOOP;
    END;


    Then to run it in sql*plus use the following

    exec HOURS;
    commit;


    Then check your table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Dec 2005
    Posts
    13
    Thanks for your advice and sorry for the newbie question.

  8. #8
    Join Date
    Dec 2005
    Posts
    13
    Sorry for another newbie question but how come the code SET RATE = 'BCC' uses the = operator rather than this one :=

    Thanks.
    Last edited by 1821; 12-04-05 at 14:07.

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by 1821
    Sorry for another newbie question but how come the code SET RATE = 'BCC' uses the = operator rather than this one :=
    Hey, ask away. Interesting question

    I guess IBM chose '=' when they invented SQL back in the 1970s.

    However, SQL and PL/SQL are different languages, and Oracle chose ':=' as the PL/SQL variable assignment operator (based on the Ada language). It has to be different from the PL/SQL equality operator, as otherwise

    IF x = 42 THEN...

    would be ambiguous (maybe it would mean "if we successfully assigned the value 42 to the variable 'x'...").

    Several other languages use == for the equality test, and I have sometimes heard people complain that PL/SQL should be the same - but of course it has to fit around SQL. I think if you had to write

    WHERE x = 42

    in SQL, but

    IF x == 42

    in PL/SQL, there would be even more complaints. I suppose Oracle could have gone for an explicit SET syntax for PL/SQL variable assignment as I think I saw in a standards paper once, but they didn't.

    Meanwhile SQL manages just fine with '=', and always has done.

  10. #10
    Join Date
    Dec 2005
    Posts
    13
    Thanks for your reply

    So would the code in this post be classed as SQL or PL/SQL?

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Any CREATE PROCEDURE code is PL/SQL code; this abbreviation stands for "Procedural Language/Structured Query Language". Although procedural, they may contain both SQL statements, such as

    UPDATE SECTION a
    SET RATE = 'Rcc'
    WHERE a.some_common_column = pnt.some_common_column;

    and procedural elements, as - for example - a FOR LOOP is.

  12. #12
    Join Date
    Dec 2005
    Posts
    13
    Thanks for clearing that up.

Posting Permissions

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