Oracle中游標(biāo)Cursor的用法詳解
一、使用游標(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ù)
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)遍歷，實現(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)，實現(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í)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
oracle19c數(shù)據(jù)庫安裝相對于oracle12c安裝還是有些不一樣的,所以今天再來記錄一下安裝過程,下面這篇文章主要給大家介紹了關(guān)于Oracle19c安裝與基本配置的超詳細(xì)教程,需要的朋友可以參考下2023-01-01
Oracle的用戶、角色及權(quán)限相關(guān)操作
這篇文章主要介紹了Oracle的用戶、角色及權(quán)限相關(guān)操作,需要的朋友可以參考下2017-07-07
trunc函數(shù)用法用于截取時間或者數(shù)值,返回指定的值,下面這篇文章主要給大家介紹了關(guān)于Oracle中trunc()函數(shù)詳解的相關(guān)資料,文中通過實例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-01-01
Oracle表空間（tablespaces）是一個邏輯的概念,真正存放數(shù)據(jù)的是數(shù)據(jù)文件（data files）,下面這篇文章主要給大家介紹了關(guān)于Oracle表空間與權(quán)限的相關(guān)資料,需要的朋友可以參考下2021-11-11
DBLink的作用是在局域網(wǎng)內(nèi)，通過一臺服務(wù)器上面的數(shù)據(jù)庫訪問另外一臺服務(wù)器上面數(shù)據(jù)庫的功能。下面這篇文章主要給大家介紹了關(guān)于Oracle中dblink實際應(yīng)用的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，需要的朋友們下面來一起看看吧。2017-09-09
Oracle顯示游標(biāo)的使用及游標(biāo)for循環(huán)
本篇文章給大家介紹oracle顯示游標(biāo)的使用及游標(biāo)for循環(huán)，當(dāng)查詢返回單行記錄時使用隱式游標(biāo)，查詢返回多行記錄并逐行進行處理時使用顯式游標(biāo)，對本文感興趣的朋友一起學(xué)習(xí)吧2015-11-11
用于抽樣統(tǒng)計，從數(shù)據(jù)庫中按類別隨機 抽取各類用戶2009-05-05
這篇文章主要介紹了oracle基礎(chǔ)語法詳解,非常適合Oracle數(shù)據(jù)庫的初步學(xué)習(xí)，本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-05-05
深入Oracle的left join中on和where的區(qū)別詳解
本篇文章是對Oracle的left join中on和where的區(qū)別進行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
經(jīng)常寫 sql 的同學(xué)應(yīng)該會接觸到一些 oracle 的日期時間函數(shù), 例如: 財務(wù)軟件或者人力資源軟件需要按照每年, 每季度, 每月, 甚至每個星期來進行統(tǒng)計2014-05-05