开始
DECLARE CURSOR emp_cursor IS SELECT empno, ename, dname FROM emp, dept WHERE emp.deptno=dept.deptno AND emp.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; 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;
当没有其他session 干扰的时候，会有如下的结果：
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!
如果有另外一个session 拿住某些记录（这里是 auto commit off状态，所以是为提交。也就是 在一个隐含的事务里面）：
SQL> update emp set ename='XFORD' where deptno=20; 5 rows updated. SQL>
然后 保持这种状态，重新来执行，结果是：
Error report: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ORA-06512: at line 3 ORA-06512: at line 16 00054. 00000 - "resource busy and acquire with NOWAIT specified" *Cause: Resource interested is busy. *Action: Retry if necessary.
如果我们不是用 NOWAIT关键字，会如何？
DECLARE CURSOR emp_cursor IS SELECT empno, ename, dname FROM emp, dept WHERE emp.deptno=dept.deptno AND emp.deptno = 20 FOR UPDATE OF sal; 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; 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;
这个时候再执行，就会一直等待，等待对方释放资源。
结束
本文转自健哥的数据花园博客园博客，原文链接：http://www.cnblogs.com/gaojian/archive/2012/11/14/2770274.html，如需转载请自行联系原作者
