Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    58

    Unanswered: How to use the key word "with current of"?

    Hi, everyone, thank you evry much!

    Here I want to use cursor and the key word "with current of " to update the table, but I failed, please refer to the following example.

    CREATE OR REPLACE PROCEDURE CUR_TEST
    IS
    Cursor lc_G IS
    SELECT G.ID
    FROM G, GB
    WHERE G.ID = GB.ID AND GB.AGE > 99
    FOR UPDATE;
    str_Temp1 VARCHAR2(5);
    ln_Age number;

    BEGIN
    ln_Age := 1000;
    OPEN lc_G;
    LOOP
    FETCH lc_G INTO str_Temp1;
    IF lc_G%FOUND THEN
    ln_Age := ln_Age + 1;
    UPDATE G SET AGE = ln_Age where current of lc_G;
    DBMS_OUTPUT.PUT_LINE('ID:'||str_Temp1);
    ELSE
    EXIT;
    END IF;
    END LOOP;
    CLOSE lc_G;
    COMMIT;
    END;

    ----------------
    CREATE TABLE GB
    (
    ID VARCHAR2(5),
    AGE FLOAT(126),
    TYP VARCHAR2(1)
    )

    Records:
    11 9 1
    12 100 1
    21 1001 2
    40 1002 3


    ------------------
    CREATE TABLE G
    (
    ID VARCHAR2(5),
    AGE FLOAT(126),
    TYP VARCHAR2(1)
    )

    Records:
    11 9 1
    12 10 1
    21 11 2
    40 12 3
    50 13 5

    ------------------
    How can I do? How can I update the table "B"?

    Thank you very much!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by jp7234
    but I failed
    What is your problem exactly?
    Do you get an error message when compiling the procedure?
    Do you get an error message when running the procedure?
    Does the procedure not update the correct rows?
    Is a wrong value written into the field?

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Cursor should be declared "FOR UPDATE OF G.AGE", and the whole thing rewritten as
    Code:
    DECLARE
      CURSOR lc_G IS
        SELECT G.ID
        FROM G, GB
        WHERE G.ID = GB.ID AND GB.AGE > 99 
        FOR UPDATE OF G.age;
    
      ln_Age NUMBER := 1000;
      str_temp1 lc_G%ROWTYPE;
    
    BEGIN
      OPEN lc_G;
      LOOP
        FETCH lc_G INTO str_Temp1;
        EXIT WHEN lc_g%NOTFOUND;
    				
        UPDATE G SET AGE = ln_Age WHERE CURRENT OF lc_G;
        
      END LOOP;
      CLOSE lc_G; 
    END;
    to improve readability (oh, yes ... don't mind my modifications regarding the 'ln_age' variable; it is just an example).

  4. #4
    Join Date
    Aug 2006
    Posts
    58
    Thank you very much ,Mr. Littlefoot. According to what you said,I successed. Thank you again.

    Mr. shammat , please refer to the above source. Thank you very much!

  5. #5
    Join Date
    Aug 2006
    Posts
    58
    Mr. Littlefoot. Can you tell me why I could not success in the first source(Not modified)?

    Thank you very much!

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    FOR UPDATE clause locks selected rows from all tables in a query. If you specify the OF ... <column_name> clause, you'll indicate which table's rows will be locked. In your case, FOR UPDATE locked rows from both G and GB tables, while FOR UPDATE OF G.AGE locked rows only in table G.

    WHERE CURRENT OF clause of the UPDATE statement "knew" it has to update record in the G table, so it did it.

  7. #7
    Join Date
    Aug 2006
    Posts
    58
    Littlefoot, thank you very much!

Posting Permissions

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