What is the usage of For Update Clause and Where current of [message #297840] |
Sun, 03 February 2008 23:19  |
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   |
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 #297862 is a reply to message #297860] |
Mon, 04 February 2008 01:05   |
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
|
|
|
|
|