溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
游標概念
在PL/SQL塊中執行SELECT、INSERT、DELETE和UPDATE語句時，ORACLE會在內存中為其分配上下文區（Context Area），即緩沖區。游標是指向該區的一個指針，或是命名一個工作區（Work Area），或是一種結構化數據類型。它為應用等量齊觀提供了一種對具有多行數據查詢結果集中的每一行數據分別進行單獨處理的方法，是設計嵌入式SQL語句的應用程序的常用編程方式。
在每個用戶會話中，可以同時打開多個游標，其數量由數據庫初始化參數文件中的OPEN_CURSORS參數定義。
對于不同的SQL語句，游標的使用情況不同：
SQL語句 游標
非查詢語句 隱式的
結果是單行的查詢語句 隱式的或顯示的
結果是多行的查詢語句 顯示的
顯式游標處理
顯式游標處理需四個 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名員工的信息。
C:\Users\Administrator>sqlplus hr/hr@pdbtest
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 18 10:09:21 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> desc employees
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SQL> declare
2 cursor c_sursor
3 is select FIRST_NAME||LAST_NAME,SALARY
4 from employees
5 where rownum<11
6 v_ename employees.FIRST_NAME%type;
7 v_sal employees.FIRST_SALARY%type;
8 begin
9 open c_sursor;
10 fetch c_sursor into v_ename,v_sal;
11 while c_sursor%found loop
12 dbms_output.put_line(v_ename||'---'||to_char(v_sal));
13 fetch fetch c_sursor into v_ename,v_sal;
14 end loop;
15 close c_sursor;
16 end;
17 /
v_ename employees.FIRST_NAME%type;
ERROR at line 6:
ORA-06550: line 6, column 3:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 3, column 6:
PL/SQL: SQL Statement ignored
ORA-06550: line 13, column 11:
PLS-00103: Encountered the symbol "FETCH" when expecting one of the following:
<an identifier> <a double-quoted delimited-identifier>
<a bind variable>
ORA-06550: line 15, column 3:
PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map
SQL> list 5
5 where rownum<11
SQL> c /11/11;;
5 where rownum<11;
SQL> list 7
7 v_sal employees.FIRST_SALARY%type;
SQL> c /FIRST_SALARY/SALARY
7 v_sal employees.SALARY%type;
SQL> 13
13 fetch fetch c_sursor into v_ename,v_sal;
SQL> c/fetch fetch/fetch
13 fetch c_sursor into v_ename,v_sal;
SQL> run
1 declare
2 cursor c_sursor
3 is select FIRST_NAME||LAST_NAME,SALARY
4 from employees
5 where rownum<11;
6 v_ename employees.FIRST_NAME%type;
7 v_sal employees.SALARY%type;
8 begin
9 open c_sursor;
10 fetch c_sursor into v_ename,v_sal;
11 while c_sursor%found loop
12 dbms_output.put_line(v_ename||'---'||to_char(v_sal));
13 fetch c_sursor into v_ename,v_sal;
14 end loop;
15 close c_sursor;
16 end;
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> run
1 declare
2 cursor c_sursor
3 is select FIRST_NAME||LAST_NAME,SALARY
4 from employees
5 where rownum<11;
6 v_ename employees.FIRST_NAME%type;
7 v_sal employees.SALARY%type;
8 begin
9 open c_sursor;
10 fetch c_sursor into v_ename,v_sal;
11 while c_sursor%found loop
12 dbms_output.put_line(v_ename||'---'||to_char(v_sal));
13 fetch c_sursor into v_ename,v_sal;
14 end loop;
15 close c_sursor;
16* end;
StevenKing---24000
NeenaKochhar---17000
LexDe Haan---17000
AlexanderHunold---9000
BruceErnst---6000
DavidAustin---4800
ValliPataballa---4800
DianaLorentz---4200
NancyGreenberg---12008
DanielFaviet---9000
PL/SQL procedure successfully completed.
例2. 游標參數的傳遞方法
SQL> set serveroutput on
SQL> run
1 DECLARE
2
3 DeptRec DEPARTMENTS%ROWTYPE;
4
5 Dept_name DEPARTMENTS.DEPARTMENT_NAME%TYPE;
6
7 Dept_loc DEPARTMENTS.LOCATION_ID%TYPE;
8
9 CURSOR c1 IS
10
11 SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS
12
13 WHERE DEPARTMENT_ID <= 30;
14
15
16
17 CURSOR c2(dept_no NUMBER DEFAULT 10) IS
18
19 SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS
20
21 WHERE DEPARTMENT_ID <= dept_no;
22
23 CURSOR c3(dept_no NUMBER DEFAULT 10) IS
24
25 SELECT FROM DEPARTMENTS
26
27 WHERE DEPARTMENTS.DEPARTMENT_ID <=dept_no;
28
29 BEGIN
30
31 OPEN c1;
32
33 LOOP
34
35 FETCH c1 INTO dept_name, dept_loc;
36
37 EXIT WHEN c1%NOTFOUND;
38
39 DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc);
40
41 END LOOP;
42
43 CLOSE c1;
44
45
46
47 OPEN c2;
48
49 LOOP
50
51 FETCH c2 INTO dept_name, dept_loc;
52
53 EXIT WHEN c2%NOTFOUND;
54
55 DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc);
56
57 END LOOP;
58
59 CLOSE c2;
60
61
62
63 OPEN c3(dept_no =>20);
64
65 LOOP
66
67 FETCH c3 INTO deptrec;
68
69 EXIT WHEN c3%NOTFOUND;
70
71 DBMS_OUTPUT.PUT_LINE(deptrec.DEPARTMENT_ID||'---'||deptrec.DEPARTMENT_NAME||'---'||deptrec.LOCATION_ID);
72
73 END LOOP;
74
75 CLOSE c3;
76
77 END;
Administration---1700
Marketing---1800
Purchasing---1700
Administration---1700
10---Administration---1700
20---Marketing---1800
PL/SQL procedure successfully completed.
2.游標屬性
Cursor_name%FOUND 布爾型屬性，當最近一次提取游標操作FETCH成功則為 TRUE,否則為FALSE；
Cursor_name%NOTFOUND 布爾型屬性，與%FOUND相反；
Cursor_name%ISOPEN 布爾型屬性，當游標已打開時返回 TRUE；
Cursor_name%ROWCOUNT 數字型屬性，返回已從游標中讀取的記錄數
例3：給工資低于1200 的員工增加工資50。
SQL> DECLARE
2 v_empno EMPLOYEES.EMPLOYEE_ID%TYPE;
3 v_sal EMPLOYEES.Salary%TYPE;
4 CURSOR c_cursor IS SELECT EMPLOYEE_ID, Salary FROM EMPLOYEES;
5 BEGIN
6 OPEN c_cursor;
7 LOOP
8 FETCH c_cursor INTO v_empno, v_sal;
9 EXIT WHEN c_cursor%NOTFOUND;
10 IF v_sal<=1200 THEN
11 UPDATE EMPLOYEES SET Salary=Salary+50 WHERE EMPLOYEE_ID=v_empno;
12 DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'工資已更新!');
13 END IF;
14 DBMS_OUTPUT.PUT_LINE('記錄數:'|| c_cursor %ROWCOUNT);
15 END LOOP;
16 CLOSE c_cursor;
17 END;
18 /
記錄數:1
記錄數:2
記錄數:3
記錄數:4
記錄數:5
記錄數:6
記錄數:7
記錄數:8
記錄數:9
記錄數:10
記錄數:11
記錄數:12
記錄數:13
記錄數:14
記錄數:15
記錄數:16
記錄數:17
記錄數:18
記錄數:19
記錄數:20
記錄數:21
記錄數:22
記錄數:23
記錄數:24
記錄數:25
記錄數:26
記錄數:27
記錄數:28
記錄數:29
記錄數:30
記錄數:31
記錄數:32
記錄數:33
記錄數:34
記錄數:35
記錄數:36
記錄數:37
記錄數:38
記錄數:39
記錄數:40
記錄數:41
記錄數:42
記錄數:43
記錄數:44
記錄數:45
記錄數:46
記錄數:47
記錄數:48
記錄數:49
記錄數:50
記錄數:51
記錄數:52
記錄數:53
記錄數:54
記錄數:55
記錄數:56
記錄數:57
記錄數:58
記錄數:59
記錄數:60
記錄數:61
記錄數:62
記錄數:63
記錄數:64
記錄數:65
記錄數:66
記錄數:67
記錄數:68
記錄數:69
記錄數:70
記錄數:71
記錄數:72
記錄數:73
記錄數:74
記錄數:75
記錄數:76
記錄數:77
記錄數:78
記錄數:79
記錄數:80
記錄數:81
記錄數:82
記錄數:83
記錄數:84
記錄數:85
記錄數:86
記錄數:87
記錄數:88
記錄數:89
記錄數:90
記錄數:91
記錄數:92
記錄數:93
記錄數:94
記錄數:95
記錄數:96
記錄數:97
記錄數:98
記錄數:99
記錄數:100
記錄數:101
記錄數:102
記錄數:103
記錄數:104
記錄數:105
記錄數:106
記錄數:107
PL/SQL procedure successfully completed.
例4：沒有參數且沒有返回值的游標。
SQL> DECLARE
2 v_f_name employees.first_name%TYPE;
3 v_j_id employees.job_id%TYPE;
4 CURSOR c1 --聲明游標,沒有參數沒有返回值
5 IS
6 SELECT first_name, job_id FROM employees
7 WHERE department_id = 20;
8 BEGIN
9 OPEN c1; --打開游標
10 LOOP
11 FETCH c1 INTO v_f_name, v_j_id; --提取游標
12 IF c1%FOUND THEN
13 DBMS_OUTPUT.PUT_LINE(v_f_name||'的崗位是'||v_j_id);
14 ELSE
15 DBMS_OUTPUT.PUT_LINE('已經處理完結果集了');
16 EXIT;
17 END IF;
18 END LOOP;
19 CLOSE c1; --關閉游標
20 END;
21 /
Michael的崗位是MK_MAN
Pat的崗位是MK_REP
已經處理完結果集了
PL/SQL procedure successfully completed.
例5：有參數且沒有返回值的游標。
SQL> DECLARE
2
3 v_f_name employees.first_name%TYPE;
4
5 v_h_date employees.hire_date%TYPE;
6
7 CURSOR c2(dept_id NUMBER, j_id VARCHAR2) --聲明游標,有參數沒有返回值
8
9 IS
10
11 SELECT first_name, hire_date FROM employees
12
13 WHERE department_id = dept_id AND job_id = j_id;
14
15 BEGIN
16
17 OPEN c2(90, 'AD_VP'); --打開游標,傳遞參數值
18
19 LOOP
20
21 FETCH c2 INTO v_f_name, v_h_date; --提取游標
22
23 IF c2%FOUND THEN
24
25 DBMS_OUTPUT.PUT_LINE(v_f_name||'的雇傭日期是'||v_h_date);
26
27 ELSE
28
29 DBMS_OUTPUT.PUT_LINE('已經處理完結果集了');
30
31 EXIT;
32
33 END IF;
34
35 END LOOP;
36
37 CLOSE c2; --關閉游標
38
39 END;
40 /
Neena的雇傭日期是2005-09-21 00:00:00
Lex的雇傭日期是2001-01-13 00:00:00
已經處理完結果集了
PL/SQL procedure successfully completed.
例6：有參數且有返回值的游標。
SQL> DECLARE
2
3 TYPE emp_record_type IS RECORD(
4
5 f_name employees.first_name%TYPE,
6
7 h_date employees.hire_date%TYPE);
8
9 v_emp_record EMP_RECORD_TYPE;
10
11
12
13 CURSOR c3(dept_id NUMBER, j_id VARCHAR2) --聲明游標,有參數有返回值
14
15 RETURN EMP_RECORD_TYPE
16
17 IS
18
19 SELECT first_name, hire_date FROM employees
20
21 WHERE department_id = dept_id AND job_id = j_id;
22
23 BEGIN
24
25 OPEN c3(j_id => 'AD_VP', dept_id => 90); --打開游標,傳遞參數值
26
27 LOOP
28
29 FETCH c3 INTO v_emp_record; --提取游標
30
31 IF c3%FOUND THEN
32
33 DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇傭日期是'
34
35 ||v_emp_record.h_date);
36
37 ELSE
38
39 DBMS_OUTPUT.PUT_LINE('已經處理完結果集了');
40
41 EXIT;
42
43 END IF;
44
45 END LOOP;
46
47 CLOSE c3; --關閉游標
48
49 END;
50 /
Neena的雇傭日期是2005-09-21 00:00:00
Lex的雇傭日期是2001-01-13 00:00:00
已經處理完結果集了
PL/SQL procedure successfully completed.
例7：基于游標定義記錄變量。
SQL> DECLARE
2
3 CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --聲明游標,有參數沒有返回值
4
5 IS
6
7 SELECT first_name f_name, hire_date FROM employees
8
9 WHERE department_id = dept_id AND job_id = j_id;
10
11 --基于游標定義記錄變量，比聲明記錄類型變量要方便，不容易出錯
12
13 v_emp_record c4%ROWTYPE;
14
15 BEGIN
16
17 OPEN c4(90, 'AD_VP'); --打開游標,傳遞參數值
18
19 LOOP
20
21 FETCH c4 INTO v_emp_record; --提取游標
22
23 IF c4%FOUND THEN
24
25 DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇傭日期是'
26
27 ||v_emp_record.hire_date);
28
29 ELSE
30
31 DBMS_OUTPUT.PUT_LINE('已經處理完結果集了');
32
33 EXIT;
34
35 END IF;
36
37 END LOOP;
38
39 CLOSE c4; --關閉游標
40
41 END;
42 /
Neena的雇傭日期是2005-09-21 00:00:00
Lex的雇傭日期是2001-01-13 00:00:00
已經處理完結果集了
PL/SQL procedure successfully completed.
游標的FOR循環
PL/SQL語言提供了游標FOR循環語句，自動執行游標的OPEN、FETCH、CLOSE語句和循環語句的功能；當進入循環時，游標FOR循環語句自動打開游標，并提取第一行游標數據，當程序處理完當前所提取的數據而進入下一次循環時，游標FOR循環語句自動提取下一行數據供程序處理，當提取完結果集合中的所有數據行后結束循環，并自動關閉游標。
格式：
FOR index_variable IN cursor_name[(value[, value]…)] LOOP
-- 游標數據處理代碼
END LOOP;
其中：
index_variable為游標FOR 循環語句隱含聲明的索引變量，該變量為記錄變量，其結構與游標查詢語句返回的結構集合的結構相同。在程序中可以通過引用該索引記錄變量元素來讀取所提取的游標數據，index_variable中各元素的名稱與游標查詢語句選擇列表中所制定的列名相同。如果在游標查詢語句的選擇列表中存在計算列，則必須為這些計算列指定別名后才能通過游標FOR 循環語句中的索引變量來訪問這些列數據。
注：不要在程序中對游標進行人工操作；不要在程序中定義用于控制FOR循環的記錄。
SQL> set serveroutput on
SQL> DECLARE
2 CURSOR c_sal IS SELECT employee_id, first_name || last_name ename, salary
3 FROM employees ;
4 BEGIN
5 --隱含打開游標
6 FOR v_sal IN c_sal LOOP
7 --隱含執行一個FETCH語句
8 DBMS_OUTPUT.PUT_LINE(to_char(v_sal.employee_id)||'---'|| v_sal.ename||'---'||to_char(v_sal.salary)) ;
9 --隱含監測c_sal%NOTFOUND
10 END LOOP;
11 --隱含關閉游標
12 END;
13 /
100---StevenKing---24000
101---NeenaKochhar---17000
102---LexDe Haan---17000
103---AlexanderHunold---9000
104---BruceErnst---6000
105---DavidAustin---4800
106---ValliPataballa---4800
107---DianaLorentz---4200
108---NancyGreenberg---12008
109---DanielFaviet---9000
110---JohnChen---8200
111---IsmaelSciarra---7700
112---Jose ManuelUrman---7800
113---LuisPopp---6900
114---DenRaphaely---11000
115---AlexanderKhoo---3100
116---ShelliBaida---2900
117---SigalTobias---2800
118---GuyHimuro---2600
119---KarenColmenares---2500
120---MatthewWeiss---8000
121---AdamFripp---8200
122---PayamKaufling---7900
123---ShantaVollman---6500
124---KevinMourgos---5800
125---JuliaNayer---3200
126---IreneMikkilineni---2700
127---JamesLandry---2400
128---StevenMarkle---2200
129---LauraBissot---3300
130---MozheAtkinson---2800
131---JamesMarlow---2500
132---TJOlson---2100
133---JasonMallin---3300
134---MichaelRogers---2900
135---KiGee---2400
136---HazelPhiltanker---2200
137---RenskeLadwig---3600
138---StephenStiles---3200
139---JohnSeo---2700
140---JoshuaPatel---2500
141---TrennaRajs---3500
142---CurtisDavies---3100
143---RandallMatos---2600
144---PeterVargas---2500
145---JohnRussell---14000
146---KarenPartners---13500
147---AlbertoErrazuriz---12000
148---GeraldCambrault---11000
149---EleniZlotkey---10500
150---PeterTucker---10000
151---DavidBernstein---9500
152---PeterHall---9000
153---ChristopherOlsen---8000
154---NanetteCambrault---7500
155---OliverTuvault---7000
156---JanetteKing---10000
157---PatrickSully---9500
158---AllanMcEwen---9000
159---LindseySmith---8000
160---LouiseDoran---7500
161---SarathSewall---7000
162---ClaraVishney---10500
163---DanielleGreene---9500
164---MatteaMarvins---7200
165---DavidLee---6800
166---SundarAnde---6400
167---AmitBanda---6200
168---LisaOzer---11500
169---HarrisonBloom---10000
170---TaylerFox---9600
171---WilliamSmith---7400
172---ElizabethBates---7300
173---SunditaKumar---6100
174---EllenAbel---11000
175---AlyssaHutton---8800
176---JonathonTaylor---8600
177---JackLivingston---8400
178---KimberelyGrant---7000
179---CharlesJohnson---6200
180---WinstonTaylor---3200
181---JeanFleaur---3100
182---MarthaSullivan---2500
183---GirardGeoni---2800
184---NanditaSarchand---4200
185---AlexisBull---4100
186---JuliaDellinger---3400
187---AnthonyCabrio---3000
188---KellyChung---3800
189---JenniferDilly---3600
190---TimothyGates---2900
191---RandallPerkins---2500
192---SarahBell---4000
193---BritneyEverett---3900
194---SamuelMcCain---3200
195---VanceJones---2800
196---AlanaWalsh---3100
197---KevinFeeney---3000
198---DonaldOConnell---2600
199---DouglasGrant---2600
200---JenniferWhalen---4400
201---MichaelHartstein---13000
202---PatFay---6000
203---SusanMavris---6500
204---HermannBaer---10000
205---ShelleyHiggins---12008
206---WilliamGietz---8300
PL/SQL procedure successfully completed.
例9：當所聲明的游標帶有參數時，通過游標FOR 循環語句為游標傳遞參數。
SQL> DECLARE
2
3 CURSOR c_cursor(dept_no NUMBER DEFAULT 10)
4
5 IS
6
7 SELECT department_name, location_id FROM departments WHERE department_id <= dept_no;
8
9 BEGIN
10
11 DBMS_OUTPUT.PUT_LINE('當dept_no參數值為30：');
12
13 FOR c1_rec IN c_cursor(30) LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
14
15 END LOOP;
16
17 DBMS_OUTPUT.PUT_LINE(CHR(10)||'使用默認的dept_no參數值10：');
18
19 FOR c1_rec IN c_cursor LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
20
21 END LOOP;
22
23 END;
24 /
當dept_no參數值為30：
Administration---1700
Marketing---1800
Purchasing---1700
使用默認的dept_no參數值10：
Administration---1700
PL/SQL procedure successfully completed.
例10：PL/SQL還允許在游標FOR循環語句中使用子查詢來實現游標的功能。
SQL> BEGIN
2 FOR c1_rec IN(SELECT department_name, location_id FROM departments) LOOP
3 DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
4 END LOOP;
5 END;
6 /
Administration---1700
Marketing---1800
Purchasing---1700
Human Resources---2400
Shipping---1500
IT---1400
Public Relations---2700
Sales---2500
Executive---1700
Finance---1700
Accounting---1700
Treasury---1700
Corporate Tax---1700
Control And Credit---1700
Shareholder Services---1700
Benefits---1700
Manufacturing---1700
Construction---1700
Contracting---1700
Operations---1700
IT Support---1700
NOC---1700
IT Helpdesk---1700
Government Sales---1700
Retail Sales---1700
Recruiting---1700
Payroll---1700
PL/SQL procedure successfully completed.
4.1.2 處理隱式游標
顯式游標主要是用于對查詢語句的處理，尤其是在查詢結果為多條記錄的情況下；而對于非查詢語句，如修改、刪除操作，則由ORACLE 系統自動地為這些操作設置游標并創建其工作區，這些由系統隱含創建的游標稱為隱式游標，隱式游標的名字為SQL，這是由ORACLE 系統定義的。對于隱式游標的操作，如定義、打開、取值及關閉操作，都由ORACLE 系統自動地完成，無需用戶進行處理。用戶只能通過隱式游標的相關屬性，來完成相應的操作。在隱式游標的工作區中，所存放的數據是與用戶自定義的顯示游標無關的、最新處理的一條SQL 語句所包含的數據。
格式調用為： SQL%
注：INSERT, UPDATE, DELETE, SELECT 語句中不必明確定義游標。
例11: 刪除EMPLOYEES表中某部門的所有員工，如果該部門中已沒有員工，則在DEPARTMENT表中刪除該部門。
SQL> DECLARE
2 V_deptno employees.department_id%TYPE :=&p_deptno;
3 BEGIN
4 DELETE FROM employees WHERE department_id=v_deptno;
5 IF SQL%NOTFOUND THEN
6 DELETE FROM departments WHERE department_id=v_deptno;
7 END IF;
8 END;
9 /
Enter value for p_deptno: 1000
old 2: V_deptno employees.department_id%TYPE :=&p_deptno;
new 2: V_deptno employees.department_id%TYPE :=1000;
PL/SQL procedure successfully completed.
例12: 通過隱式游標SQL的%ROWCOUNT屬性來了解修改了多少行。‘
SQL> DECLARE
2 v_rows NUMBER;
3 BEGIN
4 --更新數據
5 UPDATE employees SET salary = 30000
6 WHERE department_id = 90 AND job_id = 'AD_VP';
7 --獲取默認游標的屬性值
8 v_rows := SQL%ROWCOUNT;
9 DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'個雇員的工資');
10 --回退更新，以便使數據庫的數據保持原樣
11 ROLLBACK;
12 END;
13 /
更新了2個雇員的工資
4.1.3 關于 NO_DATA_FOUND 和 %NOTFOUND的區別
SELECT … INTO 語句觸發 NO_DATA_FOUND；
當一個顯式游標的WHERE子句未找到時觸發%NOTFOUND；
當UPDATE或DELETE 語句的WHERE 子句未找到時觸發 SQL%NOTFOUND；在提取循環中要用 %NOTFOUND 或%FOUND 來確定循環的退出條件，不要用 NO_DATA_FOUND.
4.1.4 使用游標更新和刪除數據
游標修改和刪除操作是指在游標定位下，修改或刪除表中指定的數據行。這時，要求游標查詢語句中必須使用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；
SQL> run
1 DECLARE
2 V_deptno employees.department_id%TYPE :=&p_deptno;
3 CURSOR emp_cursor
4 IS
5 SELECT employees.employee_id, employees.salary
6 FROM employees WHERE employees.department_id=v_deptno
7 FOR UPDATE NOWAIT;
8 BEGIN
9 FOR emp_record IN emp_cursor LOOP
10 IF emp_record.salary < 1500 THEN
11 UPDATE employees SET salary=1500
12 WHERE CURRENT OF emp_cursor;
13 END IF;
14 END LOOP;
15 -- COMMIT;
16* END;
Enter value for p_deptno: 100
old 2: V_deptno employees.department_id%TYPE :=&p_deptno;
new 2: V_deptno employees.department_id%TYPE :=100;
PL/SQL procedure successfully completed.
例14：將EMPLOYEES表中部門編碼為90、崗位為AD_VP的雇員的工資都更新為2000元；
SQL> DECLARE
2 v_emp_record employees%ROWTYPE;
3 CURSOR c1
4 IS
5 SELECT * FROM employees FOR UPDATE;
6 BEGIN
7 OPEN c1;
8 LOOP
9 FETCH c1 INTO v_emp_record;
10 EXIT WHEN c1%NOTFOUND;
11 IF v_emp_record.department_id = 90 AND
12 v_emp_record.job_id = 'AD_VP'
13 THEN
14 UPDATE employees SET salary = 20000
15 WHERE CURRENT OF c1; --更新當前游標行對應的數據行
16 END IF;
17 END LOOP;
18 --COMMIT; --提交已經修改的數據
19 CLOSE c1;
20 END;
21 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM employees where department_id = 90 and job_id = 'AD_VP';
EMPLOYEE_ID FIRST_NAME LAST_NAME
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID
SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID 101 Neena KochharNKOCHHAR 515.123.4568 2005-09-21 00:00:00 AD_VP
20000 100 90
102 Lex De HaanLDEHAAN 515.123.4569 2001-01-13 00:00:00 AD_VP
20000 100 90
EMPLOYEE_ID FIRST_NAME LAST_NAME
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID
SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID4.2 游標變量
與游標一樣，游標變量也是一個指向多行查詢結果集合中當前數據行的指針。但與游標不同的是，游標變量是動態的，而游標是靜態的。游標只能與指定的查詢相連，即固定指向一個查詢的內存處理區域，而游標變量則可與不同的查詢語句相連，它可以指向不同查詢語句的內存處理區域（但不能同時指向多個內存處理區域，在某一時刻只能與一個查詢語句相連），只要這些查詢語句的返回類型兼容即可。
4.2.1 聲明游標變量
游標變量為一個指針，它屬于參照類型，所以在聲明游標變量類型之前必須先定義游標變量類型。在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;
例：創建兩個強類型定義游標變量和一個弱類型游標變量：
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;
4.2.2 游標變量操作
與游標一樣，游標變量操作也包括打開、提取和關閉三個步驟。
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：強類型參照游標變量類型
SQL> DECLARE
2 TYPE emp_job_rec IS RECORD(
3 Employee_id employees.employee_id%TYPE,
4 Employee_name employees.first_name%TYPE,
5 Job_title employees.job_id%TYPE
6 );
7 TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_rec;
8 Emp_refcur emp_job_refcur_type ;
9 Emp_job emp_job_rec;
10 BEGIN
11 OPEN emp_refcur FOR
12 SELECT employees.employee_id, employees.first_name||employees.last_name, employees.job_id
13 FROM employees
14 ORDER BY employees.department_id;
15
16 FETCH emp_refcur INTO emp_job;
17 WHILE emp_refcur%FOUND LOOP
18 DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job.job_title);
19 FETCH emp_refcur INTO emp_job;
20 END LOOP;
21 END;
22 /
200: JenniferWhalen is a AD_ASST
201: MichaelHartstein is a MK_MAN
202: PatFay is a MK_REP
114: DenRaphaely is a PU_MAN
115: AlexanderKhoo is a PU_CLERK
116: ShelliBaida is a PU_CLERK
117: SigalTobias is a PU_CLERK
118: GuyHimuro is a PU_CLERK
119: KarenColmenares is a PU_CLERK
203: SusanMavris is a HR_REP
120: MatthewWeiss is a ST_MAN
121: AdamFripp is a ST_MAN
122: PayamKaufling is a ST_MAN
123: ShantaVollman is a ST_MAN
124: KevinMourgos is a ST_MAN
125: JuliaNayer is a ST_CLERK
126: IreneMikkilineni is a ST_CLERK
127: JamesLandry is a ST_CLERK
128: StevenMarkle is a ST_CLERK
129: LauraBissot is a ST_CLERK
130: MozheAtkinson is a ST_CLERK
131: JamesMarlow is a ST_CLERK
132: TJOlson is a ST_CLERK
133: JasonMallin is a ST_CLERK
134: MichaelRogers is a ST_CLERK
135: KiGee is a ST_CLERK
136: HazelPhiltanker is a ST_CLERK
137: RenskeLadwig is a ST_CLERK
138: StephenStiles is a ST_CLERK
139: JohnSeo is a ST_CLERK
140: JoshuaPatel is a ST_CLERK
141: TrennaRajs is a ST_CLERK
142: CurtisDavies is a ST_CLERK
143: RandallMatos is a ST_CLERK
144: PeterVargas is a ST_CLERK
180: WinstonTaylor is a SH_CLERK
181: JeanFleaur is a SH_CLERK
182: MarthaSullivan is a SH_CLERK
183: GirardGeoni is a SH_CLERK
184: NanditaSarchand is a SH_CLERK
185: AlexisBull is a SH_CLERK
186: JuliaDellinger is a SH_CLERK
187: AnthonyCabrio is a SH_CLERK
188: KellyChung is a SH_CLERK
189: JenniferDilly is a SH_CLERK
190: TimothyGates is a SH_CLERK
191: RandallPerkins is a SH_CLERK
192: SarahBell is a SH_CLERK
193: BritneyEverett is a SH_CLERK
194: SamuelMcCain is a SH_CLERK
195: VanceJones is a SH_CLERK
196: AlanaWalsh is a SH_CLERK
197: KevinFeeney is a SH_CLERK
198: DonaldOConnell is a SH_CLERK
199: DouglasGrant is a SH_CLERK
103: AlexanderHunold is a IT_PROG
104: BruceErnst is a IT_PROG
105: DavidAustin is a IT_PROG
106: ValliPataballa is a IT_PROG
107: DianaLorentz is a IT_PROG
204: HermannBaer is a PR_REP
145: JohnRussell is a SA_MAN
146: KarenPartners is a SA_MAN
147: AlbertoErrazuriz is a SA_MAN
148: GeraldCambrault is a SA_MAN
149: EleniZlotkey is a SA_MAN
150: PeterTucker is a SA_REP
151: DavidBernstein is a SA_REP
152: PeterHall is a SA_REP
153: ChristopherOlsen is a SA_REP
154: NanetteCambrault is a SA_REP
155: OliverTuvault is a SA_REP
156: JanetteKing is a SA_REP
157: PatrickSully is a SA_REP
158: AllanMcEwen is a SA_REP
159: LindseySmith is a SA_REP
160: LouiseDoran is a SA_REP
161: SarathSewall is a SA_REP
162: ClaraVishney is a SA_REP
163: DanielleGreene is a SA_REP
164: MatteaMarvins is a SA_REP
165: DavidLee is a SA_REP
166: SundarAnde is a SA_REP
167: AmitBanda is a SA_REP
168: LisaOzer is a SA_REP
169: HarrisonBloom is a SA_REP
170: TaylerFox is a SA_REP
171: WilliamSmith is a SA_REP
172: ElizabethBates is a SA_REP
173: SunditaKumar is a SA_REP
174: EllenAbel is a SA_REP
175: AlyssaHutton is a SA_REP
176: JonathonTaylor is a SA_REP
177: JackLivingston is a SA_REP
179: CharlesJohnson is a SA_REP
100: StevenKing is a AD_PRES
101: NeenaKochhar is a AD_VP
102: LexDe Haan is a AD_VP
108: NancyGreenberg is a FI_MGR
109: DanielFaviet is a FI_ACCOUNT
110: JohnChen is a FI_ACCOUNT
111: IsmaelSciarra is a FI_ACCOUNT
112: Jose ManuelUrman is a FI_ACCOUNT
113: LuisPopp is a FI_ACCOUNT
205: ShelleyHiggins is a AC_MGR
206: WilliamGietz is a AC_ACCOUNT
178: KimberelyGrant is a SA_REP
PL/SQL procedure successfully completed.
例16：弱類型參照游標變量類型
SQL> PROMPT
SQL> PROMPT 'What table would you like to see?'
'What table would you like to see?'
SQL> ACCEPT tab PROMPT '(D)epartment, or (E)mployees:'
(D)epartment, or (E)mployees:
SQL> DECLARE
2 Type refcur_t IS REF CURSOR;
3 Refcur refcur_t;
4 TYPE sample_rec_type IS RECORD (
5 Id number,
6 Description VARCHAR2 (30)
7 );
8 sample sample_rec_type;
9 selection varchar2(1) := UPPER (SUBSTR ('&tab', 1, 1));
10 BEGIN
11 IF selection='D' THEN
12 OPEN refcur FOR
13 SELECT departments.department_id, departments.department_name FROM departments;
14 DBMS_OUTPUT.PUT_LINE('Department data');
15 ELSIF selection='E' THEN
16 OPEN refcur FOR
17 SELECT employees.employee_id, employees.first_name||' is a '||employees.job_id FROM employees;
18 DBMS_OUTPUT.PUT_LINE('Employee data');
19 ELSE
20 DBMS_OUTPUT.PUT_LINE('Please enter ''D'' or ''E''');
21 RETURN;
22 END IF;
23 DBMS_OUTPUT.PUT_LINE('----------------------');
24 FETCH refcur INTO sample;
25 WHILE refcur%FOUND LOOP
26 DBMS_OUTPUT.PUT_LINE(sample.id||': '||sample.description);
27 FETCH refcur INTO sample;
28 END LOOP;
29 CLOSE refcur;
30 END;
31 /
old 9: selection varchar2(1) := UPPER (SUBSTR ('&tab', 1, 1));
new 9: selection varchar2(1) := UPPER (SUBSTR ('', 1, 1));
Please enter 'D' or 'E'
例17：使用游標變量（沒有RETURN子句）
SQL> DECLARE
2 --定義一個游標數據類型
3 TYPE emp_cursor_type IS REF CURSOR;
4 --聲明一個游標變量
5 c1 EMP_CURSOR_TYPE;
6 --聲明兩個記錄變量
7 v_emp_record employees%ROWTYPE;
8 v_reg_record regions%ROWTYPE;
9
10 BEGIN
11 OPEN c1 FOR SELECT FROM employees WHERE department_id = 20;
12 LOOP
13 FETCH c1 INTO v_emp_record;
14 EXIT WHEN c1%NOTFOUND;
15 DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇傭日期是'||v_emp_record.hire_date);
16 END LOOP;
17 --將同一個游標變量對應到另一個SELECT語句
18 OPEN c1 FOR SELECT FROM regions WHERE region_id in(1,2);
19 LOOP
20 FETCH c1 INTO v_reg_record;
21 EXIT WHEN c1%NOTFOUND;
22 DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示'||v_reg_record.region_name);
23 END LOOP;
24 CLOSE c1;
25 END;
26 /
Michael的雇傭日期是2004-02-17 00:00:00
Pat的雇傭日期是2005-08-17 00:00:00
1表示Europe
2表示Americas
PL/SQL procedure successfully completed.
例18：使用游標變量（有RETURN子句）
SQL> DECLARE
2 --定義一個與employees表中的這幾個列相同的記錄數據類型
3 TYPE emp_record_type IS RECORD(
4 f_name employees.first_name%TYPE,
5 h_date employees.hire_date%TYPE,
6 j_id employees.job_id%TYPE);
7 --聲明一個該記錄數據類型的記錄變量
8 v_emp_record EMP_RECORD_TYPE;
9 --定義一個游標數據類型
10 TYPE emp_cursor_type IS REF CURSOR
11 RETURN EMP_RECORD_TYPE;
12 --聲明一個游標變量
13 c1 EMP_CURSOR_TYPE;
14 BEGIN
15 OPEN c1 FOR SELECT first_name, hire_date, job_id
16 FROM employees WHERE department_id = 20;
17 LOOP
18 FETCH c1 INTO v_emp_record;
19 EXIT WHEN c1%NOTFOUND;
20 DBMS_OUTPUT.PUT_LINE('雇員名稱：'||v_emp_record.f_name
21 ||' 雇傭日期：'||v_emp_record.h_date
22 ||' 崗位：'||v_emp_record.j_id);
23 END LOOP;
24 CLOSE c1;
25 END;
26 /
雇員名稱：Michael 雇傭日期：2004-02-17 00:00:00 崗位：MK_MAN
雇員名稱：Pat 雇傭日期：2005-08-17 00:00:00 崗位：MK_REP
PL/SQL procedure successfully completed.
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
