Oracle 游标

一、显示游标

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时会报错的.

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

posted on 2013-07-18 20:42 吴一达阅读(200) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

吴一达