oracle游标循环_oracle游标循环取值

Oracle数据库的游标和for循环使用
　　 

　　1. 游标的概念和作用

　　游标是 sql 的一个内存工作区，由系统或用户以变量的形式定义
游标的作用就是用于临时存储从数据库中提取的数据块（结果集）。
它有一个 ，从上往下移动()，从而能够遍历每条记录。
用  来提升 SQL 执行效率，适用于 大数据处理。

　　（摘抄自https://blog.csdn.net/sigusoft_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
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 游标；
————————————————
原文链接：https://blog.csdn.net/sigusoft_34745941/java/article/details/81294166

　　批量处理的关键字不是很了解，下次学习下在记录起来；

　　隐式游标写法最简洁明了，类似于程序中的for循环写法；

　　单条处理大概就是上面那些范例的写法。