游标
游标可以简单理解为指针,可实现对一组数据的逐条批量化操作
游标使用流程
语法格式如下:
declare
--游标声明
cursor 游标名 is select语句;
--变量声明
d 数据类型;
begin
--打开游标
open 游标名;
--将游标指向的一行数据赋值给变量
fetch 游标名 into 变量名;
/*
用户可在此处自由编辑语句
*/
--关闭游标
close 游标名;
end;
简单示例如下:
--使用游标打印dept表中全部数据
declare
a dept%rowtype;
cursor i is select * from dept;
begin
open i;
loop
fetch i into a;
--%notfound为游标属性稍后会在下方做详细解释
exit when i%notfound;
dbms_output.put_line(a.deptno||','||a.dname||','||a.loc);
end loop;
close i;
end;
游标属性
- %notfound——如果游标指向的数据为空,那么返回false,否则返回true。在fetch语句前使用该数据指向数据为空返回null ;隐性游标中该属性表示无行被影响时为TRUE
- %found——如果游标指向的数据不为空,那么返回true,否则返回false。在fetch语句前使用该数据指向数据为空返回false ;隐性游标中该属性表示一行或多行被影响时为TRUE
- %rowcount——游标指向的缓冲区(结果集)的数据条数;隐性右边中表示影响的条数
- %isopen——判断游标是否打开,打开返回true,关闭返回false;隐性游标中该属性始终返回false语句
显性游标
显性游标可以理解为用户需自行声明、打开、关闭的游标
带参数游标
可以从游标外部接收参数的游标,类似于函数。
示例如下:
declare
a number:=&请输入部门号;
b dept%rowtype;
cursor e(a dept.deptno%type) is select * from dept where deptno=a;
begin
open e(a);
fetch e into b;
dbms_output.put_line(b.deptno||','||b.dname||','||b.loc);
close e;
end;
隐性游标
用户在pl/sql中使用DML(增、删、改)自动创建,自动声明、打开、关闭,其名为SQL
示例如下:
begin
delete from dept where deptno=10;
if SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('影响了'||SQL%ROWCOUNT||'行');
ELSE
DBMS_OUTPUT.PUT_LINE('无数据被影响');
END IF;
IF SQL%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('开');
ELSE
DBMS_OUTPUT.PUT_LINE('关');
END IF;
end;
游标与循环
- while
while循环需写两次fetch,第一条fetch确保能进入循环,第二条fetch确保表中数据循环输出而不是只循环输出一条数据。
--循环输出dept表数据
/*
begin...end块内执行顺序详解:打开游标d,此时游标%found属性返回false,执行到第一条fetch d into a,游标d指向(select * from dept)中第一条数据并将改行数据赋给a,此时%notfound属性为true,因为d%found为true进入while循环,输出a.deptno||','||a.dname||','||a.loc,执行fetch d into a;将第二条数据赋值给a,回到while d%found loop判断是否进入循环即d%found返回的为true或false,为true继续循环,为false结束循环,关闭游标
*/
declare
cursor d is select * from dept;
a dept%rowtype;
begin
open d;
fetch d into a;
while d%found loop
dbms_output.put_line(a.deptno||','||a.dname||','||a.loc);
fetch d into a;
end loop;
close d;
end;
- loop
--循环输出dept表数据
declare
cursor d is select * from dept;
a dept%rowtype;
begin
open d;
loop
fetch d into a;
exit when d%notfound;
dbms_output.put_line(a.deptno||','||a.dname||','||a.loc);
end loop;
close d;
end;
- for
for循环自动打开、关闭、匹配游标
示例如下:
--打印dept中所有数据
declare
cursor e is select * from dept;
begin
for d in e loop
dbms_output.put_line(d.deptno||','||d.dname||','||d.loc);
end loop;
end;
ref游标
for循环不能与ref游标使用,能与while、loop搭配使用
语法格式如下:
declare
--声明ref类型
type 类型名 is ref cursor;
--声明ref类型变量
变量名 ref类型名;
begin
--打开游标
open 游标名 for select 语句;
fetch ref类型变量名 into 变量名;
......;
--关闭游标
close 游标名;
end;
简单示例如下:
--输出dept全部数据
declare
type xx is ref cursor;
a xx;
b dept%rowtype;
begin
--打开游标
open a for select * from dept;
loop
fetch a into b;
exit when a%notfound;
DBMS_OUTPUT.PUT_LINE(b.deptno||','||b.dname||','||b.loc);
end loop;
close a;
end;
current of 游标名 与for update
使用二者实现批量更新或删除,二者出现必是成对出现。
--更新
--将emp表中所有10部门人姓名改为刘
declare
cursor s is select * from emp where deptno=10 for update;
begin
for i in s loop
update emp set ename='刘' where current of s;
end loop;
end;
--删除
--将emp表中所有10部门人姓名删除
declare
cursor s is select * from emp where deptno=10 for update;
begin
for i in s loop
delete from emp where current of s;
end loop;
end;