一、認(rèn)識游標(biāo)

什么是游標(biāo)？游標(biāo)是數(shù)據(jù)庫的一種數(shù)據(jù)類型，它用來管理從數(shù)據(jù)源(表，視圖等)獲取到的數(shù)據(jù)結(jié)果集，可以想象成一個游動的光標(biāo)(指針)，指向一個結(jié)果集，通過游標(biāo)的移動逐行提取每一行的記錄，就像我們屏幕上的光標(biāo)指示當(dāng)前位置一樣，“游標(biāo)”由此得名。

游標(biāo)分成靜態(tài)游標(biāo)和動態(tài)游標(biāo)(也叫REF游標(biāo))。

靜態(tài)游標(biāo)：所謂靜態(tài)游標(biāo)，顧名思義，指的是數(shù)據(jù)已經(jīng)固定的游標(biāo)，在使用游標(biāo)前，已經(jīng)知道游標(biāo)中的數(shù)據(jù)和類型。靜態(tài)游標(biāo)又可以細(xì)分成顯式游標(biāo)和隱式游標(biāo)，顯示游標(biāo)指的是已經(jīng)定義在變量區(qū)，并且已經(jīng)指定結(jié)果集的游標(biāo)；隱式游標(biāo)則是不用定義，直接就可以用的游標(biāo)，比如系統(tǒng)定義的隱式游標(biāo)sql，比如使用for循環(huán)遍歷某個SQL(這個SQL的結(jié)果集就是一個隱式游標(biāo))的自定義游標(biāo)。

動態(tài)游標(biāo)：與靜態(tài)游標(biāo)相反，在定義的時候并不知道其結(jié)果集，在使用時，再給它定義結(jié)果集(通俗來說，就是查詢數(shù)據(jù)的SQL不是一成不變的)的游標(biāo)。動態(tài)游標(biāo)也可以細(xì)分成強(qiáng)類型和弱類型游標(biāo)，強(qiáng)類型游標(biāo)規(guī)定了其返回類型，弱類型游標(biāo)則是不規(guī)定返回類型，可以獲取任何結(jié)果集。

在使用游標(biāo)時，通常需要借助游標(biāo)的一些屬性來做邏輯判斷，比如說判斷游標(biāo)是否已經(jīng)到了結(jié)果集的尾部，這個時候可以使用游標(biāo)的found屬性來做判斷：if 游標(biāo)%found then 。。以下是游標(biāo)的一些屬性具體說明：

1.%found?：用于檢驗游標(biāo)是否成功，通常在fetch語句前使用，當(dāng)游標(biāo)按照條件查詢一條記錄是，返回true。fetch語句(獲取記錄)執(zhí)行情況True or False。

2.%notfound :?最后一條記錄是否提取出true or false。?到了游標(biāo)尾部，沒有記錄了，就返回true

3.%isopen?： 游標(biāo)是否打開true or false。

4.%rowcount?：游標(biāo)當(dāng)前提取的行數(shù)?,即獲得影響的行數(shù)。

二、游標(biāo)使用的語法

1.靜態(tài)游標(biāo)語法(顯式):

a.聲明游標(biāo)：劃分存儲區(qū)域，注意此時并沒有執(zhí)行Select?語句：

CURSOR?游標(biāo)名(參數(shù) 列表) ??[返回值類型]? ?is? select?語句；

b.打開游標(biāo)：執(zhí)行select?語句，獲得結(jié)果集存儲到游標(biāo)中，此時游標(biāo)指向結(jié)果集頭部，類似于java的迭代器，必須先執(zhí)行.next()，游標(biāo)才指向第一條記錄。

open?游標(biāo)名(參數(shù) 列表);

c.獲取記錄：移動游標(biāo)取一條記錄:

fetch ?游標(biāo)名 into ?臨時記錄或?qū)傩灶愋妥兞?#xff1b;

d.關(guān)閉游標(biāo)：將游標(biāo)放入緩沖池中，沒有完全釋放資源。可重新打開。

close ?游標(biāo)名；

2.動態(tài)游標(biāo)語法:

a.聲明REF游標(biāo)類型：這個聲明相當(dāng)于自定義一個游標(biāo)類型，在聲明REF游標(biāo)類型時，可以一并確定REF?游標(biāo)的分類：

⑴強(qiáng)類型REF游標(biāo)：指定retrun type，REF?游標(biāo)變量的類型必須和return type一致。

語法：type ??REF游標(biāo)名? ?is? ?ref cursor return ?結(jié)果集返回記錄類型；

⑵弱類型REF游標(biāo)：不指定return type，能和任何類型的CURSOR變量匹配，用于獲取任何結(jié)果集。

語法：type ??REF游標(biāo)名?is? ?ref cursor；

b.聲明REF游標(biāo)類型變量：

語法：變量名??已聲明Ref游標(biāo)類型；

c.打開REF游標(biāo)，關(guān)聯(lián)結(jié)果集：

語法：open ??REF 游標(biāo)類型變量 ??for ??查詢語句返回結(jié)果集；

d.獲取記錄，操作記錄：

語法：fetch ???REF游標(biāo)名 into? ?臨時記錄類型變量或?qū)傩灶愋妥兞苛斜?#xff1b;

e.關(guān)閉游標(biāo)，完全釋放資源：

語法：close? ?REF游標(biāo)名；

3.游標(biāo)的遍歷:

a.for循環(huán)游標(biāo):使用for循環(huán)遍歷游標(biāo)時，會自動打開游標(biāo)，并且循環(huán)結(jié)束會自動關(guān)閉游標(biāo)，所以在for循環(huán)之前和之后都不需要對游標(biāo)進(jìn)行open、close操作。另外，緊跟著for關(guān)鍵字的變量是不需要提前定義的。語法：

for 變量名 in 游標(biāo)名

loop

處理邏輯;

end loop;

b.loop循環(huán)游標(biāo):? ?loop循環(huán)是不會自動打開或者關(guān)閉游標(biāo)的，需要手動操作。退出循環(huán)語句必須在執(zhí)行邏輯操作之前執(zhí)行，原因是因為即使游標(biāo)已經(jīng)遍歷完，已經(jīng)記錄游標(biāo)變量的記錄是不會清除的，如果先執(zhí)行邏輯操作，會導(dǎo)致循環(huán)多走一次。語法:

open 游標(biāo)名;

loop

fetch ?游標(biāo)名 into ?臨時記錄或?qū)傩灶愋妥兞?多個以逗號隔開);

exit ?when ??游標(biāo)名%notfound;

邏輯操作

end ??loop;

close 游標(biāo)名;

c.while循環(huán)游標(biāo):和loop有一點類似，語法：

open 游標(biāo)名;

fetch ?游標(biāo)名 into 臨時記錄或?qū)傩灶愋妥兞?多個以逗號隔開);

while 游標(biāo)名%found

loop

邏輯處理；

fetch ?游標(biāo)名 into 臨時記錄或?qū)傩灶愋妥兞?多個以逗號隔開);

end loop;

close?游標(biāo)名;

三、示例

以下寫了兩個存儲過程，分別記錄了靜態(tài)游標(biāo)和動態(tài)游標(biāo)的基礎(chǔ)用法，可以用作參考：

靜態(tài)游標(biāo)相關(guān)：

create or replace procedure test_static_cursor is

--無參數(shù)靜態(tài)顯式游標(biāo)

-- return test_user%rowtype 這里的返回值可以要 也可以不要,因為后面的SQL已經(jīng)指定了返回值

CURSOR static_cursor return test_user%rowtype is

select * /**u.id, u.username, u.password*/

from test_user u;

--帶參數(shù)的顯示游標(biāo) (參數(shù)名 參數(shù)類型 [default 默認(rèn)值])

CURSOR static_cursor1(p_name test_user.id%type default '123') is

select * from test_user u where u.id = p_name;

--定義變量 這里的變量類型的意思是保持和test_user的id列的類型一致.

--在定義變量以獲取游標(biāo)的數(shù)據(jù)時,建議使用這種方式

v_id test_user.id%type;

v_username test_user.username%type;

v_password varchar2(32);

--定義記錄(記錄的意思是游標(biāo)的一條記錄)變量

v_record static_cursor1%rowtype;

v_num number;

begin

--初始化一些數(shù)據(jù)

delete from test_user;

commit;

select count(1) into v_num from test_user;

if v_num = 0 then

insert into test_user

(id, username, password)

values

('123', 'shaoyu', 'shaoyu');

--系統(tǒng)定義的隱式游標(biāo):SQL

--注意一句sql語句只會影響一個隱式游標(biāo),多個sql語句執(zhí)行會覆蓋隱式游標(biāo)sql

if sql%found then

dbms_output.put_line('成功插入' || sql%rowcount || '條數(shù)據(jù)');

end if;

insert into test_user

(id, username, password)

values

('456', 'admin', 'admin');

insert into test_user

(id, username, password)

values

('789', 'system', 'system');

commit;

end if;

--打開游標(biāo),此時會執(zhí)行定義游標(biāo)時的SQL

open static_cursor;

--讀取游標(biāo)數(shù)據(jù)

fetch static_cursor

into v_id, v_username, v_password;

--驗證

dbms_output.put_line(v_id || '-' || v_username || '-' || v_password);

--關(guān)閉游標(biāo)

close static_cursor;

--打開游標(biāo)

open static_cursor1('456');

--讀取游標(biāo)數(shù)據(jù)存入單個變量

fetch static_cursor1

into v_id, v_username, v_password;

--驗證

dbms_output.put_line(v_id || '-' || v_username || '-' || v_password);

close static_cursor1;

open static_cursor1('789');

--讀取游標(biāo)數(shù)據(jù)存入記錄變量

fetch static_cursor1 into v_record ;

--驗證

dbms_output.put_line(v_record.id || '-' || v_record.username || '-' || v_record.password);

close static_cursor1;

--游標(biāo)的遍歷:

--1.for循環(huán)(不需要打開游標(biāo))

dbms_output.put_line('for循環(huán)');

if static_cursor%isopen then

dbms_output.put_line('游標(biāo)已打開');

else

dbms_output.put_line('游標(biāo)未打開');

end if;

--data不需要提前定義

for data in static_cursor loop

if static_cursor%isopen then

dbms_output.put_line('游標(biāo)已打開');

else

dbms_output.put_line('游標(biāo)未打開');

end if;

dbms_output.put_line(data.id || '-' || data.username || '-' ||

data.password);

end loop;

if static_cursor%isopen then

dbms_output.put_line('游標(biāo)已打開');

else

dbms_output.put_line('游標(biāo)未打開');

end if;

--2.loop循環(huán)

dbms_output.put_line('loop循環(huán)');

open static_cursor;

loop

fetch static_cursor

into v_id, v_username, v_password;

exit when static_cursor%notfound;

dbms_output.put_line(v_id || '-' || v_username || '-' || v_password);

end loop;

close static_cursor;

--3.while循環(huán)

dbms_output.put_line('while循環(huán)');

open static_cursor;

fetch static_cursor

into v_id, v_username, v_password;

while static_cursor%found loop

dbms_output.put_line(v_id || '-' || v_username || '-' || v_password);

fetch static_cursor

into v_id, v_username, v_password;

end loop;

close static_cursor;

end test_static_cursor;

動態(tài)游標(biāo)：

create or replace procedure test_dynamic_cursor is

--定義強(qiáng)類型REF游標(biāo)類型

type dynamic_cursor_type1 is ref cursor return test_user%rowtype;

--定義弱類型REF游標(biāo)

type dynamic_cursor_type2 is ref cursor;

--定義強(qiáng)類型REF自定義返回記錄類型游標(biāo)類型 先定義自定義返回記錄類型 再定義游標(biāo)類型

type dynamic_cursor_type3_rec is record(

user_id test_user.id%type,

username test_user.username%type);

type dynamic_cursor_type3 is ref cursor return dynamic_cursor_type3_rec;

--定義之前定義好的游標(biāo)類型

dynamic_cursor1 dynamic_cursor_type1;

dynamic_cursor2 dynamic_cursor_type2;

dynamic_cursor3 dynamic_cursor_type3;

--定義返回類型變量

rec3 dynamic_cursor_type3_rec;

--定義變量

v_id test_user.id%type;

v_username test_user.username%type;

v_password varchar2(32);

v_num number;

begin

--初始化一些數(shù)據(jù)

delete from test_user;

commit;

select count(1) into v_num from test_user;

if v_num = 0 then

insert into test_user

(id, username, password)

values

('123', 'shaoyu', 'shaoyu');

insert into test_user

(id, username, password)

values

('456', 'admin', 'admin');

insert into test_user

(id, username, password)

values

('789', 'system', 'system');

commit;

end if;

dbms_output.put_line('強(qiáng)類型動態(tài)游標(biāo)');

--給強(qiáng)類型動態(tài)游標(biāo)關(guān)聯(lián)結(jié)果集

open dynamic_cursor1 for select * from test_user;

--驗證

loop

fetch dynamic_cursor1

into v_id, v_username, v_password;

exit when dynamic_cursor1%notfound;

dbms_output.put_line(v_id || '-' || v_username || '-' || v_password);

end loop;

close dynamic_cursor1;

--給弱類型動態(tài)游標(biāo)關(guān)聯(lián)結(jié)果集

dbms_output.put_line('弱類型動態(tài)游標(biāo)');

open dynamic_cursor2 for select id,password from test_user;

--驗證

loop

fetch dynamic_cursor2

into v_id, v_password;

exit when dynamic_cursor2%notfound;

dbms_output.put_line(v_id || '-' || v_password);

end loop;

close dynamic_cursor2;

--給自定義強(qiáng)類型動態(tài)游標(biāo)關(guān)聯(lián)結(jié)果集

dbms_output.put_line('自定義返回類型強(qiáng)類型動態(tài)游標(biāo)');

open dynamic_cursor3 for select id,username from test_user;

--驗證

loop

fetch dynamic_cursor3

into rec3;

exit when dynamic_cursor3%notfound;

dbms_output.put_line(rec3.user_id || '-' || rec3.username);

end loop;

close dynamic_cursor3;

end test_dynamic_cursor;

以上看起來游標(biāo)好像就這么一些用法，那還有沒有別的用法呢？有的，那就是在使用游標(biāo)時，對游標(biāo)的結(jié)果集對應(yīng)的數(shù)據(jù)源進(jìn)行操作。

四、更新、刪除游標(biāo)記錄

在定義游標(biāo)的時候，如果在定義結(jié)果集的語句后面加上for update或者for delete子串，那么在使用游標(biāo)時，就可以對游標(biāo)的結(jié)果集進(jìn)行操作，不要擔(dān)心數(shù)據(jù)源的狀態(tài)，當(dāng)使用for update、for delete子串打開一個游標(biāo)時，所有返回集中的數(shù)據(jù)行都將處于行級(ROW-LEVEL)獨占式鎖定，其他對象只能查詢這些數(shù)據(jù)行，不能進(jìn)行update、delete或select...for update操作，保證了數(shù)據(jù)的正確性。

值得提醒的是，在多表查詢中，使用of子句來鎖定特定的表，如果忽略了of子句，那么所有表中選擇的數(shù)據(jù)行都將被鎖定。如果這些數(shù)據(jù)行已經(jīng)被其他會話鎖定，那么正常情況下oracle將等待，直到數(shù)據(jù)行解鎖。

語法：

a.聲明更新或刪除顯示游標(biāo)：

cursor?游標(biāo)名 is? select?語句 ??for update [ of ?更新列列名]；

cursor?游標(biāo)名 is? select?語句 ??for delete [ of ?更新列列名]；

b.使用顯示游標(biāo)當(dāng)前記錄來更新或刪除：

update 表名 ??set 更新語句 ?where ??current ?of ??游標(biāo)名；

delete from 表名 ?where ??current ?of ??游標(biāo)名；

這個就不寫例子了，第三步的示例理解了之后，這個很容易編寫。

五、使用游標(biāo)作為存儲過程出參

說了這么多，并沒有將游標(biāo)應(yīng)用到實際中，其實web程序?qū)?shù)據(jù)庫的調(diào)用多數(shù)情況下需要返回一個結(jié)果集，很顯然，游標(biāo)是非常適合的。在這種情況下，只需要將游標(biāo)作為存儲過程的出參就可以了。

1.包的概念

在上一篇中提到了包和存儲過程，那什么是包呢？包(package)也是數(shù)據(jù)庫的一種對象類型，它包含定義和包體(body)兩個方面，【定義】類似于是java中的接口，【包體】類似于是java中對接口的實現(xiàn)類，包里面是可以包含【自定義類型】和【存儲過程】的，可以認(rèn)為是java接口中的全局變量(自定義類型)和方法(存儲過程)，就連使用方式也極其類似：包名.存儲過程名(參數(shù))。

有人覺得奇怪，不是要說游標(biāo)做為存儲過程出參嗎？怎么又扯上包這個東西了？

在java中，所有的變量都有一個作用域，oracle數(shù)據(jù)庫也不例外，假設(shè)我們單獨定義一個存儲過程，在參數(shù)那一列是要規(guī)定參數(shù)類型的，如果我們使用的是自定義的游標(biāo)，那么這個游標(biāo)類型在這個存儲過程參數(shù)里是肯定沒有定義的，所以我們需要借助包，在包中定義自定義的游標(biāo)類型，然后再把這個自定義游標(biāo)作為包中的存儲過程的出入?yún)?#xff0c;這樣就保證了游標(biāo)在存儲過程中的作用域始終可用。

2.包的語法：

包定義:

create or replace package 包名 as

定義 自定義type

定義 全局變量

procedure 存儲過程名; --沒有存儲過程具體實現(xiàn)

function 函數(shù)名;

end test_package;

包體定義:

create or replace package body test_package as

定義變量

procedure 存儲過程名(參數(shù)) is ...存儲過程具體實現(xiàn)

end test_package;

下面寫個實例:

create or replace package test_package as

--定義游標(biāo)類型

type o_cur is ref cursor;

--定義存儲過程

procedure test_static_cursor(o_data out o_cur);

end test_package;

create or replace package body test_package as

--存儲過程具體實現(xiàn)

procedure test_static_cursor(o_data out o_cur) is

v_num number;

begin

--初始化一些數(shù)據(jù)

delete from test_user;

commit;

select count(1) into v_num from test_user;

if v_num = 0 then

insert into test_user

(id, username, password)

values

('123', 'shaoyu', 'shaoyu');

insert into test_user

(id, username, password)

values

('456', 'admin', 'admin');

insert into test_user

(id, username, password)

values

('789', 'system', 'system');

commit;

end if;

--給出參關(guān)聯(lián)結(jié)果集

open o_data for

select * from test_user;

end;

end test_package;

總結(jié)

以上是生活随笔為你收集整理的小青蛙oracle跟踪,Oracle 存储过程：游标的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。