oracle的学习历程 2017/8/31

PL/SQL的游标（cursor）

游标是为了平衡两种状态。分别是sql查询的结果是集合的，一群数据。另一个是输入的变量是单次的，就是一个变量只能存放一条记录，而且事先也不知道要放多少变量，所以就用游标来平衡变量和结果的状态。

可以让开发者一次访问一个或多个结果集，然后将结果集中的内容逐条访问并显示。

工作原理是当pl/sql块执行select，insert，update，delete的时候，oracle会为他们在内存中分配一个缓冲区，那么游标就是指向该区的一个指针，从而对多行数据查询结果中的每一行数据进行分别处理。

游标分为显式游标和隐式游标。显式游标是用户声明和操作的一种游标。隐式的是oracle在数据操纵语言中自动声明和操作的游标。在每次操作数据库的时候，可以同时打开多个游标，数量是由数据库初始化参数文件open  cursors定义。

显式游标处理步骤：声明游标，打开游标，提取游标，关闭游标。

游标的声明定义了游标的名字和一个select语句相关联。语法：cursor 游标名 is select 语句

例如：cursor teacher is select tid,tname,title,sex

             from teachers

              where tid<7;

需要注意的是，定义游标中的select语句中不包含into子句，因为into子句是在提取游标部分。

打开游标 

语法：open 游标名

打开游标其实就是执行select语句，执行完了以后，查询结果进入内存，游标停在查询结果的首部，但不是第一行。

打开已经被打开的游标是可以的，系统会默认的重新打开重复游标前执行一次close语句

这里需要每次执行才可以，除非放到循环里才可以自动重复执行。

提取游标

语法：fetch游标名into变量列表

           就比如

declare

       teacher_id number(5);

       teacher_name varchar2(50);

       teacher_tile      varchar2(50);

       teacher_sex     char(1);

cursor teacher_cur is select tid,tname,title,sex

             from teachers

              where tid<7;

begin 

      open teacher_cur;

fetch teacher_cur into teacher_id,teacher_name,teacher_title,teacher_sex;    将第一行数据放到变量里，游标下移。

     执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。当检索到最后一行数据时，如果再次执行FETCH语句，将操作失败，并将游标属性%NOTFOUND置为TRUE。所以每次执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了值。

  关闭游标

当所有的活动集都被检索以后，游标就应该关闭，并释放资源。这些资源包括活动集的存储空间也包括存储活动集的临时空间。

close 游标名

如果在关闭了游标以后还去提取游标数据。系统会产生ORA-1001:Invalid cursor错误（非法游标）或者ORA-1002:FETCH out OF sequence 超出界限

一个完整实例，如何将同一张表的内容分别放到男老师表里和女老师表里。

使用显式游标的注意事项：

1、首先使用前要用%ISOPEN检查是否为打开状态。此值为true的时候游标才可以使用，否则要先将游标打开

2、使用的时候，每次都要用%found或者%notfound来检查是否有返回值。这个一般放到循环里，自动检查

3、自动添加到变量里的时候，变量个数和数据类型必须完全一致。

4、用完游标必须将其关闭，并且释放资源。

隐式游标实例，这里只需要注意一点，每一个隐式游标都必须有一个into子句。

游标属性

无论隐式还是显式游标，都有%isopen,%found,%notfound,%rowcount四种属性

%isopen       查看游标是否处于打开状态。

%found         说明当前游标是否指向有效一行，若是则为true

%notfound     与found效果相同，值正好相反

%rowcount     记录了游标所在的行号，在操作中用于中断循环。只抽取多少条数据。比如

当然，游标也可以带上参数使用

游标变量，与平常的变量不一样的地方是，游标变量可以在运行时与不同的语句关联，是动态的。游标变量用于处理多行的查询结果集。在同一个pl/sql块中，游标变量不同于特定的查询。而是打开游标时才对应查询，所以说变量是动态的。

声明变量：语法：type 类型名 is ref cursor 

                               return 返回类型

type 是已经被定义的类型，REF是指明新的类型必须是一个指向被定义的指针。所以，游标可以使用的类型是ref cursor

可以被用户详细列表写出定义的内容（显式）也可以用%rowtype方式复制其他的变量（隐式）。

详细例子：

定义游标变量方式2：他在声明的时候没有return。比如：

declare

         type t_FlexibleRef is REF CURSOR;

         V_CURSORVar  t_FlexibleRef;

这种没有return的代表没有返回类型，也就是可以在任何游标里面打开使用。

打开游标变量：

open 游标变量 for <select语句>

这里的话游标变量的类型必须和select语句中的类型一样，否则会报错ORA_6504

例子：

declare
  type t_SdudentsRef is ref CURSOR 
  RETURN students%ROWTYPE;
  V_StudentSCV t_SdudentsRef;
  begin
    open V_StudentSCV for
      select * from students;
      end;

关闭游标变量，与关闭游标一样。但是不能关闭已关闭的游标变量。