Oracle 游标

桃扇骨 2021-12-23 17:35 467阅读 0赞

## 一、显示游标 ##

PL/SQL 游标包含隐含游标和显示游标等两种游标类型，其中隐含游标用于处理SELECT INTO和 DML语句，而显示游标则专门用于处理SELECT 语句返回的多行数据。

### 1、使用显示游标 ###

为了处理SELECT 语句返回的多行数据，开发人员可以使用显示游标，使用显示游标包括定义游标、打开游标、提取数据和关闭游标四个阶段.

####  1.1定义游标 ####

使用显示游标之前，必须首先在定义部分定义游标。定义游标用于指定游标多对应的SELECT语句，语法如下：

CURSOR  cursor\_name  IS select\_statement

如上所示，cursor\_name用于定义游标名称；select\_statement用于指定游所对应的SELECT 语句。

#### 1.2打开游标 ####

当打开游标时，Oracle会执行游标所对应的SELECT语句，并且SELECT语句的结果暂时存放到结果集中，语法如下：

OPEN cursor\_name;

该游标名必须是在定义部分已经定义的游标。

#### 1.3提取数据 ####

在打开游标之后，SELECT 语句的结果被临时存放到游标结果集中.为了处理结果集中的数据，需要使用FETCH语句 提取游标数据.在Oracle 9i之前，使用FETCH语句，每次只能提取一行数据；从Oracle 9i之后，通过使用FETCH.....BULK COLLECT INTO语句，每次可以提取多行数据。语法如下：

语法一：FETCH cursor\_name  INTO variable1,variable2 ,....;

语法二：FETCH cursor\_name BULK COLLECT INTO collect1,collect2,...\[LIMIT rows\];

如上所示，variable用于指定接收游标数据的变量；collect用于指定接收游标结果的集合变量。注意，当使用语法一时，必须使用循环语句处理结果集的所有数据。

#### 1.4、关闭游标 ####

在提取并处理了结果集的所有数据之后，就可以关闭游标并释放其结果集了。 语法如下：

CLOSE cursor\_name;

### 2、显示游标属性 ###

显示游标属性用于返回显示游标的执行信息，这些属性包括%ISOPEN，%FOUND，%NOTFOUND和%ROWCOUNT。当使用显示游标属性时，必须要在显示游标属性之前带有显示游标名作为前缀（游标名.属性名）.

#### 2.1、%ISOPEN ####

该属性用于确定游标是否已经打开。如果游标已经打开，则返回值位True；如果游标没有打开，则返回值为False。示例如下：

IF c1%ISFOUND THEN         ----如果游标打开，则执行相应操作
         .....
    ELSE                      ----如果游标未打开，则打开游标
        OPEN c1;           
    END IF;

####    2.2、%FOUND ####

该属性用于检查是否从结果集中提取到了数据。如果提取到数据，则返回值位TRUE；如果没有提取到数据，返回值位FALSE。示例如下：

LOOP 
       FETCH c1 INTO var1,var2       ----提取数据到变量中
       IF c1%FOUND  THEN             ----如果提取到数据则进行处理
            ....
       ELSE                          ----如果未提取到数据，则退出循环
          EXIT;
       END IF;
    END LOOP;

#### 2.3、%NOTFOUND ####

该属性与%FOUND属性恰好相反。如果提取到数据，则返回值位FALSE；如果没有提取到数据，则返回值为TRUE。示例如下：

LOOP 
        FETCH c1 INTO var1,var2; -----提取数据到变量中
        EXIT WHEN c1%NOTFOUND;
        ...
    END LOOP;

#### 2.4、ROWCOUNT ####

该属性用于返回到当前行为为止已经提取到的实际行数。示例如下：

LOOP
        FETCH 	c1 INTO my_ename,my_deptno;
        IF c1%ROWCOUNT >10 THEN 
          ....
        END IF;
        ...  
    END LOOP;

###    3、显示游标使用示例 ###

#### 示例一：在显示游标中使用FETCH  INTO 语句 ####

在Oracle9i 之前，使用FETCH.....INTO语句每次只能处理一行数据。为了处理结果集中的多行数据，必须要使用循环语句进行处理。下面以在PL/SQL块中显示部门10的所有雇员名及其工资为例，说明在显示游标中使用FETCH...INTO语句的方法。示例如下：

DECLARE 
        CURSOR emp_cursor IS 
           SELECT ename,sal FROM emp WHERE deptno=10;             ----多行数据
        v_ename emp.ename%TYPE;   
        v_sal   emp.sal%TYPE;
    BEGIN 
       OPEN emp_cursor;
       LOOP    
          FETCH  emp_cursor INTO v_ename,v_sal ;         ---每次只能提取一行数据，所以放在循环语句里面
          EXIT WHEN emp_cursor%NOTFOUND;
          dbms_output.put_line(v_ename||': '||v_sal);
       END LOOP;
      CLOSE emp_cursor;
    END;

####     示例二、在显示游标中，使用FETCH....BULK COLLECT INTO 语句取得所有数据。 ####

从Oracle9i 开始，通过使用FETCH...BULK COLLECT INTO 语句，一次就可以取得结果集中的所有数据。下面以显示部门10的所有雇员名为例，说明使用FETCH..BULK COLLECT INTO 取得所有数据的方法。示例如下：

DECLARE
        CURSOR emp_cursor IS
          SELECT ename FROM emp WHERE deptno=10;
          TYPE ename_table_type IS TABLE OF VARCHAR2(10);
          ename_table ename_table_type;
    BEGIN 
         OPEN emp_cursor;
         FETCH emp_cursor BULK COLLECT INTO  ename_table;
         FOR i IN 1.. ename_table.COUNT LOOP
            dbms_output.put_line(ename_table(i));   
           END LOOP;
          CLOSE emp_cursor;
    END;

#### 示例三、使用游标属性 ####

当使用显示游标时，为了取得显示游标的执行信息，需要使用显示游标属性。下面以使用显示游标属性%ISOPEN和%ROWCOUNT为例，说明在PL/SQL块中使用显示游标属性的方法。示例如下：

DECLARE  
        CURSOR emp_cursor IS 
           SELECT ename FROM emp WHERE deptno=10;
           TYPE ename_table_type IS TABLE OF VARCHAR2(10);
           ename_table ename_table_type;
    BEGIN 
         IF NOT  emp_cursor%ISOPEN THEN    ------如果游标未打开，则打开游标
          OPEN emp_cursor;        
         END IF; 
         FETCH emp_cursor BULK COLLECT INTO ename_table;
         dbms_output.put_line('提取的总计行数：'||emp_cursor%ROWCOUNT);   ------显示总计行数
         CLOSE emp_cursor;
    END;

#### 示例四、基于游标定义记录变量 ####

使用%ROWTYPE属性不仅可以基于表和视图定义记录变量，也可以基于游标定义记录变量。当基于游标定义记录变量时，记录成员名实际就是SELECT语句的列名或列别名。为了简化显示游标的数据处理，建议开发人员使用记录变量存放游标数据。下面以显示所有雇员名及其工资为例，说明在处理显示游标数据时使用记录变量的方法。示例如下：

DECLARE 
        CURSOR emp_cursor IS 
          SELECT ename,sal FROM emp;
        emp_record emp_cursor%ROWTYPE;  ----基于游标定义记录变量
    BEGIN 
        OPEN emp_cursor;
        LOOP
           FETCH emp_cursor INTO emp_record; ------使用记录变量存放游标数据
           EXIT WHEN emp_cursor%NOTFOUND;
           dbms_output.put_line('雇员名：'||emp_record.ename||',雇员工资：'||emp_record.sal);    
        END LOOP;
        CLOSE emp_cursor;
    END;

##    二、游标FOR循环 ##

游标FOR循环是在PL/SQL块中使用游标最简单的方式，简化了对游标的处理。当使用游标FOR循环时，Oracle会隐含的打开游标、提取游标数据并关闭游标。使用游标FOR循环的语法如下：

FOR record_name IN cursor_name LOOP 
       statement1;
       statement2;
       ...
      END LOOP;

如上所示，cursor\_name是已经定义的游标名；record\_name是Oracle隐含定义的记录变量名。当使用游标FOR循环时，在执行循环体内容之前，Oracle会隐含的打开游标，并且没循环一次提取一次数据，在提取了所有数据之后，会自动退出循环并隐含的关闭游标。

### 1.使用游标FOR循环 ###

当使用游标开发PL/SQL程序时，为了简化程序代码，建议大家使用游标FOR循环。下面以顺序显示EMP表的所有雇员为例，说明使用游标FOR循环的方法。示例如下：

DECLARE 
        CURSOR emp_cursor IS SELECT ename,sal FROM emp;  
    BEGIN    
        FOR emp_record IN emp_cursor LOOP 
             dbms_output.put_line('第'||emp_cursor%ROWCOUNT||'个雇员：'||emp_record.ename);
          END LOOP;
    END;

### 2.在游标FOR循环中直接使用子查询 ###

当使用游标FOR循环时，习惯做法是首先在定义部分定义游标，然后在游标FOR循环中使用该游标。如果在使用游标FOR循环时，不需要使用任何的游标属性，那么可以在游标FOR循环中使用子查询.下面以显示EMP的所有雇员名为例，说明在游标FOR循环中直接使用子查询的方法。示例如下：

BEGIN 
    	   FOR emp_record IN 
    	      (SELECT ename,sal FROM emp) LOOP
    	      dbms_output.put_line(emp_record.ename);
    	   END LOOP;   
    END;

## 三、使用游标变量 ##

类似C语言中的指针变量，PL/SQL的游标变量中存放着只想内存地址的指针。当显示使用游标时，需要在定义部分指定其所对应的SELECT语句；而使用游标变量时，开发人员可以在打开游标变量时指定其所对应的SELECT语句。

### 1、游标变量使用步骤 ###

在PL/SQL块中使用游标变量包括定义游标变量、打开游标变量、提取游标数据、关闭游标等四个阶段。具体步骤如下：

（1）定义REF CURSOR类型和游标变量

为了在PL/SQL块中定义游标变量，必须首先定义REF CURSOR 类型，然后才能定义游标变量。定义REF CURSOR类型和游标变量的语法如下：

TYPE ref_type_name IS REF CURSOR [RETURN return_type];  
    cursor_variable ref_type_name;

如上所示，ref\_type\_name用于指定自定义的类别名；RETURN 子句用于指定REF CURSOR返回结果的数据类型；cursor\_variable用于指定游标变量名。注意：当指定RETURN子句时，其数据类型必须是记录类型；另外，不能在包内，定义游标变量.

(2) 打开游标

在定义游标变量之后，为了引用该游标变量，在打开游标时，需要指定其所对应的SELECT语句。当打开游标时，会执行游标变量所对应的SELECT语句，并将SELECT语句结果存放到游标结果集中。语法如下：

OPEN  cursor_variable FOR  select_statement;

如上所示，select\_statement用于指定游标所对应的SELECT语句。  
  
(3)提取游标数据

和显示游标的描述差不多.

(4)关闭游标变量

在提取并处理了所有游标数据之后，就可以关闭游标变量并释放其结果集了。语法如下：

CLOSE cursor\_variable;

### 2、游标使用示例 ###

#### 示例一：在定义REF CURSOR类型时不指定RETURN 子句 ####

如果在定义REF CURSOR类型时，不指定RETUEN 子句，那么在打开游标时可以指定热任何的SELECT语句。下面以顺序的显示所有雇员名称为例，说明使用游标变量（不使用RETURN子句）的方法。示例如下：

DECLARE 
        TYPE emp_cursor_type IS REF CURSOR;
        emp_cursor emp_cursor_type;
        emp_record emp%ROWTYPE;
     BEGIN    
         OPEN emp_cursor FOR SELECT * FROM emp WHERE deptno=10;
         LOOP 
            FETCH emp_cursor INTO emp_record;
            EXIT WHEN emp_cursor%NOTFOUND;
            dbms_output.put_line('第'||emp_cursor%ROWCOUNT||'个雇员：'||emp_record.ename);
         END LOOP;
      CLOSE  emp_cursor;
    END;

#### 示例二：在定义REF CURSOR类型时指定RETUEN 子句 ####

如果在定义REF CURSOR 类型时指定了RETURN 子句，在打开游标时SELECT 语句的返回结果必须与RETURN 子句指定的记录类型相匹配。下面以顺序显示部门20单位所有雇员名为例， 说明使用游标变量（使用RETURN子句）的方法。示例如下：

DECLARE 
        TYPE emp_record_type IS RECORD(
           name VARCHAR2(10),salary NUMBER(6,2)); 
        TYPE emp_cursor_type IS REF CURSOR
           RETURN    emp_record_type;
        emp_cursor emp_cursor_type;
        emp_record emp_record_type;
    BEGIN 
        OPEN emp_cursor FOR SELECT ename,sal FROM emp
              WHERE deptno=20;   
        LOOP
           FETCH emp_cursor INTO emp_record;
           EXIT WHEN emp_cursor%NOTFOUND;        
            dbms_output.put_line('第'||emp_cursor%ROWCOUNT||'个雇员：'||emp_record.name);
       END LOOP;
         CLOSE emp_cursor;
    END;

如上所示，因为定义REF CURSOR 类型时指定了RETURN 子句，所以游标子查询的返回结果必须与记录类型emp\_record\_type相匹配。例如，如果在打开游标变量时指定"SELECT ename,sal,deptno FROM emp  WHERE deptno=20;"，那么在执行PL/SQL时会报错的.

后续内容更精彩，敬请关注！