Oracle游标使用总结

游标（CURSOR）也叫光标，在关系数据库中经常使用，在PL/SQL程序中可以用CURSOR与SELECT一起对表或者视图

中的数据进行查询并逐行读取。

         Oracle游标分为显示游标和隐式游标。

         显示游标（Explicit Cursor）:在PL/SQL程序中定义的、用于查询的游标称作显示游标。

         隐式游标（Implicit Cursor）:是指非PL/SQL程序中定义的、而且是在PL/SQL中使用UPDATE/DELETE语句时，Oracle系统自动分配的游标。

一.显示游标

 1.使用步骤

 (1)定义 (2)打开 (3)使用 (4)关闭

 2.使用演示

 首先创建测试用表STUDENT，脚本如下：

 CREATE TABLE "STUDENT" (

         "STUNAME" VARCHAR2(10 BYTE),

         "STUNO" VARCHAR2(4 BYTE),

         "AGE" NUMBER,

         "GENDER" VARCHAR2(2 CHAR)

 )

(1).使用WHILE循环处理游标

 create or replace PROCEDURE PROC_STU1 AS

 BEGIN

  --显示游标使用，使用while循环

  declare

  --1.定义游标，名称为cur_stu

  cursor cur_stu is

  select stuno,stuname from student order by stuno;

  --定义变量，存放游标取出的数据

  v_stuno varchar(4);

  v_stuname varchar(20);

    begin

    --2.打开游标cur_stu

    open cur_stu;

    --3.将游标的当前行取出存放到变量中

    fetch cur_stu into v_stuno,v_stuname;

    while cur_stu%found --游标所指还有数据行，则继续循环

    loop

      --打印结果

      dbms_output.PUT_LINE(v_stuno||'->'||v_stuname);

      --继续将游标所指的当前行取出放到变量中

      fetch cur_stu into v_stuno,v_stuname;

    end loop;

    close cur_stu; --4.关闭游标

  end;

END PROC_STU1;

(2).使用IF..ELSE代替WHILE循环处理游标

 create or replace PROCEDURE PROC_STU2 AS

 BEGIN

  --显示游标使用，使用if判断

  declare

  --1.定义游标，名称为cur_stu

  cursor cur_stu is

  select stuno,stuname from student order by stuno;

  --定义变量，存放游标取出的数据

  v_stuno varchar(4);

  v_stuname varchar(20);

    begin

    --2.打开游标cur_stu

    open cur_stu;

    --3.将游标的当前行取出存放到变量中

    fetch cur_stu into v_stuno,v_stuname;

    loop

      if cur_stu%found then --如果游标cur_stu所指还有数据行

         --打印结果

        dbms_output.PUT_LINE(v_stuno||'->'||v_stuname);

        --继续将游标所指的当前行取出放到变量中

        fetch cur_stu into v_stuno,v_stuname;

      else

        exit;

      end if;

    end loop;

    close cur_stu; --4.关闭游标

  end;

END PROC_STU2;

(3).使用FOR循环处理游标

create or replace PROCEDURE PROC_STU3 AS

BEGIN

  --显示游标使用，使用for循环

  declare

  --定义游标，名称为cur_stu

  cursor cur_stu is

  select stuno,stuname from student order by stuno;

    begin

    for stu in cur_stu

    loop

      dbms_output.PUT_LINE(stu.stuno||'->'||stu.stuname);

      --循环做隐含检查 %notfound

    end loop;

    --自动关闭游标

  end;

END PROC_STU3;

(4).常用的使用EXIT WHEN处理游标

create or replace

PROCEDURE PROC_STU1_1 AS

BEGIN

  --显示游标使用，使用exit when循环

  declare

  --1.定义游标，名称为cur_stu

  cursor cur_stu is

  select stuno,stuname from student order by stuno;

  --定义变量，存放游标取出的数据

  v_stuno varchar(4);

  v_stuname varchar(20);

    begin

    --2.打开游标cur_stu

    open cur_stu;

    loop

    --3.将游标的当前行取出存放到变量中

      fetch cur_stu into v_stuno,v_stuname;

        exit when cur_stu%notfound; --游标所指还有数据行，则继续循环

        --打印结果

        dbms_output.PUT_LINE(v_stuno||'->'||v_stuname);

    end loop;

    close cur_stu; --4.关闭游标

  end;

END PROC_STU1_1;

 

二.隐式游标

1.使用演示

create or replace PROCEDURE PROC_STU4 AS

BEGIN

  --隐式游标使用

  update student set stuname='张燕广' where stuno='1104';

  --如果更新没有匹配则插入一条新记录

  if SQL%NOTFOUND then

    insert into student(STUNO,STUNAME,AGE,GENDER)

        values('1104','张燕广',18,'男');

  end if;

END PROC_STU4;

2.说明

         所有的SQL语句在上下文区内部都是可执行的，因为都有一个游标指向上下文区，此游标就是

SQL游标，与现实游标不同的是，SQL游标在PL/SQL中不需要打开和关闭，而是在执行UPDATE、

DELETE是自动打开和关闭。

         上面例子中就是通过SQL%NOTFOUND游标属性判断UPDATE语句的执行结果决定是否需要插入新记录。

三 动态SQL和REF 游标使用（双重游标循环）

create or replace procedure fieldviewmapping_pro is
begin
  declare
  type cur_type is ref cursor;--ref 游标
  c cur_type;--声明一个名字为c的ref游标
  v_datatype varchar2 (200);  
  v_fielddes varchar2 (200);
 
  cursor cur_getparameter is select t.viewname,t.functionid from fieldviewmapping t; --从表fieldviewmapping获得一个结果集给游 cur_getparameter
  v_viewname fieldviewmapping.viewname%type;
  v_functionid fieldviewmapping.functionid%type;
 
  begin
  open cur_getparameter;
  loop--循环游标 cur_getparameter
       fetch cur_getparameter into v_viewname,v_functionid;
       exit when cur_getparameter%notfound;
          open c for 'select s.DATA_TYPE,t.fld_ds from all_tab_columns s,rpt_wrtr_fld t where s.COLUMN_NAME = t.vw_fld_nm and s.TABLE_NAME = :1 and t.aplctn_fnctn_id = :2' using v_viewname,v_functionid; 

           -- 通过游标cur_getparameter的值获得动态SQL给ref游标c  
          loop--循环游标 c
          fetch c into v_datatype,v_fielddes;
          exit when c%notfound;
               if lower(v_datatype)='varchar2' then
                  update rpt_wrtr_fld t set t.fld_typ_cd = 'TEXT' where t.fld_ds = v_fielddes and t.aplctn_fnctn_id = v_functionid;
               elsif lower(v_datatype)= 'char' then
                  update rpt_wrtr_fld t set t.fld_typ_cd = 'TEXT' where t.fld_ds = v_fielddes and t.aplctn_fnctn_id = v_functionid;
               elsif lower(v_datatype)= 'date' then
                  update rpt_wrtr_fld t set t.fld_typ_cd = 'DATE' where t.fld_ds = v_fielddes and t.aplctn_fnctn_id = v_functionid;
               elsif lower(v_datatype)= 'number' then
                  update rpt_wrtr_fld t set t.fld_typ_cd = 'DOUBLE' where t.fld_ds = v_fielddes and t.aplctn_fnctn_id = v_functionid;
               elsif substr(lower(v_datatype),1,9)= 'timestamp' then
                  update rpt_wrtr_fld t set t.fld_typ_cd = 'DATE' where t.fld_ds = v_fielddes and t.aplctn_fnctn_id = v_functionid;           
               end if;
        end loop;
        close c;                   
  end loop;
  close cur_getparameter;
  end;
end fieldviewmapping_pro;