开发者社区> 技术小胖子> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PL/SQL游标使用

简介:
+关注继续查看

游标只是一个指向查询语句返回的结果的指针,因此在游标定义时将包含一个查询定义。当游标打开后,数据被接收到一块内存区域存储,直到游标关闭。

游标实际上指向的是一块内存区域,这块内存区域位于进程全局区内部,称为上下文区域,在上下文区域中包含如下3类信息

1.查询返回的数据行

2.查询所处理的数据的行号

3.指向共享池中的已分析的SQL语句

游标实际上指向一块内存区域

游标定义时并不会获取游标数据,只有在游标被打开后,游标相关的查询语句被执行,然后将检索到的结果保存到内存中。

游标结构示意图

游标指向PGA,上下文区

查询结果集

查询处理的行数

共享池中已分析的查询语句 


使用游标例子

declare

emprow emp%ROWTYPE

cursor emp_cur

is

select * from emp where deptno is not null;

begin

open emp_cur;

loop

fetch emp_cur

into emprow

dbms_output.put_line('员工编号:'

||emprow.empno

||' '

||'员工名称:'

||emprow.ename

);

exit when emp_cur%NOTFOUND;

end loop;

close emp_cur;

end;


游标分类

1.显式游标

2.隐式游标  也叫SQL游标

执行每一个DML操纵语句时,oracle都会在PGA中的一个上文区域中具有一个隐式的游标。

begin

update emp set comm = comm * 1.12 where empno = 7369;

dbms_output.put_line(SQL%ROWCOUNT || '行被更新');

if SQL%NOTFOUND

then

dbms_output.put_line('不能更新员工号为7369的员工!');

end if;

commit;

exception

when others

then

dbms_output.put_line(SQLERRM);

end;


定义游标类型

declare

cursor emp_cursor

is

select * from emp where deptno=20;

begin

null;

end;

不能把值赋给游标名或者在表达式中使用它,但是游标和变量有着同样的作用域规则


declare

v_deptno NUMBER;

cursor emp_cursor

is

select * from emp where deptno = v_deptno;

begin

v_deptno:=20;

open emp_cursor;

if emp_cursor%ISOPEN then

dbms_output.put_line('游标已经被打开');

end if;

end;

   v_deptno是绑定变量,该变量必须在游标声明前之前进行声明,否则oracle会报错。游标的形式参数都必须IN模式,并且不能给游标的参数添加NOT NULL约束

declare

cusor emp_cursor(p_deptno in number)

is

select * from emp where deptno=p_deptno;

begin

open emp_cursor(20);

end;

可以指定return子句定义游标返回值的类型,返回值的类型一定要和返回的结果集的类型一致,因此返回值一般为记录类型或%ROWTYPE指定的表中的类型

declare

cursor emp_cursor(p_deptno in number)return emp%ROWTYPE

is

select * from emp where deptno=p_deptno;

begin

open emp_cursor(20);

end;


使用游标属性

1.%ISOPEN

判断对应的游标变量是否打开,如果游标变量打开,则返回true,否则返回false。


declare

cursor emp_cursor(p_deptno in number)

is

select * from emp where deptno=p_deptno;

begin

if not emp_cursor%ISOPEN then

open emp_cursor(20);

end if;

if emp_cursor%ISOPEN then

dbms_output.put_line('游标已经打开了');

else

dbms_output.put_line('游标还没有被打开!');

end if;

close emp_cursor;

end;

2.%FOUND

检查是否从结果集中提取到了数据

declare

emp_row emp%ROWTYPE;

cursor emp_cursor(p_deptno in number)

is

select * from emp where deptno=p_deptno;

begin

if not emp_cursor%ISOPEN

then

open emp_cursor(20);

end if;

if emp_cursor%FOUND is null

then

dbms_output.put_line('%FOUND属性为NULL');

end if;

loop

fetch emp_cursor

into emp_row;

exit when not emp_cursor%FOUND;

end loop;

close emp_cursor;

end;


3.%NOTFOUND属性

该属性与%FOUND属性相反,当没有从游标中提取到数据时,该属性返回True,否则返回False,与%Found一样

declare

emp_row emp%ROWTYPE;

cursor emp_cursor(p_deptno in number)

is select * from emp where deptno=p_deptno;

begin

open emp_row(20);

if emp_cursor%NOTFOUND is null;

then

dbms_output.put_line('%NOTFOUND属性为null');

end if;

loop

fetch emp_cursor

into emp_row;

exit when emp_cursor%NOTFOUND;

end loop;

close emp_cursor;

end;

4.%ROWCOUNT

用来返回到目前为止已经从游标中取出的记录的行数

declare

emp_row emp%ROWTYPE;

cursor emp_cursor(p_deptno in number)

is

select * from emp where deptno=p_deptno;

begin

open emp_cursor(20);

loop

fetch emp_cursor

into emp_row;

exit when emp_cursor%NOTFOUND;

dbms_output.put_line('当前已提取的行数为:'||emp_cursor%ROWCOUNT ||'行!');

end loop;

close emp_cursor;

end;

5.提取游标数据

declare

deptno dept.deptno%TYPE;

dname dept.dname%TYPE;

loc dept.loc%TYPE;

dept_row dept%ROWTYPE;

cursor dept_cur is select * from dept;

begin

open dept_cur;

loop

if dept_cur%ROWCOUNT<=4 then

fetch dept_cur into dept_row;

if dept_cur%FOUND then

dbms_output.put_line(dept_row.deptno||' '|| dept_row.dname||' '||dept_row.loc);

end if;

else

fetch dept_cur into deptno,dname,loc;

if dept_cur%FOUND then

dbms_output.put_line(deptno||' '||dname||' '||loc);

end if;

end if;

exit when dept_cur%NOTCOUND;

end loop;

close dept_cur;

end;

批量提取数据

因为fetch语句一次只能提取一行,并且提取只能是向前的,因此如果要重新提取已经提取过的数据,只有重新打开游标

可以使用BULK COLLECT批处理子句可以一次性将游标中的结果集保存到集合中,这样就可以在集合中进行前进和后退处理。

declare

type depttab_type is table of dept%ROWTYPE;

depttab depttab_type;

cursor deptcur is select * from dept;

begin

open deptcur;

fetch deptcur BULK COLLECT INTO depttab;

for i IN 1 ..depttab.count

loop

dbms_output.put_line(depttab(i).deptno

||' '

||depttab(i).dname

||' '

||depttab(i).loc

);

end loop;

close deptcur;

end;


bulk collect into 会一次把所有数据都提取到集合中,如果数据量特别大,并且使用varray这样的具有固定元素个数的集合时,可能需要限制每次提取的行数,可以使用fetch bulk collect into limit语句提取部分数据。

declare

type dept_type is varray(4) of dept%ROWTYPE;

depttab dept_type

cursor dept_cursor

is

select * from dept;

v_rows int :=4;

v_count int :=0;

begin

open dept_cursor;

loop

fetch dept_cursor bulk collect into depttab limit v_rows;

exit when dept_cursor%NOTFOUND;

dbms_output.put('部门名称:');

for i in 1 .. (dept_cursor%ROWCOUNT-v_count)

loop

dbms_output.put(depttab(i).dname ||' ');

end loop;

dbms_output.new_line;

v_count:=dept_cursor%ROWCOUNT;

end loop;

close dept_cursor;

end;



操纵游标数据

1.loop循环

declare

dept_row dept%ROWTYPE

cursor dept_cursor is select * from dept;

begin

open dept_cursor;

loop

fetch dept_cursor into dept_row;

exit when dept_cursor%NOTFOUND;

dbms_output.put_line('部门名称:'||dept_row.dname);

end loop;

close dept_cursor;

end;

2.while循环

declare

dept_row dept%ROWTYPE;

cursor dept_cursor is select * from dept;

begin

open dept_cursor;

fetch dept_cursor into dept_row;

while dept_cursor%FOUND loop

dbms_output.put_line('部门名称:'||dept_row.dname);

fetch dept_cursor into dept_row;

end loop;

close dept_cursor;

end;

3.游标for循环

declare

cursor dept_cursor is select * from dept;

begin

for dept_row in dept_cursor loop

dbms_output.put_line('部门名称:'||dept_row.dname);

end loop;

end;


可以在需要传递参数时,可以直接在游标名后面加入参数值

for dept_row in dept_cursor(20) loop

可以直接在for语句的in子句中使用子查询,而不是显式的声明一个游标

begin

for dept_row in (select * from dept)loop

dbms_output.put_line('部门名称:'||dept_row.dname);

end loop;

end;


修改游标数据

1.在游标的声明部分添加for update子句

2.子句是在update或delete 语句 中添加where  current of 子句

1.for update

会对用select语句提取出来结果进行锁定,相当于给结果集的行加了一把互斥锁,实行行级锁定。这样其他的用户就不能对当前游标行进行修改或删除。

例子:定义select语句中使用了for update来锁定deptno 和dname这两个列。

cursor dept_cursor is select * from dept for update deptno,dname;


2.where current of

在使用for update语句锁定了表中的行后,可以在update或delete语句中使用where current of子句来得到当前游标所检索出来的行

where current of cursorname;

cursorname是当前使用for update子句的游标的名称,用来更新游标数据。

where cursor of 子句检索的游标一定要有for update子句,并且游标要被打开且至少返回一行,不然oracle会触发错误。


使用for update与where current of 子句来更新emp表中部门编号为20的员工提成

declare

cursor emp_cursor(p_deptno in number)

is

select * from emp where deptno=p_deptno for update;

begin

for emp_row in emp_cursor(20)

loop

update emp

set comm = comm * 1.12

where cursor of emp_cursor;

end loop;

commit;

end;

commit语句必须放在循环语句的后面,否则会导致游标更新或删除失败。



使用游标删除数据

declare

cursor emp_cursor(p_empno in number)

is

select * from emp where empno = p_empno for update;

begin

for emp_row in emp_cursor(7369)

loop

delete from emp where cursor of emp_cursor;

end loop;

end;


游标变量

在前面定义一个游标,就为其绑定一个查询语句,这种游标称为静态游标。游标变量是另一种类型的游标,在定义时并不绑定到具体的查询,而是可以打开任何类型兼容的查询,灵活性相当大。

在PL/SQL中,指针是使用REF作为前缀进行定义的,因此游标变量类型就是REF cursor类型。

游标变量示例

declare

type emp_type is ref cursor return emp%ROWTYPE;

emp_cur emp_type;

emp_row emp%ROWTYPE;

begin

open emp_cur for select * from emp;

loop

fetch emp_cur into emp_row;

exit when emp_cur%NOTFOUND;

dbms_output.put_line('员工名称:'||emp_row.ename);

end loop;

end;



静态游标与游标变量的一个区别是游标变量指向的是一个查询的工作区,而静态游标指向的是数据库中的一个命名的工作区。游标变量不依赖一个特定的工作区,这个工作区是动态的。当一个游标变量指向一个特定的工作区的时候,oracle会为它保留该存储空间。因此可以在运行时为游标变量赋值一个新的值,将它作为一个参数传递给本地和存储过程,使得子程序可以用一个方便的路径来集中检索数据。



声明游标变量类型

游标变量是一种引用类型,类似于C语言的指针。

REF表示为一个指针类型。

declare

type emp_type is ref cursor return emp%ROWTYPE;

type gen_type is ref cursor;

emp_type使用了return子句进行约束,又称为强类型的游标变量,任何使用这种类型的游标变量在使用fetch into 语句提取数据时,都必须要匹配return指定的数据结构。


gen_type没有使用return子句,又称为弱类型的游标变量,使用这种类型的游标变量没有与任何记录数据结构关联,使用这种类型的定义可以比强类型的游标变量提供更多的灵活性,使其可以用于任意的查询,匹配任意的记录类型。



定义游标变量

declare

type emp_type is ref cursor return emp%ROWTYPE;

type gen_type is ref cursor;

emp_cur emp_type;

gen_cur gen_type;

begin

open emp_cur for select * from emp where deptno=20;

end;

静态游标是一个指向具体结果集的常量,是一个具体的游标对象;而游标变量是一个指向游标对象的指针,它指向具体的游标对象

PGA             游标变量1           游标变量2

| |

|————————————

SGA 实际的游标对象 查询的结果


如果type语句中未指定return子句,则可以连续地打开多次,分别为其赋不同的查询select子句。

重新打开一个游标变量以前不需要关闭它,当用不同的查询语句打开同一个游标变量的时候,上一个查询将被丢弃掉。

declare

type emp_curtype is ref cursor;

emp_cur emp_curtype;

begin

open emp_cur for select * from emp;

open emp_cur for select empno from emp;

open emp_cur for select deptno from dept;

end;



处理游标变量异常

如何通过处理INVALID_CURSOR异常,将一个已经打开的游标变量赋给另一个未打开的游标变量

declare

type emp_curtype is ref cursor;

emp_cur1 emp_curtype;

emp_cur2 emp_curtype;

emp_row emp%ROWTYPE;

begin

open emp_cur1 for select * from emp where deptno=20;

fetch emp_cur1 into emp_row;

dbms_output.put_line('员工名称:'||emp_row.ename||'部门编号:'||emp_row.deptno);

fetch emp_cur2 into emp_row;

exception

when invalid_cursor then

emp_cur2:=emp_cur1;

fetch emp_cur2 into emp_row;

dbms_output.put_line('员工名称:'||emp_row.ename||'部门编号:'emp_row.deptno);

open emp_cur2 for select * from emp where deptno=30;

fetch emp_cur1 into emp_row;

dbms_output.put_line('员工名称:'||emp_row.ename||'部门编号:'||emp_row.deptno);

end;



另一个常见的异常类型rowtype_mismatch异常。

declare

type emp_curtype is ref cursor;

emp_cur emp_curtype;

emp_row emp%ROWTYPE;

dept_row dept%ROWTYPE;

begin

open emp_cur for select * from emp where deptno=20;

fetch emp_cur into dept_row;

exception

when ROWTYPE_MISMATCH then

fetch emp_cur into emp_row;

dbms_output.put_line('员工名称:'||emp_row.ename||'部门编号:'||emp_row.deptno);

end;

除了使用弱类型的游标变量定义方式,也可以使用SYS_REFCURSOR类型

declare

emp_cur SYS_REFCURSOR;

emp_row emp%ROWTYPE;

dept_row dept%ROWTYPE;

begin

open emp_cur for select * from emp where deptno=20;

fetch emp_cur into dept_row;

exception

when ROWTYPE_MISMATCH then

fetch emp_cur into emp_row;

dbms_output.put_line('员工名称:'||emp_row.ename||'部门编号:'||emp_row.deptno);

end;

 


在包中使用游标变量

create or replace package emp_data_action as

type emp_type is ref cursor return emp%ROWTYPE;

procedure getempbydeptno(emp_cur in out emp_type,p_deptno number);

end emp_data_action;


create or replace package body emp_data_action as procedure getembydeptno(emp_cur in out emp_type,p_deptno number) is emp_row emp%ROWTYPE;

begin

open emp_cur for select * from emp where deptno=p_deptno;

loop

fetch emp_cur into emp_row;

exit when emp_cur%NOTFOUND;

dbms_output.put_line('员工名称:'||emp_row.ename||'部门编号:'||emp_row.deptno);

end loop;

close emp_cur;

end;

end emp_data_action;


可以在包中声明游标类型,但是不能在包中声明游标变量

调用包中的过程:

declare

emp_cursors emp_data_action.emp_type;

begin

emp_data_action.getempbydeptno(emp_cursors,20);

end;


游标变量的限制

1.不能在包中声明游标变量

2.不能在创建表或创建视图的语句中把字段类型指定为ref cursor类型,数据库字段是不能存放游标变量值的

3.游标类型的参数不支持使用远程过程调用(RPC)将游标变量从一个服务器传递到另一个服务器。

4.不能用比较操作符来判断两个游标变量是否相等,不相等或者为NULL

5.不能为游标变量赋空值

6.不能将ref cursor 类型作为集合的元素类型,也就是说在索引表,嵌套表和变长数组中不能存放游标变量的值

7.不能将在游标中使用的游标for循环用在游标变量上,也就是说游标和游标变量不要试图互相替换。




      本文转自潘阔 51CTO博客,原文链接:http://blog.51cto.com/pankuo/1630252,如需转载请自行联系原作者





版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL高阶技能:游标的使用
SQL高阶技能:游标的使用
35 0
SQL游标使用及实例
declare my_cursor cursor scroll dynamic for select * from t_msg open my_cursor declare @name sysname fetch next from m...
828 0
PL/SQL之--游标
一、游标简介   在PL/SQL中执行SELECT、INSERT、DELETE和UPDATE语句时,ORACLE会在内存中为其分配上下文区(Context Area),也称为缓冲区。游标是指向该区的一个指针。
612 0
游标使用经典范例
A.在简单的游标中使用 FETCH获取记录下例为 authors 表中姓以字母 B 开头的行声明了一个简单的游标,并使用 FETCH NEXT 逐个提取这些行。
641 0
21114
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载