Oracle数据库之PL/SQL游标

1. 游标概念

字面意思是游动的光标，是指向上下文区域的句柄或指针。

在PL/SQL块中执行CRUD操作时，ORACLE会在内存中为其分配上下文区。用数据库语言来描述游标就是：映射在上下文区结果集中一行数据上的位置实体。

用户可以使用游标访问结果集中的任意一行数据，将游标指向某行后，即可对该行数据进行操作。游标为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。

在每个用户会话中，可以同时打开多个游标，其最大数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。

游标可分为显式游标和隐式游标两类。

2. 显式游标

显式游标使用主要有四个步骤：

1. 声明/定义游标
2. 打开游标
3. 读取数据
4. 关闭游标

2.1 声明/定义游标

语法：

CURSOR cursor_name
  [(parameter_dec [, parameter_dec ]…)]
    [RETURN datatype]
      IS
        select_statement;

示例：

DECLARE
  CURSOR c1 RETURN departments%ROWTYPE;    -- 声明C1游标

  CURSOR c2 IS                             -- 声明C2游标并定义
    SELECT employee_id, job_id, salary FROM employees
    WHERE salary > 2000; 

  CURSOR c1 RETURN departments%ROWTYPE IS  -- 定义C1游标
    SELECT * FROM departments
    WHERE department_id = 110;

  CURSOR c3 RETURN locations%ROWTYPE;      -- 声明C3游标

  CURSOR c3 IS                             -- 定义C3游标
    SELECT * FROM locations
    WHERE country_id = 'JP';

  CURSOR c4(sal number) IS                             -- 声明C4游标并定义
    SELECT employee_id, job_id, salary FROM employees
    WHERE salary > sal; 
BEGIN
  NULL;
END;

说明：

在指定参数数据类型时，不能使用长度约束，如C4游标的参数，不能写为number(10,4)这种结构。

[RETURN datatype]是可选的，表示游标返回数据的数据。如果选择，则应该严格与select_statement中的选择列表在次序和数据类型上匹配。一般是记录数据类型（RECORD）或带“%ROWTYPE”的数据。

2.2 打开游标

执行游标所对应的SELECT语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集。

语法：

OPEN cursor_name [ ( cursor_parameter [ [,] actual_cursor_parameter ]... ) ] 

示例：

OPEN c4 (1300);

2.3 读取数据

检索结果集合中的数据行，放入指定的输出变量中。

语法：

FETCH { cursor | cursor_variable | :host_cursor_variable }
  { into_clause | bulk_collect_into_clause [ LIMIT numeric_expression ] } ;

执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。当检索到最后一行数据时，如果再次执行FETCH语句，将操作失败，并将游标属性%NOTFOUND置为TRUE。所以每次执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了值。

示例：

fetch c4 into eid, jid, sal;

2.4 关闭游标

当处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源。

关闭游标后不能再使用FETCH语句获取其中数据。关闭后的游标可以使用OPEN语句重新打开。

语法：

CLOSE cursor_name;

完整示例1：

DECLARE
   -- 定义游标
   CURSOR c_cursor IS 
   SELECT first_name || last_name, Salary FROM EMPLOYEES WHERE rownum<11;   

   -- 声明变量
   v_ename  EMPLOYEES.first_name%TYPE;
   v_sal    EMPLOYEES.Salary%TYPE;   
BEGIN
  -- 打开游标
  OPEN c_cursor;
  -- 获取数据
  FETCH c_cursor INTO v_ename, v_sal;
  -- 处理数据
  WHILE c_cursor%FOUND LOOP
     DBMS_OUTPUT.PUT_LINE(v_ename||'---'||to_char(v_sal) );
     FETCH c_cursor INTO v_ename, v_sal;
  END LOOP;
  -- 关闭游标
  CLOSE c_cursor;
END;

完整示例2：

DECLARE
   -- 定义RECORD记录类型
   TYPE emp_record_type IS RECORD(
        f_name   employees.first_name%TYPE,
        h_date   employees.hire_date%TYPE);
   -- 声明记录变量
   v_emp_record EMP_RECORD_TYPE;
   -- 定义游标，有参数与返回值
   CURSOR c3(dept_id NUMBER, j_id VARCHAR2) 
      RETURN EMP_RECORD_TYPE
   IS
      SELECT first_name, hire_date FROM employees 
      WHERE department_id = dept_id AND job_id = j_id;

BEGIN
   -- 打开游标，传递参数值
   OPEN c3(j_id => 'AD_VP', dept_id => 90); 
   LOOP
      FETCH c3 INTO v_emp_record;    -- 获取数据
      IF c3%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是'||v_emp_record.h_date);
      ELSE
         DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
         EXIT; -- 处理完则退出循环
      END IF;
   END LOOP;
   CLOSE c3;   --关闭游标
END;

3. 显式游标属性

游标的状态（如是否打开，获取了多少行数据等）可以使用游标属性来获取。

游标属性以“%属性名”的形式加在游标名之后。显式游标属性有：

属性名	说明
%FOUND	如果记录成功获取，返回TRUE，否则返回FALSE
%NOTFOUND	如果记录获取失败，返回TRUE，否则返回FALSE
%ROWCOUNT	返回已经从游标中获取的记录数
%ISOPEN	如果游标是打开的，返回TRUE，否则返回FALSE

示例：

DECLARE
   v_empno  EMPLOYEES.EMPLOYEE_ID%TYPE;
   v_sal      EMPLOYEES.Salary%TYPE;
   -- 定义游标
   CURSOR c_cursor IS SELECT EMPLOYEE_ID, Salary FROM EMPLOYEES; 
BEGIN
   -- 打开游标
   OPEN c_cursor;
   LOOP
      -- 获取数据
      FETCH c_cursor INTO v_empno, v_sal;
      EXIT WHEN c_cursor%NOTFOUND;  -- 未读取到记录，则退出循环
      IF v_sal<=1200 THEN
            UPDATE EMPLOYEES SET Salary=Salary+50 WHERE EMPLOYEE_ID=v_empno;
            DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!');
      END IF;
      DBMS_OUTPUT.PUT_LINE('记录数:'|| c_cursor %ROWCOUNT);
   END LOOP;
   -- 关闭游标
   CLOSE c_cursor;
END;

4. 基于游标定义记录变量

使用%ROWTYPE属性不仅可以基于表和视图定义记录变量，也可以基于游标定义记录变量。当基于游标定义记录变量时，记录成员名实际就是SELECT语句的列名和列别名。

为了简化显式游标的数据处理，建议使用基于游标的记录变量存放游标数据。基于游标定义记录变量，比声明记录类型变量要方便，不容易出错。

示例：

DECLARE 
   -- 定义游标
   CURSOR emp_cursor IS SELECT ename,sal FROM emp；
   emp_reocrd emp_cursor%ROWTYPE；-- 游标变量
BEGIN
   -- 打开游标
   OPEN emp_cursor；
   LOOP
      -- 获取记录
      FETCH emp_cursor INTO emp_record；
      EXIT WHEN emp_record%NOTFOUND；
      dbms_ouput.put_line('雇员名:'||emp_record.ename||',雇员工资:'||emp_record.sal)；
   END LOOP；
   -- 关闭游标
   CLOSE emp_cursor；
END；

5. 隐式游标

如果在PL/SQL块中使用了SELECT语句进行操作，PL/SQL会隐含处理游标定义，而对于非查询语句，如修改、删除操作，则由ORACLE系统自动地为这些操作设置游标并创建其工作区。由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL。

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

隐式游标的属性：

属性名	说明
SQL%FOUND	如果记录成功获取，返回TRUE，否则返回FALSE
SQL%NOTFOUND	如果记录获取失败，返回TRUE，否则返回FALSE
SQL%ROWCOUNT	返回已经从游标中获取的记录数
SQL%ISOPEN	如果游标是打开的，返回TRUE，否则返回FALSE

隐式游标在INSERT，UPDATE，DELETE，SELECT语句中不必明确定义游标。

示例：

DECLARE
   v_rows NUMBER;
BEGIN
   -- 更新表数据
   UPDATE employees SET salary = 5000 WHERE department_id = 90 AND job_id = 'AD_VP';
   -- 获取受影响行数
   v_rows := SQL%ROWCOUNT;
   DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个员工的工资');
END;

6. 游标FOR循环

游标FOR循环和显示游标的一种快捷使用方式，它使用FOR循环依次读取结果集中的行数据，当FOR循环开始时，游标自动打开（不需要OPEN）,每循环一次系统自动读取游标当前行的数据（不需要FETCH)，当退出FOR循环时，游标被自动关闭（不需要使用CLOSE）使用游标FOR循环的时候不能使用OPEN语句，FETCH语句和CLOSE语句，否则会产生错误。

语法：

FOR index_variable IN cursor_name[(value[, value]…)] LOOP
    -- 游标处理语句
END LOOP;

示例：

DECLARE
    CURSOR emp_cur(vartype number) IS
        SELECT emp_no,emp_zc FROM cus_emp_basic WHERE com_no=vartype;
BEGIN
    FOR person IN emp_cur(123) LOOP
        DBMS_OUTPUT.PUT_LINE('编号:'||person.emp_no||',地址:'||person.emp_zc);
END LOOP;
END;

7. 使用显示游标修改数据

在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行。显式游标只有在需要获得多行数据的情况下使用。PL/SQL提供了仅仅使用游标就可以执行删除或更新记录的方法。

UPDATE或DELETE语句中的WHERE CURRENT OF子句专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。要使用这个方法，在声明游标时必须使用FOR UPDATE子句，当使用FOR UPDATE子句打开一个游标时，所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，不能进行UPDATE、DELETE或SELECT…FOR UPDATE操作。

语法：

FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..
[NOWAIT]

在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。当加上NOWAIT子句时，如果这些行真的被另一个会话锁定，则OPEN立即返回并给出：

ORA-00054 ：resource busy  and  acquire with nowait specified.

在UPDATE和DELETE中使用WHERE CURRENT OF子串的语法如下：

WHERE{CURRENT OF cursor_name|search_condition}

示例：

DELCARE
    CURSOR c1 IS 
        SELECT empno,salary FROM emp
        WHERE comm IS NULL
        FOR UPDATE OF comm;
    v_comm NUMBER(10,2);
BEGIN
    FOR r1 IN c1 LOOP
        IF r1.salary<500 THEN
            v_comm:=r1.salary*0.25;
　　      ELSEIF r1.salary<1000 THEN
            v_comm:=r1.salary*0.20;
　　      ELSEIF r1.salary<3000 THEN
            v_comm:=r1.salary*0.15;
        ELSE
            v_comm:=r1.salary*0.12;
        END IF;
        UPDATE emp SET comm=v_comm WHERE CURRENT OF c1;
    END LOOP;
END

8. 游标变量

与游标类似，游标变量指向多行查询的结果集的当前行。但是，游标与游标变量是不同的，就像常量和变量的关系一样。游标是静态的，游标变量是动态的，因为它不与特定的查询绑定在一起。

8.1 声明游标变量

语法：

TYPE ref_type_name IS REF CURSOR
    [ RETURN return_type];

说明：

游标变量类型有强类型定义和弱类型定义两种。强类型定义必须指定游标变量的返回值类型，而弱类型定义则不说明返回值类型。

return_type为游标变量的返回值类型，它必须为记录变量。

示例：

-- 定义一个REF CURSOU类型
TYPE ref_cursor_type IS REF CURSOR;
-- 声明一个游标变量
cv_ref REF_CURSOR_TYPE;

8.2 游标变量的使用

与游标一样，游标变量操作也包括打开、提取和关闭三个步骤。

8.2.1 打开游标变量

语法：

OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;

说明：

host_cursor_variable_name为PL/SQL主机环境（如OCI: ORACLE Call Interface，Pro*c 程序等）中声明的游标变量。

OPEN…FOR 语句可以在关闭当前的游标变量之前重新打开游标变量，而不会导致CURSOR_ALREAD_OPEN异常错误。新打开游标变量时，前一个查询的内存处理区将被释放。

8.2.2 提取数据

语法：

FETCH {cursor_variable_name | :host_cursor_variable_name}
INTO {variable [, variable]…| record_variable};

说明：

将提取到的数据放入普通变量和记录变量中存放。

8.2.3 关闭游标

语法：

CLOSE {cursor_variable_name | :host_cursor_variable_name}

说明：

如果应用程序试图关闭一个未打开的游标变量，则将导致INVALID_CURSOR异常错误。

示例1：

DECLARE
TYPE ref_type_table IS REF CURSOR;
v_cursor            ref_type_table;
emp_record          emp%rowtype;
BEGIN
     OPEN v_cursor FOR select * from emp where deptno=&no;
     LOOP
         FETCH v_cursor INTO emp_record;
         EXIT WHEN v_cursor%NOTFOUND;
         dbms_output.put_line('员工号：'||emp_record.ename||'部门号：'||emp_record.deptno);
     END LOOP;
     CLOSE v_cursor;
END;

示例2：

DECLARE
    emp_record          emp%rowtype;
    TYPE ref_type_table IS REF CURSOR RETURN emp%rowtype;
    v_cursor            ref_type_table;
BEGIN
     OPEN v_cursor FOR select * from emp where deptno=&no;
     LOOP
         FETCH v_cursor INTO emp_record;
         EXIT WHEN v_cursor%NOTFOUND;
         dbms_output.put_line('员工号：'||emp_record.ename||'部门号：'||emp_record.deptno);
     END LOOP;
     CLOSE v_cursor;
END;

DECLARE
Type emp_record_type IS RECORD(
       ename emp.ename%TYPE,
       salary emp.sal%TYPE,
       deptno emp.deptno%TYPE);
emp_record emp_record_type;

TYPE ref_type_table IS REF CURSOR RETURN emp_record_type;
v_cursor            ref_type_table;
BEGIN
     OPEN v_cursor FOR select ename,sal,deptno from emp where deptno=&no;
     LOOP
         FETCH v_cursor INTO emp_record;
         EXIT WHEN v_cursor%NOTFOUND;
         dbms_output.put_line('员工号：'||emp_record.ename||'，部门号：'||emp_record.deptno||'，工资：'||emp_record.salary);
     END LOOP;
     CLOSE v_cursor;
END;

9. 使用游标批量获取

语法：

FETCH ... BULK COLLECT INTO ...[LIMIT row_number];

说明：

使用BULK COLLECT，我们可以用对数据库的一个来回，返回多行数据。BULK COLLECT减少了PL/SQL和SQL引擎之间的上下文开关数目，因而加速了数据获取的速度。

示例：

DECLARE
     CURSOR emp_cursor(v_deptno number) IS SELECT * FROM EMP WHERE deptno = v_deptno;
     TYPE type_emp_table IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
     emp_table type_emp_table;
     v_dno emp.deptno%TYPE;
BEGIN
     v_dno := &no;
     OPEN emp_cursor(v_dno);
     FETCH emp_cursor BULK COLLECT INTO emp_table;
     CLOSE emp_cursor;
     FOR i IN 1..emp_table.COUNT LOOP
         dbms_output.put_line('员工号：'||emp_table(i).ename||'工资：'||emp_table(i).sal);
     END LOOP;
     CLOSE emp_cursor;
END;

10. 游标表达式

游标表达式作用是用于返回嵌套游标。语法：

CURSOR(sub_query)

示例：

DECLARE
       CURSOR dept_emp_cursor(v_deptno number) IS 
   SELECT dname,cursor(SELECT * FROM emp e WHERE e.deptno = d.deptno) 
   FROM dept d WHERE deptno = v_deptno;
       TYPE emp_cursor_type IS REF CURSOR;
       emp_cursor emp_cursor_type;
       emp_record emp%ROWTYPE;
       v_name dept.dname%TYPE;
       v_dno emp.deptno%TYPE;
BEGIN
     v_dno := &no;
     OPEN dept_emp_cursor(v_dno);
     loop 
          FETCH dept_emp_cursor INTO v_name,emp_cursor;
          EXIT WHEN dept_emp_cursor%NOTFOUND;
          dbms_output.put_line('部门名称：'||v_name);
          LOOP
              FETCH emp_cursor INTO emp_record;            
              EXIT WHEN emp_cursor%NOTFOUND;
              dbms_output.put_line('员工名称：'||emp_record.ename||'，工资：'||emp_record.sal);
          END LOOP;
     end loop;
     CLOSE dept_emp_cursor;
END;