| how to use 'where current of' if cursor has multiple tables [message #156025] |
Fri, 20 January 2006 06:21  |
vgs2005
Messages: 123
Registered: April 2005
|
Senior Member |
|
|
How come the emp table does not get updated in my code below???
When the cursor selects from multiple tables and i use FOR UPDATE and WHERE CURRENT OF, can i use the WHERE CURRENT OF in any of the tables included in the cursor???
DECLARE
CURSOR emp_cur IS
SELECT e.ename, d.dname FROM emp e, dept d
WHERE d.deptno = 10
and e.deptno = d.deptno
FOR UPDATE;
BEGIN
FOR employee_rec IN emp_cur
LOOP
UPDATE emp
SET sal = sal - 1
WHERE CURRENT OF emp_cur;
END LOOP;
commit;
END;
thanks..
|
|
|
|
|
|
| Re: how to use 'where current of' if cursor has multiple tables [message #156266 is a reply to message #156035] |
Tue, 24 January 2006 04:39  |
vgs2005
Messages: 123
Registered: April 2005
|
Senior Member |
|
|
Yes, you are right. However, I read that when a column is not specified in the FOR UPDATE clause, it means that Oracle locks all the records retrieved from all the tables in the CURSOR. In my example, from both EMP and DEPT tables.
How about if I have something like below, the update does not take effect at all in any of the tables:
DECLARE
CURSOR emp_dept_cur IS
SELECT e.empno, e.deptno, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
FOR UPDATE;
BEGIN
FOR employee_rec IN emp_dept_cur
LOOP
UPDATE emp
SET sal = sal + 2
WHERE CURRENT OF emp_dept_cur;
UPDATE dept
SET dname = dname||' '
WHERE CURRENT OF emp_dept_cur;
END LOOP;
COMMIT;
END;
How do I resolve this?
|
|
|
|
| Re: how to use 'where current of' if cursor has multiple tables [message #156298 is a reply to message #156266] |
Tue, 24 January 2006 09:41 |
Gerardo Fernandez Herrera
Messages: 58
Registered: January 2006
Location: Montevideo, Uruguay
|
Member |
|
|
Well, the PL/SQL User's Guide and Reference states the opposite:
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.
Why do you want to use FOR UPDATE ?
This will work either:
Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0
Connected as scott
SQL> select sal from emp;
SAL
---------
800.00
1600.00
1250.00
2975.00
1250.00
2850.00
2450.00
3000.00
5000.00
1500.00
1100.00
950.00
3000.00
1300.00
14 rows selected
SQL> select dname from dept;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
SQL> DECLARE
2 CURSOR emp_dept_cur IS
3 SELECT e.empno, e.deptno, e.sal, d.dname
4 FROM emp e, dept d
5 WHERE e.deptno = d.deptno
6 and rownum = 1;
7
8 BEGIN
9 FOR employee_rec IN emp_dept_cur
10 LOOP
11 UPDATE emp
12 SET sal = sal + 2;
13
14 UPDATE dept
15 SET dname = dname||'WWW';
16 END LOOP;
17 --COMMIT;
18 END;
19 /
PL/SQL procedure successfully completed
SQL> select sal from emp;
SAL
---------
802.00
1602.00
1252.00
2977.00
1252.00
2852.00
2452.00
3002.00
5002.00
1502.00
1102.00
952.00
3002.00
1302.00
14 rows selected
SQL> select dname from dept;
DNAME
--------------
ACCOUNTINGWWW
RESEARCHWWW
SALESWWW
OPERATIONSWWW
SQL>
|
|
|
|
| Re: how to use 'where current of' if cursor has multiple tables [message #156911 is a reply to message #156298] |
Sun, 29 January 2006 21:26 |
vgs2005
Messages: 123
Registered: April 2005
|
Senior Member |
|
|
| Gerardo Fernandez Herrera wrote on Tue, 24 January 2006 10:41 |
Well, the PL/SQL User's Guide and Reference states the opposite:
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.
|
So it should be possible to have:
DECLARE
CURSOR emp_dept_cur IS
SELECT e.empno, e.deptno, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
FOR UPDATE of d.dname, e.sal;
BEGIN
FOR employee_rec IN emp_dept_cur
LOOP
UPDATE emp
SET sal = sal + 2
WHERE CURRENT OF emp_dept_cur;
UPDATE dept
SET dname = dname||' '
WHERE CURRENT OF emp_dept_cur;
END LOOP;
COMMIT;
END;
But it does not work.. Should it lock only rows from one table??
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
