Oracle数据库的游标和for循环使⽤
1. 游标的概念和作⽤
游标是 sql 的⼀个内存⼯作区，由系统或⽤户以变量的形式定义
游标的作⽤就是⽤于临时存储从数据库中提取的数据块（结果集）。
它有⼀个指针，从上往下移动(fetch)，从⽽能够遍历每条记录。
⽤牺牲内存来提升 SQL 执⾏效率，适⽤于⼤数据处理。
（摘抄⾃blog.csdn/qq_34745941/java/article/details/81294166）。
2.游标结构图
3.具体⽤法
游标有四⼤属性，分别是
1. “SQL%ISOPEN” ：布尔类型。判断游标是否打开
2.“SQL%FOUND”：布尔类型。判断上⼀条fetch语句是否有值，有则为true，否则为false；
3.“SQL%NOTFOUND”：布尔类型。与2相反，常⽤作退出循环的条件。
4.“SQL%ROWCOUNT”：整型。当前成功执⾏更改的数据⾏数。
3.1 静态游标
3.1.1 隐式游标
使⽤DML操作（增删改）或select……into……会⾃动创建隐式游标，名称是“sql”，该游标会⾃动声明，打开和关闭。⽆需⼈为开启或关闭。
create or replace procedure ATest(
O_Result Out VarChar2
)is
v_id staff.id%type;
begin
insert into staff(id,name) values(1,'张三');
if sql%found then
oracle游标的使用O_Result:='添加成功';
end if;
update staff set name ='李四'where id =1;
if sql%found then
O_Result:='更新成功';
end if;
delete from staff where id =1;
if sql%found then
O_Result:='删除成功';
end if;
select id into v_id from staff;
if sql%found then
O_Result:='查询成功';
end if;
if sql%isopen then
O_Result:='游标为开启状态，但不可能⾛到这⼀步'; --游标只有在执⾏上述增删改操作才会开启并⾃动关闭else
O_Result:='游标为关闭状态';
end if;
exception
when Others then
begin
O_Result:='N_SQLCODE is '||SQLCODE||' and SQLERRM is '||SQLERRM;
rollback;
end;
end;
3.1.2 显式游标
显⽰游标⼜分为不带参数和带参数两种
⽆参：
create or replace procedure ATest(
O_Result Out VarChar2
)is
v_cur_info staff%rowtype;
cursor v_cur is--声明游标为staff表的数据集
select*from staff;
begin
open v_cur; --打开游标
fetch v_cur into v_cur_info; --赋值给游标
O_Result:='ID:'||v_cur_info.id||',Name:'||v_cur_info.name;--输出值
close v_cur; --关闭游标
exception
when Others then
begin
O_Result:='N_SQLCODE is '||SQLCODE||' and SQLERRM is '||SQLERRM;
rollback;
end;
end;
输出结果: ID:1,Name：张三
带参:
create or replace procedure ATest(
O_Result Out VarChar2
)
is
v_cur_info staff%rowtype;
cursor v_cur(v_id staff.id%type) is--声明游标为staff表的数据集
select*from staff where id =v_id; --参数：v_id
begin
open v_cur(1); --打开游标
fetch v_cur into v_cur_info; --赋值给游标
O_Result:='ID:'||v_cur_info.id||',Name:'||v_cur_info.name;
close v_cur; --关闭游标
exception
when Others then
begin
O_Result:='N_SQLCODE is '||SQLCODE||' and SQLERRM is '||SQLERRM;
rollback;
end;
end;
输出结果: ID:1,Name：张三
3.2 动态游标
3.2.1 ⾃定义类型游标
⾃定义游标类型声明写法：
TYPE ref_type_name IS REF CURSOR
[RETURN return_type];
ref_type_name代表我们⾃定义类型的名称，cursor是系统默认的
return_type代表数据库表中的⼀⾏，或⼀个记录类型，是⼀个返回类型；
返回值不是必要的，⽆返回值则称为弱类型，更加灵活；有返回值称为强类型，减少错误；弱类型写法：
create or replace procedure ATest(
O_Result Out VarChar2
)is
v_cur_info staff%rowtype;
type v_cur_type is ref cursor; --⾃定义游标类型
v_cur v_cur_type;
begin
open v_cur for--打开游标并声明
select*from staff where id<5;
loop --开始循环
fetch v_cur into v_cur_info; -- 赋值
exit when v_cur%notfound; --判断没有值就退出循环
O_Result:= O_Result||chr(10)||'ID:'||v_cur_info.id||',Name:'||v_cur_info.name;
end loop;
close v_cur;
exception
when Others then
begin
O_Result:='N_SQLCODE is '||SQLCODE||' and SQLERRM is '||SQLERRM;
rollback;
end;
end;
另⼀种写法：
create or replace procedure ATest(
O_Result Out VarChar2
)is
v_sql varchar(1000);
v_param staff.id%type:=5;
v_cur_info staff%rowtype;
type v_cur_type is ref cursor; --⾃定义游标类型
v_cur v_cur_type;
begin
v_sql:='select * from staff where id <:id';
open v_cur for v_sql --打开游标并声明
using v_param; --绑定参数⽅法
loop --开始循环
fetch v_cur into v_cur_info; -- 赋值
exit when v_cur%notfound; --判断没有值就退出循环
O_Result:= O_Result||chr(10)||'ID:'||v_cur_info.id||',Name:'||v_cur_info.name;
end loop;
close v_cur;
exception
when Others then
begin
O_Result:='N_SQLCODE is '||SQLCODE||' and SQLERRM is '||SQLERRM;
rollback;
end;
end;
强类型写法：
三个注意事项：
1.强类型⽆法使⽤绑定参数⽅法
2.for后⾯必须是sql，不能是字符串，如上⾯的v_sql;
3.参数必须对应；
create or replace procedure ATest(
O_Result Out VarChar2
)is
v_cur_info staff%rowtype;
type v_cur_type is ref cursor return staff%rowtype ; --⾃定义游标类型
v_cur v_cur_type;
begin
open v_cur for--打开游标并声明
select*from staff where id <5;
loop --开始循环
fetch v_cur into v_cur_info; -- 赋值
exit when v_cur%notfound; --判断没有值就退出循环
O_Result:= O_Result||chr(10)||'ID:'||v_cur_info.id||',Name:'||v_cur_info.name;
end loop;
close v_cur;
exception
when Others then
begin
O_Result:='N_SQLCODE is '||SQLCODE||' and SQLERRM is '||SQLERRM;
rollback;
end;
end;
3.2.2 系统类型游标
简写⼿动声明⾃定义游标的过程
type v_cur_type is ref cursor return staff%rowtype ; --⾃定义游标类型
v_cur v_cur_type;
等同于 v_cur sys_refcursor;
4.效率问题
没有实际测试过，根据其他博客总结是这样：⼀般来说批量处理的速度要最好，隐式游标的次之，单条处理的最差以下是⽰例：
1、批量处理
open游标；
loop
fetch游标bulk collect into集合变量（也就是table类型哦） limit 数值; -- ⼀般 500 左右
exit when条件--（变量.count = 0，如果⽤ sql%notfound 不⾜ limit 的记录就不会被执⾏哦）close游标；
2、隐式游标
for x in (sql 语句) loop
... 逻辑处理
end loop;
3、单条处理
open游标;
loop
fetch游标into变量；
exit when条件
end loop;
close游标；
————————————————
原⽂链接：blog.csdn/qq_34745941/java/article/details/81294166
批量处理的关键字不是很了解，下次学习下在记录起来；
隐式游标写法最简洁明了，类似于程序中的for循环写法；
单条处理⼤概就是上⾯那些范例的写法。
(如果有错误，再回来改正)。
内容参考于和
------------恢复内容结束------------
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论