session cursor 的种类和用法

oracle 中的session cursor分为三种：显式游标（explicit cursor） 隐式游标（implicit cursor） 参考游标（ref cursor)

一、 隐式游标（implicit cursor）

   无处不在，oracle中最常见的游标，只要执行一个SQL或者pl/sql，Oracle就会自动创建一个隐式游标，它的生命周期（open，bind，parse，execute，fetch，close）由SQL引擎或者pl/sql引擎自动控制，所有也意味着我们失去了对隐式游标的控制权。

  不过还是可以通过隐式游标的下列几个属性来了解与之相关的sql信息

SQL%FOUND

SQL%NOTFOUND

SQL%ISOPEN

SQL%ROWCOUNT

1、 SQL%FOUND表示一条语句被执行后，受其影响而改变的记录数是否大于等于1，故通常适用于DML语句，或者select into. SQL执行前这个值为null,成功改变记录数后变为true,否则为false

declare

   empno_no number(4) :=7934;

  begin

 delete from emp where empno=empno_no;

   if sql%found then

  insert into emp(empno,ename,mgr) values(8000,'JACK',7902);

  end if;

  commit;

 end;

  /

这个例子即利用 SQL%FOUND，当删除一条记录后，才插入一条记录

特别注意select into的情况，仅当返回结果只有一条记录，Oracle才不会报错，如果返回结果0，则报错no data found,如果返回结果大于1条，则报错too many rows

declare

      emp1 varchar2(14);

     vc_message varchar2(4000);

    begin

     select empno into emp1 from emp where empno = 7900;

    exception

    when no_data_found then

    dbms_output.put_line('no data found!');

    return;

    when too_many_rows then

   dbms_output.put_line('too many rows!');

   return;

  when  others then

  vc_message := 'E'||'_'||sqlcode||'_'||sqlerrm;

     dbms_output.put_line(vc_message);

 return;end;

2、SQL%NOTFOUND

与 SQL%FOUND 相反，受其影响而改变的记录数是否为0，故通常适用于DML语句，或者select into. SQL执行前这个值为null,没有改变记录数为true，改变了就是false

3、SQL%ISOPEN

隐式游标中，这个值永远为false 

4、SQL%ROWCOUNT

SQL%FOUND表示一条语句被执行后，受其影响而改变的记录数，与SQL%FOUND SQL%NOTFOUND一样，这个值适用于update,delete,insert 等DML操作和select into，注意在select into中，返回值多于1时，Oracle会报错 ，这是这个值返回的是1，而不是select了多少条记录。当前 SQL%ROWCOUNT只代表上一个被执行的sql,如果有新的SQL执行，这个值会被覆盖，所以如果需要用到某条SQL执行后产生的这个值，可以在执行完后将该值放入一个变量中

二、显式游标（explicit cursor)

用于pl/sql的代码中（比如package，函数，存储过程），其生命周期中的open，fetch，close可以由我们在代码中显示控制，显式游标的四个常见属性 CURSORNAME%FOUND，CURSORNAME%NOTFOUND,CURSORNAME$ISOPEN,CURSORNAME%ROWCOUNT

1、CURSORNAME%FOUND

指定的显式游标(即cursorname的游标名字）是否有一条记录被fetch，当一个游标被open,还没有fetch,该值为null，fetch后该值为true,fetch 完所有记录后该值还是为true,这时再fetch一次，Oracle不会报错，而是该值变为false。如果该游标还没有被open就试图使用这个值，则会报invalid_cursor

 declare

    cursor c1 is select ename,sal from emp where rownum<10;

    my_name emp.ename%type;

    my_sal emp.sal%type;

  begin 

     open c1;

       loop

         fetch c1 into my_name,my_sal;

         if c1%found then 

           dbms_output.put_line('name='||my_name||',salary='||my_sal);      

         else

          exit;

         end if;

       end loop;

      close c1;

   end;

2、 CURSORNAME$ISOPEN

指定的游标是否被open ,通常用于标准的exception处理流程，用于 close那些由于exception而导致显示游标open了却没有被正常关闭的时候

exception 

   when others then 

    if c1%isopen=ture then 

      close c1;

   end if;

   return;

end;

3、 CURSORNAME%NOTFOUND

与1相反， 指定的显式游标(即cursorname的游标名字）是否有一条记录被fetch，当一个游标被open,还没有fetch,该值为null，fetch后该值为false,fetch 完所有记录后该值还是为false,这时再fetch一次，Oracle不会报错，而是该值变为true。如果该游标还没有被open就试图使用这个值，则会报invalid_cursor

declare

    cursor c1 is select ename,sal from emp where rownum<10;

    my_name emp.ename%type;

    my_sal emp.sal%type;

  begin 

     open c1;

       loop

         fetch c1 into my_name,my_sal;

         if c1%notfound then 

             exit;

          else

           dbms_output.put_line('name='||my_name||',salary='||my_sal);  

         end if;

       end loop;

      close c1;

   end;

4、 CURSORNAME%ROWCOUNT  表示该游标一共被fetch了多少行记录

   declare

    cursor c1 is select ename from emp where rownum<10;

    my_name emp.ename%type;

   begin

     open c1;

      loop

        fetch c1 into my_name;

        if c1%found then

          dbms_output.put_line(c1%rowcount||':='||my_name );

       else

          exit;

       end if ;

      end loop;

     close c1;

 end;

对显示游标四个属性的总结

1、当一个显示游标没有被open时，使用cursorname%found,cursorname%notfound,cursorname%rowcount,oracle会报错invalid_cursor

2、首次fetch时结果集返回一个空值，则cursorname%found 为false ,cursorname%notfound 为true，cursorname%rowcount 为0

最后看一个显式游标在pl/sql中的标准用法

create or replace procedure p_demo_explicit_cursor_std

  2  is

  3  cursor c1 is select * from emp where rownum<10;

  4  emp_rec emp%rowtype;

  5  begin

  6  open c1;

  7  fetch c1 into emp_rec;

  8  while (c1%found) loop

  9   dbms_output.put_line('name='||emp_rec.ename||',salary='||emp_rec.sal);

 10  fetch c1 into emp_rec;

 11  end loop;

 12  close c1;

 13  exception

 14   when others then 

 15     --o_parm:='E'||sqlcode||sqlerrm;

 16     rollback;

 17     --写日志

 18     RETURN；

 19  end p_demo_explicit_cursor_std;

注意以下两点

显示游标的标准用法，先open再fetch，然后一个while循环逐条处理数据，最后close

在while内部循环处理完一条记录后，一定要执行fetch以跳到下一条记录，不然会死循环

三、参考游标（ref_cursor)

和显式游标一样，参考游标也是用于pl/sql的代码中（比如package，函数，存储过程），其生命周期中的open，fetch，close可以由我们在代码中显示控制，参考游标的四个常见属性 CURSORNAME%FOUND，CURSORNAME%NOTFOUND,CURSORNAME$ISOPEN,CURSORNAME%ROWCOUNT，属性也是跟显式游标一样

参考游标是这三种游标中灵活性最好的一种游标，主要体现在以下几点

1、定义方式灵活，可以有多种定义方式

第一种方式

type typ_cur_emp is ref cursor return emp%rowtype;

cur_emp typ_cur_emp;

第二种方式

type typ_result is record(ename emp.ename%type,sal emp.sal%type);

type typ_cur_strong is ref cursor return typ_result;

cur_emp typ_cur_stong;

第三种方式

type typ_cur_weak is ref cursor;

cur_emp typ_cur_weak;

第四种方式

cur_emp sys_refcursor;

2、open方式灵活，不跟具体SQL绑定，可以随时open,每次open可以对应不同的sql

declare

 type typ_cur_emp is ref cursor return emp%rowtype;

 cur_emp typ_cur_emp;

 procedure process_emp_cv(emp_cv in typ_cur_emp) is

   person emp%rowtype;

 begin

   dbms_output.put_line('----');

   loop

     fetch emp_cv into person;

     exit when emp_cv%notfound;

     dbms_output.put_line('name='||person.ename);

   end loop;

 end;

 begin

   open cur_emp for select * from emp where rownum<11;

   process_emp_cv(cur_emp);

   close cur_emp;

   open cur_emp for select * from emp where ename like 'C%';

   process_emp_cv(cur_emp);

   close cur_emp;

  end;

3、参考游标可以做为存储过程的输入参数和函数的输出参数

4、参考游标的额外用法

除了一次fetch一条记录，还可以一次性fetch多条记录

可以和显示游标嵌套使用