|
在每個(gè)用戶(hù)會(huì)話(huà)中，可以同時(shí)打開(kāi)多個(gè)游標(biāo)，其數(shù)量由數(shù)據(jù)庫(kù)初始化參數(shù)文件中的OPEN_CURSORS參數(shù)定義。 對(duì)于不同的SQL語(yǔ)句，游標(biāo)的使用情況不同：SQL語(yǔ)句 | 游標(biāo) | 非查詢(xún)語(yǔ)句 | 隱式的 | 結(jié)果是單行的查詢(xún)語(yǔ)句 | 隱式的或顯示的 | 結(jié)果是多行的查詢(xún)語(yǔ)句 | 顯示的 |
處理顯式游標(biāo)例：
- DECLARE
- CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --1、聲明游標(biāo),有參數(shù)沒(méi)有返回值
- IS
- SELECT first_name f_name, hire_date FROM employees
- WHERE department_id = dept_id AND job_id = j_id;
-
- --基于游標(biāo)定義記錄變量，比聲明記錄類(lèi)型變量要方便，不容易出錯(cuò)
- v_emp_record c4%ROWTYPE;
- BEGIN
- OPEN c4(90, 'AD_VP'); --2、打開(kāi)游標(biāo),傳遞參數(shù)值
- LOOP
- FETCH c4 INTO v_emp_record; --3、提取游標(biāo)fetch into
- 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)處理完結(jié)果集了');
- EXIT;
- END IF;
- END LOOP;
- CLOSE c4; --4、關(guān)閉游標(biāo)
- END;
退出LOOP或者用： EXIT WHEN c4%NOTFOUND;
游標(biāo)屬性：
Cursor_name%FOUND 布爾型屬性，當(dāng)最近一次提取游標(biāo)操作FETCH成功則為 TRUE,否則為FALSE； Cursor_name%NOTFOUND 布爾型屬性，與%FOUND相反；——注意區(qū)別于DO_DATA_FOUND(select into拋出異常) Cursor_name%ISOPEN 布爾型屬性，當(dāng)游標(biāo)已打開(kāi)時(shí)返回 TRUE； Cursor_name%ROWCOUNT 數(shù)字型屬性，返回已從游標(biāo)中讀取的記錄數(shù)。
游標(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)查詢(xún)語(yǔ)句返回的結(jié)構(gòu)集合的結(jié)構(gòu)相同。在程序中可以通過(guò)引用該索引記錄變量元素來(lái)讀取所提取的游標(biāo)數(shù)據(jù)，index_variable中各元素的名稱(chēng)與游標(biāo)查詢(xún)語(yǔ)句選擇列表中所制定的列名相同。如果在游標(biāo)查詢(xún)語(yǔ)句的選擇列表中存在計(jì)算列，則必須為這些計(jì)算列指定別名后才能通過(guò)游標(biāo)FOR 循環(huán)語(yǔ)句中的索引變量來(lái)訪問(wèn)這些列數(shù)據(jù)。 例： - DECLARE
- CURSOR c_cursor(dept_no NUMBER DEFAULT 10)
- IS
- SELECT department_name, location_id FROM departments WHERE department_id <= dept_no;
- BEGIN
- --當(dāng)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;
-
- --使用默認(rèn)的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;
或者可以在游標(biāo)FOR循環(huán)語(yǔ)句中使用子查詢(xú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;
處理隱式游標(biāo)顯式游標(biāo)主要是用于對(duì)查詢(xún)語(yǔ)句的處理，尤其是在查詢(xún)結(jié)果為多條記錄的情況下； 而對(duì)于非查詢(xún)語(yǔ)句，如修改、刪除操作，則由ORACLE 系統(tǒng)自動(dòng)地為這些操作設(shè)置游標(biāo)并創(chuàng)建其工作區(qū)，隱式游標(biāo)的名字為SQL，這是由ORACLE 系統(tǒng)定義的。 對(duì)于隱式游標(biāo)的操作，如定義、打開(kāi)、取值及關(guān)閉操作，都由ORACLE 系統(tǒng)自動(dòng)地完成，無(wú)需用戶(hù)進(jìn)行處理。用戶(hù)只能通過(guò)隱式游標(biāo)的相關(guān)屬性，來(lái)完成相應(yīng)的操作。在隱式游標(biāo)的工作區(qū)中，所存放的數(shù)據(jù)是與用戶(hù)自定義的顯示游標(biāo)無(wú)關(guān)的、最新處理的一條SQL 語(yǔ)句所包含的數(shù)據(jù)。 格式調(diào)用為： SQL%
- DECLARE
- v_rows NUMBER;
- BEGIN
- --更新數(shù)據(jù)
- UPDATE employees SET salary = 30000
- WHERE department_id = 90 AND job_id = 'AD_VP';
- --獲取默認(rèn)游標(biāo)的屬性值
- v_rows := SQL%ROWCOUNT;
- DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'個(gè)雇員的工資');
-
- --刪除指定雇員；如果部門(mén)中沒(méi)有雇員，則刪除部門(mén)
- DELETE FROM employees WHERE department_id=v_deptno;
- IF SQL%NOTFOUND THEN
- DELETE FROM departments WHERE department_id=v_deptno;
- END IF;
- END;
更新或刪除當(dāng)前游標(biāo)數(shù)據(jù)游標(biāo)查詢(xún)語(yǔ)句中必須使用FOR UPDATE選項(xiàng)，以便在打開(kāi)游標(biāo)時(shí)鎖定游標(biāo)結(jié)果集合在表中對(duì)應(yīng)數(shù)據(jù)行的所有列和部分列。 如果另一個(gè)會(huì)話(huà)已對(duì)活動(dòng)集中的行加了鎖，那么SELECT FOR UPDATE操作一直等待到其它的會(huì)話(huà)釋放這些鎖后才繼續(xù)自己的操作；對(duì)于這種情況，當(dāng)加上NOWAIT子句時(shí)，如果這些行真的被另一個(gè)會(huì)話(huà)鎖定，則OPEN立即返回并給出： ORA-0054 ：resource busy and acquire with nowait specified. - 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; --1、for update
- BEGIN
- FOR emp_record IN emp_cursor LOOP
- IF emp_record.salary < 1500 THEN
- UPDATE employees SET salary=1500
- WHERE CURRENT OF emp_cursor; --2、WHERE CURRENT OF cursor_name子句
- END IF;
- END LOOP;
- END;
動(dòng)態(tài)游標(biāo)與游標(biāo)一樣，動(dòng)態(tài)游標(biāo)（游標(biāo)變量）也是一個(gè)指向多行查詢(xún)結(jié)果集合中當(dāng)前數(shù)據(jù)行的指針。但與游標(biāo)不同的是，游標(biāo)變量是動(dòng)態(tài)的，而游標(biāo)是靜態(tài)的。 游標(biāo)只能與指定的查詢(xún)相連，即固定指向一個(gè)查詢(xún)的內(nèi)存處理區(qū)域，而游標(biāo)變量則可與不同的查詢(xún)語(yǔ)句相連，它可以指向不同查詢(xún)語(yǔ)句的內(nèi)存處理區(qū)域（但不能同時(shí)指向多個(gè)內(nèi)存處理區(qū)域，在某一時(shí)刻只能與一個(gè)查詢(xún)語(yǔ)句相連），只要這些查詢(xún)語(yǔ)句的返回類(lèi)型兼容即可。 - DECLARE
- --定義一個(gè)游標(biāo)數(shù)據(jù)類(lèi)型
- TYPE emp_cursor_type IS REF CURSOR;
- --聲明一個(gè)游標(biāo)變量
- c1 EMP_CURSOR_TYPE;
- --聲明兩個(gè)記錄變量
- 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;
- --將同一個(gè)游標(biāo)變量對(duì)應(yīng)到另一個(gè)SELECT語(yǔ)句
- 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;
|
