plsql学习笔记（cursor篇）

游标语法

显示定义游标

1)无参数游标

CURSOR company_cur 

         IS

         SELECT company_id FROM company;

2)有参数游标

CURSOR company_cur (id_in IN NUMBER) IS

      SELECT name FROM company

      WHERE  company_id = id_in;

打开游标

OPEN cursor_name [(argument [,argument ...])];

从游标里面取值

FETCH cursor_name INTO record_or_variable_list;

关闭游标

CLOSE cursor_name;

 

游标属性

%ISOPEN

 

TRUE if cursor is open.

 

FALSE if cursor is not open.

 

%FOUND

 

INVALID_CURSOR is raised if cursor has not been OPENed.

 

NULL before the first fetch.

 

TRUE if record was fetched successfully.

 

FALSE if no row was returned.

 

INVALID_CURSOR if cursor has been CLOSEd.

 

%NOTFOUND

 

INVALID_CURSOR is raised if cursor has not been OPENed.

 

NULL before the first fetch.

 

FALSE if record was fetched successfully.

 

TRUE if no row was returned.

 

INVALID_CURSOR if cursor has been CLOSEd.

 

%ROWCOUNT

 

INVALID_CURSOR is raised if cursor has not been OPENed.

 

The number of rows fetched from the cursor.

 

INVALID_CURSOR if cursor has been CLOSEd.

 

完整例子

DECLARE

   CURSOR user_cur IS SELECT * from user_info;

   user_rec user_info%rowtype;

 

BEGIN

   OPEN user_cur;

   LOOP

      FETCH user_cur into user_rec;--读取游标当前值

      EXIT WHEN user_cur%NOTFOUND;--读取完成时跳出循环

 

      UPDATE user_info set last_accessed 

 

      DBMS_OUTPUT.put_line(user_rec.name);      

   END LOOP;

   CLOSE user_cur;

END;

 

当使用代码块进行INSERT, UPDATE, DELETE, 和SELECT INTO 等操作时，隐式游标被自动使用。它不需要进行打开、关闭、取值等操作，可以通过SQL游标来获取其属性

%ISOPEN

 

Always FALSE since the cursor is opened implicitly and closed immediately after the statement is executed.

 

%FOUND

 

NULL before the statement.

 

TRUE if one or more rows were inserted, updated, or deleted or if only one row was selected.

 

FALSE if no row was selected, updated, inserted, or deleted.

 

%NOTFOUND

 

NULL before the statement.

 

TRUE if no row was selected, updated, inserted, or deleted.

 

FALSE if one or more rows were inserted, updated, or deleted.

 

%ROWCOUNT

 

The number of rows affected by the cursor.

 

%BULK_ROWCOUNT (Oracle8i)

 

A pseudo index-by table containing the numbers of rows affected by the statements executed in bulk bind operations. See the "Bulk Binds (Oracle8i)" section for more information on %BULK_ROWCOUNT.

 

代码例子

BEGIN

   UPDATE activity SET last_accessed := SYSDATE

   WHERE UID = user_id;

 

   IF SQL%NOTFOUND THEN--引用隐式游标的NOTFOUND属性

      INSERT INTO activity_log (uid,last_accessed)

      VALUES (user_id,SYSDATE);

   END IF

END;

 

利用SELECT FOR UPDATE 实现部分锁定游标打开的表

语法

SELECT ...

  FROM ...

   FOR UPDATE [OF column_reference] [NOWAIT];

例子

DECLARE

   CURSOR hounds_in_stock_cur IS

      SELECT pet.stock_no, pet.breeder, dog.size

        FROM dog_breeds dog ,inventory pet

       WHERE dog.breed = pet.breed

         AND dog.class = 'HOUND'

         FOR UPDATE OF pet.stock_no, pet.breeder;

BEGIN

上面的代码只锁定表pet而不会锁定表dog

 

使用WHERE CURRENT OF操作游标当前记录

语法

[UPDATE | DELETE ] ...

   WHERE CURRENT OF cursor_name;

例子

DECLARE

   CURSOR wip_cur IS

      SELECT acct_no, enter_date FROM wip

       WHERE enter_date < SYSDATE -7

         FOR UPDATE;

BEGIN

   FOR wip_rec IN wip_cur

   LOOP

      INSERT INTO acct_log (acct_no, order_date)

         VALUES (wip_rec.acct_no, wip_rec.enter_

            date);

      DELETE FROM wip 

         WHERE CURRENT OF wip_cur;

   END LOOP;

END; 

 

游标变量

OPEN cursor_name FOR select_statement;