Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    58

    Unanswered: Oracle Cursor help

    Is it possible to have a cursor pulling data from 2 different tables and marking it as FOR UPDATE and then using the UPDATE statement for one table with the WHERE CURRENT OF clause. For example can I do this

    CURSOR C2 IS
    SELECT t1.date_created, t2.customer, t2.name1, t2.sman
    FROM lead_when t1, leads t2
    where t1.leadno = t2.leadno AND
    t1.lastsale is null
    FOR UPDATE of t2.sman;
    BEGIN
    FOR c2_index in c2
    LOOP
    IF(c2_index.date_created < sysdate -180)
    THEN
    INSERT into sman_null_backup (sman, customer, name1) VALUES (c2_index.sman, c2_index.customer, c2_index.name1);
    UPDATE leads
    SET sman = NULL
    WHERE CURRENT OF C2;
    END IF;
    END LOOP;
    CLOSE C2;
    COMMIT;

    Will this update the LEADS table. Also, do I need to have commit there, or is it implicit?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, you can do that. You should explicitly commit.

    Your code can be improved. First, the condition " IF(c2_index.date_created < sysdate -180)" should be part of the cursor definition. Second, there is no need to close the for-loop cursor explicitly (in fact, that will fail). So:
    Code:
    DECLARE
      CURSOR C2 IS
        SELECT t1.date_created, t2.customer, t2.name1, t2.sman
        FROM lead_when t1, leads t2
        WHERE t1.leadno = t2.leadno
        AND t1.lastsale is null
        AND t1.date_created < sysdate-180
        FOR UPDATE of t2.sman;
    BEGIN
      FOR c2_index in c2
      LOOP
        INSERT into sman_null_backup (sman, customer, name1) VALUES (c2_index.sman, c2_index.customer, c2_index.name1);
        UPDATE leads
        SET sman = NULL
        WHERE CURRENT OF C2;
      END LOOP;
      COMMIT;
    END;

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    58
    Thanks! And about the condition being part of the cursor....I kinda feel silly for not doing that. Thank you so much for pointing that out.

Posting Permissions

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