PL/SQL--Cursor

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

游标的概念：

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

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

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

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

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

 1 DECLARE 2   V_ROWID ROWID; 3   CURSOR DEPT_CURSOR_1 IS 4     SELECT ROWID FROM TEST_DEPT T WHERE T.DEPTNO < 100; 5   CURSOR DEPT_CURSOR_2 IS 6     SELECT ROWID FROM TEST_DEPT T WHERE T.DEPTNO < 100; 7 BEGIN 8   --打开游标1，将其中的数据删除 9   OPEN DEPT_CURSOR_1;10   DELETE FROM TEST_DEPT T WHERE T.DEPTNO < 100;11   --打开游标212   OPEN DEPT_CURSOR_2;13 14   --检查游标115   FETCH DEPT_CURSOR_116     INTO V_ROWID;17   IF DEPT_CURSOR_1%ROWCOUNT > 0 THEN18     DBMS_OUTPUT.PUT_LINE('游标1包含删除的数据');19   ELSE20     DBMS_OUTPUT.PUT_LINE('游标1不包含删除的数据');21   END IF;22   --检查游标123   FETCH DEPT_CURSOR_224     INTO V_ROWID;25   IF DEPT_CURSOR_2%ROWCOUNT > 0 THEN26     DBMS_OUTPUT.PUT_LINE('游标2包含删除的数据');27   ELSE28     DBMS_OUTPUT.PUT_LINE('游标2不包含删除的数据');29   END IF;30   CLOSE DEPT_CURSOR_1;--关闭游标31   CLOSE DEPT_CURSOR_2;32   ROLLBACK;--回滚33   EXCEPTION WHEN OTHERS THEN34     DBMS_OUTPUT.PUT_LINE(Sqlerrm);35 END;

结果：

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

 1 --定义一个游标 2 DECLARE 3   CURSOR EMP_CURSOR_1 IS( 4     SELECT * FROM EMP); 5   CURSOR EMP_CURSOR_2 IS( 6     SELECT * FROM EMP); 7   V_EMP_RECORD EMP%ROWTYPE; 8 BEGIN 9   --打开游标110   IF NOT EMP_CURSOR_1%ISOPEN THEN11     OPEN EMP_CURSOR_1;12     DBMS_OUTPUT.PUT_LINE('OPEN CURSOR1');13   END IF;14   --提取数据--使用基本LOOP循环15   LOOP16     FETCH EMP_CURSOR_117       INTO V_EMP_RECORD;18     DBMS_OUTPUT.PUT_LINE('ENAME:' || V_EMP_RECORD.ENAME);19     EXIT WHEN EMP_CURSOR_1%NOTFOUND;20   END LOOP;21   CLOSE EMP_CURSOR_1;22   OPEN EMP_CURSOR_1;23   --提取数据--使用WHILE-----LOOP循环24   WHILE EMP_CURSOR_1%FOUND LOOP25     FETCH EMP_CURSOR_126       INTO V_EMP_RECORD;27     DBMS_OUTPUT.PUT_LINE('ENAME:' || V_EMP_RECORD.ENAME);28   END LOOP;29   --关闭游标30   IF EMP_CURSOR_1%ISOPEN THEN31     CLOSE EMP_CURSOR_1;32     DBMS_OUTPUT.PUT_LINE('CLOSE CURSOR');33   END IF;34   DBMS_OUTPUT.PUT_LINE('=============分隔符==============');35   --提取数据--使用FOR-----LOOP循环.使用这种循环，会自动的打开和关闭游标36   FOR IDX IN EMP_CURSOR_2 LOOP37     DBMS_OUTPUT.PUT_LINE('ENAME:' || IDX.ENAME);38   END LOOP;39   --测试%ROWCOUNT属性40   OPEN EMP_CURSOR_1;41   FETCH EMP_CURSOR_142       INTO V_EMP_RECORD;43     DBMS_OUTPUT.PUT_LINE('ROWCOUNT' || EMP_CURSOR_1%ROWCOUNT);44   CLOSE EMP_CURSOR_1;45   DBMS_OUTPUT.PUT_LINE(CHR(1));46 END;

--隐式游标的四个属性

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

1 DECLARE2   V_DEPTNO DEPT.DEPTNO%TYPE := &部门编号;3 BEGIN4   UPDATE TEST_DEPT T SET T.LOC = '西安市' WHERE T.DEPTNO = V_DEPTNO;5   IF SQL%NOTFOUND THEN6     DBMS_OUTPUT.PUT_LINE('没有该部门');7   END IF;8 END;

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

1  DECLARE2    CURSOR EMP_TEST_CUR IS3      SELECT * FROM EMP_TEST FOR UPDATE OF ENAME  NOWAIT;4  BEGIN5    FOR EMP_REC IN EMP_TEST_CUR LOOP6      UPDATE EMP_TEST SET ename = '小明' WHERE CURRENT OF EMP_TEST_CUR;7    END LOOP;8  END;

--游标变量的使用

 1 --游标变量的使用 2 DECLARE 3   TYPE EMP_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE; 4   V_EMP_CURSOR EMP_CURSOR; 5   V_EMP_RECORD EMP%ROWTYPE; 6 BEGIN 7   --打开游标，使用FOR来赋初始值 8   OPEN V_EMP_CURSOR FOR 9     SELECT * FROM EMP;10   --提取数据11   FETCH V_EMP_CURSOR12     INTO V_EMP_RECORD;13   --输出数据14   DBMS_OUTPUT.PUT_LINE('DEPTNO:' || V_EMP_RECORD.DEPTNO ||15                        'ENAME:' || V_EMP_RECORD.ENAME);16   CLOSE V_EMP_CURSOR;17 END;

--游标子查询

 1 --游标子查询 2 DECLARE 3   EMP_CURSOR SYS_REFCURSOR; 4   EMP_RECORD EMP%ROWTYPE; 5   DEPT_NAME  DEPT.DNAME%TYPE; 6   CURSOR DEPT_CURSOR IS 7     SELECT D.DNAME, CURSOR (SELECT * FROM EMP E WHERE E.DEPTNO = D.DEPTNO) 8       FROM DEPT D; 9 BEGIN10   OPEN DEPT_CURSOR;11   LOOP12     FETCH DEPT_CURSOR13       INTO DEPT_NAME, EMP_CURSOR;14     EXIT WHEN DEPT_CURSOR%NOTFOUND;15     DBMS_OUTPUT.PUT_LINE('DNAME:' || DEPT_NAME);16     LOOP17       FETCH EMP_CURSOR18         INTO EMP_RECORD;19       EXIT WHEN EMP_CURSOR%NOTFOUND;20       DBMS_OUTPUT.PUT_LINE('ENAME:' || EMP_RECORD.ENAME);21     END LOOP;22   END LOOP;23 END;

REF_CURSOR的使用：

 1 DECLARE 2   TYPE EMP_CURSOR_REF IS REF CURSOR; 3   EMP_CURSOR EMP_CURSOR_REF; 4   EMP_RECORD EMP%ROWTYPE; 5 BEGIN 6   OPEN EMP_CURSOR FOR 7     SELECT * FROM EMP; 8   FETCH EMP_CURSOR 9     INTO EMP_RECORD;10   WHILE EMP_CURSOR%FOUND LOOP11     DBMS_OUTPUT.PUT_LINE('ename:' || EMP_RECORD.ENAME);12     FETCH EMP_CURSOR13       INTO EMP_RECORD;14   END LOOP;15 END;

游标和varray的联合使用

 1 DECLARE 2   TYPE EMP_VARRAY IS VARRAY(100) OF EMP%ROWTYPE; 3   EMPS EMP_VARRAY; 4   CURSOR EMP_CURSOR IS 5     SELECT * FROM EMP; 6   COUNTS PLS_INTEGER := 0; 7 BEGIN 8   --初始化数组(下标从1开始) 9   EMPS := EMP_VARRAY();10   FOR IDX IN EMP_CURSOR LOOP11     --添加一行新数据12     COUNTS := COUNTS + 1;13     EMPS.EXTEND();14     EMPS(COUNTS).EMPNO := IDX.EMPNO;15     EMPS(COUNTS).ENAME := IDX.ENAME;16     EMPS(COUNTS).JOB := IDX.JOB;17     EMPS(COUNTS).MGR := IDX.MGR;18     EMPS(COUNTS).HIREDATE := IDX.HIREDATE;19     EMPS(COUNTS).SAL := IDX.SAL;20     EMPS(COUNTS).COMM := IDX.COMM;21     EMPS(COUNTS).DEPTNO := IDX.DEPTNO;22   END LOOP;23 24   FOR IDX1 IN 1 .. EMPS.COUNT LOOP25     DBMS_OUTPUT.PUT_LINE('empno:' || EMPS(IDX1).EMPNO);26   END LOOP;27 END;