Oracle 游标

发布时间：2020-06-03 15:05:36 来源：网络阅读：663 作者：whshurk 栏目：关系型数据库

游标概念
在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

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 语句中不必明确定义游标。
Oracle 游标
例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                Kochhar

NKOCHHAR 515.123.4568 2005-09-21 00:00:00 AD_VP
20000 100 90

    102 Lex                  De Haan

LDEHAAN 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_ID

4.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.

向AI问一下细节

Oracle 游标

猜你喜欢

最新资讯

相关推荐

相关标签