2.ORACLE游标的使用

游标的概念

为了处理 SQL 语句，ORACLE 必须分配一片叫上下文( context area )的区域来处理所必需的信息，其中包括要处理的行的数目，一个指向语句被分析以后的表示形式的指针以及查询的活动集(active set)。

游标是一个指向上下文的句柄( handle)或指针。

对于不同的 SQL 语句，游标的使用情况不同：

SQL 语句	游标类型
非查询语句（UPDATE、DELETE）	隐式游标
结果是单行的查询语句	隐式或显示
结果是多行的查询语句	显示游标

游标属性类型

游标属性	作用
%FOUND	布尔型属性，当最近一次读记录时成功返回,则值为 TRUE
%NOTFOUND	布尔型属性，与%FOUND 相反
%ISOPEN	布尔型属性，当游标已打开时返回 TRUE
%ROWCOUNT	数字型属性，返回已从游标中读取的记录数

游标的用法

显示游标（非FOR循环）

1. 定义游标（声明）
   就是定义一个游标名，以及与其相对应的 SELECT 语句。
   格式：CURSOR cursor_name[(parameter[, parameter]…)] IS select_statement;
   游标参数只能为输入参数，在指定数据类型时，不能使用长度约束。如 NUMBER(4)、CHAR(10) 等都是错误的。

2. 打开游标（传参）
   打开游标时，执行游标所对应的 SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。
   
   如果游标查询语句中带有 FOR UPDATE 选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。
   
   格式：OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];
   在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示。

3. 提取游标数据（调用）
   检索结果集合中的数据行，放入指定的输出变量中。
   
   格式：FETCH cursor_name INTO {variable_list | record_variable };

4. 关闭游标（结束）
   当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用 FETCH 语句取其中数据。
   
   关闭后的游标可以使用 OPEN 语句重新打开。
   
   格式：CLOSE cursor_name;

DECLARE
  --声明一个记录类型，相当于JAVA的类
  TYPE EMP_RECORD IS RECORD(
    --薪资
    V_SALARY      EMPLOYEES.SALARY % TYPE,
    --员工名称
    V_EMPLOYEE_NAME EMPLOYEES.FIRST_NAME % TYPE,
    --入职日期
    V_HIRE_DATE   EMPLOYEES.HIRE_DATE % TYPE);
    
  -- 用记录类型声明一个变量，相当于JAVA用类声明一个对象
  V_EMP_RECORD EMP_RECORD;
  
  -- 1.定义游标，并指定形参，参数类型不能指定长度，可以指定默认值，指定了默认值位置的参数可以不传
  CURSOR EMP_CURSOR(D_ID NUMBER DEFAULT 90, D_NAME VARCHAR2) IS SELECT T.SALARY, T.FIRST_NAME, T.HIRE_DATE FROM EMPLOYEES T WHERE T.DEPARTMENT_ID = D_ID AND T.FIRST_NAME LIKE '%'||D_NAME||'%';
BEGIN
  --2.打开游标，执行游标所对应的 SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合
  --  打开游标的同时传参，实参的个数和类型要和定义游标的形参个数和类型一一对应
  OPEN EMP_CURSOR(90, 'N');
  -- 指定了默认值的形参可以不传，但必须使用类似这种格式来指明给哪个参数传参数
  --OPEN EMP_CURSOR(D_NAME => 'N');
  
  --3.提取游标数据到记录类型变量，记录类型的字段类型要和游标对应的查询语句字段类型一一对应
  FETCH EMP_CURSOR INTO V_EMP_RECORD;
  
  --3.1遍历提取获取游标数据，格式：游标变量%FOUND，相当于JAVA的迭代器方法hasNext，表示工作区中是否还有下一行数据
  WHILE EMP_CURSOR%FOUND LOOP
    --输出打印工作区当前行数据
    dbms_output.put_line('员工' || V_EMP_RECORD.V_EMPLOYEE_NAME || '薪资为' || V_EMP_RECORD.V_SALARY || ', ' || V_EMP_RECORD.V_HIRE_DATE || '入职');
    --3.2使游标指向工作区下一行数据，并将下一行数据赋值给V_EMP_RECORD记录类型变量，相当于JAVA迭代器的next方法
    FETCH EMP_CURSOR INTO V_EMP_RECORD;
  END LOOP;
  
  --4.关闭游标以释放该游标所占用的系统资源，并使该游标的工作区变成无效
  CLOSE EMP_CURSOR;
END;
/

显示游标（FOR循环）

ORACLE语言提供了游标 FOR 循环语句，自动执行游标的 OPEN、FETCH、CLOSE 语句和循环语句的功能；

当进入循环时，游标 FOR 循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标 FOR 循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。

格式：
FOR index_variable IN cursor_name[value[, value]…] LOOP
– 游标数据处理代码
END LOOP;

index_variable 为游标 FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结果集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据。

index_variable 中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标 FOR 循环语句中的索引变量来访问这些列数据。

DECLARE
  -- 1.定义游标，并指定形参，参数类型不能指定长度
  CURSOR EMP_CURSOR(D_ID NUMBER DEFAULT 90, D_NAME VARCHAR2) IS SELECT T.SALARY, T.FIRST_NAME, T.HIRE_DATE FROM EMPLOYEES T WHERE T.DEPARTMENT_ID = D_ID AND T.FIRST_NAME LIKE '%'||D_NAME||'%';
BEGIN
  --V_C为隐含声明记录类型变量，其结构与游标查询语句返回的结果集合的结构相同
  FOR V_C IN EMP_CURSOR(D_NAME => 'N') LOOP
    --通过“记录类型变量.列名”方式来访问数据，如果是计算列，如COUNT(1)等，必须指定别名，通过“记录类型变量.别名”来访问数据
    --输出打印工作区当前行数据
    dbms_output.put_line('员工' || V_C.FIRST_NAME || '薪资为' || V_C.SALARY || ', ' || V_C.HIRE_DATE || '入职');
    END LOOP;
END;
/

由此可知，以上两个案例皆实现了相同的功能，但是方式2使用FOR循环的方式明显节省了很多篇幅。

初此之外，ORACLE还允许在游标 FOR 循环语句中使用子查询来实现游标的功能，个人不推荐使用这种方式，因为这查询语句的WHERE限定词语句只能使用连接符来拼接条件，会存在解析SQL语句性能，并且可读性也不高。

DECLARE
  -- 定义游标，并指定形参，参数类型不能指定长度
  --CURSOR EMP_CURSOR(D_ID NUMBER DEFAULT 90, D_NAME VARCHAR2) IS SELECT T.SALARY, T.FIRST_NAME, T.HIRE_DATE FROM EMPLOYEES T WHERE T.DEPARTMENT_ID = D_ID AND T.FIRST_NAME LIKE '%'||D_NAME||'%';
BEGIN
  --使用子查询来替代游标变量
  FOR V_C IN (SELECT T.SALARY, T.FIRST_NAME, T.HIRE_DATE FROM EMPLOYEES T WHERE T.DEPARTMENT_ID = 90 AND T.FIRST_NAME LIKE '%N%') LOOP
    dbms_output.put_line('员工' || V_C.FIRST_NAME || '薪资为' || V_C.SALARY || ', ' || V_C.HIRE_DATE || '入职');
    END LOOP;
END;
/

隐式游标

显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如UPDATE、DELETE操作，则由 ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为 SQL，这是由 ORACLE 系统定义的。

对于隐式游标的操作，如定义、打开、取值及关闭操作，都由 ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条 SQL 语句所包含的数据。

DECLARE
  V_EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE := 100;
BEGIN
  UPDATE EMPLOYEES T SET T.FIRST_NAME = 'BOB' WHERE T.EMPLOYEE_ID = V_EMPLOYEE_ID;
  --隐式游标的名字为 SQL，存放的为最新处理的一条 SQL 语句所包含的数据
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.put_line('更新失败，查无此人');
  END IF;
END;
/

关于 NO_DATA_FOUND 和 %NOTFOUND 的区别

1. SELECT … INTO 语句未找到数据时触发 NO_DATA_FOUND异常；
2. 当一个显式游标的 WHERE 子句未找到时触发%NOTFOUND； 当 UPDATE 或 DELETE 语句的 WHERE 子句未找到时触发 SQL%NOTFOUND；
3. 在提取循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件，不要用 NO_DATA_FOUND。

游标修改和删除操作

• 游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。这时，要求游标查询语句中必须使用 FOR UPDATE 选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列。
• 为了对正在处理(查询)的行不被另外的用户改动，ORACLE 提供一个 FOR UPDATE 子句来对所选择的行进行锁住。该需求迫使 ORACLE 锁定游标结果集合的行，可以防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退为止。
• 如果另一个会话已对活动集中的行加了锁，那么 SELECT FOR UPDATE 操作一直等待到其它的会话释放这些锁后才继续自己的操作，对于这种情况，当加上 NOWAIT 子句时，如果这些行真的被另一个会话锁定，则 OPEN 立即返回并抛出异常：ORA-0054 ：resource busy and acquire with nowait specified.

新开一个窗口，修改DEPARTMENT_ID为90的数据，但不提交事务。

此时另外一个游标使用SELECT UPDATE NOWAIT方式处理DEPARTMENT_ID为90的数据，由于不等待，则立马抛出ORA-0054异常。

如果去掉NOWAIT关键字，则会一直等待另外一个事务处理完毕。