Oracle 遊標使用總結

Oracle遊標分為顯示遊標和隱式遊標。
顯示遊標（Explicit Cursor）:在PL/SQL程式中定義的、用於查詢的遊標稱作顯示遊標。
隱式遊標（Implicit Cursor）:是指非PL/SQL程式中定義的、而且是在PL/SQL中使用UPDATE/DELETE語句時，Oracle系統自動分配的遊標。
一.顯示遊標
1.使用步驟
(1)定義 (2)開啟 (3)使用 (4)關閉
2.使用示範
首先建立測試用表STUDENT，指令碼如下： 複製代碼 代碼如下:CREATE TABLE "STUDENT" (
"STUNAME" VARCHAR2(10 BYTE),
"STUNO" VARCHAR2(4 BYTE),
"AGE" NUMBER,
"GENDER" VARCHAR2(2 CHAR)
)

(1).使用WHILE迴圈處理遊標
create or replace PROCEDURE PROC_STU1 AS
BEGIN
--顯示遊標使用，使用while迴圈
declare
--1.定義遊標，名稱為cur_stu
cursor cur_stu is
select stuno,stuname from student order by stuno;
--定義變數，存放遊標取出的資料
v_stuno varchar(4);
v_stuname varchar(20);
begin
--2.開啟遊標cur_stu
open cur_stu;
--3.將遊標的當前行取出存放到變數中
fetch cur_stu into v_stuno,v_stuname;
while cur_stu%found --遊標所指還有資料行，則繼續迴圈
loop
--列印結果
dbms_output.PUT_LINE(v_stuno||'->'||v_stuname);
--繼續將遊標所指的當前行取出放到變數中
fetch cur_stu into v_stuno,v_stuname;
end loop;
close cur_stu; --4.關閉遊標
end;
END PROC_STU1;
(2).使用IF..ELSE代替WHILE迴圈處理遊標
create or replace PROCEDURE PROC_STU2 AS
BEGIN
--顯示遊標使用，使用if判斷
declare
--1.定義遊標，名稱為cur_stu
cursor cur_stu is
select stuno,stuname from student order by stuno;
--定義變數，存放遊標取出的資料
v_stuno varchar(4);
v_stuname varchar(20);
begin
--2.開啟遊標cur_stu
open cur_stu;
--3.將遊標的當前行取出存放到變數中
fetch cur_stu into v_stuno,v_stuname;
loop
if cur_stu%found then --如果遊標cur_stu所指還有資料行
--列印結果
dbms_output.PUT_LINE(v_stuno||'->'||v_stuname);
--繼續將遊標所指的當前行取出放到變數中
fetch cur_stu into v_stuno,v_stuname;
else
exit;
end if;
end loop;
close cur_stu; --4.關閉遊標
end;
END PROC_STU2;
(3).使用FOR迴圈處理遊標
create or replace PROCEDURE PROC_STU3 AS
BEGIN
--顯示遊標使用，使用for迴圈
declare
--定義遊標，名稱為cur_stu
cursor cur_stu is
select stuno,stuname from student order by stuno;
begin
for stu in cur_stu
loop
dbms_output.PUT_LINE(stu.stuno||'->'||stu.stuname);
--迴圈做隱含檢查 %notfound
end loop;
--自動關閉遊標
end;
END PROC_STU3;
(4).常用的使用EXIT WHEN處理遊標
create or replace
PROCEDURE PROC_STU1_1 AS
BEGIN
--顯示遊標使用，使用exit when迴圈
declare
--1.定義遊標，名稱為cur_stu
cursor cur_stu is
select stuno,stuname from student order by stuno;
--定義變數，存放遊標取出的資料
v_stuno varchar(4);
v_stuname varchar(20);
begin
--2.開啟遊標cur_stu
open cur_stu;
loop
--3.將遊標的當前行取出存放到變數中
fetch cur_stu into v_stuno,v_stuname;
exit when cur_stu%notfound; --遊標所指還有資料行，則繼續迴圈
--列印結果
dbms_output.PUT_LINE(v_stuno||'->'||v_stuname);
end loop;
close cur_stu; --4.關閉遊標
end;
END PROC_STU1_1;
二.隱式遊標
1.使用示範
create or replace PROCEDURE PROC_STU4 AS
BEGIN
--隱式遊標使用
update student set stuname='張燕廣' where stuno='1104';
--如果更新沒有匹配則插入一條新記錄
if SQL%NOTFOUND then
insert into student(STUNO,STUNAME,AGE,GENDER)
values('1104','張燕廣',18,'男');
end if;
END PROC_STU4;
2.說明
所有的SQL語句在上下文區內部都是可執行檔，因為都有一個遊標指向上下文區，此遊標就是
SQL遊標，與現實遊標不同的是，SQL遊標在PL/SQL中不需要開啟和關閉，而是在執行UPDATE、
DELETE是自動開啟和關閉。
上面例子中就是通過SQL%NOTFOUND遊標屬性判斷UPDATE語句的執行結果決定是否需要插入新記錄。