Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45

    Unanswered: cursor select for update

    hello

    I have a table of 100 rows that I have to update using a procedure.

    is
    cnum number;

    cursor c1 is

    select a.id
    from c_id a, b_id b
    where a.bcode = b.bcode
    for update of b.id

    begin
    open c1;
    loop
    fetch c1 into cnum;
    exit when c1%notfound;

    update b_id b set b.id = ?
    where current of c1;

    this is where am not sure which param to put if the c1 is in where clause.

    Please help.

    Thank you


    Al

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I have a table of 100 rows that I have to update using a procedure.
    But, you didn't tell to what value ... My lucky guess would be that you'd like to update b.id to a.id. In such a case, your procedure's update statement would be
    Code:
    update b_id b set
      b.id = cnum.id
    However, why bother with a procedure? Pure SQL would make it as
    Code:
    update b_id b set
      b.id = (select a.id from c_id a where a.bcode = b.bcode);
    Besides that, you could simplify the procedure - omit declaration of "cnum" and use a cursor FOR loop. For example,
    Code:
    create or replace procedure myproc is
      cursor c1 is select ...
    begin
      for c1r in c1 loop
        update b_id set ...
      end loop;
    end;
    /

  3. #3
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45
    Quote Originally Posted by Littlefoot
    But, you didn't tell to what value ... My lucky guess would be that you'd like to update b.id to a.id. In such a case, your procedure's update statement would be
    Code:
    update b_id b set
      b.id = cnum.id
    However, why bother with a procedure? Pure SQL would make it as
    Code:
    update b_id b set
      b.id = (select a.id from c_id a where a.bcode = b.bcode);
    Besides that, you could simplify the procedure - omit declaration of "cnum" and use a cursor FOR loop. For example,
    Code:
    create or replace procedure myproc is
      cursor c1 is select ...
    begin
      for c1r in c1 loop
        update b_id set ...
      end loop;
    end;
    /



    Thank you , it worked

Posting Permissions

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