--刚好有个学习笔记 declare cursor emp_cur is select empno,ename,sal from emp for update; emp_row emp_cur%rowtype; begin open emp_cur; loop fetch emp_cur into emp_row; exit when emp_cur%notfound; if emp_row.sal<2000 then --使用current of emp_cur时一定要for update; update emp set sal=sal*1.1 where current of emp_cur; end if; end loop; close emp_cur; end;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------------------- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980-12-17 800.10 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
14 rows selected
SQL> SQL> declare 2 emp_cur sys_refcursor; 3 begin 4 for emp_cur in ( select empno,ename,sal,rowid from emp for update ) loop 5 update emp set sal=sal*1.1 where rowid=emp_cur.rowid; 6 END LOOP; 7 end; 8 /
PL/SQL procedure successfully completed
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------------------- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980-12-17 880.11 20 7499 ALLEN SALESMAN 7698 1981-2-20 1760.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1375.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 3272.50 20 7654 MARTIN SALESMAN 7698 1981-9-28 1375.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 3135.00 30 7782 CLARK MANAGER 7839 1981-6-9 2695.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3300.00 20 7839 KING PRESIDENT 1981-11-17 5500.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1650.00 0.00 30 7876 ADAMS CLERK 7788 1987-5-23 1210.00 20 7900 JAMES CLERK 7698 1981-12-3 1045.00 30 7902 FORD ANALYST 7566 1981-12-3 3300.00 20 7934 MILLER CLERK 7782 1982-1-23 1430.00 10
14 rows selected
SQL>
上面的那个emp_cur sys_refcursor;的定义可以不用.
Using the CURRENT OF Clause You use the CURRENT OF cursor_name clause in a DELETE or UPDATE statement to refer to the latest row fetched from the named cursor. The cursor must be open and positioned on a row. If no fetch has been done or if the cursor is not open, the CURRENT OF clause results in an error and processes no rows.The FOR UPDATE OF clause is optional when you declare a cursor that is referenced in the CURRENT OF clause of an UPDATE or DELETE statement. The CURRENT OF clause signals the precompiler to add a FOR UPDATE clause if necessary.
declare
cursor emp_cur is select empno,ename,sal from emp for update;
emp_row emp_cur%rowtype;
begin
open emp_cur;
loop
fetch emp_cur into emp_row;
exit when emp_cur%notfound;
if emp_row.sal<2000 then
--使用current of emp_cur时一定要for update;
update emp set sal=sal*1.1 where current of emp_cur;
end if;
end loop;
close emp_cur;
end;
如果是我的话估计会把rowid写到cursor里,然后用rowid来锁定要修改的数据.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------------------- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.10 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
14 rows selected
SQL>
SQL> declare
2 emp_cur sys_refcursor;
3 begin
4 for emp_cur in ( select empno,ename,sal,rowid from emp for update ) loop
5 update emp set sal=sal*1.1 where rowid=emp_cur.rowid;
6 END LOOP;
7 end;
8 /
PL/SQL procedure successfully completed
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------------------- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 880.11 20
7499 ALLEN SALESMAN 7698 1981-2-20 1760.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1375.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 3272.50 20
7654 MARTIN SALESMAN 7698 1981-9-28 1375.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 3135.00 30
7782 CLARK MANAGER 7839 1981-6-9 2695.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3300.00 20
7839 KING PRESIDENT 1981-11-17 5500.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1650.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1210.00 20
7900 JAMES CLERK 7698 1981-12-3 1045.00 30
7902 FORD ANALYST 7566 1981-12-3 3300.00 20
7934 MILLER CLERK 7782 1982-1-23 1430.00 10
14 rows selected
SQL>
You use the CURRENT OF cursor_name clause in a DELETE or UPDATE statement to refer to the latest row fetched from the named cursor. The cursor must be open and positioned on a row. If no fetch has been done or if the cursor is not open, the CURRENT OF clause results in an error and processes no rows.The FOR UPDATE OF clause is optional when you declare a cursor that is referenced in the CURRENT OF clause of an UPDATE or DELETE statement. The CURRENT OF clause signals the precompiler to add a FOR UPDATE clause if necessary.