session cursor 的種類和用法

oracle 中的session cursor分為三種：顯式游標(biāo)（explicit cursor） 隱式游標(biāo)（implicit cursor） 參考游標(biāo)（ref cursor)

一、 隱式游標(biāo)（implicit cursor）

   無(wú)處不在，oracle中最常見的游標(biāo)，只要執(zhí)行一個(gè)SQL或者pl/sql，Oracle就會(huì)自動(dòng)創(chuàng)建一個(gè)隱式游標(biāo)，它的生命周期（open，bind，parse，execute，fetch，close）由SQL引擎或者pl/sql引擎自動(dòng)控制，所有也意味著我們失去了對(duì)隱式游標(biāo)的控制權(quán)。

  不過(guò)還是可以通過(guò)隱式游標(biāo)的下列幾個(gè)屬性來(lái)了解與之相關(guān)的sql信息

SQL%FOUND

SQL%NOTFOUND

SQL%ISOPEN

SQL%ROWCOUNT

1、 SQL%FOUND表示一條語(yǔ)句被執(zhí)行后，受其影響而改變的記錄數(shù)是否大于等于1，故通常適用于DML語(yǔ)句，或者select into. SQL執(zhí)行前這個(gè)值為null,成功改變記錄數(shù)后變?yōu)閠rue,否則為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;

  /

這個(gè)例子即利用 SQL%FOUND，當(dāng)刪除一條記錄后，才插入一條記錄

特別注意select into的情況，僅當(dāng)返回結(jié)果只有一條記錄，Oracle才不會(huì)報(bào)錯(cuò)，如果返回結(jié)果0，則報(bào)錯(cuò)no data found,如果返回結(jié)果大于1條，則報(bào)錯(cuò)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 相反，受其影響而改變的記錄數(shù)是否為0，故通常適用于DML語(yǔ)句，或者select into. SQL執(zhí)行前這個(gè)值為null,沒(méi)有改變記錄數(shù)為true，改變了就是false

3、SQL%ISOPEN

隱式游標(biāo)中，這個(gè)值永遠(yuǎn)為false 

4、SQL%ROWCOUNT

SQL%FOUND表示一條語(yǔ)句被執(zhí)行后，受其影響而改變的記錄數(shù)，與SQL%FOUND SQL%NOTFOUND一樣，這個(gè)值適用于update,delete,insert 等DML操作和select into，注意在select into中，返回值多于1時(shí)，Oracle會(huì)報(bào)錯(cuò) ，這是這個(gè)值返回的是1，而不是select了多少條記錄。當(dāng)前 SQL%ROWCOUNT只代表上一個(gè)被執(zhí)行的sql,如果有新的SQL執(zhí)行，這個(gè)值會(huì)被覆蓋，所以如果需要用到某條SQL執(zhí)行后產(chǎn)生的這個(gè)值，可以在執(zhí)行完后將該值放入一個(gè)變量中

二、顯式游標(biāo)（explicit cursor)

用于pl/sql的代碼中（比如package，函數(shù)，存儲(chǔ)過(guò)程），其生命周期中的open，fetch，close可以由我們?cè)诖a中顯示控制，顯式游標(biāo)的四個(gè)常見屬性 CURSORNAME%FOUND，CURSORNAME%NOTFOUND,CURSORNAME$ISOPEN,CURSORNAME%ROWCOUNT

1、CURSORNAME%FOUND

指定的顯式游標(biāo)(即cursorname的游標(biāo)名字）是否有一條記錄被fetch，當(dāng)一個(gè)游標(biāo)被open,還沒(méi)有fetch,該值為null，fetch后該值為true,fetch 完所有記錄后該值還是為true,這時(shí)再fetch一次，Oracle不會(huì)報(bào)錯(cuò)，而是該值變?yōu)閒alse。如果該游標(biāo)還沒(méi)有被open就試圖使用這個(gè)值，則會(huì)報(bào)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

指定的游標(biāo)是否被open ,通常用于標(biāo)準(zhǔn)的exception處理流程，用于 close那些由于exception而導(dǎo)致顯示游標(biāo)open了卻沒(méi)有被正常關(guān)閉的時(shí)候

exception 

   when others then 

    if c1%isopen=ture then 

      close c1;

   end if;

   return;

end;

3、 CURSORNAME%NOTFOUND

與1相反， 指定的顯式游標(biāo)(即cursorname的游標(biāo)名字）是否有一條記錄被fetch，當(dāng)一個(gè)游標(biāo)被open,還沒(méi)有fetch,該值為null，fetch后該值為false,fetch 完所有記錄后該值還是為false,這時(shí)再fetch一次，Oracle不會(huì)報(bào)錯(cuò)，而是該值變?yōu)閠rue。如果該游標(biāo)還沒(méi)有被open就試圖使用這個(gè)值，則會(huì)報(bào)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  表示該游標(biāo)一共被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;

對(duì)顯示游標(biāo)四個(gè)屬性的總結(jié)

1、當(dāng)一個(gè)顯示游標(biāo)沒(méi)有被open時(shí)，使用cursorname%found,cursorname%notfound,cursorname%rowcount,oracle會(huì)報(bào)錯(cuò)invalid_cursor

2、首次fetch時(shí)結(jié)果集返回一個(gè)空值，則cursorname%found 為false ,cursorname%notfound 為true，cursorname%rowcount 為0

最后看一個(gè)顯式游標(biāo)在pl/sql中的標(biāo)準(zhǔn)用法

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;

注意以下兩點(diǎn)

顯示游標(biāo)的標(biāo)準(zhǔn)用法，先open再fetch，然后一個(gè)while循環(huán)逐條處理數(shù)據(jù)，最后close

在while內(nèi)部循環(huán)處理完一條記錄后，一定要執(zhí)行fetch以跳到下一條記錄，不然會(huì)死循環(huán)

三、參考游標(biāo)（ref_cursor)

和顯式游標(biāo)一樣，參考游標(biāo)也是用于pl/sql的代碼中（比如package，函數(shù)，存儲(chǔ)過(guò)程），其生命周期中的open，fetch，close可以由我們?cè)诖a中顯示控制，參考游標(biāo)的四個(gè)常見屬性 CURSORNAME%FOUND，CURSORNAME%NOTFOUND,CURSORNAME$ISOPEN,CURSORNAME%ROWCOUNT，屬性也是跟顯式游標(biāo)一樣

參考游標(biāo)是這三種游標(biāo)中靈活性最好的一種游標(biāo)，主要體現(xiàn)在以下幾點(diǎn)

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綁定，可以隨時(shí)open,每次open可以對(duì)應(yīng)不同的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、參考游標(biāo)可以做為存儲(chǔ)過(guò)程的輸入?yún)?shù)和函數(shù)的輸出參數(shù)

4、參考游標(biāo)的額外用法

除了一次fetch一條記錄，還可以一次性fetch多條記錄

可以和顯示游標(biāo)嵌套使用