一、认识游标

    什么是游标？游标是数据库的一种数据类型，它用来管理从数据源（表，视图等）获取到的数据结果集，可以想象成一个游动的光标（指针），指向一个结果集，通过游标的移动逐行提取每一行的记录，就像我们屏幕上的光标指示当前位置一样，“游标”由此得名。

    游标分成静态游标和动态游标(也叫REF游标)。

    静态游标：所谓静态游标，顾名思义，指的是数据已经固定的游标，在使用游标前，已经知道游标中的数据和类型。静态游标又可以细分成显式游标和隐式游标，显示游标指的是已经定义在变量区，并且已经指定结果集的游标；隐式游标则是不用定义，直接就可以用的游标，比如系统定义的隐式游标sql，比如使用for循环遍历某个SQL（这个SQL的结果集就是一个隐式游标）的自定义游标。

    动态游标：与静态游标相反，在定义的时候并不知道其结果集，在使用时，再给它定义结果集（通俗来说，就是查询数据的SQL不是一成不变的）的游标。动态游标也可以细分成强类型和弱类型游标，强类型游标规定了其返回类型，弱类型游标则是不规定返回类型，可以获取任何结果集。

    在使用游标时，通常需要借助游标的一些属性来做逻辑判断，比如说判断游标是否已经到了结果集的尾部，这个时候可以使用游标的found属性来做判断：if 游标%found then 。。以下是游标的一些属性具体说明：

    1.%found ：用于检验游标是否成功，通常在fetch语句前使用，当游标按照条件查询一条记录是，返回true。fetch语句（获取记录）执行情况True or False。

    2.%notfound : 最后一条记录是否提取出true or false。 到了游标尾部，没有记录了，就返回true 

    3.%isopen ： 游标是否打开true or false。

    4.%rowcount ：游标当前提取的行数 ,即获得影响的行数。

二、游标使用的语法

    1.静态游标语法(显式):

    a.声明游标：划分存储区域，注意此时并没有执行Select 语句：

                CURSOR 游标名(参数 列表)   [返回值类型]   is  select 语句；

   b.打开游标：执行select 语句，获得结果集存储到游标中，此时游标指向结果集头部，类似于java的迭代器，必须先执行.next()，游标才指向第一条记录。

                open 游标名(参数 列表);

   c.获取记录：移动游标取一条记录:

                fetch  游标名 into  临时记录或属性类型变量；

   d.关闭游标：将游标放入缓冲池中，没有完全释放资源。可重新打开。

                close  游标名；

 

   2.动态游标语法: 

    a.声明REF游标类型：这个声明相当于自定义一个游标类型，在声明REF游标类型时，可以一并确定REF 游标的分类：

      ⑴强类型REF游标：指定retrun type，REF 游标变量的类型必须和return type一致。

           语法：type   REF游标名   is   ref cursor return  结果集返回记录类型；

      ⑵弱类型REF游标：不指定return type，能和任何类型的CURSOR变量匹配，用于获取任何结果集。

           语法：type   REF游标名   is   ref cursor；

    b.声明REF游标类型变量：

      语法：变量名  已声明Ref游标类型；

    c.打开REF游标，关联结果集：

          语法：open   REF 游标类型变量   for   查询语句返回结果集；

     d.获取记录，操作记录：

          语法：fetch    REF游标名 into   临时记录类型变量或属性类型变量列表；

     e.关闭游标，完全释放资源：

          语法：close   REF游标名；

  3.游标的遍历:

    a.for循环游标: 使用for循环遍历游标时，会自动打开游标，并且循环结束会自动关闭游标，所以在for循环之前和之后都不需要对游标进行open、close操作。另外，紧跟着for关键字的变量是不需要提前定义的。语法：

        for 变量名 in 游标名

          loop

            处理逻辑;

        end loop;

    b.loop循环游标:   loop循环是不会自动打开或者关闭游标的，需要手动操作。退出循环语句必须在执行逻辑操作之前执行，原因是因为即使游标已经遍历完，已经记录游标变量的记录是不会清除的，如果先执行逻辑操作，会导致循环多走一次。语法:

            open 游标名;

            loop

                 fetch  游标名 into  临时记录或属性类型变量(多个以逗号隔开);

                 exit  when   游标名%notfound;

                 逻辑操作

            end   loop;

            close 游标名;

    c.while循环游标:和loop有一点类似，语法：

            open 游标名;

            fetch  游标名 into 临时记录或属性类型变量(多个以逗号隔开);             while 游标名%found                 loop                    逻辑处理；                   fetch  游标名 into 临时记录或属性类型变量(多个以逗号隔开);            end loop;

           close 游标名;

三、示例

以下写了两个存储过程，分别记录了静态游标和动态游标的基础用法，可以用作参考：

静态游标相关：

create or replace procedure test_static_cursor is  --无参数静态显式游标   -- return test_user%rowtype 这里的返回值可以要 也可以不要,因为后面的SQL已经指定了返回值  CURSOR static_cursor return test_user%rowtype is    select * /**u.id, u.username, u.password*/      from test_user u;  --带参数的显示游标 (参数名 参数类型 [default 默认值])  CURSOR static_cursor1(p_name test_user.id%type default '123') is    select * from test_user u where u.id = p_name;  --定义变量 这里的变量类型的意思是保持和test_user的id列的类型一致.  --在定义变量以获取游标的数据时,建议使用这种方式  v_id       test_user.id%type;  v_username test_user.username%type;  v_password varchar2(32);  --定义记录(记录的意思是游标的一条记录)变量  v_record static_cursor1%rowtype;  v_num number;begin  --初始化一些数据  delete from test_user;  commit;  select count(1) into v_num from test_user;  if v_num = 0 then    insert into test_user      (id, username, password)    values      ('123', 'shaoyu', 'shaoyu');        --系统定义的隐式游标:SQL     --注意一句sql语句只会影响一个隐式游标,多个sql语句执行会覆盖隐式游标sql    if sql%found then       dbms_output.put_line('成功插入' || sql%rowcount || '条数据');    end if;        insert into test_user      (id, username, password)    values      ('456', 'admin', 'admin');    insert into test_user      (id, username, password)    values      ('789', 'system', 'system');    commit;  end if;  --打开游标,此时会执行定义游标时的SQL  open static_cursor;  --读取游标数据  fetch static_cursor    into v_id, v_username, v_password;  --验证  dbms_output.put_line(v_id || '-' || v_username || '-' || v_password);  --关闭游标  close static_cursor;  --打开游标  open static_cursor1('456');  --读取游标数据存入单个变量  fetch static_cursor1    into v_id, v_username, v_password;  --验证  dbms_output.put_line(v_id || '-' || v_username || '-' || v_password);  close static_cursor1;  open static_cursor1('789');  --读取游标数据存入记录变量  fetch static_cursor1 into v_record ;  --验证  dbms_output.put_line(v_record.id || '-' || v_record.username || '-' || v_record.password);  close static_cursor1;  --游标的遍历:  --1.for循环(不需要打开游标)  dbms_output.put_line('for循环');  if static_cursor%isopen then    dbms_output.put_line('游标已打开');  else    dbms_output.put_line('游标未打开');  end if;  --data不需要提前定义  for data in static_cursor loop    if static_cursor%isopen then      dbms_output.put_line('游标已打开');    else      dbms_output.put_line('游标未打开');    end if;    dbms_output.put_line(data.id || '-' || data.username || '-' ||                         data.password);  end loop;  if static_cursor%isopen then    dbms_output.put_line('游标已打开');  else    dbms_output.put_line('游标未打开');  end if;  --2.loop循环  dbms_output.put_line('loop循环');  open static_cursor;  loop    fetch static_cursor      into v_id, v_username, v_password;    exit when static_cursor%notfound;    dbms_output.put_line(v_id || '-' || v_username || '-' || v_password);  end loop;  close static_cursor;  --3.while循环  dbms_output.put_line('while循环');  open static_cursor;  fetch static_cursor    into v_id, v_username, v_password;  while static_cursor%found loop    dbms_output.put_line(v_id || '-' || v_username || '-' || v_password);    fetch static_cursor      into v_id, v_username, v_password;  end loop;  close static_cursor;end test_static_cursor;

动态游标：

create or replace procedure test_dynamic_cursor is  --定义强类型REF游标类型  type dynamic_cursor_type1 is ref cursor return test_user%rowtype;  --定义弱类型REF游标  type dynamic_cursor_type2 is ref cursor;  --定义强类型REF自定义返回记录类型游标类型 先定义自定义返回记录类型 再定义游标类型  type dynamic_cursor_type3_rec is record(    user_id  test_user.id%type,    username test_user.username%type);    type dynamic_cursor_type3 is ref cursor return dynamic_cursor_type3_rec;  --定义之前定义好的游标类型  dynamic_cursor1 dynamic_cursor_type1;  dynamic_cursor2 dynamic_cursor_type2;  dynamic_cursor3 dynamic_cursor_type3;  --定义返回类型变量  rec3 dynamic_cursor_type3_rec;    --定义变量  v_id       test_user.id%type;  v_username test_user.username%type;  v_password varchar2(32);    v_num number;begin  --初始化一些数据  delete from test_user;  commit;  select count(1) into v_num from test_user;  if v_num = 0 then    insert into test_user      (id, username, password)    values      ('123', 'shaoyu', 'shaoyu');    insert into test_user      (id, username, password)    values      ('456', 'admin', 'admin');    insert into test_user      (id, username, password)    values      ('789', 'system', 'system');    commit;  end if;  dbms_output.put_line('强类型动态游标');  --给强类型动态游标关联结果集  open dynamic_cursor1 for select * from test_user;  --验证  loop    fetch dynamic_cursor1      into v_id, v_username, v_password;    exit when dynamic_cursor1%notfound;    dbms_output.put_line(v_id || '-' || v_username || '-' || v_password);  end loop;  close dynamic_cursor1;    --给弱类型动态游标关联结果集  dbms_output.put_line('弱类型动态游标');  open dynamic_cursor2 for select id,password from test_user;  --验证  loop    fetch dynamic_cursor2      into v_id, v_password;    exit when dynamic_cursor2%notfound;    dbms_output.put_line(v_id || '-' || v_password);  end loop;  close dynamic_cursor2;    --给自定义强类型动态游标关联结果集  dbms_output.put_line('自定义返回类型强类型动态游标');  open dynamic_cursor3 for select id,username from test_user;  --验证  loop    fetch dynamic_cursor3      into rec3;    exit when dynamic_cursor3%notfound;    dbms_output.put_line(rec3.user_id || '-' || rec3.username);  end loop;  close dynamic_cursor3;end test_dynamic_cursor;

以上看起来游标好像就这么一些用法，那还有没有别的用法呢？有的，那就是在使用游标时，对游标的结果集对应的数据源进行操作。

四、更新、删除游标记录

在定义游标的时候，如果在定义结果集的语句后面加上for update或者for delete子串，那么在使用游标时，就可以对游标的结果集进行操作，不要担心数据源的状态，当使用for update、for delete子串打开一个游标时，所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select...for update操作，保证了数据的正确性。

值得提醒的是，在多表查询中，使用of子句来锁定特定的表，如果忽略了of子句，那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下oracle将等待，直到数据行解锁。

语法：

    a.声明更新或删除显示游标：

       cursor 游标名 is  select 语句   for update [ of  更新列列名]；

       cursor 游标名 is  select 语句   for delete [ of  更新列列名]；

    b.使用显示游标当前记录来更新或删除：

       update 表名   set 更新语句  where   current  of   游标名；

       delete from 表名  where   current  of   游标名；

这个就不写例子了，第三步的示例理解了之后，这个很容易编写。

五、使用游标作为存储过程出参

说了这么多，并没有将游标应用到实际中，其实web程序对数据库的调用多数情况下需要返回一个结果集，很显然，游标是非常适合的。在这种情况下，只需要将游标作为存储过程的出参就可以了。

1.包的概念

在上一篇中提到了包和存储过程，那什么是包呢？包（package）也是数据库的一种对象类型，它包含定义和包体（body）两个方面，【定义】类似于是java中的接口，【包体】类似于是java中对接口的实现类，包里面是可以包含【自定义类型】和【存储过程】的，可以认为是java接口中的全局变量（自定义类型）和方法（存储过程），就连使用方式也极其类似：包名.存储过程名(参数)。

有人觉得奇怪，不是要说游标做为存储过程出参吗？怎么又扯上包这个东西了？

在java中，所有的变量都有一个作用域，oracle数据库也不例外，假设我们单独定义一个存储过程，在参数那一列是要规定参数类型的，如果我们使用的是自定义的游标，那么这个游标类型在这个存储过程参数里是肯定没有定义的，所以我们需要借助包，在包中定义自定义的游标类型，然后再把这个自定义游标作为包中的存储过程的出入参，这样就保证了游标在存储过程中的作用域始终可用。

2.包的语法：

包定义:

create or replace package 包名 as

定义 自定义type

定义 全局变量

procedure 存储过程名; --没有存储过程具体实现

function 函数名;

end test_package;

包体定义:

create or replace package body test_package as

定义变量

  procedure 存储过程名(参数) is ...存储过程具体实现 end test_package;

下面写个实例:

create or replace package test_package as  --定义游标类型  type o_cur is ref cursor;  --定义存储过程  procedure test_static_cursor(o_data out o_cur);end test_package;create or replace package body test_package as  --存储过程具体实现  procedure test_static_cursor(o_data out o_cur) is    v_num number;  begin      --初始化一些数据    delete from test_user;    commit;    select count(1) into v_num from test_user;    if v_num = 0 then      insert into test_user        (id, username, password)      values        ('123', 'shaoyu', 'shaoyu');      insert into test_user        (id, username, password)      values        ('456', 'admin', 'admin');      insert into test_user        (id, username, password)      values        ('789', 'system', 'system');      commit;    end if;    --给出参关联结果集    open o_data for      select * from test_user;    end;end test_package;