Oracle cursor(游标)总结：从显式游标到REF游标的配置与验证

1. 为什么你写的游标总在%NOTFOUND上翻车Oracle 的 cursor游标本质上是一个指向查询结果集的指针你可以把它想成「数据库帮你把 SELECT 结果先缓存成一个可逐行读取的容器」。它真正解决的问题是当结果集有几千上万行、又需要在 PL/SQL 里逐行做业务判断时一次性SELECT INTO会直接抛TOO_MANY_ROWS而游标能让你一行一行地取、一行一行地处理。游标分三类隐式游标DML 自动带的 SQL 游标、显式游标静态编译期就绑定 SQL、REF 游标动态运行时才绑定 SQL。前两者属于静态游标REF 游标属于动态游标这个区别决定了你能不能把「查什么表」当成参数传进存储过程。这篇面向正在写存储过程、批处理脚本的数据库开发者交付的是可以直接复制进 SQL*Plus 或 SQL Developer 跑通的游标骨架包括声明、打开、取值、关闭全流程以及%FOUND、%NOTFOUND、%ROWCOUNT三个属性的验证动作。如果你之前遇到过「循环多输出一行」「exit when位置写错导致死循环」「REF 游标在包里声明报错」下面的排障部分基本能对上号。2. 前置准备环境与 TaoToken 接入在动手写游标之前先把执行环境理清楚。游标代码本身不依赖任何外部服务但如果你想让 AI 辅助生成或审查游标逻辑可以走 TaoToken 的模型对话入口把 PL/SQL 片段贴进去让它帮你找%NOTFOUND位置问题。TaoToken 官网地址是 https://taotoken.net/?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_content API 入口是 https://taotoken.net/api 。如果你打算在脚本里批量调用模型来审查游标代码需要先去控制台创建密钥控制台https://taotoken.net/console?utm_sourcetaotoken_aicg_blog_endutm_contentconsoleutm_campaignrewriteAPI Keys 管理https://taotoken.net/api-keys?utm_sourcetaotoken_aicg_blog_endutm_contentapi-keysutm_campaignrewrite接入文档https://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_contentdocutm_campaignrewrite数据库侧的准备很简单一个能连上的 Oracle 实例11g 及以上都行一张有数据的测试表。下面统一用经典的emp表举例字段包括empno、ename、sal。执行前记得打开输出SET SERVEROUTPUT ON;注意DBMS_OUTPUT.PUT_LINE只有在SERVEROUTPUT打开时才会显示很多人写完游标看不到输出第一反应是代码错了其实是这个开关没开。3. 可复制配置三类游标的完整骨架3.1 隐式游标DML 自动管理属性挂在 SQL 上隐式游标不需要你声明任何UPDATE、DELETE、INSERT执行时 Oracle 自动创建属性通过SQL%属性访问。它的%ISOPEN永远是FALSE因为 Oracle 在执行完 DML 后立刻关闭了它。DECLARE v_empno emp.empno%TYPE : 7000; BEGIN UPDATE emp SET ename fxe WHERE empno v_empno; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || 行被更新); END IF; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE(雇员编号 || v_empno || 不存在); END IF; END; /这里的关键点是SQL%ROWCOUNT必须在 DML 之后、下一条 DML 之前读取否则会被覆盖。我见过有人在IF里先调了一次SQL%ROWCOUNT再在ELSE分支里又调一次结果第二次拿到的是 0因为中间夹了别的语句。3.2 显式游标声明、打开、取值、关闭四步走显式游标是静态的声明时就绑定了 SQL。标准四步DECLARE CURSOR emp_cur IS SELECT * FROM emp; empRecord emp%ROWTYPE; BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO empRecord; EXIT WHEN emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(empRecord.ename); END LOOP; CLOSE emp_cur; END; /EXIT WHEN必须放在FETCH之后、业务逻辑之前。原因FETCH取不到行时%NOTFOUND才变TRUE如果你把EXIT写在FETCH前面第一次循环时%NOTFOUND还是初始的FALSE会多处理一行空数据。带参数的显式游标把过滤条件参数化DECLARE CURSOR emp_cur(dest VARCHAR2) IS SELECT * FROM emp WHERE empno dest; empRecord emp%ROWTYPE; BEGIN OPEN emp_cur(7369); LOOP FETCH emp_cur INTO empRecord; EXIT WHEN emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(empRecord.ename); END LOOP; CLOSE emp_cur; END; /3.3 游标更新FOR UPDATE配合WHERE CURRENT OF当你要在遍历过程中更新「当前行」声明游标时必须加FOR UPDATE更新时用WHERE CURRENT OF 游标名这样 Oracle 会锁定活动集里的行避免并发修改。DECLARE old_sal NUMBER(4); emp_name VARCHAR2(20); CURSOR emp_cur IS SELECT ename, sal FROM emp WHERE sal 1000 FOR UPDATE OF sal; BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO emp_name, old_sal; EXIT WHEN emp_cur%NOTFOUND; UPDATE emp SET sal 1.1 * old_sal WHERE CURRENT OF emp_cur; DBMS_OUTPUT.PUT_LINE(emp_name || 更新成功); END LOOP; CLOSE emp_cur; END; /3.4 循环游标省掉 OPEN/FETCH/CLOSE 的简化写法如果你只是要遍历全部记录、不需要手动控制打开关闭用FOR ... IN循环游标最省事Oracle 自动完成打开、取值、关闭DECLARE CURSOR emp_cur IS SELECT empno, ename, sal FROM emp; BEGIN FOR empRecord IN emp_cur LOOP DBMS_OUTPUT.PUT_LINE(empRecord.empno || || empRecord.ename || || empRecord.sal); END LOOP; END; /注意empRecord是隐式声明的记录变量不需要你提前定义也不能在循环外引用。3.5 REF 游标运行时绑定 SQL 的动态游标REF 游标分两步先声明类型再声明变量。强类型带RETURN弱类型不带。DECLARE TYPE emp_cur IS REF CURSOR RETURN emp%ROWTYPE; -- 强类型 empObj emp_cur; empRecord emp%ROWTYPE; BEGIN OPEN empObj FOR SELECT * FROM emp; LOOP FETCH empObj INTO empRecord; EXIT WHEN empObj%NOTFOUND; DBMS_OUTPUT.PUT_LINE(empRecord.ename); END LOOP; CLOSE empObj; END; /弱类型就是把RETURN emp%ROWTYPE去掉这样同一个变量可以OPEN FOR不同的 SELECT。REF 游标最大的价值是可以作为存储过程的OUT参数把结果集返回给调用方这是静态游标做不到的。4. 验证请求跑一遍看属性对不对把下面这段放进 SQL Developer 执行验证%ROWCOUNT和%NOTFOUND的行为DECLARE CURSOR emp_cur IS SELECT ename FROM emp WHERE sal 5000; v_name emp.ename%TYPE; v_count NUMBER : 0; BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO v_name; EXIT WHEN emp_cur%NOTFOUND; v_count : v_count 1; DBMS_OUTPUT.PUT_LINE(第 || v_count || 行: || v_name); END LOOP; DBMS_OUTPUT.PUT_LINE(游标属性 %ROWCOUNT || emp_cur%ROWCOUNT); CLOSE emp_cur; END; /预期结果每行输出带序号最后一行打印%ROWCOUNT等于实际取到的行数。如果%ROWCOUNT比实际行数多 1说明你的EXIT WHEN位置有问题——FETCH失败那次也会让%ROWCOUNT加 1但%NOTFOUND为TRUE时你已经退出了所以正常情况不会多。再验证 REF 游标作为过程参数CREATE OR REPLACE PROCEDURE get_emp_by_dept( p_deptno IN NUMBER, p_cursor OUT SYS_REFCURSOR ) AS BEGIN OPEN p_cursor FOR SELECT empno, ename FROM emp WHERE deptno p_deptno; END; /调用时用SYS_REFCURSOR接收这是 Oracle 预定义的弱类型 REF 游标省去自己声明类型。5. 本篇常见错排查报错ORA-01001: invalid cursor通常是OPEN之前就FETCH或者CLOSE之后又FETCH。检查你的OPEN/CLOSE是否成对循环里有没有提前CLOSE。报错ORA-06550: PLS-00201: identifier SYS_REFCURSOR must be declared客户端版本太老或者你在匿名块里用了但没权限。换成自己声明的TYPE ... IS REF CURSOR即可。循环多输出一行空值EXIT WHEN写在了FETCH前面。记住顺序永远是FETCH→EXIT WHEN %NOTFOUND→ 业务逻辑。%ROWCOUNT拿到 0在 DML 之后插了别的语句才读属性。隐式游标的属性必须紧跟 DML 读取。REF 游标在包PACKAGE里声明报错这是 Oracle 的限制游标变量不能在包规范里声明只能在过程或匿名块里声明。如果你需要跨过程传递结果集用SYS_REFCURSOR作为参数类型。FOR UPDATE和 REF 游标一起用报错FOR UPDATE子句不能与游标变量一起使用这是 REF 游标的硬限制。需要锁行的话改用静态显式游标。WHERE CURRENT OF报ORA-01410: invalid ROWID游标声明时没加FOR UPDATE或者活动集被其他会话改了。确认声明语句里有FOR UPDATE OF 列名。6. 接下来怎么用如果你只是偶尔写几个游标上面这些骨架复制改改就够了。但如果你在维护几十个存储过程、需要批量审查游标逻辑比如统一检查EXIT WHEN位置、%ROWCOUNT读取时机手动看效率很低。这种场景可以走 TaoToken 的 Coding Plan把 PL/SQL 文件批量丢进去做静态审查https://taotoken.net/coding-plan?utm_sourcetaotoken_aicg_blog_endutm_contentcoding-planutm_campaignrewrite如果你更习惯在对话里逐段调试直接用模型对话入口贴代码问https://taotoken.net/chat?utm_sourcetaotoken_aicg_blog_endutm_contentmodel-chatutm_campaignrewrite接入细节和参数说明在文档里https://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_contentdocutm_campaignrewrite最后留一个我踩过的坑写循环游标时不要在里面做COMMITFOR ... IN循环游标底层用的是隐式打开的快照中途COMMIT可能导致ORA-01555: snapshot too old尤其是大表遍历时。要提交就等循环结束后统一提交。