This forum is currently locked. You can't register or post questions at this time. (read more)

using for update clause in cursor

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

yairk30
Member
Posts: 35
Joined: Thu Apr 20, 2006 6:05 am

using for update clause in cursor

Postby yairk30 » Mon Feb 10, 2014 9:02 pm

Hey tim ,
I'm trying to update records fetched with cursor with for update clause,
and I can't find any answer to my question on other sites.

I have a population fetched with a join of 2 tables as followes:

select *
from orders a, orders_items b
where a.order_no=b.order_no
and a.order_date<to_date('01/01/10','dd/mm/rr')
and b.order_bill_no is null
and a.order_status not in ('á','è')
and a.order_bill_no is null

of course orders table is header table and order_items is lines table (pk/fk - order_no).

if I want to update both order_bill_no columns in orders table and in order_item table with constant value (1),
what is the best way?
I considers run with a cursor with for update clause , but the key for the update is order_no column.
what do you think is the best way?

can I run with this block:

Code: Select all

declare
  v_order_no order_items.order_no%type;

  cursor order_cur is
  select b.order_no order_no
  from orders a, order_items b
  where a.order_no=b.order_no
  and order_date<to_date('01/01/10','dd/mm/rr')
  and b. order_bill_no is null
  and a.order_status not in ('á','è')
  and a. order_bill_no is  null
  for update;


begin
  for ord in order_cur loop
 
  update orders
  set order_bill_no = 1
  where current of order_cur;
 
  update order_items
  set order_bill_no = 1
  where current of order_cur;
 
  end loop;
 
end;


thanks!

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: using for update clause in cursor

Postby Tim... » Wed Feb 12, 2014 8:37 am

Hi.

Sorry. I'm not understanding what the question is here. Are you having a problem with this?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: using for update clause in cursor

Postby Tim... » Wed Feb 12, 2014 8:41 am

I think this looks a bit more normal to me.

Code: Select all

declare
  v_order_no order_items.order_no%type;

  cursor order_cur is
  select b.order_no order_no
  from orders a, order_items b
  where a.order_no=b.order_no
  and order_date<to_date('01/01/10','dd/mm/rr')
  and b. order_bill_no is null
  and a.order_status not in ('á','è')
  and a. order_bill_no is  null
  for update;
begin
  for ord in order_cur loop
    update orders
    set order_bill_no = 1
    where order_no = ord.order_no;
   
    update order_items
    set order_bill_no = 1
    where order_no = ord.order_no;
  end loop;
end;
/


Note. Your records in the cursor will remain locked until you COMMIT or ROLLBACK.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

yairk30
Member
Posts: 35
Joined: Thu Apr 20, 2006 6:05 am

Re: using for update clause in cursor

Postby yairk30 » Wed Feb 12, 2014 7:33 pm

Hey ,
Sorry if I were misunderstood before.

my question here is regarding the clause:

Code: Select all

where current of order_cur;
.

I see that in your code you have omitted that clause.

whice method is better , with the "where current of.." clause ,
or without it?

thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: using for update clause in cursor

Postby Tim... » Wed Feb 12, 2014 7:58 pm

Hi.

I think it is always better if you say explicitly what you want. That way people reading the code can see exactly what you are doing. I never use "current of".

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests