oracle游标存储过程循环插数据_oracle游标的使用详解

Oracle数据库的游标和for循环使用 　　  　　1. 游标的概念和作用 　　游标是 sql 的一个内存工作区，由系统或用户以变量的形式定义 游标的作用就是用于临时存储从数据库中提取的数据块（结果集）。 它有一个 ，从上往下移动()，从而能够遍历每条记录。 用  来提升 SQL 执行效率，适用于 大数据处理。 　　（摘抄自https://blog.csdn.net/sigusoft_/java/article/details/）。  　　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_/java/article/details/
　　批量处理的关键字不是很了解，下次学习下在记录起来； 　　隐式游标写法最简洁明了，类似于程序中的for循环写法； 　　单条处理大概就是上面那些范例的写法。