开始
DECLARE CURSOR emp_cursor IS SELECT empno, ename, dname FROM emp_cpy, dept WHERE emp_cpy.deptno=dept.deptno AND emp_cpy.deptno = 20 FOR UPDATE OF sal NOWAIT; emp_record emp_cursor%ROWTYPE; BEGIN LOOP IF NOT emp_cursor%ISOPEN THEN OPEN emp_cursor; END IF; FETCH emp_cursor INTO emp_record; EXIT WHEN emp_cursor%NOTFOUND; UPDATE emp_cpy SET empno=1000 WHERE CURRENT OF emp_cursor; dbms_output.put_line ('empno is:' || emp_record.empno || '-- emp name is:' || emp_record.ename ||'-- dept is:' || emp_record.dname); END LOOP; IF emp_cursor%ISOPEN THEN dbms_output.put_line('Now to close cursor!'); CLOSE emp_cursor; END IF; END;
当时看到结果:
anonymous block completed empno is:7369-- emp name is:SMITH-- dept is:RESEARCH empno is:7566-- emp name is:JONES-- dept is:RESEARCH empno is:7788-- emp name is:SCOTT-- dept is:RESEARCH empno is:7876-- emp name is:ADAMS-- dept is:RESEARCH empno is:7902-- emp name is:FORD-- dept is:RESEARCH Now to close cursor!
我们再查询看看:
SQL> select * from emp_cpy where deptno=20; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 1000 SMITH CLERK 7902 17-DEC-80 800 20 1000 JONES MANAGER 7839 02-APR-81 2975 20 1000 SCOTT ANALYST 7566 19-APR-87 3000 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 1000 ADAMS CLERK 7788 23-MAY-87 1100 20 1000 FORD ANALYST 7566 03-DEC-81 3000 20 SQL>
再前面的整个的代码结束的时候,自动进行了提交:在结果集的范畴里,改变了 deptno的值。
结束
本文转自健哥的数据花园博客园博客,原文链接:http://www.cnblogs.com/gaojian/archive/2012/11/14/2770320.html,如需转载请自行联系原作者