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

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

 

 

1. 游标的概念和作用

• 游标是 sql 的一个内存工作区，由系统或用户以变量的形式定义
• 游标的作用就是用于临时存储从数据库中提取的数据块（结果集）。
• 它有一个 指针，从上往下移动(fetch)，从而能够遍历每条记录。
• 用 牺牲内存 来提升 SQL 执行效率，适用于 大数据处理。
  
  （摘抄自https://blog.csdn.net/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
     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/qq_34745941/java/article/details/81294166

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

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

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