PL/SQL --> 游标

亦凉 2022-05-15 01:40 168阅读 0赞

\--==================

\-- PL/SQL --> 游标

\--==================

一、游标的相关概念及特性

1.定义

映射在结果集中某一行数据的具体位置，类似于C语言中的指针。即通过游标方式定位到结果集中某个特定的行，然后根据业务需求

对该行进行相应特定的操作。

2.游标的分类

显示游标:即用户自定义游标，专门用于处理select语句返回的多行数据

隐式游标:系统自动定义的游标，记录集只有单行数据，用于处理select into 和DML语句

3.游标使用的一般过程：

显示游标：声明,打开,读取,关闭

隐式游标：直接使用读取，声明、打开、关闭都是系统自动进行的

4.显示游标的过程描述

a.声明游标

CURSOR cursor\_name IS select\_statement

如：CURSOR emp\_cur IS SELECT empno,ename,job,sal FROM scott.emp;

b.打开游标

OPEN cursor\_name \--打开游标则执行对应的select语句，将对应的结果集存放到游标当中

如：OPEN emp\_cur

c.读取数据

FETCH cursor\_name INTO var\_name1,...var\_name2 ; \--提取单行数据，需要配合循环语句来使用

FETCH cursor\_name BULK COLLECT INTO collect1,collect2,...\[LIMIT rows\]; \--提取多行数据，collect为集合变量

d.关闭游标

CLOSE cursor\_name

5.显示游标的个属性

cursor\_name%ISOPEN 游标是否打开

cursor\_name%FOUND 最近的FETCH是否提取到数据

cursor\_name%NOTFOUND 最近的FETCH是否没有提取到数据

cursor\_name%ROWCOUNT 返回到目前为止，已经从游标缓冲区中提取到数据的行数

二、显示游标应用示例

\--例：浏览数据，输入职位，查看每个人工资(使用fetch cursor\_name into来提取单行记录)

scott@ORCL> get /u01/bk/scripts/emp\_cur1.sql

1 DECLARE

2 v\_name emp.ename%TYPE; \--定义用于存放游标提取的数据的变量

3 v\_job emp.job%TYPE;

4 v\_sal emp.sal%TYPE;

5 CURSOR emp\_cur IS select ename,sal FROM emp WHERE job=v\_job;

6 BEGIN

7 v\_job:='&inputjob';

8 OPEN emp\_cur;

9 DBMS\_OUTPUT.PUT\_LINE('Name Sal');

10 LOOP

11 FETCH emp\_cur INTO v\_name,v\_sal;

12 EXIT WHEN emp\_cur%NOTFOUND;

13 DBMS\_OUTPUT.PUT\_LINE(v\_name||' '||v\_sal);

14 END LOOP;

15 CLOSE emp\_cur;

16\* END;

scott@ORCL> start /u01/bk/scripts/emp\_cur1.sql

Enter value for inputjob: CLERK

old 7: v\_job:='&inputjob';

new 7: v\_job:='CLERK';

Name Sal

SMITH 800

ADAMS 1100

JAMES 950

MILLER 1300

PL/SQL procedure successfully completed.

\--例：定义一个游标，输入部门号时，则显示该部门所有成员的名字(使用fetch cursor\_name bulk collect into提取所有数据)

scott@ORCL> get /u01/bk/scripts/emp\_cur2.sql

1 DECLARE

2 v\_deptno emp.deptno%type;

3 type ename\_table\_type is table of varchar2(10); \--定义PL/SQL表类型

4 ename\_table ename\_table\_type; \--定义PL/SQL表变量存放游标数据

5 cursor emp\_cur is

6 select ename from emp where deptno=v\_deptno;

7 BEGIN

8 v\_deptno:=&inputno;

9 open emp\_cur;

10 fetch emp\_cur bulk collect into ename\_table; \--使用bulk collect into提取所有数据

11 for i in 1..ename\_table.count

12 loop

13 dbms\_output.put\_line(ename\_table(i));

14 end loop;

15 close emp\_cur;

16\* END;

scott@ORCL> start /u01/bk/scripts/emp\_cur2.sql

Enter value for inputno: 10

old 8: v\_deptno:=&inputno;

new 8: v\_deptno:=10;

CLARK

KING

MILLER

\--例：游标属性使用示例(使用%isopen和%rowcount属性)

scott@ORCL> get /u01/bk/scripts/emp\_cur3.sql

1 DECLARE

2 v\_deptno emp.deptno%type;

3 type ename\_table\_type is table of varchar2(10);

4 ename\_table ename\_table\_type;

5 cursor emp\_cur is

6 select ename from emp where deptno=v\_deptno;

7 BEGIN

8 v\_deptno:=&inputno;

9 if not emp\_cur%isopen then \--判断游标是否打开，如未打开，则打开游标

10 open emp\_cur;

11 end if;

12 fetch emp\_cur bulk collect into ename\_table;

13 dbms\_output.put\_line

14 ('All record counts from cursor is : '||emp\_cur%rowcount); \--使用cursor\_name%rowcount 统计游标的记录数

15 close emp\_cur;

16\* END;

scott@ORCL> start /u01/bk/scripts/emp\_cur3.sql

Enter value for inputno: 20

old 8: v\_deptno:=&inputno;

new 8: v\_deptno:=20;

All record counts from cursor is : 5

\--例:基于游标定义记录变量(该方式大大简化了所需要定义的变量个数)

scott@ORCL> get /u01/bk/scripts/emp\_cur4.sql

1 DECLARE

2 cursor emp\_cur is select ename,sal from emp;

3 emp\_record emp\_cur%rowtype; \--定义游标类型记录变量

4 BEGIN

5 open emp\_cur;

6 loop

7 fetch emp\_cur into emp\_record;

8 exit when emp\_cur%notfound;

9 dbms\_output.put\_line

10 ('Employee Name : '||emp\_record.ename ||' ,Sal: '||emp\_record.sal);

11 end loop;

12 close emp\_cur;

13\* END;

scott@ORCL> start /u01/bk/scripts/emp\_cur4.sql

Employee Name : SMITH ,Sal: 800

Employee Name : ALLEN ,Sal: 1600

Employee Name : WARD ,Sal: 1250

.......

三、使用游标更新记录

通过游标既可以逐行检索结果集中的记录，又可以更新或删除当前游标行的数据

如果要通过游标更新和删除数据，在定义游标时必须要带有FOR UPDATE子句

格式：

CURSOR cursor\_name IS select\_statement FOR UPDATE \[ OF column\_reference \]\[NOWAIT\]

FOR UPDATE：子句用于在游标结果集数据上加行共享锁，以防止其它用户在相应行上执行DML操作

OF：子句用于游标子查询到多张表时来确定哪些表要加锁，如未指定，则select语句所引用的全部表将被加锁

NOWAIT ：子句指定不等待锁

使用DML语句操作游标中的当前行时，需要在update或delete语句中引用wherecurrent of子句

UPDATE tbname set col1=.. WHERE CURRENT OF cursor\_name;

DELETE tbname WHERE CURRENT OF cursor\_name;

\--例：使用游标修改所有记录的工资，根据JOB来作不同的修改。

scott@ORCL> create table tb\_emp as select \* from emp;

scott@ORCL> get /u01/bk/scripts/emp\_cur6.sql

1 DECLARE

2 v\_job tb\_emp.job%TYPE;

3 CURSOR emp\_cur IS SELECT job FROM tb\_emp FOR UPDATE; \--定义时，使用FOR UPDATE

4 BEGIN

5 OPEN emp\_cur;

6 LOOP

7 FETCH emp\_cur INTO v\_job;

8 EXIT WHEN emp\_cur%NOTFOUND;

9 CASE

10 WHEN v\_job='CLERK' THEN

11 UPDATE tb\_emp SET sal=sal\*1.1 WHERE CURRENT OF emp\_cur; \--注意，需要使用WHERE CURRENT OF

12 WHEN v\_job='SALESMAN' THEN

13 UPDATE tb\_emp SET sal=sal\*1.08 WHERE CURRENT OF emp\_cur;

14 ELSE

15 UPDATE tb\_emp SET sal=sal\*1.05 WHERE CURRENT OF emp\_cur;

16 END CASE;

17 END LOOP;

18 CLOSE emp\_cur;

19\* END;

\--例：利用游标删除数据

scott@ORCL> get /u01/bk/scripts/emp\_cur7.sql

1 DECLARE

2 v\_job tb\_emp.job%type;

3 v\_sal tb\_emp.sal%type;

4 cursor emp\_cur is select job,sal from tb\_emp for update;

5 BEGIN

6 open emp\_cur;

7 fetch emp\_cur into v\_job,v\_sal;

8 while emp\_cur%found

9 loop

10 if v\_sal>3000 then

11 delete from tb\_emp where current of emp\_cur;

12 end if;

13 fetch emp\_cur into v\_job,v\_sal;

14 end loop;

15 close emp\_cur;

16\* END;

17 /

\--例：使用OF子句对特定的表加共享锁

scott@ORCL> get /u01/bk/scripts/emp\_cur8.sql

1 DECLARE

2 cursor emp\_cur is

3 select ename,sal,dname,e.deptno

4 from tb\_emp e join dept d

5 on e.deptno=d.deptno for update of e.deptno;

6 emp\_record emp\_cur%rowtype;

7 BEGIN

8 open emp\_cur;

9 loop

10 fetch emp\_cur into emp\_record;

11 exit when emp\_cur%notfound;

12 if emp\_record.deptno=20 then

13 update tb\_emp set sal=sal\+100 where current of emp\_cur;

14 end if;

15 dbms\_output.put\_line('Ename: '||emp\_record.ename||

16 ',Sal: '||emp\_record.sal||

17 ',Deptname:'||emp\_record.dname);

18 end loop;

19 close emp\_cur;

20\* END;

21 /

Ename: SMITH,Sal: 880,Deptname:RESEARCH

Ename: ALLEN,Sal: 1728,Deptname:SALES

........

\--例:NOWAIT子句的使用

scott@ORCL> get /u01/bk/scripts/emp\_cur9.sql

1 DECLARE

2 v\_ename tb\_emp.ename%type;

3 v\_oldsal tb\_emp.sal%type;

4 cursor emp\_cur is

5 select ename,sal from tb\_emp for update nowait; \--使用nowait子句指定不等待锁，会给出错误提示

6 BEGIN

7 open emp\_cur;

8 loop

9 fetch emp\_cur into v\_ename,v\_oldsal;

10 exit when emp\_cur%notfound;

11 if v\_oldsal<2000 then

12 update tb\_emp set sal=sal\+200 where current of emp\_cur;

13 end if;

14 end loop;

15 close emp\_cur;

16\* END;

scott@ORCL> start /u01/bk/scripts/emp\_cur9.sql

DECLARE

ERROR at line 1:

ORA\-00054: resource busy and acquire with NOWAIT specified

ORA\-06512: at line 5

ORA\-06512: at line 7

四、游标FOR循环

游标FOR循环是为了简化游标使用过程而设计的。使用游标FOR循环检索游标时，游标的打开、数据提取、数据是否检索到的判断与游标

的关闭都是ORACLE系统自动进行的。

游标FOR循环两种语句格式：

格式一：

先在定义部分定义游标，然后在游标FOR循环中引用该游标

FOR record\_name IN cursor\_name LOOP

statement1;

statement2;

END LOOP;

格式二：

在FOR循环中直接引用子查询，隐式定义游标

FOR record\_name IN subquery LOOP

statement;

END LOOP;

\--例：定义游标并使用for循环逐个显示记录

DECLARE

v\_job emp.job%TYPE;

CURSOR emp\_cur IS SELECT ename,sal FROM emp WHERE job=v\_job;

BEGIN

v\_job:='&inputjob';

DBMS\_OUTPUT.PUT\_LINE('NO. Name Sal');

FOR emp\_record IN emp\_cur LOOP

DBMS\_OUTPUT.PUT\_LINE(emp\_cur%ROWCOUNT||' '||

emp\_record.ename||' '||emp\_record.sal);

END LOOP;

END;

scott@ORCL> start /u01/bk/scripts/emp\_cur10.sql

Enter value for inputjob: SALESMAN

old 5: v\_job:='&inputjob';

new 5: v\_job:='SALESMAN';

NO. Name Sal

1 ALLEN 1600

2 WARD 1250

3 MARTIN 1250

4 TURNER 1500

\--例：直接在游标for循环中使用子查询来逐个显示记录

DECLARE

v\_job emp.job%TYPE;

BEGIN

v\_job:='&inputjob';

DBMS\_OUTPUT.PUT\_LINE('Name Sal');

FOR emp\_record IN (SELECT ename,sal FROM emp WHERE job=v\_job) LOOP

DBMS\_OUTPUT.PUT\_LINE(emp\_record.ename||' '

||emp\_record.sal);

END LOOP;

END;

五、参数游标

参数游标是指带有参数的游标。当定义了参数游标后，使用不同的参数值多次打开游标则会生成不同的结果集。

定义参数游标：

CURSOR cursor\_name (para\_name1 datatype \[,para\_name2 datatype ...\])

IS select\_statement;

注：datatype 只指定数据类型即可，不能指定参数的长度、精度、刻度

打开参数游标：

OPEN cursor\_name \[(vlaues)\]

参数个数、类型必须与定义时的形参相匹配。

对于定义的参数游标，一定要在游标子查询的where子句中指定定义的参数，否则将使得参数游标失去意义

例：用部门编号deptno作形参，显示每个人的姓名和工资

scott@ORCL> get /u01/bk/scripts/emp\_cur5.sql

1 DECLARE

2 v\_deptno emp.deptno%type;

3 cursor emp\_cur(v\_deptno emp.deptno%type) is \--定义游标时指定了参数v\_deptno及类型

4 select ename,sal from emp where deptno=v\_deptno; \--必须在where子句中指定定义的参数

5 emp\_record emp\_cur%rowtype;

6 BEGIN

7 v\_deptno:=&inputno;

8 open emp\_cur(v\_deptno);

9 loop

10 fetch emp\_cur into emp\_record;

11 exit when emp\_cur%notfound;

12 dbms\_output.put\_line

13 ('Employe Name is :'||emp\_record.ename||' ,Sal:'||emp\_record.sal);

14 end loop;

15 close emp\_cur;

16\* END;

17 /

Enter value for inputno: 10

old 7: v\_deptno:=&inputno;

new 7: v\_deptno:=10;

Employe Name is :CLARK ,Sal:2450

Employe Name is :KING ,Sal:5000

Employe Name is :MILLER ,Sal:1300

六、游标变量

简言之，其一是一个游标，其次则是一个变量，因此称之为游标变量，可以用来存储不同的游标

对于游标变量的使用，在打开游标变量时指定其对应的select语句

1.游标变量的使用步骤

a.定义REF CURSOR类型和游标变量

TYPE ref\_type\_name IS REF CURSOR \[RETURN return\_type\]; \--必须先定义REF CURSOR类型

cursor\_variable ref\_type\_name; \--接下来再定义游标变量

ref\_type\_name:指定自定义的类型名

RETURN:指定REF CURSOR返回结果的数据类型

cursor\_variable:定义游标变量的名字

注：若指定RETURN子句，其数据类型必须是记录类型，此外，不能在包内定义游标变量

b.打开游标

在打开游标时必须指定其对应的select语句，一旦打开游标变量则对应的select结果集将存放到游标变量中

OPEN cursor\_variable FOR select\_statement;

c.提取数据

提取数据与普通的显示游标提取数据的方法类似

FETCH cursor\_variable INTO variable1,...variable2 ; \--提取单行数据，需要配合循环语句来使用

FETCH cursor\_variable BULK COLLECT INTO collect1,collect2,...\[LIMIT rows\]; \--提取多行数据，collect为集合变量

d.关闭游标变量

CLOSE cursor\_vairable;

2.游标变量使用的例子

\--例.根据部门名称显示该部门的所有雇员(定义REF CURSOR时不指定RETURN子句)

scott@ORCL> get /u01/bk/scripts/emp\_cur12.sql

1 DECLARE

2 type emp\_cur\_type is ref cursor; \--定义游标类型为ref cursor

3 emp\_cur emp\_cur\_type; \--定义游标变量为emp\_cur

4 emp\_record emp%rowtype; \--定义游标变量记录类型为emp\_record

5 v\_deptno emp.deptno%type;

6 BEGIN

7 v\_deptno:=&inputno;

8 open emp\_cur for select \* from emp where deptno=v\_deptno;

9 dbms\_output.put\_line('No Name');

10 loop

11 fetch emp\_cur into emp\_record;

12 exit when emp\_cur%notfound;

13 dbms\_output.put\_line(emp\_cur%rowcount||' '||emp\_record.ename);

14 end loop;

15 close emp\_cur;

16\* END;

17 /

Enter value for inputno: 10

old 7: v\_deptno:=&inputno;

new 7: v\_deptno:=10;

No Name

1 CLARK

2 KING

3 MILLER

\--例:根据部门名称显示该部门的所有雇员名字及薪水(定义REF CURSOR时指定RETURN子句)

scott@ORCL> get /u01/bk/scripts/emp\_cur13.sql

1 DECLARE

2 type emp\_record\_type is record(name varchar2(10),salary number(6,2)); \--定义PL/SQL记录变量类型

3 type emp\_cur\_type is ref cursor return emp\_record\_type; \--定义游标类型为ref cursor，且具有返回类型

4 emp\_cur emp\_cur\_type; \--定义游标变量为emp\_cur

5 emp\_record emp\_record\_type; \--定义类型为emp\_record\_type记录变量emp\_record

6 v\_deptno emp.deptno%type;

7 BEGIN

8 v\_deptno:=&inputno;

9 open emp\_cur for select ename,sal from emp where deptno=v\_deptno;

10 dbms\_output.put\_line('Name Salary');

11 loop

12 fetch emp\_cur into emp\_record;

13 exit when emp\_cur%notfound;

14 dbms\_output.put\_line(emp\_record.name||' '||emp\_record.salary);

15 end loop;

16 close emp\_cur;

17\* END;

18 /

Enter value for inputno: 10

old 8: v\_deptno:=&inputno;

new 8: v\_deptno:=10;

Name Salary

CLARK 2450

KING 5000

MILLER 1300

\--如果REF CURSOR指定RETURN子句的数据列于select子句的数据列不一致将收到如下的错误提示信息

scott@ORCL> start /u01/bk/scripts/emp\_cur13.sql

Enter value for inputno: 10

old 8: v\_deptno:=&inputno;

new 8: v\_deptno:=10;

open emp\_cur for select ename,sal,job from emp where deptno=v\_deptno; \--多出了一列

ERROR at line 9:

ORA\-06550: line 9, column 22:

PLS\-00382: expression is of wrong type

ORA\-06550: line 9, column 5:

PL/SQL: SQL Statement ignored

\--例：游标变量的多次使用

scott@ORCL> get /u01/bk/scripts/emp\_cur14.sql

1 DECLARE

2 type cur\_type is ref cursor;

3 scott\_cur cur\_type;

4 v\_emp emp%rowtype;

5 v\_dept dept%rowtype;

6 BEGIN

7 open scott\_cur for select \* from emp where deptno=10; \--使用for select首次打开游标

8 dbms\_output.put\_line('No, Name');

9 loop

10 fetch scott\_cur into v\_emp;

11 exit when scott\_cur%notfound;

12 dbms\_output.put\_line(scott\_cur%rowcount||','||v\_emp.ename);

13 end loop;

14 open scott\_cur for select \* from dept where deptno=10;\--使用for select 再此打开游标，此次加载了不同数据

15 dbms\_output.put\_line('Deptno, Name');

16 loop

17 fetch scott\_cur into v\_dept;

18 exit when scott\_cur%notfound;

19 dbms\_output.put\_line(v\_dept.deptno||','||v\_dept.dname);

20 end loop;

21\* END;

22 /

No, Name

1,CLARK

2,KING

3,MILLER

Deptno, Name

10,ACCOUNTING

七、更多参考

有关SQL请参考

[SQL 基础--> 子查询][SQL _--_]

[SQL 基础-->多表查询][SQL _--]

[SQL基础-->分组与分组函数][SQL_--]

[SQL 基础-->常用函数][SQL _-- 1]

[SQL 基础--> ROLLUP与CUBE运算符实现数据汇总][SQL _--_ ROLLUP_CUBE]

[SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)][SQL_--_START BY ... CONNECT BY PRIOR]

有关PL/SQL请参考

[PL/SQL --> 语言基础][PL_SQL --_]

[PL/SQL --> 流程控制][PL_SQL --_ 1]

[PL/SQL --> 存储过程][PL_SQL --_ 2]

[PL/SQL --> 函数][PL_SQL --_ 3]

[PL/SQL --> 游标][PL_SQL --_ 4]

[PL/SQL -->隐式游标(SQL%FOUND)][PL_SQL --_SQL_FOUND]

[PL/SQL --> 异常处理(Exception)][PL_SQL --_ _Exception]

[PL/SQL --> PL/SQL记录][PL_SQL --_ PL_SQL]

[PL/SQL --> 包的创建与管理][PL_SQL --_ 5]

[PL/SQL --> 包重载、初始化][PL_SQL --_ 6]

[PL/SQL --> DBMS\_DDL包的使用][PL_SQL --_ DBMS_DDL]

[PL/SQL --> DML 触发器][PL_SQL --_ DML]

[PL/SQL --> INSTEAD OF 触发器][PL_SQL --_ INSTEAD OF]

[SQL _--_]: http://blog.csdn.net/robinson_0612/archive/2010/05/14/5592002.aspx
[SQL _--]: http://blog.csdn.net/robinson_0612/archive/2010/05/06/5563199.aspx
[SQL_--]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559110.aspx
[SQL _-- 1]: http://blog.csdn.net/robinson_0612/archive/2010/04/16/5495170.aspx
[SQL _--_ ROLLUP_CUBE]: http://blog.csdn.net/robinson_0612/archive/2010/06/01/5640031.aspx
[SQL_--_START BY ... CONNECT BY PRIOR]: http://blog.csdn.net/robinson_0612/archive/2010/05/22/5616877.aspx
[PL_SQL --_]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063399.aspx
[PL_SQL --_ 1]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063422.aspx
[PL_SQL --_ 2]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063434.aspx
[PL_SQL --_ 3]: http://blog.csdn.net/robinson_0612/archive/2010/12/11/6069531.aspx
[PL_SQL --_ 4]: http://blog.csdn.net/robinson_0612/archive/2010/12/15/6078622.aspx
[PL_SQL --_SQL_FOUND]: http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092066.aspx
[PL_SQL --_ _Exception]: http://blog.csdn.net/robinson_0612/archive/2010/12/16/6080119.aspx
[PL_SQL --_ PL_SQL]: http://blog.csdn.net/robinson_0612/archive/2010/12/18/6084390.aspx
[PL_SQL --_ 5]: http://blog.csdn.net/robinson_0612/archive/2010/12/18/6084475.aspx
[PL_SQL --_ 6]: http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092097.aspx
[PL_SQL --_ DBMS_DDL]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098248.aspx
[PL_SQL --_ DML]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098258.aspx
[PL_SQL --_ INSTEAD OF]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098263.aspx