本篇主要内容如下：
4.1 遊标概念
4.1.1 處理顯式遊标
4.1.2 處理隐式遊标
4.1.3 關于 NO_DATA_FOUND 和 %NOTFOUND的差別
4.1.4 使用遊标更新和删除資料
4.2 遊标變量
4.2.1 聲明遊标變量
4.2.2 遊标變量操作
在 PL/SQL 程式中，對于處理多行記錄的事務經常使用遊标來實作。
在PL/SQL塊中執行SELECT、INSERT、DELETE和UPDATE語句時，ORACLE會在記憶體中為其配置設定上下文區（Context Area），即緩沖區。遊标是指向該區的一個指針，或是命名一個工作區（Work Area），或是一種結構化資料類型。它為應用等量齊觀提供了一種對具有多行資料查詢結果集中的每一行資料分别進行單獨處理的方法，是設計嵌入式SQL語句的應用程式的常用程式設計方式。
在每個使用者會話中，可以同時打開多個遊标，其數量由資料庫初始化參數檔案中的OPEN_CURSORS參數定義。
對于不同的SQL語句，遊标的使用情況不同：
SQL語句
遊标
非查詢語句
隐式的
結果是單行的查詢語句
隐式的或顯示的
結果是多行的查詢語句
顯示的
1. 顯式遊标處理
顯式遊标處理需四個 PL/SQL步驟:
l 定義/聲明遊标：就是定義一個遊标名，以及與其相對應的SELECT 語句。
格式：
CURSOR cursor_name[(parameter[, parameter]…)]
[RETURN datatype]
IS
select_statement;
遊标參數隻能為輸入參數，其格式為：
parameter_name [IN] datatype [{:= | DEFAULT} expression]
在指定資料類型時，不能使用長度限制。如NUMBER(4),CHAR(10) 等都是錯誤的。
[RETURN datatype]是可選的，表示遊标傳回資料的資料。如果選擇，則應該嚴格與select_statement中的選擇清單在次序和資料類型上比對。一般是記錄資料類型或帶“%ROWTYPE”的資料。
l 打開遊标：就是執行遊标所對應的SELECT 語句，将其查詢結果放入工作區，并且指針指向工作區的首部，辨別遊标結果集合。如果遊标查詢語句中帶有FOR UPDATE選項，OPEN 語句還将鎖定資料庫表中遊标結果集合對應的資料行。
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];
在向遊标傳遞參數時，可以使用與函數參數相同的傳值方法，即位置表示法和名稱表示法。PL/SQL 程式不能用OPEN 語句重複打開一個遊标。
l 提取遊标資料：就是檢索結果集合中的資料行，放入指定的輸出變量中。
FETCH cursor_name INTO {variable_list | record_variable };
執行FETCH語句時，每次傳回一個資料行，然後自動将遊标移動指向下一個資料行。當檢索到最後一行資料時，如果再次執行FETCH語句，将操作失敗，并将遊标屬性%NOTFOUND置為TRUE。是以每次執行完FETCH語句後，檢查遊标屬性%NOTFOUND就可以判斷FETCH語句是否執行成功并傳回一個資料行，以便确定是否給對應的變量賦了值。
l 對該記錄進行處理；
l 繼續處理，直到活動集合中沒有記錄；
l 關閉遊标：當提取和處理完遊标結果集合資料後，應及時關閉遊标，以釋放該遊标所占用的系統資源，并使該遊标的工作區變成無效，不能再使用FETCH 語句取其中資料。關閉後的遊标可以使用OPEN 語句重新打開。
CLOSE cursor_name;
注：定義的遊标不能有INTO 子句。
例1. 查詢前10名員工的資訊。
DECLARE
CURSOR c_cursor
IS SELECT first_name || last_name, Salary
FROM EMPLOYEES
WHERE rownum<11;
v_ename EMPLOYEES.first_name%TYPE;
v_sal EMPLOYEES.Salary%TYPE;
BEGIN
OPEN c_cursor;
FETCH c_cursor INTO v_ename, v_sal;
WHILE c_cursor%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(v_ename||'---'||to_char(v_sal) );
FETCH c_cursor INTO v_ename, v_sal;
END LOOP;
CLOSE c_cursor;
END;
例2. 遊标參數的傳遞方法。
DeptRec DEPARTMENTS%ROWTYPE;
Dept_name DEPARTMENTS.DEPARTMENT_NAME%TYPE;
Dept_loc DEPARTMENTS.LOCATION_ID%TYPE;
CURSOR c1 IS
SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 30;
CURSOR c2(dept_no NUMBER DEFAULT 10) IS
SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= dept_no;
CURSOR c3(dept_no NUMBER DEFAULT 10) IS
SELECT * FROM DEPARTMENTS
WHERE DEPARTMENTS.DEPARTMENT_ID <=dept_no;
OPEN c1;
LOOP
FETCH c1 INTO dept_name, dept_loc;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO dept_name, dept_loc;
EXIT WHEN c2%NOTFOUND;
CLOSE c2;
OPEN c3(dept_no =>20);
FETCH c3 INTO deptrec;
EXIT WHEN c3%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(deptrec.DEPARTMENT_ID||'---'||deptrec.DEPARTMENT_NAME||'---'||deptrec.LOCATION_ID);
CLOSE c3;
2.遊标屬性
Cursor_name%FOUND 布爾型屬性，當最近一次提取遊标操作FETCH成功則為 TRUE,否則為FALSE；
Cursor_name%NOTFOUND 布爾型屬性，與%FOUND相反；
Cursor_name%ISOPEN 布爾型屬性，當遊标已打開時傳回 TRUE；
Cursor_name%ROWCOUNT 數字型屬性，傳回已從遊标中讀取的記錄數。
例3：給工資低于1200 的員工增加工資50。
v_empno EMPLOYEES.EMPLOYEE_ID%TYPE;
v_sal EMPLOYEES.Salary%TYPE;
CURSOR c_cursor IS SELECT EMPLOYEE_ID, Salary FROM EMPLOYEES;
OPEN c_cursor;
LOOP
FETCH c_cursor INTO v_empno, v_sal;
EXIT WHEN c_cursor%NOTFOUND;
IF v_sal<=1200 THEN
UPDATE EMPLOYEES SET Salary=Salary+50 WHERE EMPLOYEE_ID=v_empno;
DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'工資已更新!');
END IF;
DBMS_OUTPUT.PUT_LINE('記錄數:'|| c_cursor %ROWCOUNT);
END LOOP;
CLOSE c_cursor;
END;
例4：沒有參數且沒有傳回值的遊标。
v_f_name employees.first_name%TYPE;
v_j_id employees.job_id%TYPE;
CURSOR c1 --聲明遊标,沒有參數沒有傳回值
IS
SELECT first_name, job_id FROM employees
WHERE department_id = 20;
OPEN c1; --打開遊标
FETCH c1 INTO v_f_name, v_j_id; --提取遊标
IF c1%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_f_name||'的崗位是'||v_j_id);
ELSE
DBMS_OUTPUT.PUT_LINE('已經處理完結果集了');
EXIT;
CLOSE c1; --關閉遊标
例5：有參數且沒有傳回值的遊标。
v_h_date employees.hire_date%TYPE;
CURSOR c2(dept_id NUMBER, j_id VARCHAR2) --聲明遊标,有參數沒有傳回值
SELECT first_name, hire_date FROM employees
WHERE department_id = dept_id AND job_id = j_id;
OPEN c2(90, 'AD_VP'); --打開遊标,傳遞參數值
FETCH c2 INTO v_f_name, v_h_date; --提取遊标
IF c2%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_f_name||'的雇傭日期是'||v_h_date);
CLOSE c2; --關閉遊标
例6：有參數且有傳回值的遊标。
TYPE emp_record_type IS RECORD(
f_name employees.first_name%TYPE,
h_date employees.hire_date%TYPE);
v_emp_record EMP_RECORD_TYPE;
CURSOR c3(dept_id NUMBER, j_id VARCHAR2) --聲明遊标,有參數有傳回值
RETURN EMP_RECORD_TYPE
OPEN c3(j_id => 'AD_VP', dept_id => 90); --打開遊标,傳遞參數值
FETCH c3 INTO v_emp_record; --提取遊标
IF c3%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇傭日期是'
||v_emp_record.h_date);
CLOSE c3; --關閉遊标
例7：基于遊标定義記錄變量。
CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --聲明遊标,有參數沒有傳回值
SELECT first_name f_name, hire_date FROM employees
--基于遊标定義記錄變量，比聲明記錄類型變量要友善，不容易出錯
v_emp_record c4%ROWTYPE;
OPEN c4(90, 'AD_VP'); --打開遊标,傳遞參數值
FETCH c4 INTO v_emp_record; --提取遊标
IF c4%FOUND THEN
||v_emp_record.hire_date);
CLOSE c4; --關閉遊标
3. 遊标的FOR循環
PL/SQL語言提供了遊标FOR循環語句，自動執行遊标的OPEN、FETCH、CLOSE語句和循環語句的功能；當進入循環時，遊标FOR循環語句自動打開遊标，并提取第一行遊标資料，當程式處理完目前所提取的資料而進入下一次循環時，遊标FOR循環語句自動提取下一行資料供程式處理，當提取完結果集合中的所有資料行後結束循環，并自動關閉遊标。
FOR index_variable IN cursor_name[(value[, value]…)] LOOP
-- 遊标資料處理代碼
其中：
index_variable為遊标FOR 循環語句隐含聲明的索引變量，該變量為記錄變量，其結構與遊标查詢語句傳回的結構集合的結構相同。在程式中可以通過引用該索引記錄變量元素來讀取所提取的遊标資料，index_variable中各元素的名稱與遊标查詢語句選擇清單中所制定的列名相同。如果在遊标查詢語句的選擇清單中存在計算列，則必須為這些計算列指定别名後才能通過遊标FOR 循環語句中的索引變量來通路這些列資料。
注：不要在程式中對遊标進行人工操作；不要在程式中定義用于控制FOR循環的記錄。
例8：
CURSOR c_sal IS SELECT employee_id, first_name || last_name ename, salary
FROM employees ;
--隐含打開遊标
FOR v_sal IN c_sal LOOP
--隐含執行一個FETCH語句
DBMS_OUTPUT.PUT_LINE(to_char(v_sal.employee_id)||'---'|| v_sal.ename||'---'||to_char(v_sal.salary)) ;
--隐含監測c_sal%NOTFOUND
--隐含關閉遊标
例9：當所聲明的遊标帶有參數時，通過遊标FOR 循環語句為遊标傳遞參數。
CURSOR c_cursor(dept_no NUMBER DEFAULT 10)
IS
SELECT department_name, location_id FROM departments WHERE department_id <= dept_no;
DBMS_OUTPUT.PUT_LINE('當dept_no參數值為30：');
FOR c1_rec IN c_cursor(30) LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
DBMS_OUTPUT.PUT_LINE(CHR(10)||'使用預設的dept_no參數值10：');
FOR c1_rec IN c_cursor LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
例10：PL/SQL還允許在遊标FOR循環語句中使用子查詢來實作遊标的功能。
FOR c1_rec IN(SELECT department_name, location_id FROM departments) LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
顯式遊标主要是用于對查詢語句的處理，尤其是在查詢結果為多條記錄的情況下；而對于非查詢語句，如修改、删除操作，則由ORACLE 系統自動地為這些操作設定遊标并建立其工作區，這些由系統隐含建立的遊标稱為隐式遊标，隐式遊标的名字為SQL，這是由ORACLE 系統定義的。對于隐式遊标的操作，如定義、打開、取值及關閉操作，都由ORACLE 系統自動地完成，無需使用者進行處理。使用者隻能通過隐式遊标的相關屬性，來完成相應的操作。在隐式遊标的工作區中，所存放的資料是與使用者自定義的顯示遊标無關的、最新處理的一條SQL 語句所包含的資料。
格式調用為： SQL%
注：INSERT, UPDATE, DELETE, SELECT 語句中不必明确定義遊标。
隐式遊标屬性
屬性
值
SELECT
INSERT
UPDATE
DELETE
SQL%ISOPEN
FALSE
SQL%FOUND
TRUE
有結果
成功
沒結果
失敗
SQL%NOTFUOND
SQL%NOTFOUND
SQL%ROWCOUNT
傳回行數，隻為1
插入的行數
修改的行數
删除的行數
例11: 删除EMPLOYEES表中某部門的所有員工，如果該部門中已沒有員工，則在DEPARTMENT表中删除該部門。
V_deptno department_id%TYPE :=&p_deptno;
DELETE FROM employees WHERE department_id=v_deptno;
IF SQL%NOTFOUND THEN
DELETE FROM departments WHERE department_id=v_deptno;
END IF;
例12: 通過隐式遊标SQL的%ROWCOUNT屬性來了解修改了多少行。
v_rows NUMBER;
--更新資料
UPDATE employees SET salary = 30000
WHERE department_id = 90 AND job_id = 'AD_VP';
--擷取預設遊标的屬性值
v_rows := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'個雇員的工資');
--回退更新，以便使資料庫的資料保持原樣
ROLLBACK;
SELECT … INTO 語句觸發 NO_DATA_FOUND；
當一個顯式遊标的WHERE子句未找到時觸發%NOTFOUND；
遊标修改和删除操作是指在遊标定位下，修改或删除表中指定的資料行。這時，要求遊标查詢語句中必須使用FOR UPDATE選項，以便在打開遊标時鎖定遊标結果集合在表中對應資料行的所有列和部分列。
為了對正在處理(查詢)的行不被另外的使用者改動，ORACLE 提供一個 FOR UPDATE 子句來對所選擇的行進行鎖住。該需求迫使ORACLE鎖定遊标結果集合的行，可以防止其他事務處理更新或删除相同的行，直到您的事務處理送出或回退為止。
文法：
SELECT column_list FROM table_list 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子句，修改或删除遊标結果集合目前行對應的資料庫表中的資料行。
例13：從EMPLOYEES表中查詢某部門的員工情況，将其工資最低定為 1500；
DECLARE
V_deptno employees.department_id%TYPE :=&p_deptno;
CURSOR emp_cursor
IS
SELECT employees.employee_id, employees.salary
FROM employees WHERE employees.department_id=v_deptno
FOR UPDATE NOWAIT;
FOR emp_record IN emp_cursor LOOP
IF emp_record.salary < 1500 THEN
UPDATE employees SET salary=1500
WHERE CURRENT OF emp_cursor;
-- COMMIT;
例14：将EMPLOYEES表中部門編碼為90、崗位為AD_VP的雇員的工資都更新為2000元；
v_emp_record employees%ROWTYPE;
CURSOR c1
SELECT * FROM employees FOR UPDATE;
OPEN c1;
FETCH c1 INTO v_emp_record;
EXIT WHEN c1%NOTFOUND;
IF v_emp_record.department_id = 90 AND
v_emp_record.job_id = 'AD_VP'
THEN
UPDATE employees SET salary = 20000
WHERE CURRENT OF c1; --更新目前遊标行對應的資料行
COMMIT; --送出已經修改的資料
CLOSE c1;
與遊标一樣，遊标變量也是一個指向多行查詢結果集合中目前資料行的指針。但與遊标不同的是，遊标變量是動态的，而遊标是靜态的。遊标隻能與指定的查詢相連，即固定指向一個查詢的記憶體處理區域，而遊标變量則可與不同的查詢語句相連，它可以指向不同查詢語句的記憶體處理區域（但不能同時指向多個記憶體處理區域，在某一時刻隻能與一個查詢語句相連），隻要這些查詢語句的傳回類型相容即可。
遊标變量為一個指針，它屬于參照類型，是以在聲明遊标變量類型之前必須先定義遊标變量類型。在PL/SQL中，可以在塊、子程式和包的聲明區域内定義遊标變量類型。
文法格式為：
TYPE ref_type_name IS REF CURSOR
[ RETURN return_type];
其中：ref_type_name為新定義的遊标變量類型名稱；
return_type 為遊标變量的傳回值類型，它必須為記錄變量。
在定義遊标變量類型時，可以采用強類型定義和弱類型定義兩種。強類型定義必須指定遊标變量的傳回值類型，而弱類型定義則不說明傳回值類型。
聲明一個遊标變量的兩個步驟：
步驟一：定義一個REF CURSOU資料類型，如：
TYPE ref_cursor_type IS REF CURSOR;
步驟二：聲明一個該資料類型的遊标變量，如：
cv_ref REF_CURSOR_TYPE;
例：建立兩個強類型定義遊标變量和一個弱類型遊标變量：
TYPE deptrecord IS RECORD(
Deptno departments.department_id%TYPE,
Dname departments.department_name%TYPE,
Loc departments.location_id%TYPE
);
TYPE deptcurtype IS REF CURSOR RETURN departments%ROWTYPE;
TYPE deptcurtyp1 IS REF CURSOR RETURN deptrecord;
TYPE curtype IS REF CURSOR;
Dept_c1 deptcurtype;
Dept_c2 deptcurtyp1;
Cv curtype;
與遊标一樣，遊标變量操作也包括打開、提取和關閉三個步驟。
1． 打開遊标變量
打開遊标變量時使用的是OPEN…FOR 語句。格式為：
OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;
其中：cursor_variable_name為遊标變量，host_cursor_variable_name為PL/SQL主機環境（如OCI: ORACLE Call Interface，Pro*c 程式等）中聲明的遊标變量。
OPEN…FOR 語句可以在關閉目前的遊标變量之前重新打開遊标變量，而不會導緻CURSOR_ALREAD_OPEN異常錯誤。新打開遊标變量時，前一個查詢的記憶體處理區将被釋放。
2． 提取遊标變量資料
使用FETCH語句提取遊标變量結果集合中的資料。格式為：
FETCH {cursor_variable_name | :host_cursor_variable_name}
INTO {variable [, variable]…| record_variable};
其中：cursor_variable_name和host_cursor_variable_name分别為遊标變量和宿主遊标變量名稱；variable和record_variable分别為普通變量和記錄變量名稱。
3． 關閉遊标變量
CLOSE語句關閉遊标變量，格式為：
CLOSE {cursor_variable_name | :host_cursor_variable_name}
其中：cursor_variable_name和host_cursor_variable_name分别為遊标變量和宿主遊标變量名稱，如果應用程式試圖關閉一個未打開的遊标變量，則将導緻INVALID_CURSOR異常錯誤。
例15：強類型參照遊标變量類型
TYPE emp_job_rec IS RECORD(
Employee_id employees.employee_id%TYPE,
Employee_name employees.first_name%TYPE,
Job_title employees.job_id%TYPE
TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_rec;
Emp_refcur emp_job_refcur_type ;
Emp_job emp_job_rec;
OPEN emp_refcur FOR
SELECT employees.employee_id, employees.first_name||employees.last_name, employees.job_id
FROM employees
ORDER BY employees.department_id;
FETCH emp_refcur INTO emp_job;
WHILE emp_refcur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job.job_title);
例16：弱類型參照遊标變量類型
PROMPT
PROMPT 'What table would you like to see?'
ACCEPT tab PROMPT '(D)epartment, or (E)mployees:'
Type refcur_t IS REF CURSOR;
Refcur refcur_t;
TYPE sample_rec_type IS RECORD (
Id number,
Description VARCHAR2 (30)
sample sample_rec_type;
selection varchar2(1) := UPPER (SUBSTR ('&tab', 1, 1));
IF selection='D' THEN
OPEN refcur FOR
SELECT departments.department_id, departments.department_name FROM departments;
DBMS_OUTPUT.PUT_LINE('Department data');
ELSIF selection='E' THEN
SELECT employees.employee_id, employees.first_name||' is a '||employees.job_id FROM employees;
DBMS_OUTPUT.PUT_LINE('Employee data');
ELSE
DBMS_OUTPUT.PUT_LINE('Please enter ''D'' or ''E''');
RETURN;
DBMS_OUTPUT.PUT_LINE('----------------------');
FETCH refcur INTO sample;
WHILE refcur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(sample.id||': '||sample.description);
FETCH refcur INTO sample;
CLOSE refcur;
例17：使用遊标變量（沒有RETURN子句）
--定義一個遊标資料類型
TYPE emp_cursor_type IS REF CURSOR;
--聲明一個遊标變量
c1 EMP_CURSOR_TYPE;
--聲明兩個記錄變量
v_reg_record regions%ROWTYPE;
OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20;
DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇傭日期是'
--将同一個遊标變量對應到另一個SELECT語句
OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1，2）;
FETCH c1 INTO v_reg_record;
DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示'
||v_reg_record.region_name);
例18：使用遊标變量（有RETURN子句）
--定義一個與employees表中的這幾個列相同的記錄資料類型
h_date employees.hire_date%TYPE,
j_id employees.job_id%TYPE);
--聲明一個該記錄資料類型的記錄變量
TYPE emp_cursor_type IS REF CURSOR
RETURN EMP_RECORD_TYPE;
OPEN c1 FOR SELECT first_name, hire_date, job_id
FROM employees WHERE department_id = 20;
DBMS_OUTPUT.PUT_LINE('雇員名稱：'||v_emp_record.f_name
||' 雇傭日期：'||v_emp_record.h_date
||' 崗位：'||v_emp_record.j_id);
本文轉自yonghu86 51CTO部落格，原文連結：http://blog.51cto.com/yonghu/1321355，如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)