PL/SQLCursor

显式游标
隐式游标
游标变量
游标子查询

游标的概念：

 游标给出了数据的一个子集，这个子集是由某个查询语句定义的，在打开游标的时候，将数据加载到内存中，在游标未关闭的过程中，该数据将一直存在在内存中，游标指向PGA(PROCESS GLOBAL AREA)的一个内存区域，一般将PGA称为上下文区域。该区域存储下列数据：

  1、查询语句返回的记录行。

  2、查询语句处理的记录行数目。

  3、指向共享池中(Share Pool)中已解析查询语句的一个指针。

如果游标打开后，又新增或者删除了数据，则新增添和删除的数据就不会反馈到游标的查询结果中，打开游标就像是获取当前数据的一个快照：例如

DECLARE
  V_ROWID ROWID;
  CURSOR DEPT_CURSOR_1 IS
    SELECT ROWID FROM TEST_DEPT T WHERE T.DEPTNO < 100;
  CURSOR DEPT_CURSOR_2 IS
    SELECT ROWID FROM TEST_DEPT T WHERE T.DEPTNO < 100;
BEGIN
  --打开游标1，将其中的数据删除
  OPEN DEPT_CURSOR_1;
  DELETE FROM TEST_DEPT T WHERE T.DEPTNO < 100;
  --打开游标2
  OPEN DEPT_CURSOR_2;

  --检查游标1
  FETCH DEPT_CURSOR_1
    INTO V_ROWID;
  IF DEPT_CURSOR_1%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('游标1包含删除的数据');
  ELSE
    DBMS_OUTPUT.PUT_LINE('游标1不包含删除的数据');
  END IF;
  --检查游标1
  FETCH DEPT_CURSOR_2
    INTO V_ROWID;
  IF DEPT_CURSOR_2%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('游标2包含删除的数据');
  ELSE
    DBMS_OUTPUT.PUT_LINE('游标2不包含删除的数据');
  END IF;
  CLOSE DEPT_CURSOR_1;--关闭游标
  CLOSE DEPT_CURSOR_2;
  ROLLBACK;--回滚
  EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(Sqlerrm);
END;

结果：

--显式游标的四个属性
/*%ROWCOUNT
%FOUND
%NOTFOUND
%ISOPEN*/

--定义一个游标
DECLARE
  CURSOR EMP_CURSOR_1 IS(
    SELECT * FROM EMP);
  CURSOR EMP_CURSOR_2 IS(
    SELECT * FROM EMP);
  V_EMP_RECORD EMP%ROWTYPE;
BEGIN
  --打开游标1
  IF NOT EMP_CURSOR_1%ISOPEN THEN
    OPEN EMP_CURSOR_1;
    DBMS_OUTPUT.PUT_LINE('OPEN CURSOR1');
  END IF;
  --提取数据--使用基本LOOP循环
  LOOP
    FETCH EMP_CURSOR_1
      INTO V_EMP_RECORD;
    DBMS_OUTPUT.PUT_LINE('ENAME:' || V_EMP_RECORD.ENAME);
    EXIT WHEN EMP_CURSOR_1%NOTFOUND;
  END LOOP;
  CLOSE EMP_CURSOR_1;
  OPEN EMP_CURSOR_1;
  --提取数据--使用WHILE-----LOOP循环
  WHILE EMP_CURSOR_1%FOUND LOOP
    FETCH EMP_CURSOR_1
      INTO V_EMP_RECORD;
    DBMS_OUTPUT.PUT_LINE('ENAME:' || V_EMP_RECORD.ENAME);
  END LOOP;
  --关闭游标
  IF EMP_CURSOR_1%ISOPEN THEN
    CLOSE EMP_CURSOR_1;
    DBMS_OUTPUT.PUT_LINE('CLOSE CURSOR');
  END IF;
  DBMS_OUTPUT.PUT_LINE('=============分隔符==============');
  --提取数据--使用FOR-----LOOP循环.使用这种循环，会自动的打开和关闭游标
  FOR IDX IN EMP_CURSOR_2 LOOP
    DBMS_OUTPUT.PUT_LINE('ENAME:' || IDX.ENAME);
  END LOOP;
  --测试%ROWCOUNT属性
  OPEN EMP_CURSOR_1;
  FETCH EMP_CURSOR_1
      INTO V_EMP_RECORD;
    DBMS_OUTPUT.PUT_LINE('ROWCOUNT' || EMP_CURSOR_1%ROWCOUNT);
  CLOSE EMP_CURSOR_1;
  DBMS_OUTPUT.PUT_LINE(CHR(1));
END;

--隐式游标的四个属性

SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN--永远为FALSE

DECLARE
  V_DEPTNO DEPT.DEPTNO%TYPE := &部门编号;
BEGIN
  UPDATE TEST_DEPT T SET T.LOC = '西安市' WHERE T.DEPTNO = V_DEPTNO;
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('没有该部门');
  END IF;
END;

在进行更新操作的过程中，最好使用for update，可以添加nowait，当该行数据被其他锁定时，会提示：

 DECLARE
   CURSOR EMP_TEST_CUR IS
     SELECT * FROM EMP_TEST FOR UPDATE OF ENAME  NOWAIT;
 BEGIN
   FOR EMP_REC IN EMP_TEST_CUR LOOP
     UPDATE EMP_TEST SET ename = '小明' WHERE CURRENT OF EMP_TEST_CUR;
   END LOOP;
 END;

--游标变量的使用

--游标变量的使用
DECLARE
  TYPE EMP_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
  V_EMP_CURSOR EMP_CURSOR;
  V_EMP_RECORD EMP%ROWTYPE;
BEGIN
  --打开游标，使用FOR来赋初始值
  OPEN V_EMP_CURSOR FOR
    SELECT * FROM EMP;
  --提取数据
  FETCH V_EMP_CURSOR
    INTO V_EMP_RECORD;
  --输出数据
  DBMS_OUTPUT.PUT_LINE('DEPTNO:' || V_EMP_RECORD.DEPTNO ||
                       'ENAME:' || V_EMP_RECORD.ENAME);
  CLOSE V_EMP_CURSOR;
END;

--游标子查询

--游标子查询
DECLARE
  EMP_CURSOR SYS_REFCURSOR;
  EMP_RECORD EMP%ROWTYPE;
  DEPT_NAME  DEPT.DNAME%TYPE;
  CURSOR DEPT_CURSOR IS
    SELECT D.DNAME, CURSOR (SELECT * FROM EMP E WHERE E.DEPTNO = D.DEPTNO)
      FROM DEPT D;
BEGIN
  OPEN DEPT_CURSOR;
  LOOP
    FETCH DEPT_CURSOR
      INTO DEPT_NAME, EMP_CURSOR;
    EXIT WHEN DEPT_CURSOR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('DNAME:' || DEPT_NAME);
    LOOP
      FETCH EMP_CURSOR
        INTO EMP_RECORD;
      EXIT WHEN EMP_CURSOR%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('ENAME:' || EMP_RECORD.ENAME);
    END LOOP;
  END LOOP;
END;

REF_CURSOR的使用：

DECLARE
  TYPE EMP_CURSOR_REF IS REF CURSOR;
  EMP_CURSOR EMP_CURSOR_REF;
  EMP_RECORD EMP%ROWTYPE;
BEGIN
  OPEN EMP_CURSOR FOR
    SELECT * FROM EMP;
  FETCH EMP_CURSOR
    INTO EMP_RECORD;
  WHILE EMP_CURSOR%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('ename:' || EMP_RECORD.ENAME);
    FETCH EMP_CURSOR
      INTO EMP_RECORD;
  END LOOP;
END;

游标和varray的联合使用

DECLARE
  TYPE EMP_VARRAY IS VARRAY(100) OF EMP%ROWTYPE;
  EMPS EMP_VARRAY;
  CURSOR EMP_CURSOR IS
    SELECT * FROM EMP;
  COUNTS PLS_INTEGER := 0;
BEGIN
  --初始化数组(下标从1开始)
  EMPS := EMP_VARRAY();
  FOR IDX IN EMP_CURSOR LOOP
    --添加一行新数据
    COUNTS := COUNTS + 1;
    EMPS.EXTEND();
    EMPS(COUNTS).EMPNO := IDX.EMPNO;
    EMPS(COUNTS).ENAME := IDX.ENAME;
    EMPS(COUNTS).JOB := IDX.JOB;
    EMPS(COUNTS).MGR := IDX.MGR;
    EMPS(COUNTS).HIREDATE := IDX.HIREDATE;
    EMPS(COUNTS).SAL := IDX.SAL;
    EMPS(COUNTS).COMM := IDX.COMM;
    EMPS(COUNTS).DEPTNO := IDX.DEPTNO;
  END LOOP;

  FOR IDX1 IN 1 .. EMPS.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('empno:' || EMPS(IDX1).EMPNO);
  END LOOP;
END;