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,否则为falsedeclare   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%ROWCOUNT1、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可以对应不同的sqldeclare 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多条记录可以和显示游标嵌套使用