PL/SQL Cursor Example

      SQL语言是面向集合的，其结果一般是集合量（多条记录），而PL/SQL语言的变量一般是标量，其一组变量只能存放一条记录。所以仅仅使用变量并不能完全满足SQL语句向应用程序输出数据的要求。因为查询结果的记录数是不确定的，事先也就不知道要声明几个变量。为此，在PL/SQL中引入了游标（cursor）的概念，用游标来协调这两种不同的处理方式。

      在PL/SQL块中执行select, insert, update, delete语句时，Oracle会在内存中为其分配上下文区（Context Area），即一个缓存区。游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。它为应用程序提供了一种对具有多行数据的查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入了SQL语句的应用程序的常用编程方式。

      游标分为显式游标、隐式游标两种。隐式游标是Oracle为所有数据语句（包括只返回单行数据的查询语句）自动声明和操作的一种游标；显式游标是由用户声明和操作的一种游标。

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

 

显式游标

无论是显式游标、隐式游标，均有%ISOPEN、%FOUND、%NOTFOUND、%ROWCOUNT四种属性。它们描述与游标操作相关的DML语句的执行情况。游标属性只能用在PL/SQL的流程控制语句内，而不能用在SQL语句内。

 

--example 1 : 声明一个没有参数没有返回值的游标c1

DECLARE
   v_ename   emp.ename%TYPE;
   v_job     emp.job%TYPE;

   CURSOR c1
   IS
      SELECT ename, job
        FROM emp
       WHERE deptno = 20;
BEGIN
   OPEN c1;

   LOOP
      FETCH c1
      INTO v_ename, v_job;

      IF c1%FOUND
      THEN
         DBMS_OUTPUT.put_line (v_ename || '的岗位是' || v_job);
      ELSE
         DBMS_OUTPUT.put_line ('已经处理完结果集了');
         EXIT;
      END IF;
   END LOOP;

   CLOSE c1;
END;
/

 

--example 2 : 声明一个有参数没有返回值的游标c2

DECLARE
   v_ename      emp.ename%TYPE;
   v_hiredate   emp.hiredate%TYPE;

   CURSOR c2 (c_deptno NUMBER, c_job VARCHAR2)
   IS
      SELECT ename, hiredate
        FROM emp
       WHERE deptno = c_deptno AND job = c_job;
BEGIN
   OPEN c2 (20, 'MANAGER');

   LOOP
      FETCH c2
      INTO v_ename, v_hiredate;

      IF c2%FOUND
      THEN
         DBMS_OUTPUT.put_line (v_ename || '的雇佣日期是 ' || v_hiredate);
      ELSE
         DBMS_OUTPUT.put_line ('已经处理完结果集了');
         EXIT;
      END IF;
   END LOOP;

   CLOSE c2;
END;
/

 

--example 3 : 声明一个有参数有返回值的游标c3

DECLARE
   TYPE emp_record_type IS RECORD
   (
      ename      emp.ename%TYPE,
      hiredate   emp.hiredate%TYPE
   );

   v_emp_record   emp_record_type;

   CURSOR c3 (c_deptno NUMBER, c_job VARCHAR2)
      RETURN emp_record_type
   IS
      SELECT ename, hiredate
        FROM emp
       WHERE deptno = c_deptno AND job = c_job;
BEGIN
   OPEN c3 (c_job => 'MANAGER', c_deptno => 20);

   --OPEN c3 (c_deptno => 20, c_job => 'MANAGER');
   --OPEN c3 (20,  'MANAGER');

   LOOP
      FETCH c3 INTO v_emp_record;

      IF c3%FOUND
      THEN
         DBMS_OUTPUT.put_line (
               v_emp_record.ename
            || '的雇佣日期是'
            || v_emp_record.hiredate);
      ELSE
         DBMS_OUTPUT.put_line ('已经处理完结果集了');
         EXIT;
      END IF;
   END LOOP;

   CLOSE c3;
END;
/

 

--example 4 : 声明一个有参数没有返回值的游标c4，使用%ROWTYPE属性不仅可以基于表定义记录变量，也可以基于游标定义记录变量（这比声明记录类型变量要方便，不容易出错）

DECLARE
   CURSOR c4 (c_deptno NUMBER, c_job VARCHAR2)
   IS
      SELECT ename f_ename, hiredate
        FROM emp
       WHERE deptno = c_deptno AND job = c_job;

   v_emp_record   c4%ROWTYPE;
BEGIN
   OPEN c4 (20, 'ANALYST');

   LOOP
      FETCH c4 INTO v_emp_record;

      IF c4%FOUND
      THEN
         DBMS_OUTPUT.put_line (
               v_emp_record.f_ename
            || '的雇佣日期是'
            || v_emp_record.hiredate);
      ELSE
         DBMS_OUTPUT.put_line ('已经处理完结果集了');
         EXIT;
      END IF;
   END LOOP;

   CLOSE c4;
END;
/

 

 

隐式游标

隐式游标是由PL/SQL控制的。当执行一条DML语句或SELECT...INTO语句时，都会创建一个隐式游标。隐式游标的名称是SQL。不能对SQL游标显式地执行OPEN、FETCH、CLOSE语句。Oracle隐式地打开、提取、并总是自动关闭SQL游标。

当使用SELECT语句时，SQL游标一次只能返回一行或没有返回行（对应于NO_DATA_FOUND异常），如果返回多行，就会产生TOO_MANY_ROWS异常，这时就应该使用显示游标来处理了。当使用INSERT、UPDATE、DELETE时，SQL游标可以处理多行。

 

--example 5 : 通过访问隐式游标SQL的%ROWCOUNT属性来了解修改了多少行

DECLARE
   v_rows   NUMBER;
BEGIN
   UPDATE emp
      SET comm = 1234
    WHERE deptno = 20;

   v_rows := SQL%ROWCOUNT;

   DBMS_OUTPUT.put_line ('更新了 ' || v_rows || ' 个雇员的奖金');

   ROLLBACK;
END;
/

 

游标FOR循环

为了简化游标操作，PL/SQL语言提供了游标FOR循环语句。一个游标FOR循环可以隐含地实现OPEN、FETCH、CLOSE游标以及循环处理结果集的功能。其步骤是：

1.当进入循环时，自动打开一个已经声明的游标，并提取第一行游标数据。

2.当处理完当前所提取的数据而进入下一次循环时，自动提取下一行游标数据。

3.当提取完结果集中的所有数据行后结束循环，并自动关闭游标。

 

--example 6 : 使用游标FOR循环来查询显示多行记录数据集

DECLARE
   CURSOR c1 (c_empno NUMBER DEFAULT 7788)
   IS
      SELECT ename, hiredate
        FROM emp
       WHERE empno = c_empno;
BEGIN
   DBMS_OUTPUT.put_line ('----给c_empno传递参数7369时：----');

   FOR c1_record IN c1 (7369)
   LOOP
      DBMS_OUTPUT.put_line (
         c1_record.ename || ' 的雇佣日期是 ' || c1_record.hiredate);
   END LOOP;
   
   DBMS_OUTPUT.put_line ('----给c_empno传递参数7788时：----');

   FOR c1_record IN c1
   LOOP
      DBMS_OUTPUT.put_line (
         c1_record.ename || ' 的雇佣日期是 ' || c1_record.hiredate);
   END LOOP;
END;
/

 

使用游标更新或删除数据

要求游标查询语句中必须使用FOR UPDATE子句，以便在打开游标时锁定游标结果集在数据表中对应的数据行，从而不被其他用户更新或删除，这样才能更新或删除被锁定的数据行。

使用FOR UPDATE选项的游标查询语句的语法格式为：

SELECT column_list FROM table_list FOR UPDATE [NOWAIT];

使用FOR UPDATE打开游标之后，就可以在UPDATE,DELETE语句中使用WHERE CURRENT OF子句，修改或删除游标结果集中当前行所对应的数据库表中的数据行。其语法格式为：

不带WHERE条件的UPDATE语句或DELETE语句
WHERE CURRENT OF cursor_name;

 

--example 7

DECLARE
   v_emp_record   emp%ROWTYPE;

   CURSOR c1
   IS
      SELECT *
        FROM emp
      FOR UPDATE;
BEGIN
   OPEN c1;

   LOOP
      FETCH c1 INTO v_emp_record;

      EXIT WHEN c1%NOTFOUND;

      IF v_emp_record.empno = 7788
      THEN
         UPDATE emp
            SET comm = 1234
          WHERE CURRENT OF c1;
      END IF;
   END LOOP;

   COMMIT;

   CLOSE c1;
END;

 如果将其中的UPDATE语句更改为：

DELETE FROM emp WHERE CURRENT OF c1;

 就会将emp表中empno等于7788的记录删除。