Oracle 游标循环两种写法：open cursor loop fetch into 与 for in cursor loop 的配置与验证

1. 从一次数据迁移踩坑说起两种游标循环到底差在哪如果你正在做 Oracle 到其他库的迁移或者维护一套跑了多年的 PL/SQL 批处理大概率绕不开显式游标。open cursor loop fetch into和for in cursor loop这两种写法表面看只是代码风格差异实际在异常处理、资源释放、执行计划复用上完全是两回事。我见过太多迁移脚本因为混用这两种写法导致游标泄漏、结果集少一行、或者%NOTFOUND判断失效。先说结论for in cursor loop是语法糖Oracle 自动帮你做了OPEN、FETCH、EXIT WHEN %NOTFOUND、CLOSE四件事代码短、不容易漏关游标而open fetch into是手动挡你需要自己声明变量、自己控制退出条件、自己保证CLOSE被执行。手动挡灵活但每个环节都可能出错。这篇面向数据库开发和迁移场景交付可复制的游标声明、循环骨架、异常处理配置并给出执行计划与结果集一致性的验证动作。适合已经会写基本 SQL、但想在迁移或重构时把游标逻辑写扎实的读者。下面所有代码都可以直接在 SQL*Plus 或 SQL Developer 里跑我用的是 Oracle 19c 的HR示例 schema。2. 前置准备TaoToken 接入与 SQL 客户端环境在开始写游标之前先把执行环境理顺。我平时调试 PL/SQL 会用两种方式一种是在本地 SQL 客户端里直接跑另一种是通过 API 把生成的 SQL 或 PL/SQL 块发给模型做审查和改写。后者在迁移场景特别有用因为不同数据库的游标语法差异大让模型帮你做语法映射能省不少时间。如果你也想用 API 方式做 SQL 审查可以先去 TaoToken 拿一个 Key。地址是 https://taotoken.net/api 注册后在控制台创建 API Key接入文档在 https://taotoken.net/doc 。拿到 Key 之后你可以把下面这段游标代码发给模型让它帮你检查%NOTFOUND的位置是否正确、CLOSE是否在所有分支都被执行。需要说明的是TaoToken 在这里的角色是帮你做代码审查和语法迁移的辅助工具不是替代你的数据库客户端。真正的执行、执行计划查看、结果集比对还是要在 SQL*Plus 或 SQL Developer 里完成。另外如果你长期要做 PL/SQL 迁移和批量改写可以了解一下 Coding Plan适合需要反复调用模型做代码审查的场景。环境方面你需要Oracle 数据库 11g 及以上%ROWTYPE和FOR ... IN游标在 11g 都支持有HRschema 的读权限或者换成你自己的表DBMS_OUTPUT已启用否则看不到输出启用DBMS_OUTPUT的命令SET SERVEROUTPUT ON SIZE UNLIMITED;3. 可复制配置两种游标循环的完整骨架3.1 open cursor loop fetch into 手动挡写法先看手动挡。核心是四步声明游标、声明接收变量、OPEN、循环FETCH并判断%NOTFOUND、最后CLOSE。DECLARE CURSOR emp_cur IS SELECT first_name, last_name, salary FROM hr.employees WHERE department_id 50; v_first_name hr.employees.first_name%TYPE; v_last_name hr.employees.last_name%TYPE; v_salary hr.employees.salary%TYPE; v_count PLS_INTEGER : 0; BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO v_first_name, v_last_name, v_salary; EXIT WHEN emp_cur%NOTFOUND; v_count : v_count 1; DBMS_OUTPUT.PUT_LINE( v_count || : || v_first_name || || v_last_name || salary || v_salary ); END LOOP; CLOSE emp_cur; DBMS_OUTPUT.PUT_LINE(total rows || v_count); EXCEPTION WHEN OTHERS THEN IF emp_cur%ISOPEN THEN CLOSE emp_cur; END IF; DBMS_OUTPUT.PUT_LINE(error: || SQLERRM); RAISE; END; /这里有几个关键点。第一EXIT WHEN emp_cur%NOTFOUND必须放在FETCH之后、处理逻辑之前否则最后一行会被漏掉或者多处理一次。第二%NOTFOUND在FETCH之前是NULL所以不能提前判断。第三异常处理里用%ISOPEN判断游标是否还开着避免重复CLOSE报ORA-01001。如果你用%ROWTYPE接收整行写法会更简洁DECLARE CURSOR emp_cur IS SELECT first_name, last_name, salary FROM hr.employees WHERE department_id 50; v_emp emp_cur%ROWTYPE; BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO v_emp; EXIT WHEN emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp.first_name || || v_emp.last_name); END LOOP; CLOSE emp_cur; END; /注意v_emp emp_cur%ROWTYPE这种写法变量类型直接绑定游标的返回结构迁移时如果改了SELECT列表变量声明不用动这是手动挡里比较省心的一个技巧。3.2 for in cursor loop 自动挡写法自动挡就短很多BEGIN FOR v_emp IN ( SELECT first_name, last_name, salary FROM hr.employees WHERE department_id 50 ) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.first_name || || v_emp.last_name); END LOOP; END; /FOR ... IN后面可以直接跟子查询这叫匿名游标不用提前声明。循环变量v_emp是隐式声明的%ROWTYPE作用域只在循环体内。Oracle 自动处理OPEN、FETCH、EXIT WHEN %NOTFOUND、CLOSE你不需要写任何一句。如果你已经有声明好的游标也可以直接FOR v_emp IN emp_cur LOOP效果一样。两种写法在 11g 之后执行计划基本一致优化器都会做游标共享。3.3 两种写法的对照表维度open fetch intofor in cursor loop游标变量声明必须手动声明隐式声明无需声明OPEN/CLOSE必须手动写自动完成%NOTFOUND 判断必须手动写自动完成异常时游标释放需手动%ISOPEN判断自动释放循环内修改游标可以灵活不可以游标已固定代码行数多少适合场景需要精细控制、动态游标常规遍历、迁移脚本4. 验证请求与成功结果执行计划与结果集一致性写完游标只是第一步迁移场景最怕的是两种写法结果不一致。下面给出三个验证动作。4.1 结果集行数比对先跑一个基准查询拿到期望行数SELECT COUNT(*) FROM hr.employees WHERE department_id 50;假设返回 45。然后分别跑两种游标写法在循环里累加计数最后输出total rows。两次输出必须都是 45。如果手动挡输出 44大概率是EXIT WHEN位置写错了如果输出 46可能是FETCH写在了EXIT后面。4.2 执行计划查看在 SQL*Plus 里用EXPLAIN PLAN看游标对应的查询计划EXPLAIN PLAN FOR SELECT first_name, last_name, salary FROM hr.employees WHERE department_id 50; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);两种游标写法对应的查询计划应该完全一样都是对employees表的全表扫描或索引扫描。如果不一样说明你在手动挡里加了额外的WHERE或者ORDER BY需要对齐。4.3 游标泄漏检查跑完手动挡之后查一下当前会话打开的游标SELECT sql_text, cursor_type FROM v$open_cursor WHERE sid SYS_CONTEXT(USERENV, SID);如果看到emp_cur对应的 SQL 还在列表里说明CLOSE没执行到。正常情况下CLOSE之后这条记录应该消失。自动挡不需要检查Oracle 保证循环结束就释放。5. 本篇常见错排查5.1 ORA-01001: invalid cursor这个错基本出现在手动挡。原因通常是CLOSE执行了两次或者OPEN之前就FETCH。检查你的异常处理块如果WHEN OTHERS里写了CLOSE emp_cur但正常流程也CLOSE了异常触发时就会重复关闭。正确做法是用%ISOPEN判断IF emp_cur%ISOPEN THEN CLOSE emp_cur; END IF;5.2 结果集少一行手动挡里EXIT WHEN emp_cur%NOTFOUND如果写在FETCH之前第一行还没取就退出了。如果写在处理逻辑之后最后一行处理完FETCH返回%NOTFOUND为TRUE但那一行已经被处理过了不会少。少一行通常是EXIT写在了FETCH和DBMS_OUTPUT之间导致最后一行没输出。5.3 for 循环里改游标变量报错FOR v_emp IN emp_cur LOOP里的v_emp是只读的你不能在循环体里给它赋值。如果你需要修改行数据得用UPDATE ... WHERE CURRENT OF emp_cur但前提是游标声明时带了FOR UPDATE。自动挡不支持WHERE CURRENT OF这是它相比手动挡的一个硬限制。5.4 迁移到其他数据库时语法不兼容FOR ... IN子查询这种写法在 PostgreSQL 里对应FOR rec IN SELECT ... LOOP在 MySQL 里没有直接对应需要改成DECLARE ... CURSOR ... HANDLER。如果你在做跨库迁移建议先用 API 把 PL/SQL 块发给模型做语法映射接入文档在 https://taotoken.net/doc 模型对话入口在 https://taotoken.net/chat 。把两种写法的代码贴进去让它输出目标库的等价写法比手动查文档快很多。6. 按场景选型与后续动作选型其实很简单。如果你只是遍历一个固定查询的结果集不需要在循环里动态改游标直接用FOR ... IN子查询代码短、不容易漏CLOSE、迁移时也好看。如果你需要FOR UPDATE加WHERE CURRENT OF做行级更新或者需要在循环中途根据条件重新OPEN游标那就用手动挡但务必把%ISOPEN判断和异常处理写全。迁移场景还有一个坑老代码里经常用open fetch into配合%ROWCOUNT做分批提交。%ROWCOUNT在自动挡里也能用但语义是当前循环已处理的行数不是游标总行数。如果你要每 1000 行COMMIT一次两种写法都可以BEGIN FOR v_emp IN (SELECT * FROM hr.employees WHERE department_id 50) LOOP -- 处理逻辑 IF MOD(v_emp.rn, 1000) 0 THEN COMMIT; END IF; END LOOP; COMMIT; END; /注意自动挡里没有rn这个列你需要自己在子查询里加ROWNUM或者在循环里用计数器变量。手动挡直接用emp_cur%ROWCOUNT就行这是它更方便的地方。最后给一个实操建议迁移前先把两种写法各跑一遍用v$open_cursor确认没有泄漏用COUNT(*)确认行数一致用EXPLAIN PLAN确认计划一致。三个验证都过了再往生产脚本里合。如果你需要批量审查迁移脚本里的游标写法可以把脚本拆成小块发给模型做静态检查API Key 在 https://taotoken.net/api-keys 创建配合 Coding Plan 做长期迁移项目会更顺。