PL/SQL學習筆記-3

PL/SQL學習筆記-3

第6章到第7章

[@more@]

第6章 遊標

6.1 什麼是遊標
為了處理SQL語句，Oracle將在記憶體中分配一個區域，這就是上下文區。
這個區包含了完成處理過程的必要資訊，其中包括已經處理完的行數、指向被分析語句的指標和在查詢情況下的活動集，它是查詢語句返回的資料行集。
遊標是指向上下文區的控制程式碼或者指標。

處理顯示遊標的步驟
1.宣告遊標
CURSOR cursor_name IS select_statement;
遊標宣告也可以引用PL/SQL變數。但必須在這之前宣告。

2.開啟遊標
OPEN cursor_name;
當遊標開啟時：
* 檢驗繫結變數的值
* 基於的繫結變數的值和查詢中所使用的表的內容，確定活動集
* 活動集指標指向第一行

3.用遊標檢索值
FETCH cursor_name INTO list_of_variables;
FETCH cursor_name INTO PL/SQL_record;

4.關閉遊標
CLOSE cursor_name;

5.遊標屬性
%FOUND、%NOTFOUND、%ISOPEN、%ROWCOUNT、%BULK_ROWCOUNT(Oralce8i之後)

處理隱式遊標
在PL/SQL中直接執行sql，會呼叫隱式遊標，可以用SQL%FOUND SQL%NOTFOUNT SQL%ISOPEN SQL%ROWCOUNT SQL%BULK_ROWCOUNT

6.2 遊標檢索迴圈

簡單迴圈
OPEN c_xxx
LOOP
FETCH cxxx INTO xxx
EXIT WHEN c_xxx%NOTFOUND;
ENDLOOP

WHILE迴圈
OPEN cursor_name;
FETCH cursor_name INTO xxx;
WHILE cursor_name%FOUND LOOP
...
FETCH cursor_name INTO xxx;
END LOOP;
CLOSE cursor_name;

遊標FOR迴圈：
FOR v_xxx IN ('xxx') LOOP
...
END LOOP;

NO_DATA_FOUND異常只有當SELECT...INTO語句的WHERE子句沒有匹配任何資料行時出現。
當顯式遊標的WHERE子句不匹配任何資料行時，則%NOTFOUND被設定為TRUE。
UPDAT或者DELETE語句的WHERE子句不匹配任何資料行時，則%NOTFOUND被設定為TRUE。而不出現NO_DATA_FOUND。

FOR UPDATE
SELECT ... FROM ... FOR UPDATE[ OF column_reference ][ NOWAIT|WAIT n ]
n為要等待的秒數。

WHERE CURRENT OF
可以使用 WHEN CURRENT OF cursor_name 來更新當前記錄，但是遊標中的SELECT必須要有FOR UPDATE。

透過提交檢索
COMMIT會釋放該會話所所擁有的任何鎖。因為FOR UPDATE 需要鎖，當釋放鎖之後，遊標就無效了。之後的檢索就都無效了。
所以不要在FOR UPDATE的遊標迴圈裡面使用COMMIT。
即時沒有用for update，在迴圈中使用commit，則可能會出現 ORA-1555:snapshot too old 這樣的錯誤。
如果要不使用for update 來更新記錄，可以用 where id=v_xx.id 的方法，如果沒有主鍵的話，可以考慮用rowid。


6.3 遊標變數
引用型別的宣告： REF type
宣告遊標變數： TYPE type_name IS REF CURSOR [RETURN return_type]
有RETURN引數的是約束遊標變數，否則是無約束遊標變數。
開啟遊標 OPEN cursor variable FOR select_statement;

DECLARE
TYPE emp_cur_typ IS CURSOR;
emp_cur emp_cur_typ;
v_stmt VARCHAR2(200);
BEGIN
v_stmt := 'SELECT * FROM table_name';
OPEN emp_cur FOR v_stmt;
...
...
END;


第7章 錯誤處理
當某錯誤出現時，某異常將被丟擲。此時控制權將會被傳遞給異常處理器。
使用者自定義異常：
DECLARE
e_TooManyStudents EXCEPTION;
預定義異常：
NO_DATA_FOUND ZERO_DIVIDE INVALID_NUMBER ...

丟擲異常
透過RAISE語句，使用者自定義異常可以被顯式丟擲。
處理異常
EXCPETION
WHEN exception_name [or xxxxx] THEN
sequence_of_statements1;
[WHEN OTHERS THEN
sequence_of_statements3;]
END;

某給定異常最多由異常部分的一個處理器進行處理。如果某異常有多個處理器，則PL/SQL編譯器將產生PLS-483錯誤。
PL/SQL定義了一個特殊的異常處理器，即WHEN OTHERS。
SQLERRM可以帶引數使用 SQLERRM(引數) 此引數為負值，唯一例外為SQLERRM(100)。
注意SQLCODE和SQLERRM的值先賦給本地變數，然後才用於SQL語句中。因為這些函式是過程化的，所以不能直接用於SQL語句。

可以把某命名異常同某特定Oracle錯誤關聯起來。這將使你能捕獲這種特定錯誤，而不是透過OTHERS處理器。
PRAGMA EXCEPTION_INIT(exception_name,oracle_error_number);

RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);
error_number 是一個在-20000和-20999之間的值

7.2 異常傳播
如果在宣告部分某賦值產生了異常，該異常被立即傳播到外層語句塊，即使在當前語句快有一個處理器，也不會被執行。

7.3 異常準則

7.4 通用錯誤處理器
可以考慮利用 DBMS_UTILITY.FORMAT_CALL_STACK 和 DBMS_UTILITY.FORMAT_ERROR_STACK 函式製作一個通用錯誤處理器。