ORACLE 游標
一. PL/SQL 是用游標來管理 SQL 的 SELECT 語句的 . 游標是為了處理這些語句而分配的一大塊內存 . 它提供了對一個結果集進行逐行處理的能力 , 可看作是一種特殊的指針 . 它與某個查詢結果集相關聯 , 可以指向結果集的任意位置 , 以便對指定位置的數據進行處理 . 使用它可以在查詢數據的同時對數據進行處理 .
二. 游標的分類 : 靜態游標和動態游標
1. 在說明游標分類之前, 先說明一下PL/SQL 的兩個操作, 游標屬性, 變量屬性
SET SERVEROUTPUT ON ;-- 此語句用于開啟打印輸出功能
DMBS_OUTPUT.PUT_LINE();-- 打印輸出并換行.
%FOUND: 判斷當前游標是否指向有效的一條行記錄. 是則返回TRUE, 否則返回FALSE
%NOTFOUND: 與%FOUND 恰好相反.
%ISOPEN: 判斷游標是否打開, 打開則返回TRUE, 否則返回FALSE.
%ROWCOUNT: 判斷當前游標在所指向的結果集中提取的行數. 并非所有總記錄數
%TYPE: 聲明變量的類型與表中的某列的數據類型完全一致.%TYPE 聲明有兩個好處: 一是你不必知道某列的確切數據類型二是當某列的數據類型改變時不用修改變量的數據類型.
%ROWTYPE: 聲明變量的數據類型與表中的行記錄數據類型一致. 對于自定義的記錄, 則必須聲明自己的域.
2. 靜態游標:
靜態游標又可以分為兩類:
1) 隱式游標
隱式游標的特點:
- 隱式游標是由PL/SQL 來管理的, 即不需要聲明游標語句, 也不需要OPEN,FETCH,CLOSE 操作
- 隱式游標中必須要有select cur_name into [ 變量名或其他數據類型]. 此句完成OPEN,FETCH,CLOSE 操作.
- 隱式游標只能返回一行記錄, 如果無符合條件的記錄將會出現NO_DATA_FOUND 異常. 如果出現多條記錄將出現TOO_MANY_ROWS 異常.
- 隱式游標只能用SQL% 判斷其游標屬性
- 對于任何位置使用SQL%ISOPEN 結果都是FALSE, 隱式游標由PL/SQL 管理
- 對于在隱式游標位置前使用SQL%FOUND 或SQL%NOTFOUND, SQL%ROWCOUNT 結果值是NULL( 不確定值)
示例表:
表名:test
字段名
數據類型
可否為空
id( 序號)
number
N
name( 姓名)
varchar2(20)
N
zym( 專業名)
varchar(40)
Y
cj( 成績)
number
Y
logdate( 記錄日期)
date
Y
EG1: 驗證隱式游標的特性
set serveroutput on;-- 開啟輸出
declare
no test.id%type;-- 聲明變量no 的數據類型同test 表的id 列一致
ename test.name%type;
begin
if sql%isopen then -- 判斷游標是否打開
dbms_output.put_line(' cursor is openning');
else
dbms_output.put_line('cursor is closed');
end if;
if sql%notfound then – 判斷游標是否指向有效的行
dbms_output.put_line('no value');
else
dbms_output.put_line(no||' '||ename);
end if;
dbms_output.put_line(sql%rowcount);
dbms_output.put_line('---------------');
/** 去掉where 條件時, 將會出現too_many_rows 異常**/
select id ,name into no ,ename from test where cj=90;-- 隱式游標必-- 須使用INTO
dbms_output.put_line(sql%rowcount);
if sql%isopen then
dbms_output.put_line(' cursor is openning');
else
dbms_output.put_line('cursor is closed');
end if;
if sql%notfound then
dbms_output.put_line('no value');
else
dbms_output.put_line(no||' '||ename);
end if;
exception
when no_data_found then
dbms_output.put_line('no value');
when too_many_rows then
dbms_output.put_line('too many rows');
end;
執行結果為:
cursor is closed
--------------------
1
cursor is closed
1001 qbnajj
去掉where 條件時的結果:
cursor is closed
--------------------
too many rows
EG2: 使用%ROWTYPE
declare
rowtype test%rowtype;
begin
select * into rowtype from test where cj=90;
dbms_output.put_line(rowtype.name||' '||rowtype.cj);
exception
when no_data_found then
dbms_output.put_line('no value');
when too_many_rows then
dbms_output.put_line('too many rows');
end;
執行結果:
qbnajj 90
EG3: 自定義RECORD 數據類型
declare
/** 自定義RECORD 數據類型
語法為:
TYPE type_name IS RECORD(cloumn1 datatpe,.....);
**/
type r_type is record(name test.name%type ,cj test.cj%type);
re_type r_type;
begin
select name,cj into re_type from test where cj=90;
dbms_output.put_line(re_type.name||' '||re_type.cj);
exception
when no_data_found then
dbms_output.put_line('no value');
when too_many_rows then
dbms_output.put_line('too many rows');
end;
/
執行結果:
qbnajj 90
2) 顯示游標
聲明語法:
DECLARE
CURSOR cur_name( 參數名 數據類型) is select_satatements ;-- 游標名后-- 可帶參數
BEGIN
OPEN cur_name;
FETCH cur_name into [ 變量或者其他數據類型];
-- 可用循環語句迭代輸出游標所指向的結果集.
CLOSE cur_name;
END;
顯示游標特性:
- 顯示游標由用戶定義, 并由用戶來管理游標, 可返回多行記錄.
- 通常使用顯示游標需要遵守以下規則
聲明游標 -> 打開游標 -> 讀取數據 -> 關閉游標
但由于FOR IN LOOP 語句包含OPEN,FETCH,CLOSE 操作所以不在此例.
- 查看游標屬性時, 使用cur_name%.
EG:PL/SQL 演示
declare
no test.id%type;
ename test.name%type;
cjs test.cj%type;
cursor test_cur is
select id,name from test where cj>=cjs;-- 可通過PL/SQL 綁定變量輸-- 入值(&cjs)
begin
cjs:=50;
for t_cur in test_cur
loop
no:=t_cur.id;
ename:=t_cur.name;
dbms_output.put_line(no||' '||ename);
end loop;
end;
執行結果:
chenjunhua 80
qbnajj 90
cjh 52
EG2: 利用帶參數的存儲過程演示
create or replace procedure test_proce(cjs in test.cj%type)
as
no test.id%type;
ename test.name%type;
cursor test_cur is
select id,name from test where cj>=cjs;
begin
open test_cur;
fetch test_cur into no,ename;
while test_cur%found
loop
dbms_output.put_line(no||' '||ename);
fetch test_cur into no,ename;-- 將游標指向下條記錄, 否則為死循環.
end loop;
close test_cur;
end test_proce;
exec test_proce(50);
執行結果:
chenjunhua 80
qbnajj 90
cjh 52
EG3: 帶參數的游標與上面的類似
2. 動態游標(REF CURSOR)
首先 , 游標變量和游標是兩個不同的概念 . 與游標相似 , 游標變量是指向多行查游標詢的結果集的當前行 . 游標是靜態的 , 游標變量是動態的 ,. 同時游標變量并不參與與特定的查詢綁定 , 所以可以為任何兼容的查詢打開游標變量 , 從而提高靈活性 . 而且 , 還可以將新的值賦予游標變量 , 將它作為參數傳遞給本地和存儲過程 . 游標變量針對每個 PL/SQL 用戶都是可用的 , 可以在客戶端完全使用游標變量 .ORACLE 服務器同樣包含 PL/SQL 引擎 , 可以將游標變量在應用和服務器之間進行傳遞 .
1. 游標變量 : 聲明游標實際上是創建一個指針 , 指針具有數據類型 REF X.REF 是 REFERENCE ,X 是表示類對象 . 因此 , 游標變量具有數據類型 REF CURSOR.
注 : 游標總是指向相同的查詢工作區 , 游標變量能夠指向不同的工作區 , 因此游標和游標變量不能互操作 .
3. 定義 REF CURSOR 類型 , 創建游標變量有兩個步驟 :
1) 定義 REF CURSOR 類型
語法格式 :
TYPE ref_type_name
IS
REF CURSOR [RETURN return_type]
說明 :
ref_type_name 是游標變量中使用的類型 ;return_type 必須是一個記錄 (record) 或者數據庫表中的一行 .(rowtype)
下面定義 一個 REF CURSOR 類型游標
DELARE
TYPE xs_cur
IS
REF CURSOR RETURN xs%ROWTYPE;
注意 :
REF CURSOR 類型既可以是強類型 , 也可以是弱類型 , 區別是強類型有返回類型 , 弱類型沒有 . 如下所示
DECLARE
TYPE xs_cur IS REF CURSOR RETURN xs%ROWTYPE;-- 強類型
TYPE mycur IS REF CURSOR;-- 弱類型
2) 聲明這種類型的游標變量 : 一旦定義了 REF CURSOR 類型就可以在 PL/SQL 塊或子程序中聲明這個游標變量 . 如 :
DECARE
TYPE xs_cur REF CURSOR RETURN xs%ROWTYPE;
xscur xs_cur;
當然 , 在 RETURN 子句中也可以定義自定義的 RECORD 類型 , 如 :
DECLARE
TYPE kc_cj IS RECORD
(
kch number (4),
kcm number(10),
cj number(4,2)
);
TYPE kc_cjcur IS REF CURSOR RETURN kc_cj;
此外 , 也可以聲明游標變量作為函數和過程的參數 . 例如 :
DECLARE
TYPE xs_cur IS REF CURSOR RETURN xs%ROWTYPE;
PROCEDURE open_xs (xscur IN OUT xs_cur)IS
......................
3. 控制游標變量
使用游標變量時 , 要遵循以下步驟 :OPEN-FETCH-CLOSE
OPEN 語句與多行查詢的游標變量相關聯 , 它執行查詢 , 標志結果集
語法格式 :
OPEN {cursor_variable|:host_cursor_variable }FOR
{
select_statement|dynamic_string[USING bind_argument[,......]]
}
如 :
IF NOT xscur%ISOPEN THEN
OPEN xscur FOR SELECT * FROM xs;
END IF ;
游標變量同樣可以使用游標屬性 :%FOUND,%ISOPEN,%ROWTYPE
在使用過程中 , 其他的 OPEN 語句可以為不同的查詢打開相同的游標變量 . 因此 , 在重新打開之前 , 不要關閉游標變量 . 可以打開游標 , 并作為參數傳遞給存儲過程 . 如 :
CREATE PACKAGE xs_data AS
...
TYPE xs_cur IS REF CURSOR RETURN xs%ROWTYPE;
RROCEDURE open_xs(xscur IN OUT xs_cur);
END xs_data;
CREATE PACKAGE BODY xs_data AS
...
PROCEDURE open_xs(xscur IN OUT xs_cur)
AS
BEGIN
OPEN xscur FOR SELECT * FROM xs;
END open_xs;
END xs_data;
當聲明一個游標變量作為打開游標變量子程序的參數時 , 必須定義 IN OUT 模式 . 也就是說 , 子程序可以將一個打開的游標變量傳遞給調用者