游标概念
在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。它为应用等量齐观提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。
在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。
对于不同的SQL语句，游标的使用情况不同：
SQL语句 游标
非查询语句 隐式的
结果是单行的查询语句 隐式的或显示的
结果是多行的查询语句 显示的
创新互联建站主营南州晴隆网站建设的网络公司,主营网站建设方案,成都app开发,南州晴隆h5成都小程序开发搭建,南州晴隆网站营销推广欢迎南州晴隆等地区企业咨询
显式游标处理
显式游标处理需四个 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:
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
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.
PL/SQL procedure successfully completed.
使用默认的dept_no参数值10：
Administration---1700
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.
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.
