Home » SQL & PL/SQL » SQL & PL/SQL » What is the usage of For Update Clause and Where current of
What is the usage of For Update Clause and Where current of [message #297840] Sun, 03 February 2008 23:19 Go to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Friends,

I want to know usage of FOR UPDATE and
WHERE CURRENT OF

Difference between the follwoing queries also.

  cursor c1 is select * from emp 
  for update;

  cursor c1 is select * from emp for 
  update of ename;


What is the exact usage of where current of clause?

How can use Where statement
withe where current of clause?

I am expecting your answers.

Thanks

Mano
Re: What is the usage of For Update Clause and Where current of [message #297847 is a reply to message #297840] Sun, 03 February 2008 23:40 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
'FOR UPDATE' clause acquires exclusive row locks. All rows are locked whenyou open the cursor, and they are unlocked when you commit your transaction. So, you cannot fetch from a 'FOR UPDATE' cursor after a commit. If you do,PL/SQL raises an exception.

adding 'of sal' keeps other columns free except sal.

regards,
Re: What is the usage of For Update Clause and Where current of [message #297853 is a reply to message #297840] Mon, 04 February 2008 00:33 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Thanks friend.

But i could not understand clearly about.

for update 
for update [columns]and
where current of


Any other explanation welcomes.

Thanks
Mano
Re: What is the usage of For Update Clause and Where current of [message #297854 is a reply to message #297853] Mon, 04 February 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Chapter 6 Performing SQL Operations from PL/SQL
Section Overview of Transaction Processing in PL/SQL
Subsection Using FOR UPDATE

Regards
Michel
Re: What is the usage of For Update Clause and Where current of [message #297856 is a reply to message #297840] Mon, 04 February 2008 00:48 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Dear Michel.

Thank you.

select * from emp,dept where emp.deptno=dept.deptno
for update sal

Can we mention any column name instead of sal?

The table refrenced by any column or any reason
for specifying the particular column name?

Please reply.

Thanks

Mano
Re: What is the usage of For Update Clause and Where current of [message #297858 is a reply to message #297856] Mon, 04 February 2008 00:55 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Did you read the docs Michel pointed you to? It says quite clearly why and when you need to add a column name to the for update clause.

By the way, the correct syntact is
for update OF sal

[Updated on: Mon, 04 February 2008 00:55]

Report message to a moderator

Re: What is the usage of For Update Clause and Where current of [message #297860 is a reply to message #297840] Mon, 04 February 2008 00:58 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Sorry for the sytax.

I gone through docs by pointed Michel

for update of sal.

Why we have to mention sal?
Can me mention any other column like dname,etc.,
If we mention sal means, except sal column
all other columns will be free?

Thanks

Mano
Re: What is the usage of For Update Clause and Where current of [message #297862 is a reply to message #297860] Mon, 04 February 2008 01:05 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Quote:
When querying multiple tables, you can use the FOR UPDATE clause to confine row locking to particular tables. Rows in a table are locked only if the FOR UPDATE OF clause refers to a column in that table. For example, the following query locks rows in the employees table but not in the departments table:
DECLARE
  CURSOR c1 IS SELECT last_name, department_name FROM employees, departments
    WHERE employees.department_id = departments.department_id 
          AND job_id = 'SA_MAN'
      FOR UPDATE OF salary;



So, can we use any column? No. Can we use any column from the table we want to update? Yes.

Don't you have an Oracle database where you can test these kind of things? Trying it yourself really teaches you a lot and is great fun as well.

[Updated on: Mon, 04 February 2008 01:07]

Report message to a moderator

Re: What is the usage of For Update Clause and Where current of [message #297863 is a reply to message #297840] Mon, 04 February 2008 01:11 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member

Thank you frank.

I got ideas.

Please

Can you tell me real time situation to use
where current of?

Why we need to update last fetched rows from
cursor?

Thanks

Mano


Re: What is the usage of For Update Clause and Where current of [message #297866 is a reply to message #297863] Mon, 04 February 2008 01:17 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Did you read the example in the link I gave?

Regards
Michel
Previous Topic: Different order in oracle 8i and 10g
Next Topic: Exists and In operator
Goto Forum:
  


Current Time: Sat Feb 15 00:16:45 CST 2025