PL/SQL --> 游标

--==================

-- 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语句中引用where current 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 基础--> ROLLUP与CUBE运算符实现数据汇总

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

有关PL/SQL请参考

PL/SQL --> 语言基础

PL/SQL --> 流程控制

PL/SQL --> 存储过程

PL/SQL --> 函数

PL/SQL --> 游标

PL/SQL -->隐式游标(SQL%FOUND)

PL/SQL --> 异常处理(Exception)

PL/SQL --> PL/SQL记录

PL/SQL --> 包的创建与管理

PL/SQL --> 包重载、初始化

PL/SQL --> DBMS_DDL包的使用

PL/SQL --> DML 触发器

PL/SQL --> INSTEAD OF 触发器

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏微服务生态

用尽洪荒之力整理的Mysql数据库32条军规

2、控制单表数据量 int型不超过1000w,含char则不超过500w; 合理分表; 限制单库表数量在300以内;

10230
来自专栏difcareer的技术笔记

C代码 从源代码到可执行文件——编译全过程解析

程序的生命周期从一个高级C语言程序开始,这种形式能够被人读懂,却不能被机器读懂,为了在系统上运行这个程序,该源程序需要被其他程序转化为一系列低级机器语言指令,然...

63750
来自专栏Golang语言社区

服务器 数据库设计技巧--2

8.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如: select id from t wheresubstring...

35490
来自专栏黑泽君的专栏

day37_Spring学习笔记_05_CRM_01

CRM : custom releation manager 客户关系管理系统,用于维护客户和公司之间关系。 我们要做的是:学校 和 大家 之间关系。

8120
来自专栏PHP在线

MySQL SQL语句优化的10条建议

1、将经常要用到的字段(比如经常要用这些字段来排序,或者用来做搜索),则最好将这些字段设为索引 2、字段的种类尽可能用int或者tinyint类型。另外字段尽可...

33450
来自专栏Venyo 的专栏

使用 Elasticsearch 的 NGram 分词器处理模糊匹配

接到一个任务:用 Elasticsearch 实现搜索银行支行名称的功能。大概就是用户输入一截支行名称或拼音首字母,返回相应的支行名称。比如,用户输入"工行"或...

48360
来自专栏Java帮帮-微信公众号-技术文章全总结

Web-第二十四天 Oracle学习【悟空教程】

ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S...

30420
来自专栏玄魂工作室

CTF实战8 SQL注入漏洞

我们还是那句话先 重要声明 该培训中提及的技术只适用于合法CTF比赛和有合法授权的渗透测试,请勿用于其他非法用途,如用作其他非法用途与本文作者无关

21620
来自专栏Android Note

Android—Room数据库(介绍)

16950
来自专栏互联网开发者交流社区

数据库的总结

16140

扫码关注云+社区

领取腾讯云代金券