前言

厚積而薄發(fā)。

在 PL/SQL 程序中，對(duì)于處理多行記錄的事務(wù)經(jīng)常使用游標(biāo)來(lái)實(shí)現(xiàn)。

游標(biāo)的概念

--為了處理 SQL 語(yǔ)句，ORACLE 必須分配一片叫上下文( context area )的區(qū)域來(lái)處理所必需的信息，其中包括要處理的行的數(shù)目，一個(gè)指向語(yǔ)句被分析以后的表示形式的指針以及查詢的活動(dòng)集(active set)。
--游標(biāo)是一個(gè)指向上下文的句柄( handle)或指針。通過(guò)游標(biāo)，PL/SQL可以控制上下文區(qū)和處理語(yǔ)句時(shí)上下文區(qū)會(huì)發(fā)生些什么事情。
對(duì)于不同的SQL語(yǔ)句，游標(biāo)的使用情況不同：

處理顯式游標(biāo)

顯式游標(biāo)處理需四個(gè) PL/SQL步驟:

定義游標(biāo)：就是定義一個(gè)游標(biāo)名，以及與其相對(duì)應(yīng)的SELECT 語(yǔ)句。

格式：

CURSOR cursor_name[(parameter[, parameter]…)] IS select_statement;
游標(biāo)參數(shù)只能為輸入?yún)?shù)，其格式為：
parameter_name [IN] datatype [{:= | DEFAULT} expression]
在指定數(shù)據(jù)類(lèi)型時(shí)，不能使用長(zhǎng)度約束。如NUMBER(4)、CHAR(10) 等都是錯(cuò)誤的。

打開(kāi)游標(biāo)：就是執(zhí)行游標(biāo)所對(duì)應(yīng)的SELECT 語(yǔ)句，將其查詢結(jié)果放入工作區(qū)，并且指針指向工作區(qū)的首部，標(biāo)識(shí)游標(biāo)結(jié)果集合。如果游標(biāo)查詢語(yǔ)句中帶有FOR UPDATE選項(xiàng)，OPEN 語(yǔ)句還將鎖定數(shù)據(jù)庫(kù)表中游標(biāo)結(jié)果集合對(duì)應(yīng)的數(shù)據(jù)行。

格式：

OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];
在向游標(biāo)傳遞參數(shù)時(shí)，可以使用與函數(shù)參數(shù)相同的傳值方法，即位置表示法和名稱(chēng)表示 法。PL/SQL 程序不能用OPEN 語(yǔ)句重復(fù)打開(kāi)一個(gè)游標(biāo)。

提取游標(biāo)數(shù)據(jù)：就是檢索結(jié)果集合中的數(shù)據(jù)行，放入指定的輸出變量中。

格式：

FETCH cursor_name INTO {variable_list | record_variable };
對(duì)該記錄進(jìn)行處理；
繼續(xù)處理，直到活動(dòng)集合中沒(méi)有記錄；

關(guān)閉游標(biāo)：當(dāng)提取和處理完游標(biāo)結(jié)果集合數(shù)據(jù)后，應(yīng)及時(shí)關(guān)閉游標(biāo)，以釋放該游標(biāo)所占用的系統(tǒng)資源，并使該游標(biāo)的工作區(qū)變成無(wú)效，不能再使用FETCH 語(yǔ)句取其中數(shù)據(jù)。關(guān)閉后的游標(biāo)可以使用OPEN 語(yǔ)句重新打開(kāi)。

格式：

CLOSE cursor_name;

列1：查詢前10名員工的信息。

屏幕快照 2018-01-14 01.14.12.png

游標(biāo)參數(shù)的傳遞方法。

屏幕快照 2018-01-14 01.16.08.png

顯示游標(biāo)屬性

%FOUND 布爾型屬性，當(dāng)最近一次讀記錄時(shí)成功返回,則值為T(mén)RUE；
%NOTFOUND 布爾型屬性，與%FOUND相反；
%ISOPEN 布爾型屬性，當(dāng)游標(biāo)已打開(kāi)時(shí)返回 TRUE；
%ROWCOUNT 數(shù)字型屬性，返回已從游標(biāo)中讀取的記錄數(shù)。

列2：給工資低于3000 的員工工資調(diào)為 3000

屏幕快照 2018-01-14 01.19.00.png

游標(biāo)的for循環(huán)

--PL/SQL語(yǔ)言提供了游標(biāo)FOR循環(huán)語(yǔ)句，自動(dòng)執(zhí)行游標(biāo)的OPEN、FETCH、CLOSE語(yǔ)句和循環(huán)語(yǔ)句的功能；當(dāng)進(jìn)入循環(huán)時(shí)，游標(biāo)FOR循環(huán)語(yǔ)句自動(dòng)打開(kāi)游標(biāo)，并提取第一行游標(biāo)數(shù)據(jù)，當(dāng)程序處理完當(dāng)前所提取的數(shù)據(jù)而進(jìn)入下一次循環(huán)時(shí)，游標(biāo)FOR循環(huán)語(yǔ)句自動(dòng)提取下一行數(shù)據(jù)供程序處理，當(dāng)提取完結(jié)果集合中的所有數(shù)據(jù)行后結(jié)束循環(huán)，并自動(dòng)關(guān)閉游標(biāo)。
格式：

FOR index_variable IN cursor_name[value[, value]…] LOOP
-- 游標(biāo)數(shù)據(jù)處理代碼
END LOOP;

其中：
index_variable為游標(biāo)FOR 循環(huán)語(yǔ)句隱含聲明的索引變量，該變量為記錄變量，其結(jié)構(gòu)與游標(biāo)查詢語(yǔ)句返回的結(jié)構(gòu)集合的結(jié)構(gòu)相同。在程序中可以通過(guò)引用該索引記錄變量元素來(lái)讀取所提取的游標(biāo)數(shù)據(jù)，index_variable中各元素的名稱(chēng)與游標(biāo)查詢語(yǔ)句選擇列表中所制定的列名相同。如果在游標(biāo)查詢語(yǔ)句的選擇列表中存在計(jì)算列，則必須為這些計(jì)算列指定別名后才能通過(guò)游標(biāo)FOR 循環(huán)語(yǔ)句中的索引變量來(lái)訪問(wèn)這些列數(shù)據(jù)。

例3：

屏幕快照 2018-01-14 01.21.58.png

例4：當(dāng)所聲明的游標(biāo)帶有參數(shù)時(shí)，通過(guò)游標(biāo)FOR 循環(huán)語(yǔ)句為游標(biāo)傳遞參數(shù)。

屏幕快照 2018-01-14 01.23.06.png

或

屏幕快照 2018-01-14 01.23.42.png

例5：PL/SQL還允許在游標(biāo)FOR循環(huán)語(yǔ)句中使用子查詢來(lái)實(shí)現(xiàn)游標(biāo)的功能。

屏幕快照 2018-01-14 01.24.39.png

處理隱式游標(biāo)

--顯式游標(biāo)主要是用于對(duì)查詢語(yǔ)句的處理，尤其是在查詢結(jié)果為多條記錄的情況下；而對(duì)于非查詢語(yǔ)句，如修改、刪除操作，則由ORACLE 系統(tǒng)自動(dòng)地為這些操作設(shè)置游標(biāo)并創(chuàng)建其工作區(qū)，這些由系統(tǒng)隱含創(chuàng)建的游標(biāo)稱(chēng)為隱式游標(biāo)，隱式游標(biāo)的名字為SQL，這是由ORACLE 系統(tǒng)定義的。對(duì)于隱式游標(biāo)的操作，如定義、打開(kāi)、取值及關(guān)閉操作，都由ORACLE 系統(tǒng)自動(dòng)地完成，無(wú)需用戶進(jìn)行處理。用戶只能通過(guò)隱式游標(biāo)的相關(guān)屬性，來(lái)完成相應(yīng)的操作。在隱式游標(biāo)的工作區(qū)中，所存放的數(shù)據(jù)是與用戶自定義的顯示游標(biāo)無(wú)關(guān)的、最新處理的一條SQL 語(yǔ)句所包含的數(shù)據(jù)。

隱式游標(biāo)屬性

SQL%FOUND 布爾型屬性,當(dāng)最近一次讀記錄時(shí)成功返回，則值為T(mén)RUE；
SQL%NOTFOUND 布爾型屬性,與%FOUND相反；
SQL %ROWCOUNT 數(shù)字型屬性, 返回已從游標(biāo)中讀取得記錄數(shù)；
SQL %ISOPEN 布爾型屬性, 取值總是FALSE。SQL命令執(zhí)行完畢立即關(guān)閉隱式游標(biāo)。

例6：更新指定員工信息，如果該員工沒(méi)有找到，則打印”查無(wú)此人”信息。

屏幕快照 2018-01-14 01.27.13.png

游標(biāo)修改和刪除操作

--游標(biāo)修改和刪除操作是指在游標(biāo)定位下，修改或刪除表中指定的數(shù)據(jù)行。這時(shí)，要求游標(biāo)查詢語(yǔ)句中必須使用FOR UPDATE選項(xiàng)，以便在打開(kāi)游標(biāo)時(shí)鎖定游標(biāo)結(jié)果集合在表中對(duì)應(yīng)數(shù)據(jù)行的所有列和部分列。

--為了對(duì)正在處理(查詢)的行不被另外的用戶改動(dòng)，ORACLE 提供一個(gè) FOR UPDATE 子句來(lái)對(duì)所選擇的行進(jìn)行鎖住。該需求迫使ORACLE鎖定游標(biāo)結(jié)果集合的行，可以防止其他事務(wù)處理更新或刪除相同的行，直到您的事務(wù)處理提交或回退為止。

語(yǔ)法：

SELECT . . . FROM … FOR UPDATE [OF column[, column]…] [NOWAIT]

--如果使用 FOR UPDATE 聲明游標(biāo)，則可在DELETE和UPDATE 語(yǔ)句中使用WHERE CURRENT OF cursor_name子句，修改或刪除游標(biāo)結(jié)果集合當(dāng)前行對(duì)應(yīng)的數(shù)據(jù)庫(kù)表中的數(shù)據(jù)行。

例7：從EMPLOYEES表中查詢某部門(mén)的員工情況，將其工資最低定為 3000。

屏幕快照 2018-01-14 01.33.02.png