cursor之for loop、for update of

原文地址：http://blog.csdn.net/csdn1234/article/details/4000924

一.游标式for循环

不用显式open和close游标

SQL> DECLARE
  2     CURSOR c_orders IS SELECT * FROM orders;
  3     v_order orders%ROWTYPE;
  4  BEGIN
  5     OPEN c_orders;
  6     FOR v_order IN c_orders LOOP
  7        DBMS_OUTPUT.PUT_LINE(v_order.orderid||':'||v_order.positioncode);
  8     END LOOP;
  9     CLOSE c_orders;
 10  END;
 11  /

结果:

DECLARE
*
第 1 行出现错误:
ORA-06511: PL/SQL: 游标已经打开
ORA-06512: 在 line 2
ORA-06512: 在 line 6

为什么会出现错误呢?因为游标式for循环已经隐式地进行了游标的open、fetch、close处理.

SQL> DECLARE
  2     CURSOR c_orders IS SELECT * FROM orders;
  3     v_order orders%ROWTYPE;
  4  BEGIN
  5     --这里会隐式地打开游标
  6     FOR v_order IN c_orders LOOP
  7       --隐式fetch记录
  9       --进行c_orders%NOTFOUND判断
  8       DBMS_OUTPUT.PUT_LINE(v_order.orderid||':'||v_order.positioncode);
 10     END LOOP;
 11  END;
 12  /

 

二.FOR UPDATE与FOR UPDATE OF

 

1.用来给游标基表加行锁.

 

2.FOR UPDATE OF COLUMN1[,COLUMN2,..]

是指示后面的操作将更新哪些列,会给这些列所在的基表加行锁.并不是约束只能更新这些列,更新其它列也可以.

 

3.WHERE CURRENT OF

FOR UPDATE和FOR UPDATE OF只能指向一个表,不能指向多个表.

DECLARE
  CURSOR c_orders IS select a.orderid id1,b.orderid id2,a.positioncode,b.showcount from
  orders a,order2 b where a.orderid=b.orderid FOR UPDATE OF positioncode,showcount;
  v_order c_orders%ROWTYPE;
BEGIN
  OPEN c_orders;
  
  LOOP
    FETCH c_orders  INTO v_order;
    EXIT WHEN c_orders%NOTFOUND;
    dbms_output.put_line(v_order.id1||','||v_order.id2||','||v_order.positioncode||','||v_order.showcount);
    UPDATE order2 SET showcount=8 WHERE CURRENT OF c_orders;
    IF SQL%NOTFOUND THEN
      DBMS_OUTPUT.PUT_LINE('NOT FOUND');
    END IF;
  END LOOP;
  COMMIT;
  CLOSE c_orders;
END;

执行的结果是:

1,1,C,9
NOT FOUND
2,2,C,9
NOT FOUND
3,3,C,9
NOT FOUND
4,4,C,9
NOT FOUND
5,5,C,9
NOT FOUND

将FOR UPDATE OF positioncode,showcount换成FOR UPDATE也是同样的结果.

换成FOR UPDATE OF showcount,只指向一个表.

DECLARE
  CURSOR c_orders IS select a.orderid id1,b.orderid id2,a.positioncode,b.showcount from
  orders a,order2 b where a.orderid=b.orderid FOR UPDATE OF showcount;
  v_order c_orders%ROWTYPE;
BEGIN
  OPEN c_orders;
  
  LOOP
    FETCH c_orders  INTO v_order;
    EXIT WHEN c_orders%NOTFOUND;
    dbms_output.put_line(v_order.id1||','||v_order.id2||','||v_order.positioncode||','||v_order.showcount);
    UPDATE order2 SET showcount=8 WHERE CURRENT OF c_orders;
    IF SQL%NOTFOUND THEN
      DBMS_OUTPUT.PUT_LINE('NOT FOUND');
    END IF;
  END LOOP;
  COMMIT;
  CLOSE c_orders;
END;

则成功执行:

1,1,C,9
2,2,C,9
3,3,C,9
4,4,C,9
5,5,C,9