Oracle 游标使用全解：从显式游标到游标变量，一份可复用的配置骨架

1. 为什么你的 PL/SQL 里游标总是写不对写 Oracle 存储过程的人几乎都绕不开游标这个话题。它本质上就是一块指向查询结果集的指针让你能一行一行地处理数据而不是一次性把几万条记录全塞进内存。日常做批处理、跑对账、给历史数据打补丁游标出现的频率比SELECT INTO高得多。但实际开发里游标翻车的方式就那么几种显式游标忘了CLOSE跑几次会话就报ORA-01000: maximum open cursors exceededFETCH循环里EXIT WHEN写反了结果死循环隐式游标的SQL%ROWCOUNT在SELECT INTO之后拿到的是 1 而不是真实行数导致判断逻辑全错还有REF CURSOR传参时类型对不上编译期不报错运行期才炸。这篇就按「显式游标 → 隐式游标 → 游标变量 REF CURSOR」这条线把可复制的声明、循环、异常处理骨架一次性给全再配上 SQL*Plus / SQL Developer 里逐步验证的动作。你照着敲一遍基本就能把游标选型和调试方法吃透。适合已经会写基础 PL/SQL、但游标用得不够稳的开发者也适合正在准备 OCP 或者接手老存储过程的人。2. 前置准备环境与 TaoToken 接入游标代码本身不依赖任何外部服务但如果你想在写存储过程时顺手用大模型帮你检查语法、生成测试数据、或者解释一段复杂的分析函数可以先把 TaoToken 的接入配好。它兼容 OpenAI 风格的接口改个base_url就能用。官网地址是 https://taotoken.net/?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_content API 端点是 https://taotoken.net/api 。注册后在控制台创建 API Key地址是 https://taotoken.net/console/api-keys?utm_sourcetaotoken_aicg_blog_endutm_contentapi-keysutm_campaignrewrite 。拿到 Key 之后如果你用 Python 脚本批量生成测试 SQL可以这样配from openai import OpenAI client OpenAI( api_key你的TaoToken密钥, base_urlhttps://taotoken.net/api ) resp client.chat.completions.create( modelclaude-sonnet-4-20250514, messages[ {role: user, content: 写一段 Oracle PL/SQL用显式游标遍历 emp 表并打印 ename} ] ) print(resp.choices[0].message.content)如果你更习惯在编辑器里直接对话模型对话入口在 https://taotoken.net/model-chat?utm_sourcetaotoken_aicg_blog_endutm_contentmodel-chatutm_campaignrewrite 把游标报错贴进去让它帮你定位比翻文档快。长期写存储过程、需要 Agent 辅助的可以看 Coding Plan https://taotoken.net/coding-plan?utm_sourcetaotoken_aicg_blog_endutm_contentcoding-planutm_campaignrewrite 。接入文档在 https://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_contentdocutm_campaignrewrite 。注意TaoToken 只是帮你写代码和排错的辅助工具游标逻辑本身还是要在 Oracle 里跑通才算数。3. 显式游标声明、打开、提取、关闭四步骨架显式游标是你自己CURSOR ... IS ...声明出来的控制权完全在你手里。标准四步是DECLARE → OPEN → FETCH → CLOSE少一步都会出问题。3.1 基础 FETCH 循环骨架先建一张测试表后面所有例子都基于它CREATE TABLE emp_test AS SELECT empno, ename, job, sal, deptno, hiredate, comm FROM emp;然后是最经典的FETCH循环注意EXIT WHEN必须放在FETCH之后、处理逻辑之前DECLARE CURSOR c_job IS SELECT empno, ename, job, sal FROM emp_test WHERE job MANAGER; r_job c_job%ROWTYPE; BEGIN OPEN c_job; LOOP FETCH c_job INTO r_job; EXIT WHEN c_job%NOTFOUND; DBMS_OUTPUT.PUT_LINE(r_job.empno || - || r_job.ename || - || r_job.sal); END LOOP; CLOSE c_job; END; /这里c_job%ROWTYPE是游标行类型字段跟SELECT列表一一对应。%NOTFOUND在FETCH没取到行时为TRUE所以EXIT WHEN写在FETCH后面。如果你把EXIT写在FETCH前面第一次循环就会因为初始状态退出一行都处理不了。3.2 FOR 循环游标最省心的写法FOR循环游标把OPEN / FETCH / CLOSE全包了你只需要写循环体。它自动声明循环变量自动判断结束自动关闭游标BEGIN FOR r_job IN (SELECT empno, ename, job, sal FROM emp_test WHERE job MANAGER) LOOP DBMS_OUTPUT.PUT_LINE(r_job.empno || - || r_job.ename || - || r_job.job); END LOOP; END; /也可以先声明游标再在FOR里引用DECLARE CURSOR c_job IS SELECT empno, ename, job, sal FROM emp_test WHERE job MANAGER; BEGIN FOR r_job IN c_job LOOP DBMS_OUTPUT.PUT_LINE(r_job.ename || 工资 || r_job.sal); END LOOP; END; /实测下来日常批处理优先用FOR循环游标代码短、不容易漏CLOSE。只有需要手动控制提取节奏比如只取前 N 行、或者中途根据条件跳过时才用显式FETCH。3.3 带参数的游标游标可以带参数声明时写形参FOR循环或OPEN时传实参DECLARE CURSOR c_dept(p_deptno NUMBER) IS SELECT empno, ename, sal FROM emp_test WHERE deptno p_deptno; BEGIN FOR r IN c_dept(20) LOOP DBMS_OUTPUT.PUT_LINE(员工号 || r.empno || 姓名 || r.ename || 工资 || r.sal); END LOOP; END; /参数默认是IN模式可以写DEFAULT值。带参数的游标在复用性上比硬编码WHERE条件强很多一个游标能服务多个部门、多个工种。3.4 更新游标WHERE CURRENT OF当你需要在遍历的同时更新当前行用FOR UPDATE OF 列名声明游标然后用WHERE CURRENT OF 游标名定位DECLARE CURSOR c_upd IS SELECT empno, ename, sal FROM emp_test FOR UPDATE OF sal; v_new_sal emp_test.sal%TYPE; BEGIN FOR r IN c_upd LOOP IF r.sal 1500 THEN v_new_sal : r.sal * 1.2; ELSIF r.sal 2000 THEN v_new_sal : r.sal * 1.5; ELSE v_new_sal : r.sal * 2; END IF; UPDATE emp_test SET sal v_new_sal WHERE CURRENT OF c_upd; DBMS_OUTPUT.PUT_LINE(r.ename || 原工资 || r.sal || 新工资 || v_new_sal); END LOOP; COMMIT; END; /WHERE CURRENT OF比用主键再查一次快因为它直接定位到游标当前指向的行。但要注意FOR UPDATE会加行锁事务不提交别人改不了这些行批处理量大时别一次性锁太多。4. 隐式游标SQL% 属性的正确打开方式每次执行UPDATE / DELETE / INSERT / SELECT INTOOracle 都会自动开一个隐式游标名字固定叫SQL。你可以通过SQL%FOUND、SQL%NOTFOUND、SQL%ROWCOUNT、SQL%ISOPEN观察它的状态。4.1 观察 UPDATE 的隐式游标属性BEGIN UPDATE emp_test SET ename ALEARK WHERE empno 7469; IF SQL%ISOPEN THEN DBMS_OUTPUT.PUT_LINE(游标打开中); ELSE DBMS_OUTPUT.PUT_LINE(游标已关闭); END IF; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(影响了有效行); ELSE DBMS_OUTPUT.PUT_LINE(没有匹配行); END IF; DBMS_OUTPUT.PUT_LINE(影响行数: || SQL%ROWCOUNT); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(没有数据); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(返回行过多); END; /关键点隐式游标的SQL%ISOPEN永远是FALSE因为 Oracle 在执行完 SQL 后立刻自动关闭了。所以别指望用SQL%ISOPEN判断「游标还开着没」它只会告诉你「已经关了」。4.2 SELECT INTO 与 SQL%ROWCOUNT 的坑DECLARE v_empno emp_test.empno%TYPE; v_ename emp_test.ename%TYPE; BEGIN SELECT empno, ename INTO v_empno, v_ename FROM emp_test WHERE empno 7499; DBMS_OUTPUT.PUT_LINE(取到: || v_empno || / || v_ename); DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || SQL%ROWCOUNT); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(没有匹配行); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(匹配行超过一行); END; /SELECT INTO成功时SQL%ROWCOUNT是 1不是查询实际返回的行数。因为SELECT INTO要求恰好一行多了少了都抛异常。所以别用SQL%ROWCOUNT去统计SELECT的行数那是UPDATE / DELETE / INSERT的活儿。4.3 隐式游标 vs 显式游标选型维度隐式游标显式游标声明不需要自动创建需要CURSOR ... IS控制自动打开关闭手动OPEN / CLOSE适用单行 DML、SELECT INTO多行遍历、批处理属性SQL%FOUND等游标名%FOUND等风险SELECT INTO多行报错忘CLOSE导致游标泄漏简单判断要遍历多行就用显式游标或FOR循环只操作一行、或者只想知道影响了几行用隐式游标。5. REF CURSOR 游标变量把结果集传给调用方REF CURSOR是游标变量跟前面静态游标最大的区别是它可以在运行期动态关联不同的查询还能作为参数在存储过程之间传递。典型场景是存储过程返回一个结果集给上层应用。5.1 强类型与弱类型 REF CURSORDECLARE TYPE t_emp_cur IS REF CURSOR RETURN emp_test%ROWTYPE; -- 强类型 TYPE t_any_cur IS REF CURSOR; -- 弱类型 v_cur t_emp_cur; v_row emp_test%ROWTYPE; BEGIN OPEN v_cur FOR SELECT * FROM emp_test WHERE deptno 20; LOOP FETCH v_cur INTO v_row; EXIT WHEN v_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_row.ename || - || v_row.sal); END LOOP; CLOSE v_cur; END; /强类型REF CURSOR绑定了返回行类型编译期就能检查字段是否匹配弱类型更灵活但运行期才报错。日常封装存储过程返回结果集用弱类型SYS_REFCURSOR最省事。5.2 存储过程返回 SYS_REFCURSORCREATE OR REPLACE PROCEDURE get_emp_by_dept( p_deptno IN NUMBER, p_cur OUT SYS_REFCURSOR ) AS BEGIN OPEN p_cur FOR SELECT empno, ename, job, sal FROM emp_test WHERE deptno p_deptno; END; /调用方在 PL/SQL 里这样接DECLARE v_cur SYS_REFCURSOR; v_empno emp_test.empno%TYPE; v_ename emp_test.ename%TYPE; v_sal emp_test.sal%TYPE; BEGIN get_emp_by_dept(20, v_cur); LOOP FETCH v_cur INTO v_empno, v_ename, v_sal; EXIT WHEN v_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno || || v_ename || || v_sal); END LOOP; CLOSE v_cur; END; /在 SQL*Plus 里可以直接用VARIABLE接VARIABLE rc REFCURSOR; EXEC get_emp_by_dept(20, :rc); PRINT rc;PRINT rc会把结果集直接打出来这是验证REF CURSOR最快的方式。5.3 动态 SQL 配合 REF CURSOR当查询条件在运行期才能确定时用OPEN ... FOR拼字符串DECLARE v_cur SYS_REFCURSOR; v_sql VARCHAR2(1000); v_job VARCHAR2(20) : CLERK; v_row emp_test%ROWTYPE; BEGIN v_sql : SELECT * FROM emp_test WHERE job :1; OPEN v_cur FOR v_sql USING v_job; LOOP FETCH v_cur INTO v_row; EXIT WHEN v_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_row.ename || || v_row.job); END LOOP; CLOSE v_cur; END; /用绑定变量:1而不是字符串拼接能避免 SQL 注入也能让 Oracle 复用执行计划。6. 在 SQL*Plus / SQL Developer 中逐步验证写完游标别急着上生产先在工具里跑通。SQL*Plus 里记得先开输出SET SERVEROUTPUT ON SIZE UNLIMITED;然后逐段粘贴执行。SQL Developer 里按F5运行脚本或者选中代码块按CtrlEnter。如果DBMS_OUTPUT没显示检查「View → DBMS Output」窗口有没有打开以及连接是否勾选了「Enable DBMS Output」。验证REF CURSOR时SQL Developer 里用「Run as Script」配合VARIABLE和PRINT最直观。如果结果集为空先单独跑一遍SELECT确认数据存在再排查游标条件。7. 本篇常见错误排查ORA-01000: maximum open cursors exceeded显式游标忘了CLOSE或者异常路径里没关。用FOR循环游标能规避大部分必须手动管理时把CLOSE放进EXCEPTION块或者用BEGIN ... EXCEPTION ... END包住。ORA-06550 / PLS-00382: expression is of wrong typeFETCH的变量类型跟游标SELECT列表不匹配。用%ROWTYPE或者逐个字段对齐类型。循环体一次都不执行EXIT WHEN写在了FETCH前面或者%NOTFOUND判断反了。记住顺序是FETCH → EXIT WHEN %NOTFOUND → 处理。SQL%ROWCOUNT 拿到 0在SELECT INTO之后取SQL%ROWCOUNT或者 DML 没匹配到行。SELECT INTO用异常处理判断DML 用SQL%ROWCOUNT判断。REF CURSOR 传给调用方后取不到数据存储过程里OPEN之后没返回就CLOSE了或者调用方FETCH的变量类型跟SELECT列表不一致。检查OPEN ... FOR的查询列数和类型。游标参数传 NULL 导致查不到数据WHERE deptno p_deptno在p_deptno为NULL时永远不成立。需要处理NULL就用WHERE (p_deptno IS NULL OR deptno p_deptno)。8. 继续深入把游标用进真实项目游标本身不难难的是在复杂存储过程里管好生命周期和异常路径。我的习惯是能用FOR循环游标就不手写OPEN / FETCH / CLOSE必须手动控制时把CLOSE放在EXCEPTION块里兜底REF CURSOR只在跨过程返回结果集时用别为了「灵活」到处传。如果你在写游标时遇到拿不准的语法或者报错可以把代码贴到模型对话里让它帮你过一遍 https://taotoken.net/model-chat?utm_sourcetaotoken_aicg_blog_endutm_contentmodel-chatutm_campaignrewrite 。需要批量生成测试数据、或者把老存储过程改写成FOR循环游标用 Coding Plan 配合 Agent 会省不少时间 https://taotoken.net/coding-plan?utm_sourcetaotoken_aicg_blog_endutm_contentcoding-planutm_campaignrewrite 。API Key 在控制台随时创建 https://taotoken.net/console/api-keys?utm_sourcetaotoken_aicg_blog_endutm_contentapi-keysutm_campaignrewrite 接入细节看文档 https://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_contentdocutm_campaignrewrite 。最后留一个实操建议把emp_test表复制一份把上面每段代码都跑一遍然后故意把EXIT WHEN挪到FETCH前面看看输出有什么变化。这种「改坏再修好」的练习比只看文档记得牢。