-
For update of
What is the difference between for update of columnname and for update?
select * From emp
for update of sal;
Here it locks entire table.
update emp
set job ='SALESMAN'
where empno=7369;
Eventhough I am updating job from another session it is blocking. Then what is use of specifying for update of sal?
The following statement also doing the same.
select * From emp for update ;
Then what is the difference between for update of sal and for update statement?
-
Re: For update of
Which database u r using here. All doesn't support this.
-
Re: For update of
[B]>>Then what is the difference between for update of sal and for update statement?[/B]
When you are working with a single table you will not find any difference. Select for update of locks rows not columns.
Do the following exercise then it will be clear to you.
From first session execute the following statemet.
[CODE]
SELECT a.ename,a.job,a.sal,b.dname
FROM emp a, dept b
WHERE a.deptno = b.deptno
FOR UPDATE OF sal;
[/CODE]
UPDATE of Columname lock only rows from tables which have a column listed in the OF clause. Rows are not locked in tables that don't have a column appearing in the OF clause.
Here sal is of table emp. It locks all the rows in EMP table only not DEPT table.You can update rows in DEPT table without any problem.
[CODE]
UPDATE dept
SET dname = 'EDP'
WHERE deptno = 40;
[/CODE]
But it will not allowing you to update emp until the first session release lock by applying commit or rollback command.
[CODE]
UPDATE emp
SET sal = 5000
WHERE empno = 7369;
[/CODE]
I hope now it is clear to you.
Regards
Krishna
-
Re: For update of
When do we use where current of statement?
-
Re: For update of
Where current of is used to update or delete records that have been referenced by select for update without using external reference.
Example:-
[CODE]
CREATE OR REPLACE PROCEDURE test_proc AS
CURSOR emp_cur IS
SELECT rowid,a.*
FROM emp a
FOR UPDATE ;
BEGIN
FOR v_cur IN emp_cur
LOOP
-- 1st method
UPDATE emp
SET sal = sal * 1.10
WHERE [B]empno = v_cur.empno[/B];
-- 2nd method
UPDATE emp
SET sal = sal * 1.10
WHERE [B]rowid = v_cur.rowid[/B];
-- 3rd method
UPDATE emp
SET sal = sal * 1.10
WHERE [B]current of emp_cur[/B];
END LOOP;
COMMIT;
END;
[/CODE]
-
Re: For update of
Can't we use where current of without using for update?
-
Re: For update of
No we can't.
It will show error
PLS-00404: cursor 'CURSOR NAME ' must be declared with FOR UPDATE to use with CURRENT OF