游标主要用于处理多行数据。
1. 隐式游标
隐式游标一般配合显示游标使用,不需要显示声明、打开或者关闭,系统内部维护的隐式游标为:sql。
游标常用属性:
- 游标名
%found
:语句影响了一行或者多行时为true; - 游标名
%notfound
: 语句没有任何影响时为true; - 游标名
%rowcount
:语句影响的行数; - 游标名
%isopen
: 游标是否打开,始终为false;
案例如下:
begin
update student set sex = '男';
if sql%found then
dbms_output.put_line('修改成功,修改了 '|| sql%rowcount ||' 条记录');
else
dbms_output.put_line('没有这个学生');
end if;
-- 提交应该放到隐式游标后面
-- commit;
end;
2. 显示游标
显示游标在PL/SQL声明部分定义查询,该查询可以返回多行数据,并对其进行处理。
实现步骤:
- 声明一个游标
- 打开游标
- 循环提取数据
- 关闭游标
案例1:无参数类型
-- 查询所有学生信息,并显示学生姓名、性别、年龄
declare
v_row student%rowtype;
-- 1. 声明游标
cursor mycursor is select * from student;
begin
-- 2. 打开游标
open mycursor;
-- 3. 循环提取数据
loop
fetch mycursor into v_row;
exit when mycursor%notfound;
dbms_output.put_line(v_row.name || '-' || v_row.sex || '-' || v_row.age);
end loop;
-- 4. 关闭游标
close mycursor;
end;
案例2:带参数
declare
v_sex varchar2(4) := '男';
v_row student%rowtype;
-- 注:参数的类型不要指定长度大小
cursor mycursor(p_sex varchar2) is select * from student where sex = p_sex;
begin
open mycursor(v_sex);
loop
fetch mycursor into v_row;
exit when mycursor%notfound;
dbms_output.put_line(v_row.name || '-' || v_row.sex || '-' || v_row.age);
end loop;
close mycursor;
end;
案例3:循环游标简化(几不需要打开游标,也不需要关闭游标)
declare
v_sex varchar2(4) := '男';
v_row student%rowtype;
-- 注:参数的类型不要指定长度大小
cursor mycursor(p_sex varchar2) is select * from student where sex = p_sex;
begin
for v_row in mycursor(v_sex) loop
dbms_output.put_line(v_row.name || '-' || v_row.sex || '-' || v_row.age);
end loop;
end;
案例4:使用显示游标更新行
允许使用游标删除或者更新记录行,但是声明游标是必须使用 select ... for update
语句。
declare
v_sex varchar2(4) := '男';
v_row student%rowtype;
-- 注:参数的类型不要指定长度大小
cursor mycursor(p_sex varchar2) is select * from student where sex = p_sex for update;
begin
open mycursor(v_sex);
loop
fetch mycursor into v_row;
exit when mycursor%notfound;
-- 更新操作
update student set age = age + 10 where current of mycursor;
end loop;
close mycursor;
end;
3. REF游标
REF游标能够处理动态执行的SQL查询。
优点:
- 能够处理动态SQL
- 在存储过程中可以作为函数
缺点:
- 不能使用for循环游标
- 不能使用游标更新行
使用步骤:
- 声明一个ref类型
- 声明游标
- 打开游标
- 循环提取游标数据
- 关闭游标
declare
v_sex varchar2(4);
-- 1. 声明ref类型
-- 强类型的ref游标
-- type mytype is ref cursor return student%rowtype;
-- 弱类型的ref游标
type mytype is ref cursor;
-- 2. 声明游标
-- 也可以直接使用系统自带的sys_refcursor声明
-- mycursor sys_refcursor;
mycursor mytype;
v_sql varchar2(200);
v_row student%rowtype;
begin
v_sql := 'select * from student';
-- 3. 使用动态sql打开游标
open mycursor for v_sql;
-- 4. 循环提取游标数据
loop
fetch mycursor into v_row;
exit when mycursor%notfound;
dbms_output.put_line(v_row.name || '-' || v_row.sex || '-' || v_row.age);
end loop;
close mycursor;
end;