|
本篇主要內(nèi)容如下： 4.1 游標概念 4.1.1 處理顯式游標 4.1.2 處理隱式游標 4.1.3 關于 NO_DATA_FOUND 和 %NOTFOUND的區(qū)別 4.1.4 使用游標更新和刪除數(shù)據(jù) 4.2 游標變量 4.2.1 聲明游標變量 4.2.2 游標變量操作
游標重點： 1）游標用于刪除和更新時的語句 cursor cursor_name is ............. for update nowait if ........ then delete ....或者是update某個表的語句 where current of cursor_name
游標的使用在 PL/SQL 程序中，對于處理多行記錄的事務經(jīng)常使用游標來實現(xiàn),。 4.1 游標概念在PL/SQL塊中執(zhí)行SELECT、INSERT,、DELETE和UPDATE語句時,，ORACLE會在內(nèi)存中為其分配上下文區(qū)（Context Area）,，即緩沖區(qū)。游標是指向該區(qū)的一個指針,，或是命名一個工作區(qū)（Work Area）,，或是一種結構化數(shù)據(jù)類型。它為應用等量齊觀提供了一種對具有多行數(shù)據(jù)查詢結果集中的每一行數(shù)據(jù)分別進行單獨處理的方法,，是設計嵌入式SQL語句的應用程序的常用編程方式,。 在每個用戶會話中，可以同時打開多個游標,，其數(shù)量由數(shù)據(jù)庫初始化參數(shù)文件中的OPEN_CURSORS參數(shù)定義,。 對于不同的SQL語句，游標的使用情況不同：
4.1.1 處理顯式游標1. 顯式游標處理 顯式游標處理需四個 PL/SQL步驟: l 定義/聲明游標：就是定義一個游標名,，以及與其相對應的SELECT 語句,。 格式：
CURSOR cursor_name[(parameter[, parameter]…)]
[RETURN datatype] IS select_statement;
游標參數(shù)只能為輸入?yún)?shù)，其格式為：
parameter_name [IN] datatype [{:= | DEFAULT} expression]
在指定數(shù)據(jù)類型時,，不能使用長度約束,。如NUMBER(4),CHAR(10) 等都是錯誤的。 [RETURN datatype]是可選的,，表示游標返回數(shù)據(jù)的數(shù)據(jù),。如果選擇，則應該嚴格與select_statement中的選擇列表在次序和數(shù)據(jù)類型上匹配,。一般是記錄數(shù)據(jù)類型或帶“%ROWTYPE”的數(shù)據(jù),。 l 打開游標：就是執(zhí)行游標所對應的SELECT 語句，將其查詢結果放入工作區(qū),，并且指針指向工作區(qū)的首部,，標識游標結果集合。如果游標查詢語句中帶有FOR UPDATE選項,，OPEN 語句還將鎖定數(shù)據(jù)庫表中游標結果集合對應的數(shù)據(jù)行,。 格式：
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];
在向游標傳遞參數(shù)時，可以使用與函數(shù)參數(shù)相同的傳值方法,，即位置表示法和名稱表示法,。PL/SQL 程序不能用OPEN 語句重復打開一個游標。 l 提取游標數(shù)據(jù)：就是檢索結果集合中的數(shù)據(jù)行,，放入指定的輸出變量中,。 格式：
FETCH cursor_name INTO {variable_list | record_variable };
執(zhí)行FETCH語句時，每次返回一個數(shù)據(jù)行,，然后自動將游標移動指向下一個數(shù)據(jù)行,。當檢索到最后一行數(shù)據(jù)時,，如果再次執(zhí)行FETCH語句，將操作失敗,，并將游標屬性%NOTFOUND置為TRUE,。所以每次執(zhí)行完FETCH語句后，檢查游標屬性%NOTFOUND就可以判斷FETCH語句是否執(zhí)行成功并返回一個數(shù)據(jù)行,，以便確定是否給對應的變量賦了值,。 l 對該記錄進行處理； l 繼續(xù)處理,，直到活動集合中沒有記錄,； l 關閉游標：當提取和處理完游標結果集合數(shù)據(jù)后，應及時關閉游標,，以釋放該游標所占用的系統(tǒng)資源,，并使該游標的工作區(qū)變成無效，不能再使用FETCH 語句取其中數(shù)據(jù),。關閉后的游標可以使用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. 游標參數(shù)的傳遞方法,。
DECLARE
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; BEGIN 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; DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc); END LOOP; CLOSE c2; OPEN c3(dept_no =>20); LOOP FETCH c3 INTO deptrec; EXIT WHEN c3%NOTFOUND; DBMS_OUTPUT.PUT_LINE(deptrec.DEPARTMENT_ID||'---'||deptrec.DEPARTMENT_NAME||'---'||deptrec.LOCATION_ID); END LOOP; CLOSE c3; END;
2.游標屬性 Cursor_name%FOUND 布爾型屬性，當最近一次提取游標操作FETCH成功則為 TRUE,否則為FALSE,； Cursor_name%NOTFOUND 布爾型屬性,，與%FOUND相反； Cursor_name%ISOPEN 布爾型屬性,，當游標已打開時返回 TRUE,； Cursor_name%ROWCOUNT 數(shù)字型屬性，返回已從游標中讀取的記錄數(shù),。 例3：給工資低于1200 的員工增加工資50,。
DECLARE
v_empno 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_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('記錄數(shù):'|| c_cursor %ROWCOUNT); END LOOP; CLOSE c_cursor; END;
例4：沒有參數(shù)且沒有返回值的游標。
DECLARE
v_f_name employees.first_name%TYPE; v_j_id employees.job_id%TYPE; CURSOR c1 --聲明游標,沒有參數(shù)沒有返回值 IS SELECT first_name, job_id FROM employees WHERE department_id = 20; BEGIN OPEN c1; --打開游標 LOOP 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('已經(jīng)處理完結果集了'); EXIT; END IF; END LOOP; CLOSE c1; --關閉游標 END;
例5：有參數(shù)且沒有返回值的游標,。
DECLARE
v_f_name employees.first_name%TYPE; v_h_date employees.hire_date%TYPE; CURSOR c2(dept_id NUMBER, j_id VARCHAR2) --聲明游標,有參數(shù)沒有返回值 IS SELECT first_name, hire_date FROM employees WHERE department_id = dept_id AND job_id = j_id; BEGIN OPEN c2(90, 'AD_VP'); --打開游標,傳遞參數(shù)值 LOOP FETCH c2 INTO v_f_name, v_h_date; --提取游標 IF c2%FOUND THEN DBMS_OUTPUT.PUT_LINE(v_f_name||'的雇傭日期是'||v_h_date); ELSE DBMS_OUTPUT.PUT_LINE('已經(jīng)處理完結果集了'); EXIT; END IF; END LOOP; CLOSE c2; --關閉游標 END;
例6：有參數(shù)且有返回值的游標,。
DECLARE
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) --聲明游標,有參數(shù)有返回值 RETURN EMP_RECORD_TYPE IS SELECT first_name, hire_date FROM employees WHERE department_id = dept_id AND job_id = j_id; BEGIN OPEN c3(j_id => 'AD_VP', dept_id => 90); --打開游標,傳遞參數(shù)值 LOOP FETCH c3 INTO v_emp_record; --提取游標 IF c3%FOUND THEN DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇傭日期是' ||v_emp_record.h_date); ELSE DBMS_OUTPUT.PUT_LINE('已經(jīng)處理完結果集了'); EXIT; END IF; END LOOP; CLOSE c3; --關閉游標 END;
例7：基于游標定義記錄變量。
DECLARE
CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --聲明游標,有參數(shù)沒有返回值 IS SELECT first_name f_name, hire_date FROM employees WHERE department_id = dept_id AND job_id = j_id; --基于游標定義記錄變量,，比聲明記錄類型變量要方便,，不容易出錯 v_emp_record c4%ROWTYPE; BEGIN OPEN c4(90, 'AD_VP'); --打開游標,傳遞參數(shù)值 LOOP FETCH c4 INTO v_emp_record; --提取游標 IF c4%FOUND THEN DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇傭日期是' ||v_emp_record.hire_date); ELSE DBMS_OUTPUT.PUT_LINE('已經(jīng)處理完結果集了'); EXIT; END IF; END LOOP; CLOSE c4; --關閉游標 END;
3. 游標的FOR循環(huán) PL/SQL語言提供了游標FOR循環(huán)語句，自動執(zhí)行游標的OPEN,、FETCH,、CLOSE語句和循環(huán)語句的功能；當進入循環(huán)時,，游標FOR循環(huán)語句自動打開游標,，并提取第一行游標數(shù)據(jù),，當程序處理完當前所提取的數(shù)據(jù)而進入下一次循環(huán)時，游標FOR循環(huán)語句自動提取下一行數(shù)據(jù)供程序處理,，當提取完結果集合中的所有數(shù)據(jù)行后結束循環(huán),，并自動關閉游標。 格式：
FOR index_variable IN cursor_name[(value[, value]…)] LOOP
-- 游標數(shù)據(jù)處理代碼 END LOOP;
其中： index_variable為游標FOR 循環(huán)語句隱含聲明的索引變量,，該變量為記錄變量，其結構與游標查詢語句返回的結構集合的結構相同,。在程序中可以通過引用該索引記錄變量元素來讀取所提取的游標數(shù)據(jù),，index_variable中各元素的名稱與游標查詢語句選擇列表中所制定的列名相同。如果在游標查詢語句的選擇列表中存在計算列,，則必須為這些計算列指定別名后才能通過游標FOR 循環(huán)語句中的索引變量來訪問這些列數(shù)據(jù),。 注：不要在程序中對游標進行人工操作；不要在程序中定義用于控制FOR循環(huán)的記錄,。 例8： DECLARE
CURSOR c_sal IS SELECT employee_id, first_name || last_name ename, salary FROM employees ; BEGIN --隱含打開游標 FOR v_sal IN c_sal LOOP --隱含執(zhí)行一個FETCH語句 DBMS_OUTPUT.PUT_LINE(to_char(v_sal.employee_id)||'---'|| v_sal.ename||'---'||to_char(v_sal.salary)) ; --隱含監(jiān)測c_sal%NOTFOUND END LOOP; --隱含關閉游標 END;
例9：當所聲明的游標帶有參數(shù)時,，通過游標FOR 循環(huán)語句為游標傳遞參數(shù)。
DECLARE
CURSOR c_cursor(dept_no NUMBER DEFAULT 10) IS SELECT department_name, location_id FROM departments WHERE department_id <= dept_no; BEGIN DBMS_OUTPUT.PUT_LINE('當dept_no參數(shù)值為30：'); FOR c1_rec IN c_cursor(30) LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id); END LOOP; DBMS_OUTPUT.PUT_LINE(CHR(10)||'使用默認的dept_no參數(shù)值10：'); FOR c1_rec IN c_cursor LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id); END LOOP; END;
例10：PL/SQL還允許在游標FOR循環(huán)語句中使用子查詢來實現(xiàn)游標的功能,。
BEGIN
FOR c1_rec IN(SELECT department_name, location_id FROM departments) LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id); END LOOP; END;
顯式游標主要是用于對查詢語句的處理,，尤其是在查詢結果為多條記錄的情況下；而對于非查詢語句,，如修改,、刪除操作，則由ORACLE 系統(tǒng)自動地為這些操作設置游標并創(chuàng)建其工作區(qū),，這些由系統(tǒng)隱含創(chuàng)建的游標稱為隱式游標,，隱式游標的名字為SQL，這是由ORACLE 系統(tǒng)定義的,。對于隱式游標的操作,，如定義、打開,、取值及關閉操作,，都由ORACLE 系統(tǒng)自動地完成，無需用戶進行處理,。用戶只能通過隱式游標的相關屬性,，來完成相應的操作。在隱式游標的工作區(qū)中,，所存放的數(shù)據(jù)是與用戶自定義的顯示游標無關的,、最新處理的一條SQL 語句所包含的數(shù)據(jù)。 格式調(diào)用為： SQL% 注：INSERT, UPDATE, DELETE, SELECT 語句中不必明確定義游標,。 隱式游標屬性
例11: 刪除EMPLOYEES表中某部門的所有員工,，如果該部門中已沒有員工，則在DEPARTMENT表中刪除該部門,。
DECLARE
V_deptno department_id%TYPE :=&p_deptno; BEGIN DELETE FROM employees WHERE department_id=v_deptno; IF SQL%NOTFOUND THEN DELETE FROM departments WHERE department_id=v_deptno; END IF; END;
例12: 通過隱式游標SQL的%ROWCOUNT屬性來了解修改了多少行,。 DECLARE
v_rows NUMBER; BEGIN --更新數(shù)據(jù) UPDATE employees SET salary = 30000 WHERE department_id = 90 AND job_id = 'AD_VP'; --獲取默認游標的屬性值 v_rows := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'個雇員的工資'); --回退更新，以便使數(shù)據(jù)庫的數(shù)據(jù)保持原樣 ROLLBACK; END;
4.1.3 關于 NO_DATA_FOUND 和 %NOTFOUND的區(qū)別SELECT … INTO 語句觸發(fā) NO_DATA_FOUND,； 當一個顯式游標的WHERE子句未找到時觸發(fā)%NOTFOUND,； 當UPDATE或DELETE 語句的WHERE 子句未找到時觸發(fā) SQL%NOTFOUND；在提取循環(huán)中要用 %NOTFOUND 或%FOUND 來確定循環(huán)的退出條件,，不要用 NO_DATA_FOUND.4.1.4 使用游標更新和刪除數(shù)據(jù) 游標修改和刪除操作是指在游標定位下,，修改或刪除表中指定的數(shù)據(jù)行。這時,，要求游標查詢語句中必須使用FOR UPDATE選項,，以便在打開游標時鎖定游標結果集合在表中對應數(shù)據(jù)行的所有列和部分列。 為了對正在處理(查詢)的行不被另外的用戶改動,，ORACLE 提供一個 FOR UPDATE 子句來對所選擇的行進行鎖住,。該需求迫使ORACLE鎖定游標結果集合的行，可以防止其他事務處理更新或刪除相同的行,，直到您的事務處理提交或回退為止,。 語法：
SELECT column_list FROM table_list FOR UPDATE [OF column[, column]…] [NOWAIT]
如果另一個會話已對活動集中的行加了鎖，那么SELECT FOR UPDATE操作一直等待到其它的會話釋放這些鎖后才繼續(xù)自己的操作,，對于這種情況,，當加上NOWAIT子句時，如果這些行真的被另一個會話鎖定,，則OPEN立即返回并給出： ORA-0054 ：resource busy and acquire with nowait specified. 如果使用 FOR UPDATE 聲明游標,，則可在DELETE和UPDATE 語句中使用 WHERE CURRENT OF cursor_name子句，修改或刪除游標結果集合當前行對應的數(shù)據(jù)庫表中的數(shù)據(jù)行,。 例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; BEGIN FOR emp_record IN emp_cursor LOOP IF emp_record.salary < 1500 THEN UPDATE employees SET salary=1500 WHERE CURRENT OF emp_cursor; END IF; END LOOP; -- COMMIT; END;
例14：將EMPLOYEES表中部門編碼為90,、崗位為AD_VP的雇員的工資都更新為2000元,；
DECLARE
v_emp_record employees%ROWTYPE; CURSOR c1 IS SELECT * FROM employees FOR UPDATE; BEGIN OPEN c1; LOOP 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; --更新當前游標行對應的數(shù)據(jù)行 END IF; END LOOP; COMMIT; --提交已經(jīng)修改的數(shù)據(jù) CLOSE c1; END; 4.2 游標變量與 游標一樣，游標變量也是一個指向多行查詢結果集合中當前數(shù)據(jù)行的指針,。但與游標不同的是,，游標變量是動態(tài)的，而游標是靜態(tài)的,。游標只能與指定的查詢相連,， 即固定指向一個查詢的內(nèi)存處理區(qū)域，而游標變量則可與不同的查詢語句相連,，它可以指向不同查詢語句的內(nèi)存處理區(qū)域（但不能同時指向多個內(nèi)存處理區(qū)域,，在某 一時刻只能與一個查詢語句相連）,，只要這些查詢語句的返回類型兼容即可。 4.2.1 聲明游標變量游標變量為一個指針,，它屬于參照類型,，所以在聲明游標變量類型之前必須先定義游標變量類型。在PL/SQL中,，可以在塊,、子程序和包的聲明區(qū)域內(nèi)定義游標變量類型。 語法格式為：
TYPE ref_type_name IS REF CURSOR
[ RETURN return_type];
其中：ref_type_name為新定義的游標變量類型名稱,； return_type 為游標變量的返回值類型,，它必須為記錄變量。 在定義游標變量類型時,，可以采用強類型定義和弱類型定義兩種。強類型定義必須指定游標變量的返回值類型,，而弱類型定義則不說明返回值類型,。 聲明一個游標變量的兩個步驟： 步驟一：定義一個REF CURSOU數(shù)據(jù)類型，如： TYPE ref_cursor_type IS REF CURSOR; 步驟二：聲明一個該數(shù)據(jù)類型的游標變量,，如： cv_ref REF_CURSOR_TYPE; 例：創(chuàng)建兩個強類型定義游標變量和一個弱類型游標變量：
DECLARE
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主機環(huán)境（如OCI: ORACLE Call Interface,，Pro*c 程序等）中聲明的游標變量,。 OPEN…FOR 語句可以在關閉當前的游標變量之前重新打開游標變量，而不會導致CURSOR_ALREAD_OPEN異常錯誤,。新打開游標變量時,，前一個查詢的內(nèi)存處理區(qū)將被釋放。 2． 提取游標變量數(shù)據(jù) 使用FETCH語句提取游標變量結果集合中的數(shù)據(jù),。格式為：
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：強類型參照游標變量類型
DECLARE
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; BEGIN 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); FETCH emp_refcur INTO emp_job; END LOOP; END; 例16：弱類型參照游標變量類型
PROMPT
PROMPT 'What table would you like to see?' ACCEPT tab PROMPT '(D)epartment, or (E)mployees:' DECLARE 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)); BEGIN 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 OPEN refcur FOR 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; END IF; DBMS_OUTPUT.PUT_LINE('----------------------'); FETCH refcur INTO sample; WHILE refcur%FOUND LOOP DBMS_OUTPUT.PUT_LINE(sample.id||': '||sample.description); FETCH refcur INTO sample; END LOOP; CLOSE refcur; END; 例17：使用游標變量（沒有RETURN子句）
DECLARE
--定義一個游標數(shù)據(jù)類型 TYPE emp_cursor_type IS REF CURSOR; --聲明一個游標變量 c1 EMP_CURSOR_TYPE; --聲明兩個記錄變量 v_emp_record employees%ROWTYPE; v_reg_record regions%ROWTYPE; BEGIN OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20; LOOP FETCH c1 INTO v_emp_record; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇傭日期是' ||v_emp_record.hire_date); END LOOP; --將同一個游標變量對應到另一個SELECT語句 OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1,，2）; LOOP FETCH c1 INTO v_reg_record; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示' ||v_reg_record.region_name); END LOOP; CLOSE c1; END;
例18：使用游標變量（有RETURN子句）
DECLARE
--定義一個與employees表中的這幾個列相同的記錄數(shù)據(jù)類型 TYPE emp_record_type IS RECORD( f_name employees.first_name%TYPE, h_date employees.hire_date%TYPE, j_id employees.job_id%TYPE); --聲明一個該記錄數(shù)據(jù)類型的記錄變量 v_emp_record EMP_RECORD_TYPE; --定義一個游標數(shù)據(jù)類型 TYPE emp_cursor_type IS REF CURSOR RETURN EMP_RECORD_TYPE; --聲明一個游標變量 c1 EMP_CURSOR_TYPE; BEGIN OPEN c1 FOR SELECT first_name, hire_date, job_id FROM employees WHERE department_id = 20; LOOP FETCH c1 INTO v_emp_record; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE('雇員名稱：'||v_emp_record.f_name ||' 雇傭日期：'||v_emp_record.h_date ||' 崗位：'||v_emp_record.j_id); END LOOP; CLOSE c1; END;
© 2011 EricHu |
|
|
