PLSQL中select for update cursor

转载

mob604757027d04 2012-11-14 17:21:00

文章标签 plsql select for update NOWAIT sql 释放资源 文章分类 代码人生

开始

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;