文章詳情頁

Oracle中游標(biāo)Cursor的用法詳解

瀏覽：202日期：2023-03-12 15:25:10

一、使用游標(biāo)

對于DML語句和單行select into ,oracle自動分配隱形游標(biāo)。處理select返回多行語句，可以使用顯式游標(biāo)。

使用顯示游標(biāo)處理多行數(shù)據(jù),也可使用SELECT..BULK COLLECT INTO 語句處理多行數(shù)據(jù).

1.定義游標(biāo)

cursor cursor_name is select_statement;

2.打開游標(biāo)

執(zhí)行對應(yīng)的SELECT語句并將SELECT語句的結(jié)果暫時存放到結(jié)果集中.

open cursor_name;

3.提取數(shù)據(jù)

打開游標(biāo)后,SELECT語句的結(jié)果被臨時存放到游標(biāo)結(jié)果集中,使用FETCH語句只能提取一行數(shù)據(jù)

通過使用FETCH..BULK COLLECT INTO語句每次可以提取多行數(shù)據(jù)

fetch cursor_name into variable1,varibale2,...;

fetch cursor_name bulk collect into collect1,collect2,...[limit rows];

(1)游標(biāo)中使用fetch..into語句:只能處理一行數(shù)據(jù),除非用循環(huán)語句

declare
  v_bookname varchar2(100);
  cursor c_book(i_id number) is select bookname from book where id = i_id;
    begin
Open c_book(10);--打開游標(biāo)
Loop
    Fetch c_book into v_bookname; --提取游標(biāo)
    exit when c_book%notfound;
    update book set price = "33" where bookname = v_bookname;
End Loop;
Close c_book;--關(guān)閉游標(biāo)
    end;

或

declare
  v_bookname varchar2(100);
  cursor c_book(i_id number) is select bookname from book where id = i_id;
begin
  Open c_book(10);
  Fetch c_book into v_bookname;--預(yù)先Fetch一次
  While c_book%found Loop
      update book set price = "33" where bookname = v_bookname;
       Fetch c_book into v_bookname;
  End Loop;
 Close c_book;
end;

(3)基于游標(biāo)定義記錄變量

declare
    cursor emp_cursor is select ename,sal from emp;
    emp_record emp_cursor%rowtype;
  begin
    open emp_cursor;
    loop
     fetch emp_cursor into emp_record;
     exit when emp_cursor%notfound;
     dbms_output.put_line("雇員名:"||emp_record.ename||",雇員工資:"||emp_record.sal);
    end loop;
 end;

4.關(guān)閉游標(biāo)

close cursor_name;

5.游標(biāo)屬性

用于返回顯示游標(biāo)的執(zhí)行信息,包括%isopen,%found,%notfound,%rowcount

%isopen:確定游標(biāo)是否打開
%found:檢查是否從結(jié)果集中提取到了數(shù)據(jù)
%notfound:與%found行為相反。
%rowcount:返回當(dāng)前行為止已經(jīng)提取到的實(shí)際行數(shù)

no_data_found和%notfound的用法是有區(qū)別的，小結(jié)如下1)SELECT. . . INTO 語句觸發(fā) no_data_found；
2)當(dāng)一個顯式光標(biāo)(靜態(tài)和動態(tài))的 where 子句未找到時觸發(fā) %notfound；
3)當(dāng)UPDATE或DELETE語句的where 子句未找到時觸發(fā) sql%notfound；
4)在光標(biāo)的提取(Fetch)循環(huán)中要用 %notfound 或%found 來確定循環(huán)的退出條件，不要用no_data_found。

6.參數(shù)游標(biāo)

注意：定義參數(shù)游標(biāo)時,游標(biāo)參數(shù)只能指定數(shù)據(jù)類型,而不能指定長度。

declare
    cursor emp_cursor(no number) is select ename from emp where deptno=no;
    v_ename emp.ename%type;
  begin
    open emp_cursor(10);
    loop
     fetch emp_cursor into v_ename;
     exit when emp_cursor%notfound;
     dbms_output.put_line(v_ename);
    end loop;
    close emp_cursor;
  end;

二、for循環(huán)遍歷，實(shí)現(xiàn)遍歷游標(biāo)最高效方式。

使用FOR循環(huán)時,ORACLE會隱含的打開游標(biāo),提取游標(biāo)數(shù)據(jù)并關(guān)閉游標(biāo)。

每循環(huán)一次提取一次數(shù)據(jù)，在提取了所有數(shù)據(jù)后,自動退出循環(huán)并隱含的關(guān)閉游標(biāo)。

1.使用游標(biāo)FOR循環(huán)

--不需要聲明v_bookname,Open和Close游標(biāo)和fetch操作(不用打開游標(biāo)和關(guān)閉游標(biāo)，實(shí)現(xiàn)遍歷游標(biāo)最高效方式)
declare
 cursor c_book(i_id number) is select bookname from book where id = i_id;
begin
   for cur in c_book(10) loop --循環(huán)變量cur不需要聲明
     update book set price = "53" where bookname = cur.bookname;
   end loop;
end;

2.在游標(biāo)FOR循環(huán)中直接使用子查詢

begin
     for emp_record in (select ename,sal from emp) loop
dbms_output.put_line(emp_record.ename);
    end loop;
end;

三、使用游標(biāo)更新或刪除數(shù)據(jù)

要通過游標(biāo)更新或刪除數(shù)據(jù),在定義游標(biāo)時必須要帶有FOR UPDATE子句

cursor cursor_name(parameter_name datetype) is select_statement for update [of column_reference] [nowait];

for update子句：用于在游標(biāo)結(jié)果集數(shù)據(jù)上家行共享鎖,防止其他用戶在相應(yīng)行執(zhí)行DML操作
of子句：確定哪些表要加鎖,沒有OF子句,則在所引用的全部表上加鎖
nowait子句：用于指定不等待鎖
必須在UPDATE后DELETE語句中引用WHERE CURRENT OF子句
update table_name set column=.. where current of cursor_name;
delete table_name where current of cursor_name;

declare
    cursor emp_cursor is select ename,sal from emp for update;
    v_ename emp.ename%type;
    v_sal emp.sal%tyep;
  begin
    open emp_cursor;
    loop
     fetch emp_cursor into v_ename,v_oldsal;
     exit when emp_cursor%notfound;
     if v_oldsal<2000 then
update emp set sal=sal+100 where current of emp_cursor;--delete from emp where current of emp_cursor;
     end if;
   end loop;
   close emp_cursor;
 end;

四、通過bulk collect減少loop處理的開銷

將查詢結(jié)果一次性加載到集合中，而不是一條一條的加載。

（1）在顯示游標(biāo)中,使用FETCH..BALK COLLECT INTO語句提取所有數(shù)據(jù)

declare
   cursor emp_cursor is select ename from emp where deptno=10;
    type ename_table_type is table of varchar2(10);
    ename_table ename_table_type;
  begin
    open emp_cursor;
    fetch emp_cursor bulk collect into ename_table;
    for i in 1..ename_table.count loop
       dbms_output.put_line(ename_table(i));
    end loop;
    close emp_cursor;
  end;

（2）游標(biāo)中使用FETCH..BULK COLLECT INTO ..LIMIT語句提取部分?jǐn)?shù)據(jù)

declare
    type name_array_type is varray(5) of varchar2(10);
    name_array name_array_type;
    cursor emp_cursor is select ename from emp;
    rows int:=5;
    v_count int:=0;
  begin
    open emp_cursor;
    loop
     fetch emp_cursor bulk collect into name_array limit rows;
     dbms_output.pur("雇員名");
     for i in 1..(emp_currsor%rowcount-v_count) loop
       dbms_output.put(name_array(i)||" ");
     end loop;
     dbms_output.new_line;
    v_count:=emp_cursor%rowcount;
    exit when emp_cursor%notfound;
    end loop;
    close emp_cursor;
  end;

五、使用游標(biāo)變量

PL/SQL的游標(biāo)變量中存放著指向內(nèi)存地址的指針.

1.游標(biāo)變量使用步驟

包括定義游標(biāo)變量,打開游標(biāo),提取游標(biāo)數(shù)據(jù),關(guān)閉游標(biāo)等四個階段

1.1定義ref cursor類型和游標(biāo)變量

type ref_type_name is ref cursor [return return_type];

cursor_varibale ref_type_name;

當(dāng)指定RETURN子句時,其數(shù)據(jù)類型必須是記錄類型,不能在包內(nèi)定義游標(biāo)變量

1.2打開游標(biāo)

open cursor_variable for select_statement;

1.3提取游標(biāo)數(shù)據(jù)

fetch cursor_varibale into variable1,variable2,...;

fetch cursor_varibale bulk collect into collect1,collect2,...[limit rows]

1.4關(guān)閉游標(biāo)變量

close cursor_varibale;

2.游標(biāo)變量使用示例

1、在定義FEF CURSOR類型時不指定RETURN子句

在打開游標(biāo)時可以指定任何的SELECT語句

declare
    type emp_cursor_type is ref cursor;
    emp_cursor emp_cursor_type;
    emp_record emp%rowtype;
  begin
    open emp_cursor for select * from emp where deptno=10;
    loop
     fetch emp_cursor into emp_record;
     exit when emp_cursor%notfound;
     dbms_output.put_line("第"||emp_curosr%rowcount||"個雇員: "||emp_record.ename);
    end loop;
    close emp_cursor;
  end;

2、在定義REF CURSOR類型時指定RETURN子句

在打開游標(biāo)時SELECT語句的返回結(jié)果必須與RETURN子句所指定的記錄類型相匹配.

declare
    type emp_record_type is record(name varchar2(10),salary number(6,2));
    type emp_cursor_type is ref cursor return emp_record_type;
    emp_cursor emp_cursor_type;
    emp_record emp_record_type;
  begin
    open emp_cursor for select ename,sal from emp where deptno=20;
    loop
     fetch emp_cursor into emp_record;
     exit when emp_cursor%notfound;
     dbms_output.put_line("第"||emp_curosr%rowcount||"個雇員: "||emp_record.ename);
    end loop;
    close emp_cursor;
 end;

到此這篇關(guān)于Oracle中游標(biāo)Cursor用法的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持。

Oracle

上一條：Oracle對PL/SQL中的異常處理下一條：Oracle ORA-00904:標(biāo)識符無效解決方法(太坑了!!)

相關(guān)文章：

1. Oracle數(shù)據(jù)庫備份與恢復(fù)精華資料集錦2. Oracle數(shù)據(jù)庫不完全恢復(fù)實(shí)例3. 解讀Oracle數(shù)據(jù)庫后臺進(jìn)程的功能4. SQL Server與Oracle鏈接服務(wù)器應(yīng)用5. 實(shí)例講解如何使用Oracle數(shù)據(jù)庫to_date()6. 我的oracle筆記三（系統(tǒng)函數(shù)和系統(tǒng)包使用方面）7. ORACLE中常用的幾種正則表達(dá)式小結(jié)8. Oracle 9i在Red Hat7.1和7.2上的安裝9. SQL Server和Oracle數(shù)據(jù)鎖定比較10. 流行的oracle恢復(fù)工具

排行榜

					
					Oracle數(shù)據(jù)庫備份與恢復(fù)精華資料集錦
Oracle數(shù)據(jù)庫不完全恢復(fù)實(shí)例
怎樣才能保護(hù)好SQL Server 數(shù)據(jù)庫
解讀Oracle數(shù)據(jù)庫后臺進(jìn)程的功能
并行方式處理DB2 for iSeries索引（1）
MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理
MySQL case when使用方法實(shí)例解析
MySQL 如何設(shè)計(jì)統(tǒng)計(jì)數(shù)據(jù)表
MySQL系列之三 基礎(chǔ)篇
Sql Server 壓縮數(shù)據(jù)庫日志文件的方法
MySQL OOM（內(nèi)存溢出）的解決思路
				