PL/SQL 遊標的使用

PL/SQL 遊標的使用

遊標的使用

①遊標概念
為了處理SQL 陳述式，Oracle 必須分配一片叫上下文( context area )的地區來處理所必需的資訊，
其中包括要處理的行的數目，一個指向語句被分析以後的表示形式的指標以及查詢的活動集(active set)。
遊標是一個指向內容相關的控制代碼( handle)或指標。通過遊標，PL/SQL可以控制上下文區和處理語句時上下文區會發生些什麼事情

②顯式遊標處理

1.顯式遊標處理需四個PL/SQL步驟:

定義遊標：就是定義一個遊標名，以及與其相對應的SELECT 語句。
格式：
CURSOR cursor_name[(parameter[, parameter]…)] IS select_statement;
遊標參數只能為輸入參數，其格式為：
parameter_name [IN] datatype [{:= | DEFAULT} expression]
在指定資料類型時，不能使用長度約束。如NUMBER(4)、CHAR(10) 等都是錯誤的。

開啟遊標：就是執行遊標所對應的SELECT 語句，將其查詢結果放入工作區，並且指標指向工作區的首部，標識遊標結果集合。
如果遊標查詢語句中帶有FOR UPDATE選項，OPEN 語句還將鎖定資料庫表中遊標結果集合對應的資料行。
格式：
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];
在向遊標傳遞參數時，可以使用與函數參數相同的傳值方法，即位置標記法和名稱標記法。PL/SQL 程式不能用OPEN 語句重複開啟一個遊標。

提取遊標資料：就是檢索結果集合中的資料行，放入指定的輸出變數中。
格式：
FETCH cursor_name INTO {variable_list | record_variable };
對該記錄進行處理；
繼續處理，直到活動集合中沒有記錄；

關閉遊標：當提取和處理完遊標結果集合資料後，應及時關閉遊標，以釋放該遊標所佔用的系統資源，並使該遊標的工作區變成無效，
不能再使用FETCH 語句取其中資料。關閉後的遊標可以使用OPEN 語句重新開啟。
格式：
CLOSE cursor_name;
註：定義的遊標不能有INTO 子句。

--------------------------------------分割線 --------------------------------------

rlwrap - 解決Linux下SQLPLUS退格、上翻鍵亂碼問題

SQLPLUS spool 到動態記錄檔名

Oracle SQLPLUS提示符設定

通過設定SQLPLUS ARRAYSIZE(行預取)加快SQL返回速度

PL/SQL Developer實用技巧分享

--------------------------------------分割線 --------------------------------------

2.遊標屬性
%FOUND      布爾型屬性，當最近一次讀記錄時成功返回,則值為TRUE；
%NOTFOUND  布爾型屬性，與%FOUND相反；
%ISOPEN      布爾型屬性，當遊標已開啟時返回TRUE；
%ROWCOUNT  數字型屬性，返回已從遊標中讀取的記錄數。

3.遊標的FOR迴圈
PL/SQL語言提供了遊標FOR迴圈語句，自動執行遊標的OPEN、FETCH、CLOSE語句和迴圈語句的功能；當進入迴圈時，遊標FOR迴圈語句自動開啟遊標，並提取第一行遊標資料，當程式處理完當前所提取的資料而進入下一次迴圈時，遊標FOR迴圈語句自動提取下一行資料供程式處理，當提取完結果集合中的所有資料行後結束迴圈，並自動關閉遊標。
格式：
FOR index_variable  IN cursor_name[value[, value]…]  LOOP
    --遊標資料處理代碼
END LOOP;
其中：
index_variable為遊標FOR 迴圈語句隱含聲明的索引變數，該變數為記錄變數，其結構與遊標查詢語句返回的結構集合的結構相同。在程式中可以通過引用該索引記錄變數元素來讀取所提取的遊標資料，
index_variable中各元素的名稱與遊標查詢語句挑選清單中所制定的列名相同。
如果在遊標查詢語句的挑選清單中存在計算資料行，則必須為這些計算資料行指定別名後才能通過遊標FOR 迴圈語句中的索引變數來訪問這些列資料。
註：不要在程式中對遊標進行人工操作；不要在程式中定義用於控制FOR 迴圈的記錄。

③處理隱式遊標
顯式遊標主要是用於對查詢語句的處理，尤其是在查詢結果為多條記錄的情況下；
而對於非查詢語句，如修改、刪除操作，則由ORACLE 系統自動地為這些操作設定遊標並建立其工作區，
這些由系統隱含建立的遊標稱為隱式遊標，隱式遊標的名字為SQL，這是由ORACLE 系統定義的。對於隱式遊標的操作，
如定義、開啟、取值及關閉操作，都由ORACLE 系統自動地完成，無需使用者進行處理。使用者只能通過隱式遊標的相關屬性，來完成相應的操作。
在隱式遊標的工作區中，所存放的資料是與使用者自訂的顯示遊標無關的、最新處理的一條SQL 陳述式所包含的資料。

格式調用為：SQL%
隱式遊標屬性
SQL%FOUND      布爾型屬性,當最近一次讀記錄時成功返回，則值為TRUE；
SQL%NOTFOUND  布爾型屬性,與%FOUND相反；
SQL %ROWCOUNT  數字型屬性, 返回已從遊標中讀取得記錄數；
SQL %ISOPEN    布爾型屬性, 取值總是FALSE。SQL命令執行完畢立即關閉隱式遊標。

④關於NO_DATA_FOUND 和%NOTFOUND的區別
SELECT … INTO 語句觸發NO_DATA_FOUND；（EXCEPTION  when  NO_DATA_FOUND then ......）
當一個顯式遊標的WHERE子句未找到時觸發%NOTFOUND；
當UPDATE或DELETE 語句的WHERE 子句未找到時觸發SQL%NOTFOUND；
在提取迴圈中要用%NOTFOUND 或%FOUND 來確定迴圈的允出準則，不要用NO_DATA_FOUND.

⑤遊標修改和刪除操作
遊標修改和刪除操作是指在遊標定位下，修改或刪除表中指定的資料行。
這時，要求遊標查詢語句中必須使用FOR UPDATE選項，以便在開啟遊標時鎖定遊標結果集合在表中對應資料行的所有列和部分列。
為了對正在處理(查詢)的行不被另外的使用者改動，ORACLE 提供一個FOR UPDATE 子句來對所選擇的行進行鎖住。
該需求迫使ORACLE鎖定遊標結果集合的行，可以防止其他交易處理更新或刪除相同的行，直到您的交易處理提交或回退為止。
文法：
SELECT . . . FROM … FOR UPDATE [OF column[, column]…] [NOWAIT]
如果另一個會話已對活動集中的行加了鎖，那麼SELECT FOR UPDATE操作一直等待到其它的會話釋放這些鎖後才繼續自己的操作，
對於這種情況，當加上NOWAIT子句時，如果這些行真的被另一個會話鎖定，則OPEN立即返回並給出：
ORA-0054 ：resource busy  and  acquire with nowait specified.
如果使用FOR UPDATE 聲明遊標，則可在DELETE和UPDATE 語句中使用WHERE CURRENT OF cursor_name子句，
修改或刪除遊標結果集合當前行對應的資料庫表中的資料行

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁