我們經常寫這樣的代碼:
-- Created on 2015-7-15 by JellyThink
declare
strAreaCode VARCHAR2(10);
strUserName VARCHAR2(30);
strTelNum VARCHAR2(12);begin
-- 我們這裡確信只返回一行數據
select a.area_code, a.user_name, a.tel_num into strAreaCode,
strUserName, strTelNum
from xg.sys_spec_tel a where a.tel_num='15034974832';
dbms_output.put_line(strAreaCode);
dbms_output.put_line(strUserName);
dbms_output.put_line(strTelNum);
exception
when NO_DATA_FOUND then
dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
when TOO_MANY_ROWS then
dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
when others then
dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);end;
在使用select ... into ...
語句時,我們笃信肯定會返回一行數據,而且只有一行;但是,如果返回的數據不止一行,這樣都會拋出異常。而我們期望的不是拋出異常,那麼如果返回的數據不止一行的時候,如何將每一行的值賦值給變量進行處理呢?這就要用到游標了,這篇文章就對Oracle中的游標進行詳細的總結。
在Oracle中,游標分為以下兩種:
我們先說說隱士游標。
隱式游標又叫SQL游標,是在執行DML操作和查詢操作返回單條記錄時,由PL/SQL自動、隱藏定義。隱式游標由PL/SQL自動定義、自動打開、自動關閉、不需要用戶的參與;隱式游標的游標名叫SQL。
隱式游標有以下四個屬性,這些游標屬性用於返回DML和查詢操作的信息。
屬性名稱
屬性含義
結果類型
%FOUND
用於判斷DML語句是否改變了行,或者判斷SELECT INTO是否返回了一行數據
布爾類型
%NOTFOUND
和%FOUND的含義相反
布爾類型
%ISOPEN
判斷游標是否打開,對於隱式游標來說,當SQL語句執行完成以後,游標被自動關閉,因此SQL%ISOPEN的值永遠是FALSE
布爾類型
%ROWCOUNT
用於判斷DML語句影響了多少行,或者SELECT INTO返回了多少行
整數
看一個小小的例子:
declare
begin
delete from xg.sys_spec_tel where tel_num='15034784641';
if sql%found then
dbms_output.put_line(sql%rowcount);
commit; -- 刪除成功了,就提交
else
NULL; -- 執行空語句
end if;
exception
when others then
dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);end;
顯式游標用CURSOR...IS
命令定義,它可以對查詢語句返回的多條記錄進行處理。顯示游標需要用戶顯示進行定義、顯示打開、從游標取數據和顯示關閉。
-- 其中,cursor_name是游標的名字,SELECT_statement是查詢語句
CURSOR cursor_name IS SELECT_statement;
比如:
declare
cursor cSpecTel is select * from xg.sys_spec_tel;begin
-- Do Somethingend;
OPEN cursor_name;
FETCH cursor_name INTO variable1[, variable2, ... ];
使用FETCH命令從游標中提取數據,每提取一次,游標都指向結果集的下一行。其中variable1是變量,從游標中取得的數據就存放在該變量中。
-- Created on 2015-7-17 by JellyThink
declare
cursor cSpecTel is select tel_num from xg.sys_spec_tel;
phone varchar2(13);begin
dbms_output.enable(1000000); -- 1~1000000
open cSpecTel;
loop
fetch cSpecTel into phone;
exit when cSpecTel%NOTFOUND or cSpecTel%NOTFOUND is null;
dbms_output.put_line('Special Phone Number:' || phone);
end loop;
close cSpecTel;
exception
when others then
dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
-- 如果發生異常以後,檢查游標是否被關閉
if cSpecTel%isopen then
close cSpecTel;
end if;end;
CLOSE cursor_name;
在上面的代碼中,我使用了%NOTFOUND
、%ISOPEN
等顯式游標的屬性,對於顯式游標來說,它也有四大屬性,分別如下:
屬性名稱
屬性含義
結果類型
%FOUND
當最後一次讀(FETCH)記錄成功,則返回TRUE
布爾類型
%NOTFOUND
和%FOUND的含義相反
布爾類型
%ISOPEN
判斷游標是否打開,當游標已打開時返回TRUE
布爾類型
%ROWCOUNT
返回已從游標中讀取的記錄數(到目前為止)
整數
我們可以清楚的知道,使用顯式游標的標准步驟如下:
這樣的過程非常繁瑣,因此Oracle中引入了FOR循環。FOR循環把上面的過程融合在一起,忽略了顯式的打開游標、關閉游標、從游標中取數據等過程。使用這種方式,系統隱藏定義了一個%ROWTYPE
類型的記錄。游標FOR循環的語法如下所示:
FOR record_name IN (cursor_name) | (query_difinition)
LOOP
statements
END LOOP;
元素名稱
說明
record_name
記錄的名稱,%ROWTYPE類型的記錄
cursor_name
游標的名稱
query_difinition
如果不指定游標的話,可以指定一個查詢
現在將上面的示例代碼改寫成使用FOR的形式。
-- Created on 2015-7-17 by JellyThink
declare
cursor cSpecTel is select * from xg.sys_spec_tel;begin
dbms_output.enable(1000000);
for specTel in cSpecTel
loop
dbms_output.put_line('Phone Number:' || specTel.Tel_Num);
end loop;
exception
when others then
dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);end;
這樣寫起來,代碼是不是簡潔多了。
可以看到,上面的查詢語句select * from xg.sys_spec_tel
都是寫死的。如果我們想動態的根據條件改變查詢,那怎麼辦?這個時候,我們可以利用游標參數動態改變查詢語句。
-- 定義一個根據區號獲得特殊號碼的存儲過程
create or replace procedure p_get_spec_tel(area varchar2)as
phone varchar2(13);
areaCode varchar2(5);
cursor cSpecTel(area1 varchar2) is -- area1就是定義的游標參數
select a.tel_num, a.area_code
from XG.SYS_SPEC_TEL a
where a.area_code=area1;begin
dbms_output.enable(1000000);
-- 打開游標時,將參數傳遞進去
-- 使用FOR隱藏了打開游標的過程,不知道怎麼將參數傳遞進去
open cSpecTel(area);
loop
fetch cSpecTel into phone, areaCode;
exit when cSpecTel%notfound or cSpecTel%notfound is null;
dbms_output.put_line('Phone Number:' || phone || ' Area Code:' || areaCode);
end loop;
close cSpecTel;
exception
when others then
dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);end;
這樣的話,我們在運行存儲過程的過程中,可以傳遞一個參數進去,定義游標的時候,可以指定參數,這樣的話,就可以成功進行動態的指定查詢條件。
在下面的代碼中:
cursor cSpecTel is
select a.tel_num, a.area_code
from XG.SYS_SPEC_TEL a;
當我們這樣定義了游標以後,這個名為cSpecTel
的游標就只對應查詢語句select a.tel_num, a.area_code from XG.SYS_SPEC_TEL a;
;也就是說一旦我們定義了這樣的一個游標,這個游標就相當於一個常量了。如果我們想定義一個游標變量,想給這個游標變量賦什麼值就賦什麼值,這該多好。
游標變量就是一個指針,這個指針可以指向不同的查詢工作區;而顯式游標總是指向相同的查詢工作區。簡單的說,游標變量是一個變量,只是這個變量可以動態指向不同的游標,所以游標變量又稱動態游標;而顯式游標又稱靜態游標。游標變量可以作為函數或者存儲過程的參數。
定義游標變量分為兩步(自定義類型都是這樣,C++也是如此):
在創建游標變量之前,我們必須定義REF CURSOR類型。定義REF CURSOR類型的語法如下:
TYPE ref_type_name IS REF CURSOR [RETURN return_type]
其中ref_type_name是新類型的名字,是聲明游標變量的類型,這裡實際上定義的是一種新的數據類型。return_type是可選的,用於指定游標變量返回值的類型,它必須是一個記錄類型(RECORD)或者行類型(ROWTYPE),如果有返回類型,則稱之為“強REF CURSOR”;如果沒有返回類型,則稱之為“弱REF CURSOR”。我們使用“弱REF CURSOR”,則允許把游標變量與任何查詢進行關聯;如果使用“強REF CURSOR”,則只允許把游標變量與特定查詢進行關聯。
定義一個強REF CURSOR:
-- 有返回類型
TYPE strongType IS REF CURSOR RETURN specialTel%ROWTYPE;
定義一個弱REF CURSOR:
-- 沒有返回類型
TYPE weakType IS REF CURSOR;
游標類型定義完成以後,我們就可以聲明一個游標變量了。
telnum_cur weekType;
現在截取我們生產庫上的一個例子看看游標變量的具體使用方法。
create or replace procedure cp.insert_accountrent_rate
IS
type t_cur IS ref cursor; -- 定義一個弱REF CURSOR類型
rate_cur t_cur; -- 聲明一個游標變量
sql_stmt varchar2(10240);begin
sql_stmt :='SELECT curve_id,base_val,rate_val,formula_id,share_num FROM cp.PM_CURVE_SEGMENTS';
-- 使用open...for...打開這個游標
open rate_cur for sql_stmt;
loop
fetch rate_cur into mrate_id,mbase_val,mrate_val,mformula_id,mshare_num;
exit when rate_cur%notfound;
end loop;
close rate_cur;end;
游標在實際工作中是經常使用的,而且對於批處理來說,使用起來也非常方便,掌握了游標,將會極大的提高你的工作效率。
這篇文章稍長,代碼稍多,希望你能耐心的看完。如果連這麼點文字都看不下去,這麼點耐心都沒有,那你也太浮躁了。最後,希望這篇文章能夠對大家有所幫助。