Oracle cursor的用法.

锁表,可以用来锁定表的数据.代码如下

DECLARE
  CURSOR lock_r IS
    SELECT *
      FROM cmx_if_invadj_detail t
     WHERE t.group_id = 1493
       FOR UPDATE NOWAIT;
BEGIN
  OPEN lock_r;
  CLOSE lock_r;
  dbms_output.put_line(11111);
EXCEPTION
  WHEN OTHERS THEN  --不需要再次关闭,因为cursor在open中已经raise了.
    dbms_output.put_line(22222);
END;

判断cursor是否为空,其实用法与exit用法一样,不过是不进行循环,直接判断.

DECLARE
  l_exists VARCHAR2(1);
  CURSOR lock_r IS
    SELECT 'X'
      FROM cmx_if_invadj_detail t
     WHERE t.group_id = 1492
       FOR UPDATE NOWAIT;
BEGIN
  OPEN lock_r;
  FETCH lock_r
    INTO l_exists;
  IF lock_r%NOTFOUND THEN
    dbms_output.put_line(11111);
  END IF;
  CLOSE lock_r;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;