天天看点
返回

ORACLE学习笔记（八）——游标——数据的缓冲区 一、什么是游标二、显式游标三、隐式游标

一、什么是游标

    1、游标的概念

        可以将游标（Cursor）形象地看成一个变动的光标。它实际上是一个指针，它在一段oracle存放数据查询结果集或者数据操作结果集的内存中，这个指针可以指向结果集中的任何一条记录。这样就可以得到它所指向的数据了，但初始时它指向首记录。这种模型很像编程中的数组。

    2、游标的类型

        oracle游标分为静态游标和REF游标两类。其中，静态游标就像一个数据快照，打开游标后的结果集是对数据库数据的一个备份，数据不随着对表执行DML操作后而改变。从这个特性来说，结果集是静态的。

        静态游标包含两中类型：

        （1）、显式游标

          是指在使用之前必须有着明确的游标声明和定义，这样的游标定义会关联数据查询语句，通常会返回一行或者多行。关闭游标后就不能再对其进行操作了。显示游标由用户自定义来完成，一切由用户控制。

        （2）、隐式游标

          和显式游标不同，它被PL/SQL自动管理，也被称为SQL游标，由oracle自动管理。该游标用户无法控制，但能得到它的属性信息。

二、显式游标

    1、游标语法 

CURSOR    cursor_name    [    parameter_name    datatype,     ...    ]    IS    select_statement;

    2、游标的使用步骤

        (1)声明游标（并关联一个查询）

DECLARE    CURSOR    cursor_name    IS    select_statement;

        (2)打开游标（游标一旦打开，结果集都是静态的）

OPEN    cursor_name;

        (3)读取游标

        读取数据要利用FETCH语句完成，它可以把游标指向位置的记录放入到PL/SQL声明的变量中。正常情况下，FETCH语句要和循环语句一起使用，这样指针会不断前进，直到某个条件不符合要求而退出。

FETCH    cursor_name    INTO    Record_name;

        (4)关闭游标

CLOSE    cursor_name;

    3、游标中的LOOP语句 

declare

  cursor info_cursor is select id,position,strain from CONSTANTINFO where id <11;
  --使用%type可以取得表中的字段类型，使用%rowtype可以申明基于某个表的行类型
  cur_id CONSTANTINFO.id%TYPE;
  cur_pos CONSTANTINFO.position%TYPE;
  cur_str CONSTANTINFO.strain%TYPE;

begin
  --打开游标
  open info_cursor;
    --循环获取记录集
    loop
      fetch info_cursor into cur_id,cur_pos,cur_str;
      --使用游标属性：记录集到尾，没有记录退出循环
      exit when info_cursor%notfound;
      --输出游标获取的记录集
      dbms_output.put_line(cur_id ||'-----'|| cur_pos ||'-----'|| cur_str);
    end loop;
  --关闭游标
  close info_cursor;
  --游标中使用异常处理
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('没有找到数据！');
  WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('数据过多！');
END;

    4、使用BULK COLLECT和FOR语句的游标——实现批量读取数据

        使用FETCH INTO语句提取数据，这种方式是单条数据提取，在数据量很大的情况下执行效率并不理想。而FETCH...BULK COLLECT INTO语句可以批量提取数据，在数据量大的情况下它的执行效率相当快。

declare
  cursor info_cursor is select id,position,strain from CONSTANTINFO where id <11;
  --定义和表STRACONSTANTINFO行对象一致的集合类型constantinfo_rd，用于存放批量得到的数据
  type constantinfo_tab is table of CONSTANTINFO%rowtype;
  constantinfo_rd constantinfo_tab;
begin
  --打开游标
  open info_cursor;
    --循环获取记录集
    loop
      fetch info_cursor bulk collect into constantinfo_rd limit 2;
      for i in 1..constantinfo_rd.count loop
          dbms_output.put_line('数据ID：'||constantinfo_rd(i).id ||'  位置：'||
             constantinfo_rd(i).position ||'  应变：'|| constantinfo_rd(i).strain);
          end loop;
      --记录集到尾，没有记录退出循环
      exit when info_cursor%notfound;
    end loop;
  --关闭游标
  close info_cursor;
  --游标中使用异常处理
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('没有找到数据！');
  WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('数据过多！');
END;

    5、使用CURSOR FOR LOOP——使用更加简单

        游标有很多机会都是迭代结果集，在PL/SQL这个过程中可以使用更简单的方式实现，CURSOR FOR LOOP不需要特别的声明变量，它可以提出行对象类型的数据。这种方式在隐式游标中使用更显方便。

declare
--声明游标关联查询
  CURSOR info_cursor IS select id,position,strain from CONSTANTINFO where id<11;
begin
     --把游标返回数据放到curcfl中，该类型是个%ROWTYPE类型
     FOR curcfl IN info_cursor
     --迭代输出数据
     LOOP
         DBMS_OUTPUT.PUT_LINE('数据ID：'||curcfl.id ||'  位置：'|| curcfl.position ||'  应变：'|| curcfl.strain);
     END LOOP;
     --游标中使用异常处理
     EXCEPTION
     WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('没有找到数据！');
     WHEN TOO_MANY_ROWS THEN
         DBMS_OUTPUT.PUT_LINE('数据过多！');
end;

    6、显式游标的属性

    （1）%ISOPEN：用于判断游标是否打开，打开则返回TRUE，否则FALSE；

    （2）%FOUND：用来检测行数据是否有效。有效则返回TRUE,否则反之；

    （3）%NOTFOUND：与%FOUND正好相反，没有提取出数据返回TRUE，否则反之；

    （4）%ROWCOUNT：累计到当前为止使用FETCH提取数据的行数。较为常用，使用它可以知道当前已经返回多少行数据了

    7、带参数的游标

        在使用显式游标时是可以指定参数的，指定的参数包括参数的顺序和参数的类型。参数可以传递给游标在查询中使用。这样就方便了用户根据不同的查询条件进行查询。也方便了游标在存储过程中的使用。

DECLARE
  --使用%type可以取得表中的字段类型，并且声明了变量赋了值；使用%rowtype可以申明基于某个表的行类型
  cur_id CONSTANTINFO.id%TYPE := '100';
  cur_pos CONSTANTINFO.position%TYPE := '10000';
  cur_rcd CONSTANTINFO%ROWTYPE;
  
  --声明游标，包括两个参数和类型
  CURSOR info_cursor(iid VARCHAR2,pos NUMBER) IS SELECT * FROM constantinfo WHERE id < iid AND position < pos;

BEGIN
  --打开游标,并且给参数赋予变量
  OPEN info_cursor(cur_id,cur_pos);
    --循环获取记录集
    LOOP
      FETCH info_cursor INTO cur_rcd;
      --使用游标属性：记录集到尾，没有记录退出循环
      EXIT WHEN info_cursor%NOTFOUND;
      --输出游标获取的记录集
      DBMS_OUTPUT.PUT_LINE(cur_rcd.id ||'-----'|| cur_rcd.position ||'-----'|| cur_rcd.strain);
    END LOOP;
  --关闭游标
  CLOSE info_cursor;
  --游标中使用异常处理
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('没有找到数据！');
  WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('数据过多！');
END;

三、隐式游标

    1、隐式游标的特点

        隐式游标没有像显式游标那样声明一个游标名称，而是直接使用了SQL名称，这是隐式游标的默认名称，可直接使用。

        （1）隐式游标由PL/SQL自动管理；

        （2）隐式游标的默认名称是SQL；

        （3）SELECT语句或DML操作都会长产生隐式游标的；

        （4）隐式游标的属性始终是最新执行的SQL语句的。

declare
  --使用%type可以取得表中的字段类型.
  cur_id CONSTANTINFO.id%TYPE;
  cur_pos CONSTANTINFO.position%TYPE;
  cur_str CONSTANTINFO.strain%TYPE;
begin
    select id,position,strain from CONSTANTINFO WHERE id < 11; 
    loop
      --使用隐式游标属性，记录集到尾，没有记录退出循环
      IF SQL%FOUND;
      --输出游标获取的记录集
      dbms_output.put_line(cur_id ||'-----'|| cur_pos ||'-----'|| cur_str);
      END IF;
      EXIT WHEN SQL%NOTFOUND;
    end loop;
  --游标中使用异常处理
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('没有找到数据！');
      WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('数据过多！');
END;

    2、游标中使用异常处理

       上述的所有SQL中都添加了异常处理的代码。当数据库中的记录发生变化时使用SELECT INTO语句返回的结果有可能不是单条记录，这时候会出现脚本中断并报错的现象，因此需要使用异常处理：

--游标中使用异常处理
     EXCEPTION
     WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('没有找到数据！');
     WHEN TOO_MANY_ROWS THEN
         DBMS_OUTPUT.PUT_LINE('数据过多！');

    3、隐式游标的属性

    （1）%ISOPEN：用于判断游标是否打开。HOWEVER，在隐式游标中该属性永远返回FALSE，它由ORACLE自己控制；

    （2）%FOUND：当DML操作对数据有影响的时候返回TRUE，SELECT INTO语句返回数据时返回TRUE，否则反之；

    （3）%NOTFOUND：与%FOUND正好相反；

    （4）%ROWCOUNT：该属性反映出DML操作对数据影响的行数。

ORACLE 11g 数据库 Oracle 数据库游标 游标
上一篇: oracle数据库查询空间大小,Oracle查看数据库空间使用情况
下一篇: oracle数据库删除表日志查询,oracle数据库rman删除Archivelog的方法

继续阅读