在上一文中,介绍了PLSQL中的隐式游标的用法,本文主要介绍另外一种游标的使用——显式游标。
一、显式游标声明和属性
1.1 显式游标的声明
所谓显式游标,就是我们在PLSQL的声明区,显式的去声明的游标,它在声明时已经绑定的SQL语句,将会管理该SQL语句返回的结果集,其声明格式如下:
cursor 游标名称 is SQL语句
如果声明一个管理emp表查询结果集的游标cur_emp:
cursor cur_emp is select * from emp;
定义好显示游标之后,我们在使用游标时要先进行Open:
open 游标名称
打开之后如果想要取出游标所指的数据行,使用fetch...into...
fetch 游标名 into 对象名
1.2 显式游标的属性
和隐式游标类似,显式游标也包含四个属性,如下表所示:
序号 | 属性 | 描述 |
1 | %found | 游标被打开后,如果没有执行过fetch,返回null;如果在最近的一次fetch中返回一行,则返回True,否则返回False |
2 | %isopen | 游标是打开状态返回True,否则为False |
3 | %notfound | 游标被打开后,如果没有执行过fetch,返回null;如果在最近的一次fetch中返回一行,则返回False,否则返回True |
4 | %rowcount | 其值为该游标在执行fetch语句所返回的行数。游标打开时,其初始化为0;游标每fetch一行数据,其将会在原来的基础之上加1. |
1.3 利用游标显示查询结果
假设有下表emp,我们想要打印中emp表中所有的人员的信息。如果在之前,可能需要将查询结果bulk collect into一个索引表中或者数组中,而同样可以使用游标来完成这个功能:declare
cursor cur_emp is select * from emp;
var_emp emp%rowtype;
begin
if cur_emp%isopen then --先判断游标是否已经打开
null;
else
open cur_emp; --游标已经打开
end if;
fetch cur_emp into var_emp; --先要fetch游标一行数据给var_emp,以保证cur_emp%found为true
while cur_emp%found loop --采用while循环获取游标中的数据
dbms_output.put_line('姓名:'||var_emp.ename||',薪资:'||var_emp.sal); --打印获取的数据
fetch cur_emp into var_emp; --再次获取
end loop;
close cur_emp;
end;
/
结果如下:
在上面的例子中,我们利用游标获取了select的数据集,并使用while循环将数据集进行了打印,我们总结下这个过程:
(1)在PLSQL的声明区声明游标。
(2)先利用%isopen判断游标的打开状态,如果游标没有打开,则需要open游标。
(3)fetch一行游标指向的第一行数据,以保证%found返回的结果为True。
(4)利用while循环逐行读取和显示fetch来的数据。
上述是我们使用游标的一般步骤,但是在通常情况下,我们都是利用for循环来处理游标管理的数据,因为在for循环中,第2、3步骤会自动由for循环完成,使整个过程得到极大的简化,如下所示:
declare
cursor cur_emp is select * from emp;
begin
for var_emp in cur_emp loop
dbms_output.put_line('姓名:'||var_emp.ename||',薪资:'||var_emp.sal); --打印获取的数据
end loop;
end;
/
1.4 利用游标更新数据
处理利用游标可以实现管理select语句的结果集之外,我们还可以利用游标来更新数据库里面的数据,如下面的代码,更新每个sal小于2000的雇员的sal为2000:declare
cursor cur_emp is select * from emp where sal<2000;
begin
for var_emp in cur_emp loop
update emp set sal=2000 where emp.sal=var_emp.sal;
end loop;
end;
/
上述是利用游标查询查询出来的数据,直接在表中进行更新,我们也可以通过游标的For update of和where current of子句间接的更新表
declare
cursor cur_emp is select * from emp where sal<2000 for update of sal;
begin
for var_emp in cur_emp loop
update emp set sal=2000 where current of cur_emp;
end loop;
end;
/
for update of子句在定义游标时使用,用来表明需要更新哪些字段,而where current of 后面直接加游标名,表明游标现在所在行,通过对上述两个子句的调用,我们也可以达到上述修改表格数据的目的。
二、带参游标的使用
在上面的例子中,我们声明并使用了普通的游标用来显示或更新数据表,游标也可以声明成带参数的形式,比如下面的例子,我们声明了一个带参的游标,执行时由用户手动输入参数,用来指定获取雇员的部门,最终打印这些雇员信息:
declare
cursor cur_emp(v_dept emp.deptno%type) is select * from emp where deptno=v_dept;
begin
for var_emp in cur_emp(&v_dept) loop
dbms_output.put_line('姓名:'||var_emp.ename||',薪资:'||var_emp.sal); --打印获取的数据
end loop;
end;
/