PL/SQL--Cursor

最新推荐文章于 2024-06-29 17:49:39 发布

mercenarylin

最新推荐文章于 2024-06-29 17:49:39 发布

阅读量456

点赞数

分类专栏： oracle

oracle 专栏收录该内容

48 篇文章 1 订阅

订阅专栏

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   --打开游标2
12   OPEN DEPT_CURSOR_2;
13 
14   --检查游标1
15   FETCH DEPT_CURSOR_1
16     INTO V_ROWID;
17   IF DEPT_CURSOR_1%ROWCOUNT > 0 THEN
18     DBMS_OUTPUT.PUT_LINE('游标1包含删除的数据');
19   ELSE
20     DBMS_OUTPUT.PUT_LINE('游标1不包含删除的数据');
21   END IF;
22   --检查游标1
23   FETCH DEPT_CURSOR_2
24     INTO V_ROWID;
25   IF DEPT_CURSOR_2%ROWCOUNT > 0 THEN
26     DBMS_OUTPUT.PUT_LINE('游标2包含删除的数据');
27   ELSE
28     DBMS_OUTPUT.PUT_LINE('游标2不包含删除的数据');
29   END IF;
30   CLOSE DEPT_CURSOR_1;--关闭游标
31   CLOSE DEPT_CURSOR_2;
32   ROLLBACK;--回滚
33   EXCEPTION WHEN OTHERS THEN
34     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   --打开游标1
10   IF NOT EMP_CURSOR_1%ISOPEN THEN
11     OPEN EMP_CURSOR_1;
12     DBMS_OUTPUT.PUT_LINE('OPEN CURSOR1');
13   END IF;
14   --提取数据--使用基本LOOP循环
15   LOOP
16     FETCH EMP_CURSOR_1
17       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 LOOP
25     FETCH EMP_CURSOR_1
26       INTO V_EMP_RECORD;
27     DBMS_OUTPUT.PUT_LINE('ENAME:' || V_EMP_RECORD.ENAME);
28   END LOOP;
29   --关闭游标
30   IF EMP_CURSOR_1%ISOPEN THEN
31     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 LOOP
37     DBMS_OUTPUT.PUT_LINE('ENAME:' || IDX.ENAME);
38   END LOOP;
39   --测试%ROWCOUNT属性
40   OPEN EMP_CURSOR_1;
41   FETCH EMP_CURSOR_1
42       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 DECLARE
2   V_DEPTNO DEPT.DEPTNO%TYPE := &部门编号;
3 BEGIN
4   UPDATE TEST_DEPT T SET T.LOC = '西安市' WHERE T.DEPTNO = V_DEPTNO;
5   IF SQL%NOTFOUND THEN
6     DBMS_OUTPUT.PUT_LINE('没有该部门');
7   END IF;
8 END;

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

1  DECLARE
2    CURSOR EMP_TEST_CUR IS
3      SELECT * FROM EMP_TEST FOR UPDATE OF ENAME  NOWAIT;
4  BEGIN
5    FOR EMP_REC IN EMP_TEST_CUR LOOP
6      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_CURSOR
12     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 BEGIN
10   OPEN DEPT_CURSOR;
11   LOOP
12     FETCH DEPT_CURSOR
13       INTO DEPT_NAME, EMP_CURSOR;
14     EXIT WHEN DEPT_CURSOR%NOTFOUND;
15     DBMS_OUTPUT.PUT_LINE('DNAME:' || DEPT_NAME);
16     LOOP
17       FETCH EMP_CURSOR
18         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 LOOP
11     DBMS_OUTPUT.PUT_LINE('ename:' || EMP_RECORD.ENAME);
12     FETCH EMP_CURSOR
13       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 LOOP
11     --添加一行新数据
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 LOOP
25     DBMS_OUTPUT.PUT_LINE('empno:' || EMPS(IDX1).EMPNO);
26   END LOOP;
27 END;