1. 簡介
在使用 select 陳述句查詢資料時，查詢回傳的資料存放在結果集中，用戶如何從查詢得到的結果集中逐行逐列地獲取存盤的資料，從而在應用程式中使用這些值，就需要一種定位并控制結果集的機制，即游標，
游標是映射在結果集中一行資料上的位置物體，用來處理從資料庫中檢索的多行記錄，利用游標，程式可以逐個地處理和便利一次檢索回傳的整個記錄表，即可以對當前行資料進行操作，
2. 型別
1，隱式游標：在 PL/SQL 程式中執行 DML SQL 陳述句時自動創建隱式游標，游標名字固定叫 sql，
2，顯式游標：顯式游標用于處理回傳多行的查詢，
3，REF 游標：REF （參照）游標用于處理運行時才能確定的動態 SQL 查詢的結果，
3. 隱式游標
在 PL/SQL 中使用 DML 陳述句時自動創建隱式游標，隱式游標自動宣告、打開和關閉，其名為 sql，我們可以通過檢查隱式游標的屬性可以獲得最近執行的 DML 陳述句的資訊，隱式游標的屬性有：
-
%FOUND – SQL 陳述句影響了一行或多行時為 TRUE
-
%NOTFOUND – SQL 陳述句沒有影響任何行時為TRUE
-
%ROWCOUNT – SQL 陳述句影響的行數
-
%ISOPEN - 游標是否打開，始終為FALSE，因為游標打開執行后會立即關閉，
下面我們舉例來說明隱式游標 sql 的存在，首先由如下 student 表：
SQL> select * from student;
SNO SNAME SAGE
--------------------------------------- ---------- ---------------------------------------
1 Tom 21
2 Kite 22
3 Bob 23
4 Mike 24
撰寫如下 pl/sql 執行塊：
set serverout on; -- 開啟控制臺列印
begin
update student set sage = sage + 10;
if (sql%found) then -- 判斷時候影響多行
dbms_output.put_line('表已更新' || sql%rowcount || '行記錄！'); -- 影響的行數
end if;
end;
/
表已更新4行記錄！ -- 輸出結果
begin
update student set sage = sage + 10 where sno = 5; -- sno = 5 的不存在
if (sql%notfound) then -- 判斷時候影響多行
dbms_output.put_line('表已更新' || sql%rowcount || '行記錄！'); -- 影響的行數
end if;
end;
/
表已更新0行記錄！ -- 輸出結果
4. 顯式游標
顯式游標在 PL/SQL 塊的宣告部分定義查詢，該查詢可以回傳多行，顯式游標的操作程序為宣告游標、打開游標、使用游標取出記錄和關閉游標，我們可以以下述的示例來體現說明，實作效果類似于上述使用隱式游標操作 student 一樣：
declare
stud student%rowtype; -- 表示一行 student 表記錄型別標量
cursor stu_cur is select * from student; -- 1. 宣告游標
begin
open stu_cur; -- 2. 打開游標
loop
fetch stu_cur into stud; -- 3. 使用游標取出記錄并保存到變數 stud 中
exit when stu_cur%notfound;
dbms_output.put_line('學號是：' || stud.sno || '，姓名是：' || stud.sname);
end loop;
close stu_cur; -- 4. 關閉游標
end;
/
輸出結果如下所示：
學號是：1，姓名是：Tom
學號是：2，姓名是：Kite
學號是：3，姓名是：Bob
學號是：4，姓名是：Mike
5. 帶引數顯式游標
宣告顯式游標時可以帶引數以提高靈活性，宣告帶引數的顯式游標的語法如下：
CURSOR <cursor_name>(<param_name> <param_type>) IS select_statement;
下述示例我們通過控制臺輸出序號列印輸出執行學生資訊，具體如下所示：
declare
in_sno number; -- 記錄輸出的學生學號
stud student%rowtype; -- 表示一行 student 表記錄型別標量
cursor stu_cur(in_sno number) is select * from student where sno = in_sno; -- 1. 宣告游標，in_sno 為引數
begin
in_sno := &學生學號;
open stu_cur(in_sno); -- 2. 打開游標，帶上引數
loop
fetch stu_cur into stud; -- 3. 使用游標取出記錄并保存到變數 stud 中
exit when stu_cur%notfound;
dbms_output.put_line('學號是：' || stud.sno || '，姓名是：' || stud.sname);
end loop;
close stu_cur; -- 4. 關閉游標
end;
/
當我們輸出學號 1 的時候，會列印輸出如下結果：
學號是：1，姓名是：Tom
6. 使用顯式游標更新行
允許使用游標洗掉或更新活動集中的行，但是宣告游標時必須使用 SELECT … FOR UPDATE陳述句，語法如下所示：
-- 宣告游標時
CURSOR <cursor_name> IS SELECT statement FOR UPDATE; -- 記得帶上 for update
-- 更新陳述句的語法
UPDATE <table_name> SET <set_clause> WHERE CURRENT OF <cursor_name> -- 記得帶上 current of 游標名稱
-- 洗掉陳述句的語法
DELETE FROM <table_name> WHERE CURRENT OF <cursor_name> -- 記得帶上 current of 游標名稱
示例如下：
SQL> select * from student;
SNO SNAME SAGE
--------------------------------------- ---------- ---------------------------------------
1 Tom 21
2 Kite 22
3 Bob 23
4 Mike 24
declare
stud student%rowtype; -- 表示一行 student 表記錄型別標量
cursor stu_cur is select * from student where sno = 2 or sno = 3 for update; -- 1. 宣告游標，帶上 for update
begin
open stu_cur; -- 2. 打開游標
loop
fetch stu_cur into stud; -- 3. 使用游標取出記錄并保存到變數 stud 中
exit when stu_cur%notfound;
update student set sage = sage + 100 where current of stu_cur; -- 帶上 current of stu_cur
end loop;
close stu_cur; -- 4. 關閉游標
end;
/
執行結果如下，其中學號為2 和 3 的學生的年齡增加了 100：
SQL> select * from student;
SNO SNAME SAGE
--------------------------------------- ---------- ---------------------------------------
1 Tom 21
2 Kite 122
3 Bob 123
4 Mike 24
7. 回圈游標
回圈游標只適用于查詢的情況，不適用于更新和洗掉的情況
上面的示例中我們中用到了 loop 陳述句來回圈訪問游標，同時在訪問前后分別需要打開和關閉游標，同時還需要取出游標的記錄并賦值到指定的變數中，為了簡化操作，我們可以直接使用回圈游標，當用戶需要從游標中提取所有記錄時使用，回圈游標的語法如下：
FOR <record_index> IN <cursor_name>
LOOP
<executable statements>
END LOOP;
示例如下所示：
declare
cursor stu_cur is select * from student;
begin
for cur_sub in stu_cur loop -- cur_sub 表示游標指向的每一行記錄
dbms_output.put_line('學號是：' || cur_sub.sno || '，姓名是：' || cur_sub.sname);
end loop;
end;
/
執行結果如下所示：
學號是：1，姓名是：Tom
學號是：2，姓名是：Kite
學號是：3，姓名是：Bob
學號是：4，姓名是：Mike
8. 批量處理
可以使用 fetch … bulk collect into 陳述句來進行批量、快速提取資料，如下是使用示例：
declare
cursor my_cursor is select ename from emp where deptno = 10; -- 宣告名為 my_cursor 的游標
type ename_table_type is table of varchar2(10); -- 宣告名為 ename_table_type 的表型別，該表型別由 varchar2(10) 欄位型別組成
ename_table ename_table_type; -- 宣告名為 ename_table 的 ename_table_type 表型別變數
begin
open my_cursor; -- 打開游標
fetch my_cursor bulk collect into ename_table; -- 批量取出游標執行的記錄并設定到 ename_table 變數中，其實 ename_table 保存的即是
-- select ename from emp where deptno = 10 的查詢結果集，其中 ename 的型別就是 varchar2(10)
for i in 1..ename_table.count loop -- 遍歷列印 ename_table 的所有記錄
dbms_output.put_line(ename_table(i));
end loop;
close my_cursor; -- 關閉游標
end;
/
查看上述陳述句的第七行，我們這里使用fetch my_cursor bulk collect into ename_table;，而不使用fetch my_cursor into ename_table;是可以提高游標取書速度，
9. REF 游標
REF 游標和游標變數用于處理運行時動態執行的 SQL 查詢，創建游標變數需要兩個步驟：
-
宣告 REF 游標型別
-
宣告 REF 游標型別的變數
用于宣告 REF 游標型別的語法為：
TYPE <ref_cursor_name> IS REF CURSOR [RETURN <return_type>];
打開游標變數的語法如下：
OPEN cursor_name FOR select_statement;
我們使用 REF 游標來實作通過對輸入的學號引數列印輸出指定學號的學生資訊，由于在宣告游標的時候我們無法知道要輸出的是哪個學號的學生，因此我們需要使用 REF 游標來實作，示例如下：
student 表中的資料如下所示：
SQL> select * from student;
SNO SNAME SAGE
--------------------------------------- ---------- ---------------------------------------
1 Tom 21
2 Kite 22
3 Bob 23
4 Mike 24
如下是使用參照游標的具體實作 sql：
declare
type refcur is ref cursor; -- 宣告名稱為 refcur 的參照游標型別變數
cursor2 refcur; -- 宣告名稱為 cursor2 的 refcur 型別的變數
my_sno student.sno%type;
no2 student.sno%type;
name2 student.sname%type;
begin
my_sno := '&學號'; -- 控制臺輸入學生學號
open cursor2 for select sno, sname from student where sno = my_sno; -- 打開游標，此處才系結 select 陳述句，而不是在宣告的時候
loop
fetch cursor2 into no2, name2; -- 取出游標指定的值并設定到指定變數中
exit when cursor2%notfound;
dbms_output.put_line('學號是：' || no2 || '，姓名是：' || name2);
end loop;
close cursor2;
end;
/
當我們輸入 4 的時候，執行結果如下所示：
學號是：4，姓名是：Mike
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/200134.html
標籤：其他
上一篇：2020-11-01