PL/SQL 游标

declare : CURSOR cursor_name IS

select_statement ;

open : OPEN cursor_name

if the query returns no rows when the cursor is opened , PL/SQL does not raise an exception . However , you can test the

status of the cursor after a fetch using the SQL%ROWCOUNT cursor attribute .

fetch : FETCH cursor_name INTO [variable1, variable2 ...] | record_name ; 例如 :

LOOP

FETCH emp_cursor INTO v_empno, v_ename;

EXIT WHEN ... ;

-- Process the retrieved data

END LOOP ;

close : CLOSE cursor_name ;

Explicit Cursor Attributes

%ISOPEN Boolean( type )

%NOTFOUND Boolean ( type ) Evaluates to TRUE if the most recent fetch does not return a row

%FOUND Boolean ( type ) Evaluates to TRUE if the most recent fetch returns a row

%ROWCOUNT Number Evaluates to the total number of rows returned so faf

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor ;

END IF ;

Use the %ROWCOUNT cursor attribute to retrieve an exact number of rows .

Use the %NOTFOUND cursor attribute to determine when to exit the loop .

EXIT WHEN emp_cursor%NOTFOUND ORemp_cursor%NOTFOUND IS NULL;

FOR record_name IN cursor_name LOOP

statement 1;

statement 2;

END LOOP ;

record_name is the name of the implicitly declared record .

FOR emp_record( 不用定义 , 自动定义的 ) IN emp_cursor LOOP

IF emp_record.department_id = 80 THEN

... statement 1

END LOOP ;

使用 Subqueries 来实现

FOR emp_record IN ( SELECT last_name , department_id FROM employees ) LOOP

IF emp_record.department_id = 80 THEN

END LOOP ; ( 利用子查询也可以, 这样就不需要定义游标了 )

Cursor with parameters
CURSOR cursor_name
[(parameter_name datatype,...)]
IS
select_statement;
Open cursor_name(parameter_value,...);
example:
DECLARE
CURSOR emp_cursor
(p_deptno NUMBER, p_job VARCHAR2) IS -->不需要指定参数类型大小
SELECT employee_id, last_name
FROM employees
WHERE department_id = p_deptno
AND job_id = p_job;
BEGIN
OPEN emp_cursor(80, 'SA_REP');
...
CLOSE emp_cursor;
OPEN emp_cursor(60, 'IT_PROG');
...
END ;
Cursor 中的 FOR UPDATE
WHERE CURRENT OF cursor; (cursor 必须是FOR UPDATE 上锁的)

例如 :
DECLARE
CURSOR sal_cursor IS
SELECT department_id,last_name,salary
FROM employees
WHERE department_id = 60
FOR UPDATE [ OF salary ] NOWAIT; // OF salary ( salary列名，是可选的 )
BEGIN
FOR emp_record IN sal_cursor
LOOP
IF emp_record.salary < 5000 THEN
UPDATE employee
SET salary = emp_record.salary * 1.1
WHERE CURRENT OF sal_cursor; // 表示当前行的意思
END IF;
END LOOP;
END;

Blog

1.什么是游标

为了处理SQL语句，ORACLE必须分配一片内存区域，这就是上下文区域(context area)。上下文区域包含了完成该处理所必需的信息，其中包括语句要处理的行的数目、一个指向语句被分析后产生的表示形式的指针，以及查询的活动集（active set，这是查询返回的行的集合）。

游标（cursor）就是一个指向上下文区域的句柄（handle）或指针。通过游标，PL/SQL程序可以控制上下文区域和在处理语句时上下文区域会发生些什么事情。

2.显式游标

处理显式游标包括四个PL/SQL步骤

1）声明游标

2）为查询打开游标

3）将结果提取（fetch）到PL/SQL变量中

4）关闭游标

fetch语句有两种形式

1） fetch cursor_name into list_of_variables;

2） fetch cursor_name into PL/SQL_record;

这里cursor_name标识了已经被声明并且被打开的游标，list_of_variables是已经被声明的PL/SQL变量的列表（变量之间用逗号隔开），而PL/SQL_record是已经被声明的PL/SQL记录。

游标的四个属性

1）%FOUND 一个布尔属性。如果前一个FETCH语句返回一个行，那么它就会返回TRUE，否则的话，它会返回FALSE。如果在未打开游标以前就设置了%FOUND，那么会返回ORA-1001（无效的游标）。

2）%NOTFOUND 行为方式和上面的%FOUND正好相反。如果前一个FETCH语句返回一个行，那么%NOTFOUND就会返回FALSE。仅当前一个FETCH语句没有返回任何行，%NOTFOUND才会返回TRUE。

3）%ISOPEN 此布尔属性用来决定相关的游标是否被打开了。如果被打开了则返回TRUE，否则返回FALSE。

4）%ROWCOUNT 此数字属性返回到目前为止由游标返回的行的数目。如果在相关的游标还未打开的时候进行引用，那么会返回ORA-1001错误。

参数化游标

declare    
    
v_department classes.department%type;    
    
v_course classes.course%type;    
    
cursor c_classes is    
    
select * from classes    
    
where department=v_department    
    
and course=v_course;    
    
    
    
--上面这个游标包含了两个变量。我们可以将它修改为一个等价的参数化游标。    
    
    
declare    
    
cursor c_classes(p_department classes.department%type,p_course classes.course%type) is    
    
select * from classes    
    
where department=v_department    
    
and course=v_course;    
    
    
--借助于参数化游标，OPEN语句可以用于将实际数值传递给游标。    
    
open c_classes('HIS',101);

3.隐式游标

说到这个就心痛呀……前天面试的时候问我显示游标和隐式游标的区别是什么，我脑子里只是有个印象而已，具体的概念已经记不清了。我就说显示游标就是有名字的，隐式游标没有名字。。

显示游标用来处理返回多于一行的SELECT语句，我们在前面的章节已经看到这一点了。但是，所有的SQL语句在上下文区域内部都是可执行的，因此都有一个游标指向此上下文区域。此游标就是所谓的“SQL 游标”（SQL CURSOR）。

与显式游标不同的是，SQL游标不被程序打开和关闭。PL/SQL隐含地打开SQL游标，处理其中的SQL语句，然后关闭该游标。

隐式游标用于处理INSERT、UPDATE、DELETE和单行的SELECT...INTO语句。因为SQL游标是通过PL/SQL引擎打开和关闭的，所以OPEN、FETCH和CLOSE命令是无关的。但是游标属性可以被应用于SQL游标。

隐式游标    --例如，下面的块在UPDATE语句没有找到任何行的时候就执行一条INSERT语句。  
      
    begin  
      
    update rooms  
      
    set number_seats=100  
      
    where room_id=99980;  
      
    --如果UPDATE语句没有找到任何行的时候就执行一条INSERT语句  
      
    if SQL%NOTFOUND then  
      
    insert into rooms(room_id,number_seats)  
      
    values(99980,100);  
      
    end if;  
      
    end;

NO_DATA_FOUND和%NOTFOUND

NO_DATA_FOUND异常仅仅被SELECT...INTO语句所触发，当该查询的WHERE子句没有找到任何行的时候就会触发它。当一个显式游标的WHERE子句没有找到行的时候，%NOTFOUND属性就被设置为TRUE。如果UPDATE和DELETE语句的WHERE子句没有找到任何行的时候，SQL%NOTFOUND就被设置为TRUE，而不会触发NO_DATA_FOUND。

4.SELECT FOR UPDATE游标

在多数情况下，提取循环中所完成的处理都会修改由游标检索出来的行。PL/SQL提供了进行这样处理的一种方便语法。

这种方法包含两个部分--在游标声明部分的FOR UPDATE子句和在UPDATE或DELETE语句中的WHERE CURRENT OF子句

1）FOR UPDATE

FOR UPDATE子句是SELECT语句的一部分。它是作为该语句的最后一个子句，在ORDER BY子句（如果有的话）的后面。

语法为：

SELECT...FROM...FOR UPDATE[OF COLUMN_REFERENCE] [NOWAIT]

通常，SELECT操作不会对正在处理的行执行任何锁定设置，这使得连接到该数据库的其他会话可以改变正在选择的数据。但是，结果集仍然是一致性的。当确定了活动集以后，在执行OPEN的时刻，ORACLE会截取下该表的一个快照。在此时刻以前所提交的任何更改操作都会在活动集中反映出来。在此时刻以后所进行的任何更改操作，即使已经提交了它们，都不会被反映出来，除非将该游标重新打开（这会对结果集进行重新求值）。这其实也就是读一致性处理（read-consistency process）。但是，如果使用了FOR UPDATE 子句，那么在OPEN返回以前在活动集的相应行上会加上互斥锁（exclusive lock）。这些锁会避免其他的会话对活动集中的行进行修改，直到整个的事务被提交为止。

如果另一个会话已经对活动集中的行加上了锁，那么SELECT FOR UPDATE操作将等待其他会话释放这些锁以后才能继续进行自己的操作。这种等待是没有超时限制的--SELECT FOR UPDATE将无限期挂起，直到其他会话释放该锁。如果要处理这种情形，就需要使用NOWAIT子句。这时如果这些行被另一个会话锁定，那么OPEN将立即返回，同时会触发ORACLE错误:

ORA-54:resource busy and acquire with NOWAIT specified

在这种情况下，你可能想要稍后重试OPEN或者更改活动集以提取未被锁定的行。

2）WHERE CURRENT OF

如果使用了WHERE CURRENT OF子句声明了游标，那么可以在UPDATE和DELETE语句中使用WHERE CURRENT OF子句。

这个子句的语法是：

WHERE CURRENT OF cursor

这里cursor是使用FOR UPDATE子句声明的游标的名字。WHERE CURRENT OF子句会求值算出刚刚被游标检索出的行。

For update    --这个块将更新所有在HIS 101注册的学生的当前成绩  
    DECLARE  
      -- Number of credits to add to each student's total  
      v_NumCredits  classes.num_credits%TYPE;  
      
      -- This cursor will select only those students who are currently  
      -- registered for HIS 101.  
      CURSOR c_RegisteredStudents IS  
        SELECT *  
          FROM students  
          WHERE id IN (SELECT student_id  
                         FROM registered_students  
                         WHERE department= 'HIS'  
                         AND course = 101)  
          FOR UPDATE OF current_credits;  
      
    BEGIN  
      -- Set up the cursor fetch loop.  
      FOR v_StudentInfo IN c_RegisteredStudents LOOP  
      -- Determine the number of credits for HIS 101.  
      SELECT num_credits  
        INTO v_NumCredits  
        FROM classes  
        WHERE department = 'HIS'  
        AND course = 101;  
      
      -- Update the row we just retrieved from the cursor.  
      UPDATE students  
        SET current_credits = current_credits + v_NumCredits  
        WHERE CURRENT OF c_RegisteredStudents;  
      END LOOP;  
      
      -- Commit our work.  
      COMMIT;  
    END;  
    /

请注意，UPDATE语句仅仅更新在游标声明的FOR UPDATE子句处列出的列。如果没有列出任何列，那么所有的列都可以被更新。

3）COMMIT和提取操作

我们可以注意到，在上面的例子中COMMIT是在提取循环完成以后完成的，因为COMMIT会释放由该会话持有的所有锁。因为FOR UPDATE 子句获得了锁，所以COMMIT将释放这些锁。当锁被释放的时候，该游标就无效了。所有后继的操作都将返回ORACLE错误。

ORA-1002 : fetch out of sequence

error    --这个例子就将引发这个错误  
    DECLARE  
      -- Cursor to retrieve all students, and lock the rows as well.  
      CURSOR c_AllStudents IS  
        SELECT *  
          FROM students  
          FOR UPDATE;  
      
      -- Variable for retrieved data.  
      v_StudentInfo  c_AllStudents%ROWTYPE;  
    BEGIN  
      -- Open the cursor. This will acquire the locks.  
      OPEN c_AllStudents;  
      
      -- Retrieve the first record.  
      FETCH c_AllStudents INTO v_StudentInfo;  
      
      -- Issue a COMMIT. This will release the locks, invalidating the  
      -- cursor.  
      COMMIT WORK;  
      
      -- This FETCH will raise the ORA-1002 error.  
      FETCH c_AllStudents INTO v_StudentInfo;  
    END;  
    /

这样，如果再SELECT FOR UPDATE提取循环中有一个COMMIT语句，在COMMIT语句后面的任何提取操作都将是无效的。所以我们不推荐在循环内部使用COMMIT语句。如果游标没有被定义为一个SELECT FOR UPDATE，就不会发生这个问题。

当然，如果你非要更新刚刚从游标中提取出来的行并且在提取循环内部使用COMMIT，该如何做呢？WHERE CURRENT OF不能用，因为游标不能使用FOR UPDATE子句进行定义。但是，你可以在UPDATE的WHERE子句中使用表的主键。如下面这个例子所示

    DECLARE  
      -- Number of credits to add to each student's total  
      v_NumCredits  classes.num_credits%TYPE;  
      
      -- This cursor will select only those students who are currently  
      -- registered for HIS 101.  
      CURSOR c_RegisteredStudents IS  
        SELECT *  
          FROM students  
          WHERE id IN (SELECT student_id  
                         FROM registered_students  
                         WHERE department= 'HIS'  
                         AND course = 101);  
      
    BEGIN  
      -- Set up the cursor fetch loop.  
      FOR v_StudentInfo IN c_RegisteredStudents LOOP  
      -- Determine the number of credits for HIS 101.  
      SELECT num_credits  
        INTO v_NumCredits  
        FROM classes  
        WHERE department = 'HIS'  
        AND course = 101;  
      
      -- Update the row we just retrieved from the cursor.  
      UPDATE students  
        SET current_credits = current_credits + v_NumCredits  
        WHERE id = v_Studentinfo.id;  
      
      -- We can commit inside the loop, since the cursor is  
      -- not declared FOR UPDATE.  
      COMMIT;  
      END LOOP;  
    END;  
    /

这个例子基本上模拟了WHERE CURRENT OF子句，但是没有在活动集的行上创建锁。

5.游标变量

至此我们碰到的所有显式游标都是静态游标（static cursor）--该游标与一个SQL语句相关联，并且在编译该块的时候此语句已经是可知的了。另一方面，游标变量可以再运行时刻与不同的SQL语句相关联。

游标变量是一种引用类型。定义一个游标变量类型的语法如下：

type type_name is ref cursor return return_type

这里type_name 是新的引用类型的名字，return_type是一个记录类型，它指明了最终由游标变量返回的选择列表的类型。

游标变量的返回类型必须是一个记录类型。它可以被显式声明为一个用户定义的记录，或者隐式使用%ROWTYPE进行声明。

受限和不受限游标变量

在前面介绍的游标是受限的--它们仅被声明为特定的返回类型。当稍后打开该变量时，必须为特定的查询打开它，使得该查询的选择列表匹配游标的返回类型，否则，会触发预定义错误ROWTYPE_MISMATCH。

而非受限的游标变量没有必要拥有RETURN子句，稍后打开一个非受限游标变量时，它可以为任何查询打开。

--非受限游标的例子  
  
CREATE OR REPLACE PROCEDURE ShowCursorVariable  
  /* Demonstrates the use of a cursor variable on the server. 
     If p_Table is 'classes', then information from the classes 
     table is inserted into temp_table.  If p_Table is 'rooms' 
     then information from rooms is inserted. */  
  (p_Table IN VARCHAR2) AS  
  
  /* Define the cursor variable type */  
  TYPE t_ClassesRooms IS REF CURSOR;  
  
  /* and the variable itself. */  
  v_CursorVar t_ClassesRooms;  
  
  /* Variables to hold the output. */  
  v_Department  classes.department%TYPE;  
  v_Course      classes.course%TYPE;  
  v_RoomID      rooms.room_id%TYPE;  
  v_Description rooms.description%TYPE;  
BEGIN  
  -- Based on the input parameter, open the cursor variable.  
  IF p_Table = 'classes' THEN  
    OPEN v_CursorVar FOR  
      SELECT department, course  
        FROM classes;  
  ELSIF p_table = 'rooms' THEN  
    OPEN v_CursorVar FOR  
      SELECT room_id, description  
        FROM rooms;  
  ELSE  
    /* Wrong value passed as input - raise an error */  
    RAISE_APPLICATION_ERROR(-20000,  
      'Input must be ''classes'' or ''rooms''');  
  END IF;  
  
  /* Fetch loop.  Note the EXIT WHEN clause after the FETCH - 
     with PL/SQL 2.3 we can use cursor attributes with cursor 
     variables. */  
  LOOP  
    IF p_Table = 'classes' THEN  
      FETCH v_CursorVar INTO  
        v_Department, v_Course;  
      EXIT WHEN v_CursorVar%NOTFOUND;  
  
      INSERT INTO temp_table (num_col, char_col)  
        VALUES (v_Course, v_Department);  
    ELSE  
      FETCH v_CursorVar INTO  
        v_RoomID, v_Description;  
      EXIT WHEN v_CursorVAR%NOTFOUND;  
  
      INSERT INTO temp_table (num_col, char_col)  
        VALUES (v_RoomID, SUBSTR(v_Description, 1, 60));  
    END IF;  
  END LOOP;  
  
  /* Close the cursor. */  
  CLOSE v_CursorVar;  
  
  COMMIT;  
END ShowCursorVariable;  
/