熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle數據庫游標的類型

2013-11-13 15:49:37  來源: Oracle 

  游標是SQL的一個內存工作區由系統或用戶以變量的形式定義游標的作用就是用於臨時存儲從數據庫中提取的數據塊

  Oracle數據庫的Cursor類型包含三種 靜態游標分為顯式(explicit)游標和隱式(implicit)游標REF游標是一種引用類型類似於指針

  測試數據

  create table student(sno number primary keysname varchar))

  declare i number:=;

  beginwhile i<=

  loop

  insert into student(snosname) values (iname||to_char(i))

  i:=i+;

  end loop;

  end;

  隱式游標屬性

  SQL%ROWCOUNT 整型代表DML語句成功執行的數據行數

  SQL%FOUND 布爾型值為TRUE代表插入刪除更新或單行查詢操作成功

  SQL%NOTFOUND 布爾型與SQL%FOUND屬性返回值相反

  SQL%ISOPEN 布爾型DML執行過程中為真結束後為假

  declarebegin  update student set sname =name||to_char(sno*) where sname=name;

  if sql%found then

  dbms_outputput_line(name is updated

  else

  dbms_outputput_line(沒有記錄

  end if;

  end;

  declare

  begin

  for names in (select * from student) loop

  dbms_outputput_line(namessname)

  end loop;

  exception when others then

  dbms_outputput_line(sqlerrm)

  end;

  顯式游標屬性

  %ROWCOUNT 獲得FETCH語句返回的數據行數

  %FOUND 最近的FETCH語句返回一行數據則為真否則為假

  %NOTFOUND 布爾型 與%FOUND屬性返回值相反

  %ISOPEN 布爾型 游標已經打開時值為真否則為假

  對於顯式游標的運用分為四個步驟

  a 定義游標 Cursor [Cursor Name] IS;

  b 打開游標 Open [Cursor Name];

  c 操作數據 Fetch [Cursor name];

  d 關閉游標 Close [Cursor Name];

  典型顯式游標

  declare cursor cur_rs is select * from student; sinfo student%rowtype;

  begin  open cur_rs;

  loop

  fetch cur_rs into sinfo;

  exit when cur_rs%%notfound;

  dbms_outputput_line(sinfosname)

  end loop;

  exception when others then

  dbms_outputput_line(sqlerrm)

  end;

  帶參數open的顯式cursor:

  declare cursor cur_rs(in_name varchar) is select *

  from student where sname=in_name;

  begin  for sinfo in cur_rs(sname) loop

  dbms_outputput_line(sinfosname)

  end loop;

  exception when others then

  dbms_outputput_line(sqlerrm)

  end;

  使用current of語句執行update或delete操作

  declare

  cursor cur_rs is select * from student for update;

  begin  for sinfo in cur_rs loop

  update student set sname=sname||xx where current of cur_rs;

  end loop;

  commit;

  exception when others then

  dbms_outputput_line(sqlerrm)

  end;

  REF游標用於處理運行時才能確定的動態sql查詢結果利用REF CURSOR可以在程序間傳遞結果集(一個程序裡打開游標變量在另外的程序裡處理數據)

  也可以利用REF CURSOR實現BULK SQL提高SQL性能

  REF CURSOR分兩種Strong REF CURSOR 和 Weak REF CURSOR

  Strong REF CURSOR: 指定retrun typeCURSOR變量的類型必須和return type一致

  Weak REF CURSOR: 不指定return type能和任何類型的CURSOR變量匹配

  運行時根據動態sql查詢結果遍歷

  create or replace package pkg_test as

  type student_refcursor_type is ref cursor return student%rowtype;

  procedure student_rs_loop(cur_rs IN student_refcursor_type)

  end pkg_test;

  create or replace package body pkg_test as

  procedure student_rs_loop(cur_rs IN student_refcursor_type) is

  std student%rowtype;

  begin  loop

  fetch cur_rs into std;

  exit when cur_rs%NOTFOUND;

  dbms_outputput_line(stdsname)

  end loop;

  end student_rs_loop;

  end pkg_test;

  declare stdRefCur pkg_teststudent_refcursor_type;

  begin  for i in loop

  dbms_outputput_line(Student NO= || i)

  open stdRefCur for select * from student where sno=i;

  pkg_teststudent_rs_loop(stdRefCur)

  end loop;

  exception when others then dbms_outputput_line(sqlerrm)

  close stdRefCur;

  end;

  使用FORALL和BULK COLLECT子句利用BULK SQL可以減少PLSQL Engine和SQL Engine之間的通信開銷提高性能

  加速INSERT UPDATE DELETE語句的執行也就是用FORALL語句來替代循環語句

  加速SELECT用BULK COLLECT INTO 來替代INTO

  create table

  student_tmp as select sno

  sname from student where =;

  刪除主鍵約束 alter table student drop constraint SYS_C;

  執行兩遍插入 insert into student select * from student where sno=;

  declare cursor cur_std(stdid studentsno%type) is select sno

  sname from student where sno=stdid;

  type student_table_type is table of cur_std%rowtype index by pls_integer;

  student_table student_table_type;

  begin

  open cur_std(

  fetch cur_std bulk collect into student_table;

  close cur_std;

  for i in unt loop

  dbms_outputput_line(student_table(i)sno ||

  || student_table(i)sname)

  end loop;

  forall i in student_tablefirststudent_tablelast

  insert into student_tmp values(student_table(i)sno student_table(i)sname)

  commit;

  end;

  清理實驗環境 drop table student purge; drop package pkg_test;


From:http://tw.wingwit.com/Article/program/Oracle/201311/17358.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.