1. 全栈程序员必看首页

ORACLE游标(oracle游标属性)

全栈程序员-站长 • • 未分类 • 阅读 89

ORACLE游标(oracle游标属性)文章目录1概述1.1思维导图2语法2.1基本写法（4步）2.2游标4大属性3分类3.1静态游标3.1.1隐式游标dml3.1.2显式游标cursor3.2动态游标3.2.1自定义类型refcursor3.2.2系统类型sys_refcursor4扩展4.1三种游标循环效率对比4.2实例：实际开发中，游标遍历数据1概述1.游标是什么？用来存储多条查询数据的一种数据结构（’结果集’），它有一个’指针’，从上往下移动（’fetch’），从而能够’

大家好，又见面了，我是你们的朋友全栈君。

文章目录

1 概述

1. 游标是什么？
   用来存储多条查询数据的一种数据结构（'结果集'），
   它有一个 '指针'，从上往下移动（'fetch'），从而能够 '遍历每条记录'
   
2. 优缺点
   (1) 提高 sql '执行效率'
   (2) 牺牲 '内存'

游标概念图：
在这里插入图片描述

1.1 思维导图

Oracle 游标详解

2 语法

2.1 基本写法（4步）

-- 测试基础数据
create table stu_info (
  id   number(3),
  name varchar2(30),
  sex  varchar2(2)
);

insert into stu_info(id, name, sex) values (1, '小游子', '女');
insert into stu_info(id, name, sex) values (2, '小优子', '男');
commit;

游标语法：4 个步骤，获取 学生信息表（stu_info）的记录

declare
  -- 1 声明游标
  cursor cur_stu_info is
    select * from stu_info;
  v_stu_info cur_stu_info%rowtype;
begin
  -- 2 开启游标
  open cur_stu_info;

  -- 3 获取数据（一次获取一行）
  fetch cur_stu_info
    into v_stu_info;
  dbms_output.put_line(v_stu_info.id || ' : ' || v_stu_info.name);

  -- 4 关闭游标
  close cur_stu_info;

end;

执行截图：

1 : 小游子

2.2 游标4大属性

属性            返回值类型		作用
sql%isopen      布尔型			判断游标是否 '开启'
sql%found		布尔型			判断游标是否 '获取' 到值
sql%notfound	布尔型		    判断游标是否 '没有获取' 到值（常用于 "退出循环"）
sql%rowcount	整型			    '当前' 成功执行的数据行数（非 "总记录数"）

特别说明：sql%notfound

Oracle 官方文档解释：Before the first fetch%NOTFOUND returns NULL. If fetch never executes susscessfully. the loop is never exited, because then EXIT WHEN statement executes only if it’s WHEN condition is true. To be safe. you might want to use the following EXIT statement instead:
EXIT WHEN SQL%NOTFOUND OR SQL%NOTFOUND IS NULL;

简单说，退出循环的必要条件：fetch 语句执行成功 + 第一次 fetch 的值返回 null

-- **************************************************************
-- 功能：循环读取数据
-- 核心：先后顺序 = 先 fetch ... 再 exit when *%notfound
-- ************************************************************** 
declare
  cursor cur_stu_info is
    select * from stu_info t where t.id = 9;
  v_stu_info cur_stu_info%rowtype;
begin

  open cur_stu_info;

  loop
    fetch cur_stu_info
      into v_stu_info; -- 可测试，这段 fetch 放在 %notfound 后面
  
    exit when cur_stu_info%notfound;
  
    dbms_output.put_line('该语句不会执行，因为没有 id = 9 的记录');
  
  end loop;

  close cur_stu_info;
end;

3 分类

3.1 静态游标

3.1.1 隐式游标 dml

1. 自动创建
   (1) DML
   (2) select into
   
2. 自动管理
   (1) 无需人为干预（自动声明、打开、关闭）
   (2) 默认游标名：'SQL'

演示：

declare
  v_count number;
begin
  insert into stu_info (id, name, sex) values (3, '瑶瑶', '女');
  if sql%found then
    dbms_output.put_line('插入成功!');
  end if;

  update stu_info t set t.name = '悠悠' where t.id = 3;
  if sql%found then
    dbms_output.put_line('更新成功!');
  end if;

  delete from stu_info t where t.id = 3;
  if sql%found then
    dbms_output.put_line('删除成功!');
  end if;

  select count(1) into v_count from stu_info t;
  if sql%found then
    dbms_output.put_line('总记录为： ' || v_count);
  end if;

  if sql%isopen then
    dbms_output.put_line('不可能的，永远不可能走这一步');
  else
    dbms_output.put_line('系统已自动关闭游标');
  end if;
end;

测试结果：

插入成功!
更新成功!
删除成功!
总记录为： 2
系统已自动关闭游标

3.1.2 显式游标 cursor

由关键字 cursor 声明，可带参数，也可不带参数

情况1：不带参数：同上 -> 游标语法：(4 个步骤)

情况2：带参数（声明参数值和类型）：

declare
  v_stu_info stu_info%rowtype;

  -- 步骤1: 声明游标
  cursor cur_stu_info(v_id stu_info.id%type) is
    select * from stu_info t where t.id = v_id;
begin
  -- 步骤2: 打开游标
  open cur_stu_info(1);

  -- 步骤3: 提取数据
  fetch cur_stu_info
    into v_stu_info;
  dbms_output.put_line(v_stu_info.id || ' : ' || v_stu_info.name);

  -- 步骤4: 关闭游标
  close cur_stu_info;

end;

测试结果（都一样，因为都是取得 id=1 的记录）：

1 : 小游子

3.2 动态游标

3.2.1 自定义类型 ref cursor

有两种使用情况
1. 弱类型，无 return（常用）
2. 强类型，有 return（游标的类型必须和 return 的类型完全一致）
   (1) '字段数量、字段类型、字段顺序' 均完全同 return 一致
   (2) 此时 open ... for 后只能用 sql 语句，而不能是 '字符串'

情况1：弱类型，无 return（常用）

declare
  v_sql_select varchar(200);
  v_id         scott.stu_info.id%type;
  v_name       scott.stu_info.name%type;
  type cur_stu_type is ref cursor; -- 无 return
  cur_stu_info cur_stu_type;

begin
  -- 测试
  v_id := 1;

  v_sql_select := 'SELECT t.id, t.name FROM stu_info t WHERE t.id <= :b1';

  open cur_stu_info for v_sql_select
    using v_id; -- 绑定变量（大数据处理常用优化手段）

  fetch cur_stu_info
    into v_id, v_name;

  dbms_output.put_line(v_id || ' ：' || v_name);

  close cur_stu_info;

end;

输出结果：

1 ：小游子

情况2：强类型，有 return

declare
  v_sql_select    varchar(200);
  v_stu_info_rows scott.stu_info%rowtype;
  type cur_stu_type is ref cursor return scott.stu_info%rowtype; -- 有 return 
  cur_stu_info cur_stu_type;

begin
  -- 测试
  /* v_id := 1; v_sql_select := 'SELECT t.id, t.name FROM stu_info t WHERE t.id <= :b1';*/

  open cur_stu_info for
    select t.id, t.name, t.sex from stu_info t;

  fetch cur_stu_info
    into v_stu_info_rows;

  dbms_output.put_line(v_stu_info_rows.id || ' ：' || v_stu_info_rows.name);

  close cur_stu_info;

end;

输出结果：(同弱类型一样)

1 ：小游子

3.2.2 系统类型 sys_refcursor

常用，省去了手动定义动态游标的步骤，以下效果等同：

declare
   -- type cur_stu_type is ref cursor; -- 手动定义动态游标
   -- cur_stu_info cur_stu_type;
   
   -- 声明动态游标， 这一个步骤等于上面两个步骤
   cur_stu_info sys_refcursor;  
begin
end;

4 扩展

4.1 三种游标循环效率对比

结论：一般来说 '批量处理' 的速度要最好,'隐式游标' 的次之,'单条处理' 的最差
说明：若有兴趣，可以在数据量多的表里面，分别尝试下列三种写法，并打印时间，用作比较

1. 批量处理
   open 游标;
   loop
      fetch 游标 bulk collect  
       into 集合变量（也就是 table 类型哦） limit 数值; -- 一般 500 左右
       
       exit when 条件; -- cursor.count = 0
       逻辑处理;
    end loop;
    close 游标;

2. 隐式游标
   for x in (sql 语句) loop
      逻辑处理;
   end loop;

3. 单条处理
   open  游标;
   loop
      fetch 游标 
       into 变量;
     
       exit when 条件;
       逻辑处理;
   end loop;
   close 游标;

4.2 实例：实际开发中，游标遍历数据

-- *************************************************
-- 功能：读取表 stu_info 数据
-- *************************************************
declare
  v_sql_select varchar(300);
  cur_stu_info sys_refcursor;
  type record_stu_info is record(
    v_id   scott.stu_info.id%type,
    v_name scott.stu_info.name%type);
  type table_stu_info is table of record_stu_info;
  v_stu_info_table table_stu_info;

begin
  v_sql_select := 'SELECT t.id, t.name FROM stu_info t WHERE t.id <= :b1';

  open cur_stu_info for v_sql_select
    using 3; -- 绑定变量，此处 3，仅用作测试

  loop
    fetch cur_stu_info bulk collect
      into v_stu_info_table limit 1; -- 数据量太少，仅当前测试使用哦，实际开发 建议 500 左右
    -- 此时 %notfound 不合适，count 适用
    -- 因为 可能找到数据了（found 非空值），但是小于 limit n
    exit when v_stu_info_table.count = 0; -- 退出条件！
  
    for i in v_stu_info_table.first .. v_stu_info_table.last loop
      dbms_output.put_line('序号：' || v_stu_info_table(i).v_id || ' , ' ||
                           '姓名：' || v_stu_info_table(i).v_name);
    end loop;
  end loop;

  close cur_stu_info;

exception
  when others then
    -- 异常时，仍要 关闭游标
    if cur_stu_info%isopen then
      close cur_stu_info;
    end if;
    dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

测试结果：（建议在 ‘测试窗口’ debug 看看执行步骤）

序号：1 , 姓名：小游子
序号：2 , 姓名：小优子

如果对 table 类型、record 类型有疑问，
请点击 %type、%rowtype、record，varry、table 的使用详解

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。

发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/125383.html原文链接：https://javaforall.net

赞 (0)
0 0

关于作者

全栈程序员-站长的头像

全栈程序员-站长

133.5K 文章
3 粉丝
本网站汇聚当前互联网主流语音，持续更新，欢迎关注公众号“全栈程序员社区”
上一篇 2022年4月18日 下午11:20
下一篇 2022年4月18日 下午11:20


相关推荐

  • 详解智能体应用——合同审查Agent 全栈程序员

    详解智能体应用——合同审查Agent

    详解智能体应用——合同审查Agent

    Ai探索者的头像 Ai探索者
    2026年3月16日
    2
  • 即梦AI积分这样兑换！超详细步骤教程来了～ 即梦Ai

    即梦AI积分这样兑换！超详细步骤教程来了～

    即梦AI积分这样兑换！超详细步骤教程来了～

    全栈程序员-站长的头像 全栈程序员-站长
    2026年3月12日
    2
  • 2020最新kali安装中文输入法

    2020最新kali安装中文输入法

    2020最新kali安装中文输入法2020 最新 kali 安装中文输入法 1 打开 kali 命令终端 2 安装输入法框架 fcitx3 安装输入法 4 配置 google 拼音输入法 1 打开 kali 命令终端 2 安装输入法框架 fcitx1 输入 sudoaptinsta 因为是普通用户所以需要输入登入密码进行认证 2 输入 y 进行确认 或者直接回车默认是 y3 安装完成 3 安装输入法 1 输入 sudoaptinsta googlepinyin 直接回车 默认为 y 或者输入 y 回车 2 安装完成 3 重启

    全栈程序员-站长的头像 全栈程序员-站长
    2026年3月19日
    1
  • 软件实施工程师的经验之谈（适合新手，老鸟请指正）[通俗易懂]

    软件实施工程师的经验之谈（适合新手，老鸟请指正）[通俗易懂]

    软件实施工程师的经验之谈（适合新手，老鸟请指正）[通俗易懂]干了三年实施，技术没学多少，人倒是变的圆滑多了问题1：实施干嘛的呢？说简单通俗点，开发就是研发生产电视机的，我们实施就是给买电视机的人去进行安装调试，试运行完了签验收单收款和后期的日常维护（当然，如果大公司有自己的售后服务团队就另当别论了）问题2：实施的薪资（我想大部分人都关注这个吧）以一线城市北上广为例，我在北京，第一份实施工作月薪4500，出差补助一天一百，报销路费和住宿费，不报销吃饭…

    全栈程序员-站长的头像 全栈程序员-站长
    2022年6月2日
    66
  • 详解Java异或运算符

    详解Java异或运算符

    详解Java异或运算符文章原地址目录目录 性质 应用举例 其他用途示例 异或是一种基于二进制的位运算，用符号XOR或者^表示，其运算法则是对运算符两侧数的每一个二进制位同值则取0，异值则取1.简单理解就是不进位加法，如1+1=0，0+0=0，1+0=1.Forexample:3^5=6转成二进制后就是0011^0101二号位和三号位都是异值取1末尾两个1同值取零，…

    全栈程序员-站长的头像 全栈程序员-站长
    2026年4月15日
    4
  • 布隆过滤器原理介绍「建议收藏」

    布隆过滤器原理介绍「建议收藏」

    布隆过滤器原理介绍「建议收藏」文章来着https://segmentfault.com/a/1190000002729689哈希hash原理Hash（哈希，或者散列）函数在计算机领域，尤其是数据快速查找领域，加密领域用的极广。其作用是将一个大的数据集映射到一个小的数据集上面（这些小的数据集叫做哈希值，或者散列值）。一个应用是Hashtable（散列表，也叫哈希表），是根据哈希值(Keyvalue)

    全栈程序员-站长的头像 全栈程序员-站长
    2026年4月14日
    5

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

关注全栈程序员社区公众号