【Oracle】浅析游标使用

1，什么是游标?

游标可以理解为在内存中的临时表，通过 sql从数据库中提取数据，形成一个临时表并存于内存中，这就形成游标。当需要遍历游标中的数据时，可以使用Fetch … into …的方式，Fetch就相当与于指向游标的指针，可以从头遍历游标。由于数据都被存于内存中，这样可以大大提高处理效率，用空间换时间。

2，游标的属性

我们利用游标的属性值来获取游标所处的状态，然后对应做相应的处理，常用的属性有四个：
1、%NOTFOUND。表示游标获取数据的时候是否有数据提取出来，没有数据返回TRUE，有数据返回false。经常用来判断游标是否全部循环完毕，如%NOTFOUND为true的时候，说明循环完毕，跳出LOOP循环。
2、%FOUND。正好和%NOTFOUND相反，当游标提取数据值时有值，返回TRUE，否则返回FALSE。
3、%ISOPEN。用来判断游标是否打开。
4、%ROWCOUNT。表示当前游标FETCH INTO获取了多少行的记录值，用来做计数用的。

3，游标的分类

3.1 显式游标

在DECLEAR部分按以下格式声明游标：
CURSOR 游标名[(参数1 数据类型[，参数2 数据类型…])]
IS SELECT语句;

参数是可选部分，如果定义了参数，则必须在打开游标时传递相应的实际参数。 SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句.

3.1.1 语法定义

--一般总共 4 个步骤，缺一不可：（参数可选）
DECLARE
   CURSOR cur_emp(参数值 参数类型) IS SELECT * FROM emp t [WHERE t.empno = 参数值]; -- 步骤1: 声明游标
   v_emp  cur_emp%ROWTYPE;
BEGIN
   OPEN cur_emp(参数值); -- 步骤2: 打开游标  
   
   LOOP 
     FETCH cur_emp INTO v_emp; -- 步骤3: 提取数据
     EXIT WHEN cur_emp%NOTFOUND;    --退出遍历的条件
       dbms_output.put_line(v_emp.empno ||' : '||v_emp.ename);
   END LOOP;
   
   CLOSE cur_emp; -- 步骤4: 关闭游标
END;

3.1.2 显式游标遍历

第一种： loop方式，注意OPEN、CLOSE游标，以及退出LOOP的条件

DECLARE
  CURSOR CUR_EMP(P_DEPT NUMBER) IS                  -- 步骤1: 声明带参数游标
    SELECT * FROM EMP E WHERE E.DEPTNO = P_DEPT;
  V_EMP CUR_EMP%ROWTYPE;
  DEPT  NUMBER := 30;
BEGIN
  OPEN CUR_EMP(DEPT);-- 步骤2: 打开游标，并传入参数

  LOOP
    FETCH CUR_EMP INTO V_EMP; -- 步骤3: 提取数据
    EXIT WHEN CUR_EMP%NOTFOUND; --退出遍历的条件
    IF CUR_EMP%FOUND THEN
      DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO || ',' || V_EMP.ENAME || ',' ||
                           V_EMP.DEPTNO);
    END IF;
  END LOOP;

  CLOSE CUR_EMP;

第二种： Ｗhile方式，注意OPEN、CLOSE游标，以及While的条件

DECLARE
  CURSOR CUR_EMP(P_DEPT NUMBER) IS
    SELECT * FROM EMP E WHERE E.DEPTNO = P_DEPT;　　　-- 步骤1: 声明带参数游标
  V_EMP CUR_EMP%ROWTYPE;
  DEPT  NUMBER := 30;
BEGIN
  OPEN CUR_EMP(DEPT);　　-- 步骤2: 打开游标，并传入参数
  FETCH CUR_EMP INTO V_EMP;　　　--先移动指针到第一条记录

  WHILE CUR_EMP%FOUND LOOP　　　　　--　获取到数据才进入 while
    DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO || ',' || V_EMP.ENAME || ',' ||
                         V_EMP.DEPTNO);
    FETCH CUR_EMP INTO V_EMP;　　　　--继续移动指针取下一条记录
  END LOOP;
  CLOSE CUR_EMP;

第三种：for…in…方式，这里不需显式打开和关闭游标

DECLARE
  CURSOR CUR_EMP(P_DEPT NUMBER) IS
    SELECT * FROM EMP E WHERE E.DEPTNO = P_DEPT;
  -- V_EMP CUR_EMP%ROWTYPE; -- 可以不声明游标量
  DEPT  NUMBER := 30;
BEGIN
  FOR V_EMP IN CUR_EMP(DEPT) LOOP　　　-- 传入参数
    IF CUR_EMP%FOUND THEN
      DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO || ',' || V_EMP.ENAME || ',' ||
                           V_EMP.DEPTNO);
    END IF;
  END LOOP;
END;

三种方式的比较：
1）loop循环：首先要将游标指向第一行，再判断%NOTFOUND来设置循环退出条件。
2）while循环：只有第一行%FOUND返回true时才会执行体，在循环体中执行完用户操作后，需要将游标指向下一行。
3）最简单是是for…in…循环：首先是：在DECLARE部分不需要声明变量，其次，在BEGIN部分不需要打开游标以及关闭游标。

3.2 隐式游标

我们常用到的SELECT…INTO…查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作（insert、update、delete），系统都会使用一个隐式游标。隐式游标自动声明、打开和关闭（无法手动查看），其名为 SQL。通过检查隐式游标的属性可以获得最近执行的 DML 和SELECT…INTO…语句的信息。

为什么是SELECT…INTO…查询语句，而不是SELECT？
因为在begin … end块中只能添加insert、update、delete之类的DML，不能添加纯粹的select语句。PL/SQL语法要求，如果要select，可以使用显式游标。
SELECT…INTO…查询语句，当执行的时候会有三种可能：
1，结果集只含有一行，且select是成功,；
2，没有查询到任何结果集，引发NO_DATA_FOUND异常；
3，结果集中含有两行或者更多行，引发TOO_MANY_ROWS异常。

3.2.1 语法定义

DECLARE
  EMP_ROW EMP%ROWTYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('ROWCOUNT初始值:' || SQL%ROWCOUNT);  --ROWCOUNT初始值:    
  SELECT * INTO EMP_ROW FROM EMP WHERE EMPNO = 7979;
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('SELECT..INTO语句影响的行数为:' || SQL%ROWCOUNT);  --SELECT..INTO语句影响的行数为:1
    DBMS_OUTPUT.PUT_LINE(EMP_ROW.ENAME);                 --young
  END IF;

  UPDATE EMP SET SAL = 7000 WHERE DEPTNO = 10;
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('UPDATE语句影响的行数为:' || SQL%ROWCOUNT);   --UPDATE语句影响的行数为:6
  END IF;

  INSERT INTO EMP(EMPNO, ENAME, DEPTNO, SAL) VALUES (7803, 'young', 30, 6000);
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('INSERT语句影响的行数为:' || SQL%ROWCOUNT);   --INSERT语句影响的行数为:1
  END IF;
 
  DELETE FROM emp WHERE ename = 'young';
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('DELETE语句影响的行数为:' || SQL%ROWCOUNT);  --DELETE语句影响的行数为:2
  END IF;
   COMMIT;
END;

4，游标属性的tips

通过前面的例子，我们知道游标的属性可以获得前面最近执行的 DML 和SELECT…INTO…语句的信息，有三个tips可以注意下：

1，%ISOPEN

显式游标因为有open，close所有可以用%ISOPEN来做些判断，而隐式游标自动声明、打开和关闭，所以隐式游标的%ISOPEN无法使用到，

2，%NOTFOUND

在显式游标的loop中，使用%NOTFOUND作为退出循环的判断条件，只有当%NOTFOUND 返回True才能退出loop，需要注意的是%NOTFOUND除了True、False，还可以是null。
Oracle 官方文档：Before the first fetch%NOTFOUND returns NULL，也就是在进行第一次fetch之前%NOTFOUND 返回 NULL
举个例子
表中数据

DECLARE
   CURSOR cur_stu IS SELECT * FROM stu;
   v_stu cur_stu%ROWTYPE;
BEGIN
   OPEN cur_stu;
   LOOP
      EXIT WHEN cur_stu%NOTFOUND;      
      FETCH cur_stu INTO v_stu; -- before the first fetch... 
         dbms_output.put_line(v_stu.s_id || ' : ' || v_stu.s_xm);
   END LOOP;
   CLOSE cur_stu; 
END;

上面的执行结果：

对结果进行分析：

	第一次loop	第二次loop	第三次loop	第四次loop
cur_stu%NOTFOUND	null	False	False	True
EXIT？	否	否	否	是
FETCH 结果	1 科比	2 詹姆斯	null
v_stu	1 科比	2 詹姆斯	2 詹姆斯
输出	1 科比	2 詹姆斯	2 詹姆斯

所以在使用EXIT WHEN cur_stu%NOTFOUND;时，需要紧跟在fetch之后，避免null。

3，%ROWCOUNT

在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL，对于SELECT INTO语句，如果执行成功，SQL%ROWCOUNT的值为1。如果没有成功或者没有操作(如update、insert、delete为0条），SQL%ROWCOUNT的值为0，而对于update和delete来说SQL%ROWCOUNT表示游标所检索数据库行的个数即更新或者删除的行数。当执行DML语句，都需要commit，此时需注意SQL%ROWCOUNT要在DML语句和commit之间，否则无法得到正确结果，SQL%ROWCOUNT是最近的DMLDML语句的结果。
举个例子

DECLARE
BEGIN
  UPDATE EMP SET SAL = 7000 WHERE DEPTNO = 10;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('UPDATE语句影响的行数为:' || SQL%ROWCOUNT);   --输出：UPDATE语句影响的行数为:0
END;

DECLARE
BEGIN
  UPDATE EMP SET SAL = 7000 WHERE DEPTNO = 10;
  DBMS_OUTPUT.PUT_LINE('UPDATE语句影响的行数为:' || SQL%ROWCOUNT);   --输出：UPDATE语句影响的行数为:5
   COMMIT;
END;

两个结果不同，其实不仅仅%ROWCOUNT，四个属性必须要在一个DML语句和commit之间放置，否则你就得不到正确的修改行数，一旦commit,DML事务结束，属性也就不存在了。