# Cursor
###### tags: `Oracle` `SQL`
在 Oracle 中，cursor 分為兩種，分別是：

implicit(內隱) cursor
在 Oracle 所提供的功能中，可以發現許多 implicit cursor 的蹤跡，例如：PL/SQL 中的 FOR…LOOP。
而這一類的 cursor 是沒有辦法透過程式去控制的，而是由 Oracle 本身來進行控制與管理。

explicit(外顯) cursor
此種 cursor 就可以透過開發 PL/SQL 的方式來進行控制與管理，讓管理者可以有很大的彈性來使用。

Oracle 預設提供50個cursor可使用,若因開發需求更多則透過修改起始參數"OPEN_CURSOR"來達成

非查詢語句 > 隱式
結果為單行查詢語句 > 隱式或顯式
結果為多行查詢語句 > 顯式

處理 explicit cursor
每個 explicit cursor 都會有以下幾個屬性作為程式控制之用：
%NOTFOUND 根據從資料集合中擷取的最後一筆資料，來決定回傳 TRUE(有資料) or FALSE(沒資料)。
%FOUND 與 %NOTFOUND 相反。
%ROWCOUNT 回傳資料集合中所包含的資料筆數。當 cursor 從資料集合擷取過資料後，就可以使用此屬性。
%ISOPEN 若是 cursor 狀態為 open，則回傳 TRUE；反之則回傳 FALSE。
為了暫時存放從 cursor 擷取出來的資料列，必須定義一個暫存的變數，而這個變數的型態通常為一列資料(row data)。

DECLARE
--宣告 cursor，並指定擷取資料集合的 SQL 語法(query_definition)
CURSOR cursor_name[(param p_type,....)] [return_type] IS query_definition;
--開啟 cursor(此時會根據宣告時中定義的 SQL 語法取得資料集合)
OPEN cursor_name[(param p_type,....)]
LOOP
--取得 cursor 目前所在的資料列(必須使用暫存變數來儲存取得的資料列)
FETCH record INTO buffer_var;
--當 cursor 已經將資料集合的資料巡覽完畢
EXIT WHEN cursor_name%NOTFOUND;
--各式各樣的處理工作
...;
END LOOP;
--關閉 cursor(之後 cursor 就不能再繼續使用)
CLOSE cursor_name;
游標FOR循環：
FOR record_name IN
(corsor_name[(parameter[,parameter]...)]
| (query_difinition 查詢語句 select ....)
LOOP
statements
END LOOP;

游標中的更新與刪除
在PL/SQL中提供了僅僅使用CURSOR就可以更新或刪除的方法
UPDATE OR DELETE 中 WHERE CURRENT OF CURSOR_NAME => 取出此CURSOR目前的資料
要使用此方法在宣告CURSOR時要加上 FOR UPDATE OF
當在SESSION中開啟了CURSOR則該CURSOR返回的資料會加上獨佔式鎖定(ROW-LEVEL),其他對象只能查詢
在多表查詢中使用OF 來鎖定特定的表,若是忽略了OF則所有的表皆會被鎖定

FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..
[nowait]

WHERE{CURRENT OF cursor_name|search_condition}

DECLARE
CURSOR
crs_testAvg
IS
select EMPNO,ENAME,JOB,SAL,DEPNO,AVG(SAL) OVER (PARTITION BY DEPNO ) AS DEP_AVG
FROM EMP1 for update of SAL;
r_testAvg crs_testAvg%rowtype;
salInfo emp1.sal%type;
begin
for r_testAvg in crs_testAvg loop
if r_testAvg.SAL>r_testAvg.DEP_AVG then
salInfo:=r_testAvg.SAL-50;
end if;
update emp1 set SAL=salInfo where current of crs_testAvg;
end loop;
end;

使用 Cursor Variable：REF Cursor
從上面 cursor 的使用方式可以知道，由於 cursor 的使用必須寫在 subprogram 中，並無法在程式中動態的變更所擷取的資料，因此在使用上是靜態的。
然而，若要在程式中動態的改變 cursor 所擷取的資料，或是將 cursor 所取到的資料集合當作參數傳到另一個 subprogram 中，就必須改用 REF Cursor 了!
什麼是 cursor variable 呢? 簡單來說，就是「儲存指向實際 cursor 的指標的變數」
Cursor variable ==> cursor > row1
[0XBC34AS] 記憶體位址0XBC34AS > row2
> row3 ..
其中假設實際 cursor 所在的記憶體位址為 0XBC34AD，而 cursor variable 儲存的就是 0XBC34AD 這個記憶體位址，而非 cursor 本身。
也由於 cursor variable 有如此特性，因此假設同時有多個 cursor 同時開啟，就可以將不同的記憶體位址指定給 cursor variable，
這樣就可以讓 cursor variable 很快速的取得不同 cursor 從資料庫所擷取到的不同資料。
cursor 將資料從資料庫取出後，就存放於記憶體中，接著後續的處理就會很快速。

Cursor變數(Cursor variable)與Cursor一樣，是指向一個查詢結果(result set)的指標(pointer)。
Cursor變數也稱作Ref Cursor，在PL/SQL宣告Cursor變數的data type為REF CURSOR。
Cursor與Cursor變數的差異：
Cursor指向固定的查詢結果，是靜態不可改變的，但Cursor變數可指向不同的查詢結果，是動態可改變的。
Cursor不能作為參數傳遞，或回傳給呼叫Procedure的client端(例如Java應用程式)，而Cursor變數可作為參數在子程式中傳遞，且可回傳給client端。
Cursor作用域可為global，而Cursor變數只能定義在子程式中。

DECLARE
TYPE cursor_type1 IS REF CURSOR; -- 宣告Ref Cursor資料型態, weak type
cursor_var1 cursor_type1; -- 宣告curor變數

TYPE cursor_type2 IS REF CURSOR RETURN employee%ROWTYPE -- 宣告Ref Cursor資料型態, strong type
cursor_var2 cursor_type2; -- 宣告curor變數

REF CURSOR如果有定義回傳資料型態(return type)為strong type，如沒定義return type則為weak type。
記得要先宣告好REF CURSOR資料型態後，才宣告Cursor變數。

Strong type的Ref Cursor執行時錯誤較少，因為PL/SQL編譯器在編譯時若發現查詢結果的資料型態與Ref Cursor定義的回傳形態不同就會先報錯。
而weak type的Ref Cursor比較彈性可以是任何型態的查詢結果，因為weak type的Cursor變數在編譯時不會檢查型態，所以weak type的Cursor變數的資料型態可以改變，
你可以將一個weak Cursor變數的值分配到另一個weak Cursor變數。

除了自己建立新的cursor type，也可使用定義好的SYS_REFCURSOR，這樣就不用先宣告cursor type了。
SYS_REFCURSOR和weak type的Ref Cursor是一樣的東西。
DECLARE
cursor_var3 SYS_REFCURSOR;
在Package中使用時，你可以在Package spec中宣告Ref Cursor的資料型態來避免在Package子程式(就是Package中的Procedure或Function)中重複宣告相同型態的Ref Cursor。
然後才在Package body的子程式中定義Cursor變數。

-- Ref Cursor範例一

DECLARE
TYPE cursor_type1 IS REF CURSOR; -- 定義REF CURSOR資料型態為cursor_type1
ref_cursor1 cursor_type1; --宣告Curosr變數,資料型態為cursor_type1
-- 宣告一個Procedure
PROCEDURE get_student(cv_student IN cursor_type1) IS -- 傳入參數為Cursor變數,型態為cursor_type1
l_student STUDENT%ROWTYPE; -- 宣告一個Record變數,資料型態同STUDENT,用來裝載從Cursor變數中取出的資料
BEGIN
LOOP
FETCH cv_student INTO l_student; -- 抓取cv_student中的一列資料放入l_student
EXIT WHEN ref_cursor1%NOTFOUND; -- 當讀取不到資料時跳離LOOP迴圈
DBMS_OUTPUT.PUT_LINE(l_student.student_id ||','||
l_student.student_name ||','||
l_student.student_height);
END LOOP;
END;
BEGIN
OPEN ref_cursor1 FOR SELECT * FROM STUDENT WHERE student_height > 170; -- 將Cursor變數指到一個查詢結果
DBMS_OUTPUT.PUT_LINE('----- 高的學生 students -----');
get_student(ref_cursor1); -- 呼叫Procedure並傳入Cursor變數
CLOSE ref_cursor1; -- 關閉Cursor變數

OPEN ref_cursor1 FOR SELECT * FROM STUDENT WHERE student_height < 150;
DBMS_OUTPUT.PUT_LINE('----- 矮的學生 students -----');
get_student(ref_cursor1); -- 呼叫Procedure並傳入Cursor變數
CLOSE ref_cursor1;
END;

--範例2
可設計一個PROCEDURE透過傳入參數來動態配置REF CURSOR

CREATE OR REPLACE
PROCEDURE get_emp_rs (p_deptno IN emp.deptno%TYPE,
p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT ename,
empno,
deptno
FROM emp
WHERE deptno = p_deptno
ORDER BY ename;
END GetEmpRS;

---------------------------------------------------------
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_cursor SYS_REFCURSOR;
l_ename emp.ename%TYPE;
l_empno emp.empno%TYPE;
l_deptno emp.deptno%TYPE;
BEGIN
get_emp_rs (p_deptno => 30,
p_recordset => l_cursor);

LOOP
FETCH l_cursor
INTO l_ename, l_empno, l_deptno;
EXIT WHEN l_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
END LOOP;
CLOSE l_cursor;
END;