Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Cursors updatable?

    In Sybase, cursors in our stored procedures were update/delete-able. Is the same true for Oracle? I've been reading through Feuerstein's 'Oracle's PL/SQL', but I haven't seen anything in that mentions cursors for anything other than data retrieval, but I thought I'd double-check here.

    -Chuck

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    found it:

    Code:
    declare
    cursor x is
      select field
      from table
        for update;
    table_rec x%rowtype;
    begin
      open x;
      loop
        fetch x into table_rec;
        if x%NOTFOUND then
           exit
        else
           update table
           set field = 'new_value'
           where current of x;
        end if;    
      end loop;
      commit;
    end;
    -Chuck

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Chuck, it'll work OK, but - since Oracle 8i (if I'm not wrong) you can shorten the code this way:
    Code:
    declare
    cursor x is
      select field
      from table
        for update;
    begin
      for table_rec in x loop
           update table
           set field = 'new_value'
           where current of x;
      end loop;
      commit;
    end;
    There are several advantages using this code: you don't have to declare row variable to hold values fetched by a cursor, you don't have to care about opening and closing the cursor, about exiting the loop if (when) cursor returns nothing.

    I hope this won't be misleading post in any way.

Posting Permissions

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