Oracle 游标

Oracle在运行plsql或者在进行大的sql操作时候需要从表中检索行？那么他是怎么将很多行取出来？

          通过游标一行一行的取出，一行一行的放到内存中。

游标作用：

      指定结果集中特定行的位置。 

      基于当前的结果集位置检索一行或连续的几行。 

      在结果集的当前位置修改行中的数据。 

      可以以编程的方式访问数据库。

游标类型

   隐式游标：在程序中执行dml语句时候，oracle会自动创建名字为sql的隐式游标。

   显式游标：显式游标用于处理返回多行的查询。

   Ref游标(参照游标)：ref游标用于处理运行时才能确定的动态sql查询的结果。

 

游标属性：

 %FOUND--sql语句影响了一行或多行时为true

 %NOTDOUND--sql语句没有影响任何行时为true

 %ROWCOUNT--sql语句影响的行数，必须放在一个更新或者删除等修改类语句后面执行，select语句用于查询的话无法使用。

 %ISOPEN--à游标是否打开，始终为false

使用游标属性：

begin  

  update testtable set inc_datetime = sysdate where id = 1;

  if  sql%found  then  dbms_output.put_line('更新了' || sql%rowcount);

  else dbms_output.put_line('更新了' || sql%rowcount);

  end if;

end;

select into 问题：

  在使用显示游标时候经常使用select into 语句，但是在使用select into 时候通常需要进行异常捕获，经常使用的内置异常有 no_data_-found,  too_many_rows。

 

declare

  val varchar2(4000);

begin

   select  t.random_string into val from testtable t where t.id = 10000001;

exception

  when no_data_found then dbms_output.put_line('没有查询到数据');

end;

 

declare

    val varchar2(4000);

begin

    select  t.random_string into val from testtable t; 

exception

  when too_many_rows then dbms_output.put_line('查询数据返回多行');

end;

 

显示游标：

  显式游标在plsql 声明部分定义查询，该查询语句可以返回多行(游标对应查询)

  游标使用步骤--à 声明游标，打开游标，使用游标取出记录，关闭游标。

显式游标(语法定义)：

Declare

  cursor 游标名称  is  查询语句

begin

  open 游标名称

    使用游标取出数据

  Close 游标名称

Exception

End;

使用游标取出student中所有的数据：

declare

  --保存 当前行

  rowVal student%rowtype;

  --定义游标

  cursor cursorVal is select * from student;

begin

    open cursorVal;

       loop

         --使用fetch语句把游标里面的值取出，存到变量中

          fetch[A1]  cursorVal into rowVal;

          if

             cursorVal%FOUND

          then

             dbms_output.put_line('姓名' || rowVal.Username  || '性别' ||  rowVal.Sex  || '行数' || cursorVal%rowcount );

          else

            exit;

          end if;

       end loop;

    close cursorVal;

end;

 

带有参数的显示游标：

  语法定义

     Cursor 游标名称(参数名称 参数类型) is 查询语句;

 注意：在定义带有参数游标时不能指定长度，并且在定义带参数游标时一定要在查询语句中使用，否则就失去了意义。

 

使用带参数游标查询数据：  

declare

   rowRes student%rowtype;

         --游标名称(参数名称 参数类型)

   cursor getParCursor(parEmp student.id%type ) is select * from student e where e.id = parEmp;

begin

    --打开游标传递值

    open getParCursor(3);

    loop

      fetch getParCursor into rowRes;

      if

        getParCursor%notfound

      then

        exit;

      else 

        dbms_output.put_line('姓名' || rowRes.Username  || '性别' ||  rowRes.Sex  );

      end if; 

    end loop;

end;

 

使用游标更新获删除数据：

 在使用游标时候可以进行更新或删除数据行，但是声明游标必须使用 select for update语句,然后在更新或删除语句后面加上 where current of 游标名称。

语法定义

 Course 游标名称 is 查询语句 select … for update ;

Update或者 Delete语句 …… where current of 游标名称。

使用游标更新表中记录

declare

   rowRes testtable%rowtype;

   cursor updateCursor is select * from testtable t where t.random_string = 'E983CHQABJ0722KHSWME' for update ;

begin

    open updateCursor;

    loop

      fetch updateCursor into rowRes;

      if

        updateCursor%notfound

      then

        exit;

      else

          if

             rowRes.Random_String = 'E983CHQABJ0722KHSWME'

          then

            update testtable set Random_String = '王五' where current of  updateCursor;

          else

             exit;

          end if;

       end if;

    end loop;

end;

使用游标更新或删除时注意：如果在使用游标进行删除或更新时候，在更新语句后面没有加 where current of 游标名称，那么在更新或删除时影响的行数就是这条语句的结果，如果此条语句在循环里面执行，那么将执行声明部分的游标对应的select语句查询出多少结果集的次数。

循环游标：

  循环游标可以用来简化游标处理代码，在使用循环游标时候不需要打开或者关闭游标，在使用循环游标时不能够进行更新和删除。

语法定义：

   For 变量  in 游标名称

   Loop

     ……

   End loop;

 

使用循环游标输出所有学生信息：

declare

   cursor forCursor is select * from student;

begin

   for cursor_index in  orCursor

     loop

         dbms_output.put_line('姓名' || cursor_index.Username  || '性别' ||  cursor_index.Sex  );

     end loop;

end;

 

REF(动态)游标：

 Ref游标和游标变量用于处理运行时动态执行的sql查询，显式游标是在定义的时候将某个游标对应某个查询，ref在定义时候不能确定，只有在运行时候才能确定对应的哪个sql。   

 语法定义(Ref游标)

declare  

 Type 名称 is ref  cursor-----声明ref游标类型

  游标名称 游标类型-----声明ref游标类型的变量

begin

  Open 游标名称 for select语句----打开游标

End;

 

栗子：对应于每一个学生，求出他的总的选课记录，把每个学生的选课记录插入到student2表中

   创建表语句

create table student (xh number, xm varchar2(10));

insert into student values(1,'A'); 

insert into student values(2,'B');

insert into student values(3,'C');

insert into student values(4,'D');

create table address (xh number, zz varchar2(10));

insert  into address values(2,'郑州');

insert  into address values(1,'开封');

insert  into address values(3,'洛阳');

insert  into address values(4,'新乡');

 

  student2中的结果如下：

                      XH KC

                 --- -------------------------------------------

                       1 语文数学英语历史

                       2 语文数学英语

                       3 语文英语

 

declare

  xhVal student.xh%type; 

  kcVal student.kc%type;

  --定义变量接收学生对应的所有课程

  keValAll varchar2(1000) := '';

  --定义显式游标，指向学生学号

  cursor stuCursor is select  distinct s.xh from student s;

  --定义ref游标

  type refType is ref cursor ;

  refCursor refType;

begin

  open stuCursor;

   loop

     fetch stuCursor into xhVal;

     if

       stuCursor%found

     then

         --打开ref游标

          open refCursor for select s1.kc from student s1 where s1.xh = xhVal;

              keValAll := '';

              loop

                fetch refCursor into kcVal;

                if

                  refCursor%found

                then

                  keValAll := kcVal || keValAll;

                else

                  exit;

                end if;     

              end loop;

           --插入数据到student2

               insert into student2 values (xhVal,keValAll);

          close refCursor;

     else

       exit;

     end if;   

   end loop;

  close stuCursor;

end;

 [A1]FETCH语句一次取一行数据，要返回多行需重复执行，可以使用循环语句来实现。