oracle資料庫游標

『壹』 oracle 游標是做什麼用的

游標（CURSOR）也叫游標，在關系資料庫中經常使用，在PL/sql程序中可以用CURSOR與SELECT一起對表或者視圖中的數據進行查詢並逐行讀取。

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語句的執行結果決定是否需要插入新記錄。

『貳』 資料庫裡面靜態游標包含哪兩種類型

游標是SQL的一個內存工作區，由系統或用戶以變數的形式定義。游標的作用就是用於臨時存儲從資料庫中提取的數據塊。Oracle資料庫的Cursor類型包含三種: 靜態游標：分為顯式（explicit）游標和隱式（implicit）游標；REF游標：是一種引用類型，類似於指針。下面我們一一介紹它們的使用。

1.隱式游標

1)Select …INTO…語句，DML語句，使用隱式Cursor。此外，還有一種使用FOR LOOP的Implicit Cursor用法。

2)可以通過隱式Cusor的屬性來了解操作的狀態和結果。Cursor的屬性包含：

SQL%ROWCOUNT 整型代表DML語句成功執行的數據行數。

SQL%FOUND 布爾型值為TRUE代表插入、刪除、更新或單行查詢操作成功。

SQL%NOTFOUND 布爾型與SQL%FOUND屬性返回值相反。

SQL%ISOPEN 布爾型DML執行過程中為真，結束後為假。

3) 隱式Cursor由系統自動打開和關閉.

例如：

• setserveroutputon

• declare

• begin

• updateemployeessetemployee_name='Mike'whereemployee_id=1001;

• ifSQL%FOUNDthen

• dbms_output.put_line('Nameisupdated');

• else

• dbms_output.put_line('Nameisnotupdated');

• endif;

• end;

• /

• setserveroutputon

• declare

• begin

• fortableInfoin(select*fromuser_tables)loop

• dbms_output.put_line(tableInfo.table_name);

• endloop;

• exception

• whenothersthen

• dbms_output.put_line(sqlerrm);

• end;

• /

2.顯式游標

1) 顯式Cursor的屬性包含：

游標的屬性 返回值類型 意義

%ROWCOUNT 整型 獲得FETCH語句返回的數據行數

%FOUND 布爾型 最近的FETCH語句返回一行數據則為真，否則為假

%NOTFOUND 布爾型 與%FOUND屬性返回值相反

%ISOPEN 布爾型 游標已經打開時值為真，否則為假

2) 對於顯式游標的運用分為四個步驟：

a 定義游標---Cursor [Cursor Name] IS;

b 打開游標---Open [Cursor Name];

c 操作數據---Fetch [Cursor name]

d 關閉游標---Close [Cursor Name]

以下是幾種常見顯式Cursor用法。




• <p>setserveroutputon

• declare

• cursorcurisselect*fromuser_tables;

• tableInfouser_tables%rowtype;

• begin

• opencur;

• loop

• fetchcurintotableInfo;

• exitwhencur%notfound;

• dbms_output.put_line(tableInfo.table_name);

• endloop;</p><p>exception

• whenothersthen

• dbms_output.put_line(sqlerrm);</p><p>closecur;

• end;

• /</p>

• setserveroutputon

• declare

• cursorcurisselect*fromuser_tables;

• begin

• fortableInfoincurloop

• dbms_output.put_line(tableInfo.table_name);

• endloop;

• exception

• whenothersthen

• dbms_output.put_line(sqlerrm);

• end;

• /

還可以使用帶參數open的cursor。




• <p>setserveroutputon

• declare

• cursorcur(tblNamevarchar2)isselect*fromuser_constraintswheretable_name=tblName;

• tableInfouser_constraints%rowtype;

• begin

• opencur('EMPLOYEES');

• loop

• fetchcurintotableInfo;

• exitwhencur%notfound;

• dbms_output.put_line(tableInfo.constraint_name);

• endloop;</p><p>exception

• whenothersthen

• dbms_output.put_line(sqlerrm);</p><p>closecur;

• end;

• /</p><p></p>

• setserveroutputon

• declare

• cursorcur(tblNamevarchar2)isselect*fromuser_constraintswheretable_name=tblName;

• begin

• fortableInfoincur('EMPLOYEES')loop

• dbms_output.put_line(tableInfo.constraint_name);

• endloop;

• exception

• whenothersthen

• dbms_output.put_line(sqlerrm);

• end

• /

可以使用WHERE CURRENT OF子句執行UPDATE或DELETE操作。




• setserveroutputon

• declare

• cursorcurisselect*fromemployeesforupdate;

• begin

• fortableInfoincurloop

• =salary*1.1wherecurrentofcur;

• endloop;

• commit;

• exception

• whenothersthen

• dbms_output.put_line(sqlerrm);

• end;

• /

3.REF CURSOR（Cursor Variables）

REF Cursor在運行的時候才能確定游標使用的查詢。利用REF CURSOR，可以在程序間傳遞結果集（一個程序里打開游標變數，在另外的程序里處理數據）。

也可以利用REF CURSOR實現BULK SQL，提高SQL性能。

REF CURSOR分兩種，Strong REF CURSOR 和 Weak REF CURSOR。

Strong REF CURSOR:指定retrun type，CURSOR變數的類型必須和return type一致。

Weak REF CURSOR:不指定return type，能和任何類型的CURSOR變數匹配。

Ref cursor的使用:

1) Type [Cursor type name] is ref cursor

2) Open cursor for...

3) Fetch [Cursor name]

4) Close Cursor

例如：

Step1:




• createorreplacepackageTESTas

• typeemployees_refcursor_%rowtype;

• procereemployees_loop(employees_curINemployees_refcursor_type);

• endTEST;

• /

Step2:




• procereemployees_loop(employees_curINemployees_refcursor_type)is

• empemployees%rowtype;

• begin

• loop

• fetchemployees_curintoemp;

• exitwhenemployees_cur%NOTFOUND;

• dbms_output.put_line(emp.employee_id);

• endloop;

• endemployees_loop;

• endTEST;

• /

Step3:




• setserveroutputon

• declare

• empRefCurTEST.employees_refcursor_type;

• begin

• foriin10..20loop

• dbms_output.put_line('DepartmentID='||i);

• openempRefCurforselect*fromemployeeswheredepartment_id=i;

• TEST.employees_loop(empRefCur);

• endloop;

• exception

• whenothersthen

• dbms_output.put_line(sqlerrm);

• closeempRefCur;

• end;

• /

4.BULK SQL

使用FORALL和BULK COLLECT子句。利用BULK SQL可以減少PLSQL Engine和SQL Engine之間的通信開銷，提高性能。

1. To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PL/SQL FORALL statement instead of a loop construct. 加速INSERT, UPDATE, DELETE語句的執行，也就是用FORALL語句來替代循環語句。

2. To speed up SELECT statements, include the BULK COLLECT INTO clause in the SELECT statement instead of using INTO. 加速SELECT，用BULK COLLECT INTO 來替代INTO。




• SQL>createtableemployees_tmpasselectfirst_name,last_name,salaryfromemployeeswhere0=1;

• setserveroutputon

• declare

• cursoremployees_cur(depIdemployees.department_id%type)isselectfirst_name,last_name,_id=depId;

• typeemployee_table_typeistableofemployees_cur%rowtypeindexbypls_integer;

• employee_tableemployee_table_type;

• begin

• openemployees_cur(100);

• fetchemployees_curbulkcollectintoemployee_table;

• closeemployees_cur;

• foriin1..employee_table.countloop

• dbms_output.put_line(employee_table(i).first_name||''||employee_table(i).last_name||','||employee_table(i).salary);

• endloop;

• foralliinemployee_table.first..employee_table.last

• insertintoemployees_tmpvalues(employee_table(i).first_name,employee_table(i).last_name,employee_table(i).salary);

• commit;

• end;

• /

『叄』 oracle存儲過程游標有什麼用

1，什麼是游標？
①從表中檢索出結果集，從中每次指向一條記錄進行交互的機制。

②關系資料庫中的操作是在完整的行集合上執行的。
由SELECT 語句返回的行集合包括滿足該語句的WHERE 子句所列條件的所有行。由該語句返回完整的行集合叫做結果集。
應用程序，尤其是互動和在線應用程序，把完整的結果集作為一個單元處理並不總是有效的。
這些應用程序需要一種機制來一次處理一行或連續的幾行。而游標是對提供這一機制的結果集的擴展。

游標是通過游標庫來實現的。游標庫是常常作為資料庫系統或數據訪問API 的一部分而得以實現的軟體，
用來管理從數據源返回的數據的屬性（結果集）。這些屬性包括並發管理、在結果集中的位置、返回的行數，
以及是否能夠在結果集中向前和/或向後移動（可滾動性）。

游標跟蹤結果集中的位置，並允許對結果集逐行執行多個操作，在這個過程中可能返回至原始表，也可能不返回至原始表。
換句話說，游標從概念上講基於資料庫的表返回結果集。
由於它指示結果集中的當前位置 ，就像計算機屏幕上的游標指示當前位置一樣，「游標」由此得名。