入门精通6-CURSOR

发表于 更新于 分类于 ， 阅读次数： 评论数：

游标学习笔记，其中TB1表创建方式CRATE TABLE TB1 AS SELECT * FROM EMP;

sql语句返回的结果是一个集合（包含多条记录，多个属性），而PL/SQL的变量则是标量，一组变量只能存储一条记录。变量与sql的这种矛盾，就是游标CURSOR的用处。
游标的基本原理为：在PL/SQL中执行SELECT、INSERT、UPDATE、DELETE时系统会在内存中分配上下文区，游标就可以理解为指向该区域的一个指针，它对结果集提供了分别处理每一行的方法。
游标分为显示游标和隐式游标两种。显示游标是由用户声明和操作的一种游标；隐式游标是ORACLE为所有数据操作语句自动声明的一种游标。

显示游标

显示游标的处理包括四个步骤：声明、打开、提取、关闭。其中游标声明步骤需要在声明部分进行，其他3歌步骤都在执行或者异常处理中。

声明

声明的语法如下

1
CURSOR <游标名> IS SELECT<语句>;

有两点要注意：

  • SELECT语句可以使用任何语法，包括UNION和MINUS的子句
  • 游标声明可以在WHERE子句中使用PL/SQL的变量，被使用的变量称为联编变量（bindVARIABLE）

打开

打开的语法如下

1
OPEN <游标名>;

<游标名>是一个已经声明的游标。开打游标为执行定义的SELECT语句。当打开一个游标时，会完成以下步骤：

  • 检查联编变量的值
  • 根据变量的值，确定活动集
  • 活动集的指针指向第一行

当打开的游标是已打开状态，再次执行打开操作时系统会隐试执行关闭CLOSE <游标名>操作

提取

提取游标的关键字是FETCH,语法有两种形式：FETCH <游标名> INTO <变量列表>,FETCH <游标名> INTO PL/SQL记录。其中INTO子句中的类型要和查询列的类型兼容。FETCH语句每执行一次，游标后移一行，一直到结束。

关闭

关闭的语法为CLOSE <游标名>。该操作为关闭SELECT操作释放被占用的内存区

例子

以下例子为根据变量D_NAME的部门名称获取该部门员工的编码、姓名、出生日期、薪资、奖金信息，其中TB1信息与EMP表相同。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
DECLARE
  D_NAME VARCHAR2(14);  --声明部门名称

  e_no   NUMBER(4);  --员工编码
  e_name VARCHAR2(10);  --员工名称
  hdate  DATE;  --出生日期
  sal    NUMBER(7, 2);  --工资
  comm   NUMBER(7, 2);  --奖金

  CURSOR TAB_CUR IS( -- 声明TAB_CUR游标
    SELECT TB1.empno,TB1.ename,TB1.hiredate,TB1.sal,TB1.comm
      FROM TB1
     INNER JOIN DEPT
        ON DEPT.DEPTNO = TB1.DEPTNO
     WHERE DEPT.DNAME = D_NAME);--使用联编变量D_NAME
BEGIN
  D_NAME := 'RESEARCH';--部门名变量赋值

  OPEN TAB_CUR;  --打开游标

  FETCH TAB_CUR
    INTO e_no, e_name, hdate, sal, comm;  --提取当前记录到对应变量

  /*操作对应变量*/
  DBMS_OUTPUT.put_line(hdate || ' + ' || TO_CHAR(hdate, 'YYYY-MM-DD'));
  DBMS_OUTPUT.put_line(e_name);
  DBMS_OUTPUT.put_line(sal);
  DBMS_OUTPUT.put_line(comm);

  CLOSE TAB_CUR;--关闭游标
END;

删除与更新

游标可以用于数据更新或者记录删除，实现方法有两种：

FOR UPDATE实现

使用FOR UPDATE进行更新或删除时，游标声明语法如下：

1
CURSOR <游标名> IS <SELECT语句> FOR UPDATE;

执行更新需要在提取游标后，执行更新的语法为：

1
2
UPDATE <表名> SET <列名=表达式> WHERE CURRENT OF <游标名>;
COMMIT;

例如：将SALES中COMM为空员工的姓名改为编号+原姓名的格式，实现如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DECLARE
  D_NAME VARCHAR2(14);

  e_no   NUMBER(4);
  e_name VARCHAR2(10);
  hdate  DATE;
  sal    NUMBER(7, 2);
  comm   NUMBER(7, 2);

  CURSOR TAB_CUR IS
    SELECT TB1.empno,TB1.ename,TB1.hiredate,TB1.sal,TB1.comm
    FROM TB1
     INNER JOIN DEPT ON DEPT.DEPTNO = TB1.DEPTNO
    WHERE DEPT.DNAME = D_NAME AND TB1.COMM IS NULL FOR UPDATE; --结尾需要加上FOR UPDATE关键字
BEGIN
  D_NAME := 'SALES';
  OPEN TAB_CUR;
  FETCH TAB_CUR INTO e_no, e_name, hdate, sal, comm;
  UPDATE TB1 SET TB1.ENAME=TO_CHAR(E_NO) || E_NAME WHERE CURRENT OF TAB_CUR; --加上WHERE CURRENT OF子句
  COMMIT;
  CLOSE TAB_CUR;
END;


执行记录删除也需要在提取游标后，语法为：

1
2
DELETE FROM <表名> WHERE CURRENT OF <游标名>;
COMMIT;

删除TB1表中员工名包含AOO的记录，实现如下：

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
  CURSOR TB_CUR IS SELECT ENAME FROM TB1 WHERE TB1.ENAME LIKE'%AOO%' FOR UPDATE;
  V_NAME TB1.ENAME%TYPE;
BEGIN
    OPEN TB_CUR;
    FETCH TB_CUR INTO V_NAME;
    
    DELETE FROM TB1 WHERE CURRENT OF TB_CUR;
    COMMIT;
    
    CLOSE TB_CUR;
END;

FOR实现

以下示例为将编号是75开头的员工姓名后面加上-

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
  CURSOR TB_CUR IS
    SELECT EMPNO, ENAME FROM TB1 WHERE EMPNO LIKE '75%';

BEGIN
  FOR R_TB IN TB_CUR LOOP
    DBMS_OUTPUT.put_line('名称：'||R_TB.ENAME);
    UPDATE TB1 SET TB1.ENAME = R_TB.ENAME || '-' WHERE TB1.EMPNO=R_TB.EMPNO;
  END LOOP;
  
  COMMIT;
END;


需要注意的有以下几点：

  • 这种方式不需要手动打开、提取、关闭游标
  • 循环时用到的RECORD不用在DECLARE中定义
  • FOR循环内的每次数据操作，其范围都是SELECT语句的整个结果集，所以需要WHERE子句限制操作范围。

上面代码等价于如下

1
2
3
4
5
6
7
8
DECLARE
BEGIN
  FOR R_TB IN (SELECT EMPNO,ENAME FROM TB1 WHERE EMPNO LIKE'75%') LOOP
    UPDATE TB1 SET TB1.ENAME = R_TB.ENAME || '-' WHERE TB1.EMPNO=R_TB.EMPNO;
    END LOOP;
    
  COMMIT; 
END;

隐式游标

在PL/SQL中使用SELECT时，会隐式创建一个游标，这类游标不需要定义，也不用打开、关闭。使用方法如下

1
2
3
4
5
6
DECLARE
  E_NA VARCHAR2(10);  --员工名称
BEGIN
  SELECT ENAME INTO E_NA FROM TB1 WHERE EMPNO=7566;
  DBMS_OUTPUT.put_line(E_NA);
END;

游标属性

游标有%ISOPEN、%FOUND、%NOTFOUND和%ROWCOUNT

  • %FOUND：是否找到游标，表示当前游标是否指向有效的一行，若是则为TRUE，否则为FLASE，隐式游标使用SQL%FOUND。下面例子为显示游标的属性使用
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    DECLARE
      E_NA VARCHAR2(10);  
      CURSOR TB_CUR IS SELECT ENAME FROM TB1;
    BEGIN
      OPEN TB_CUR;
      FETCH TB_CUR INTO E_NA;
      
      WHILE TB_CUR%FOUND LOOP  --当前是否指向有效记录
        DBMS_OUTPUT.put_line('姓名 '||E_NA);
        FETCH TB_CUR INTO E_NA;
        END LOOP;
      CLOSE TB_CUR;
    END;
  • %NOTFOUND：与%FOUND相反，关于隐式游标的%NOTFOUND属性，其取值为当最近的sql语句（update,insert,delete,select）没有涉及任何行的时候，则返回true。否则返回false，下面例子
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    DECLARE
      E_NA VARCHAR2(10);  
      CURSOR TB_CUR IS SELECT ENAME FROM TB1;
    BEGIN
      DELETE FROM TB1 WHERE EMPNO=10;COMMIT;
      
      IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.put_line('表记录清除失败');
        ELSE
          DBMS_OUTPUT.put_line('表记录清除成功');
        END IF;
    END;
  • %ROWCOUNT：记录游标抽取过的行数。在隐式游标中表示最近一条SQL影响的行数
  • %OPEN：游标是否打开，提取未打开的游标是不合法的。隐式游标的该属性总是为TRUE

游标也可以在声明时带上参数，打开时通过传递实际参数达到动态使用的目的。例如根据不同部门编号显示部门的人员名称

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE
  E_NA VARCHAR2(10);
  CURSOR TB_CUR(V_NUM NUMBER) IS(
    SELECT ENAME FROM TB1 WHERE DEPTNO = V_NUM);
BEGIN
  OPEN TB_CUR(20);
  LOOP
    EXIT WHEN TB_CUR%NOTFOUND;
    FETCH TB_CUR INTO E_NA;
    DBMS_OUTPUT.put_line(TB_CUR%ROWCOUNT || '.  姓名：' || E_NA);
  END LOOP;
  CLOSE TB_CUR;
END;

游标变量

与一般常量变量类似，前面所使用的游标在声明时就与SELECT语句绑定，是静态的，而游标变量在运行时与SQL进行灵活绑定。其实现方式为变量在打开时才与SELECT绑定。
与上文中游标的使用类似，游标变量的使用也是声明、打开、提取、关闭四个步骤。游标变量在声明时需要先声明它的类型，其语法为

1
TYPE <游标变量类型> IS REF CURSOR [RETURN <记录类型>];

其中RETURN以及之后的内容代表游标变量的返回数据类型，返回数据类型必须是RECORD类型，声明时可以不指定，指定则能约束SELECT的结果数据类型。变量类型声明后可以完成变量定义，语法为

1
<游标名> <游标变量类型>;

游标打开时指定SELECT语句，若定义变量类型时指定了返回类型，SELECT语句的结果类型要与返回类型兼容。打开的语法为

1
OPEN <游标变量> FOR SELECT语句;

提取操作和关闭操作与之前相同，不再赘述。以下例子为游标变量使用实践

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DECLARE
TYPE CUR_T IS REF CURSOR RETURN TB1%ROWTYPE;--创建游标类型CUR_T（创建类CUR_T）
V_C CUR_T;--创建CUR_T类型的变游标量V_C(实例化CUR_T)

TYPE RCO_T IS RECORD(
  ENO TB1.EMPNO%TYPE,ENA TB1.ENAME%TYPE,EJ TB1.JOB%TYPE,EM TB1.MGR%TYPE,
    EH TB1.HIREDATE%TYPE,ESA TB1.SAL%TYPE,ECO TB1.COMM%TYPE,DNO TB1.DEPTNO%TYPE);
V_R RCO_T;
BEGIN
  OPEN V_C FOR SELECT * FROM EMP;--打开游标变量（赋值）
  LOOP FETCH V_C INTO V_R; --提取游标
    IF V_C%NOTFOUND THEN EXIT;END IF;
    DBMS_OUTPUT.PUT_LINE('emp：'||V_R.ENA);
    END LOOP;
  CLOSE V_C;--关闭游标
  DBMS_OUTPUT.PUT_LINE('===================================');
  OPEN V_C FOR SELECT * FROM TB1;--再次打开游标变量（赋值）
  LOOP FETCH V_C INTO V_R; 
    IF V_C%NOTFOUND THEN EXIT;END IF;
    DBMS_OUTPUT.PUT_LINE('TB1：'||V_R.ENA);
    END LOOP;
  CLOSE V_C;
END;