在 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)的使用情況不同：
SQL語(yǔ)句 游標(biāo) 非查詢語(yǔ)句 隱式的 結(jié)果是單行的查詢語(yǔ)句 隱式的或顯示的 結(jié)果是多行的查詢語(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;
注：定義的游標(biāo)不能有 INTO 子句甩鳄。例 1. 查詢前 10 名員工的信息逞度。
declare --定義游標(biāo) cursor c_cursor is select last_name,salary from employees where rownum < 11 order by salary; v_name employees.last_name%type; v_sal employees.salary%type; begin --打開(kāi)游標(biāo) open c_cursor; --提取游標(biāo)數(shù)據(jù) fetch c_cursor into v_name,v_sal; while c_cursor % found loop dbms_output.put_line(v_name || ':' || v_sal); fetch c_cursor into v_name,v_sal; end loop; --關(guān)閉游標(biāo) close c_cursor; end;例 2. 游標(biāo)參數(shù)的傳遞方法。
declare --定義游標(biāo) cursor c_cursor(emp_no number default 11) is select last_name,salary from employees where rownum < emp_no order by salary; v_name employees.last_name%type; v_sal employees.salary%type; begin --打開(kāi)游標(biāo) open c_cursor; --提取游標(biāo)數(shù)據(jù) fetch c_cursor into v_name,v_sal; while c_cursor % found loop dbms_output.put_line(v_name || ':' || v_sal); fetch c_cursor into v_name,v_sal; end loop; --關(guān)閉游標(biāo) close c_cursor; end;declare --定義游標(biāo) cursor c_cursor(emp_no number default 11) is select last_name,salary from employees where rownum < emp_no order by salary; v_name employees.last_name%type; v_sal employees.salary%type; begin --打開(kāi)游標(biāo) open c_cursor(emp_no => 20); --提取游標(biāo)數(shù)據(jù) fetch c_cursor into v_name,v_sal; while c_cursor % found loop dbms_output.put_line(v_name || ':' || v_sal); fetch c_cursor into v_name,v_sal; end loop; --關(guān)閉游標(biāo) close c_cursor; end;
- 游標(biāo)屬性
%FOUND 布爾型屬性妙啃，當(dāng)最近一次讀記錄時(shí)成功返回,則值為 TRUE档泽； %NOTFOUND 布爾型屬性，與%FOUND 相反揖赴；
%ISOPEN 布爾型屬性馆匿，當(dāng)游標(biāo)已打開(kāi)時(shí)返回 TRUE；
%ROWCOUNT 數(shù)字型屬性燥滑，返回已從游標(biāo)中讀取的記錄數(shù)渐北。
- 例 3：給工資低于 3000 的員工工資調(diào)為 3000。
declare v_eid employees.employee_id%type; v_sal employees.salary%type; cursor c_cursor is select employee_id,salary from employees; begin open c_cursor; loop fetch c_cursor into v_eid,v_sal; exit when c_cursor %notfound; if v_sal <= 3000 then update employees set salary = 3000; where employee_id = v_eid; dbms_output.put_line('員工：' || v_eid || '工資已經(jīng)更新'); end if; end loop; dbms_output.put_line('記錄數(shù)為：' || c_cursor %rowcount); close c_cursor; end;
- 游標(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ù)。
- 注：不要在程序中對(duì)游標(biāo)進(jìn)行人工操作造寝；不要在程序中定義用于控制 FOR 循環(huán)的記錄
- 例4：
declare cursor c_emp is select last_name,salary sal from employees; begin for v_emp in c_emp loop dbms_output.put_line(v_emp.last_name || ',' || v_emp.sal); end loop; end;
- 例 5：當(dāng)所聲明的游標(biāo)帶有參數(shù)時(shí)，通過(guò)游標(biāo) FOR 循環(huán)語(yǔ)句為游標(biāo)傳遞參數(shù)吭练。
declare cursor c_emp(dep_id number default 50) is select last_name,salary sal from employees where department_id = dep_id; begin for v_emp in c_emp loop dbms_output.put_line(v_emp.last_name || ',' || v_emp.sal); end loop; end;declare cursor c_emp(dep_id number default 50) is select last_name,salary sal from employees where department_id = dep_id; begin for v_emp in c_emp(80) loop dbms_output.put_line(v_emp.last_name || ',' || v_emp.sal); end loop; end;
- 例 6：PL/SQL 還允許在游標(biāo) FOR 循環(huán)語(yǔ)句中使用子查詢來(lái)實(shí)現(xiàn)游標(biāo)的功能诫龙。
begin for v_emp in(select last_name,salary from employees) loop dbms_output.put_line(v_emp.last_name || ',' || v_emp.salary); end loop; end;
- 處理隱式游標(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ù)。
- 格式調(diào)用為： SQL%
- 隱式游標(biāo)屬性
SQL%FOUND 布爾型屬性,當(dāng)最近一次讀記錄時(shí)成功返回白胀，則值為 TRUE椭赋；
SQL%NOTFOUND 布爾型屬性,與%FOUND 相反；
SQL%ROWCOUNT 數(shù)字型屬性, 返回已從游標(biāo)中讀取得記錄數(shù)纹笼；
SQL%ISOPEN 布爾型屬性, 取值總是 FALSE纹份。SQL 命令執(zhí)行完畢立即關(guān)閉隱式游標(biāo)。- 例 7: 更新指定員工信息廷痘，如果該員工沒(méi)有找到蔓涧，則打印”查無(wú)此人”信息。
declare v_name employees.last_name%type; v_id employees.employee_id%type := &v_id; begin update employees set last_name = 'xx' where employee_id = v_id; if SQL%NOTFOUND then dbms_output.put_line('查無(wú)此人'); end if; end;
- 關(guān)于 NO_DATA_FOUND 和 %NOTFOUND 的區(qū)別
- SELECT … INTO 語(yǔ)句觸發(fā) NO_DATA_FOUND笋额；
- 當(dāng)一個(gè)顯式游標(biāo)的 WHERE 子句未找到時(shí)觸發(fā)%NOTFOUND元暴；
- 當(dāng) UPDATE 或 DELETE 語(yǔ)句的 WHERE 子句未找到時(shí)觸發(fā)
SQL%NOTFOUND；在提取循環(huán)中要用 %NOTFOUND 或%FOUND 來(lái)確定循環(huán)的退出條件兄猩，不要用 NO_DATA_FOUND.
- 游標(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]
- 如果另一個(gè)會(huì)話已對(duì)活動(dòng)集中的行加了鎖啸驯，那么 SELECT FOR UPDATE 操作一直等待到其它的會(huì)話釋放這 些鎖后才繼續(xù)自己的操作，對(duì)于這種情況祟峦，當(dāng)加上 NOWAIT 子句時(shí)罚斗，如果這些行真的被另一個(gè)會(huì)話鎖定， 則 OPEN 立即返回并給出： ORA-0054 ：resource busy and acquire with nowait specified.
- 如果使用 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ù)行。
- 例 8：從 EMPLOYEES 表中查詢某部門(mén)的員工情況咱筛，將其工資最低定為 3000搓幌；
declare v_dep_id employees.department_id%type := &v_dep_id; cursor emp_cursor is select last_name,salary from employees where department_id = v_dep_id for update nowait; begin for emp_rec in emp_cursor loop if emp_rec.salary < 3000 then update employees set salary = 3000 where current of emp_cursor; end if; end loop; end;
