Oracle 游标使用全解：从显式游标到游标变量的完整实践

1. 为什么你的 PL/SQL 游标总是出问题Oracle 游标Cursor是 PL/SQL 里最容易被“会用但用不对”的语法点。它本质上是一块指向查询结果集的私有内存区域你可以把它理解成一个带指针的结果集指针每移动一行你就拿到一行数据。问题在于很多开发者在写存储过程时要么忘记关闭显式游标导致ORA-01000: maximum open cursors exceeded要么在FETCH循环里写错退出条件造成死循环要么把隐式游标的SQL%ROWCOUNT用在了错误的位置。这篇内容面向数据库开发和运维场景覆盖显式游标、隐式游标、参数化游标、FOR UPDATE更新游标以及REF CURSOR游标变量的完整流程。每一段代码都可以直接复制到 SQL*Plus 或 SQL Developer 里执行我会给出建表脚本、执行步骤和预期输出。同时游标报错往往伴随着一堆上下文信息我会说明如何通过 TaoToken 统一 Key/API 通道接入 AI 工具把报错日志和游标定义一起丢进去做辅助排查减少在文档和搜索引擎之间来回切换的时间。适合谁看写过SELECT INTO但没系统学过游标的初级开发维护老存储过程、经常被ORA-01000困扰的运维以及想搞清楚REF CURSOR到底什么时候该用的中级工程师。2. 前置准备环境与 TaoToken 通道2.1 数据库环境你需要一个可用的 Oracle 实例11g、12c、19c 都可以本文语法在 11g 及以上通用。用 SQL*Plus 或 SQL Developer 连接后先确认能执行匿名块SET SERVEROUTPUT ON SIZE UNLIMITED; BEGIN DBMS_OUTPUT.PUT_LINE(env ok); END; /如果DBMS_OUTPUT没有输出检查SET SERVEROUTPUT ON是否执行以及客户端是否开启了输出窗口。2.2 准备演示表为了不污染你的业务表我建一张独立的emp_demo结构和经典EMP表对齐CREATE TABLE emp_demo ( empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(20), job VARCHAR2(20), sal NUMBER(7,2), deptno NUMBER(2), hiredate DATE ); INSERT INTO emp_demo VALUES (7369,SMITH,CLERK,800,20,TO_DATE(1980-12-17,YYYY-MM-DD)); INSERT INTO emp_demo VALUES (7499,ALLEN,SALESMAN,1600,30,TO_DATE(1981-02-20,YYYY-MM-DD)); INSERT INTO emp_demo VALUES (7566,JONES,MANAGER,2975,20,TO_DATE(1981-04-02,YYYY-MM-DD)); INSERT INTO emp_demo VALUES (7698,BLAKE,MANAGER,2850,30,TO_DATE(1981-05-01,YYYY-MM-DD)); INSERT INTO emp_demo VALUES (7782,CLARK,MANAGER,2450,10,TO_DATE(1981-06-09,YYYY-MM-DD)); INSERT INTO emp_demo VALUES (7788,SCOTT,ANALYST,3000,20,TO_DATE(1987-04-19,YYYY-MM-DD)); INSERT INTO emp_demo VALUES (7839,KING,PRESIDENT,5000,10,TO_DATE(1981-11-17,YYYY-MM-DD)); INSERT INTO emp_demo VALUES (7844,TURNER,SALESMAN,1500,30,TO_DATE(1981-09-08,YYYY-MM-DD)); COMMIT;2.3 TaoToken 通道配置游标报错排查时我习惯把完整的报错栈、游标定义和表结构一起交给 AI 做上下文分析。TaoToken 提供统一的 Key 和 API 入口不用为每个模型单独维护一套凭证。配置方式如下# 环境变量方式避免把 Key 写进代码 export TAOTOKEN_API_KEY你的Key export TAOTOKEN_BASE_URLhttps://taotoken.net/apiKey 在控制台创建https://taotoken.net/console?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentcursor_console创建后到 API Keys 页面复制https://taotoken.net/api-keys?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentcursor_apikeys如果你用的是支持 OpenAI 兼容协议的客户端把base_url指向https://taotoken.net/api模型名按文档填写即可。接入细节参考文档https://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentcursor_doc注意Key 只放在环境变量或密钥管理服务里不要硬编码进 PL/SQL 或提交到 Git。3. 显式游标声明、打开、提取、关闭3.1 标准四步法显式游标需要你手动控制生命周期四步是DECLARE、OPEN、FETCH、CLOSE。下面这段代码遍历emp_demo中所有MANAGERSET SERVEROUTPUT ON; DECLARE CURSOR c_job IS SELECT empno, ename, job, sal FROM emp_demo WHERE job MANAGER; v_row c_job%ROWTYPE; BEGIN OPEN c_job; LOOP FETCH c_job INTO v_row; EXIT WHEN c_job%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_row.empno || - || v_row.ename || - || v_row.job || - || v_row.sal); END LOOP; CLOSE c_job; END; /执行后输出三行7566-JONES-MANAGER-2975、7698-BLAKE-MANAGER-2850、7782-CLARK-MANAGER-2450。这里的关键点是EXIT WHEN c_job%NOTFOUND必须放在FETCH之后。如果放在FETCH之前第一次循环就会因为游标还没取数据而误判退出。3.2 游标属性对照属性含义典型用途%FOUND最近一次 FETCH 是否取到行循环条件%NOTFOUND最近一次 FETCH 是否没取到行退出循环%ROWCOUNT到目前为止已取出的行数计数、分批控制%ISOPEN游标是否处于打开状态关闭前判断%ROWCOUNT在FETCH之后才有意义OPEN之后立即读它是 0。3.3 FOR 循环游标最省心的写法如果你不需要手动控制打开关闭FOR循环游标会自动完成OPEN、FETCH、CLOSE而且循环变量自动声明为%ROWTYPEBEGIN FOR r IN (SELECT empno, ename, job, sal FROM emp_demo WHERE job MANAGER) LOOP DBMS_OUTPUT.PUT_LINE(r.empno || - || r.ename || - || r.job || - || r.sal); END LOOP; END; /这种写法在只读遍历场景下应该优先使用代码短、不会忘记关闭、不会出现ORA-01000。4. 隐式游标与 SQL 属性4.1 隐式游标是什么每次执行INSERT、UPDATE、DELETE、SELECT INTO时Oracle 会自动创建一个隐式游标名字固定为SQL。你不需要声明和打开它但可以读取它的属性来判断执行结果。BEGIN UPDATE emp_demo SET ename ALEARK WHERE empno 7369; IF SQL%ISOPEN THEN DBMS_OUTPUT.PUT_LINE(opening); ELSE DBMS_OUTPUT.PUT_LINE(closing); END IF; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(游标指向了有效行); END IF; DBMS_OUTPUT.PUT_LINE(影响行数: || SQL%ROWCOUNT); ROLLBACK; END; /输出会是closing、游标指向了有效行、影响行数: 1。注意SQL%ISOPEN对隐式游标永远是FALSE因为 Oracle 在执行完语句后立即关闭了它。4.2 SELECT INTO 的属性观察SELECT INTO也是隐式游标但它的属性读取时机更微妙DECLARE v_empno emp_demo.empno%TYPE; v_ename emp_demo.ename%TYPE; BEGIN SELECT empno, ename INTO v_empno, v_ename FROM emp_demo WHERE empno 7499; DBMS_OUTPUT.PUT_LINE(rowcount || SQL%ROWCOUNT); DBMS_OUTPUT.PUT_LINE(empno || v_empno || , ename || v_ename); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(No Value); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(too many rows); END; /SELECT INTO必须恰好返回一行返回零行抛NO_DATA_FOUND返回多行抛TOO_MANY_ROWS。这两个异常必须显式处理否则会向上传播。提示SQL%ROWCOUNT在SELECT INTO成功后是 1但如果你在异常处理块里读它值可能不可靠建议在正常路径读取。5. 参数化游标与更新游标5.1 带参数的游标参数化游标让同一个游标定义适配不同的过滤条件声明时在游标名后加参数列表DECLARE CURSOR c_dept(p_deptno NUMBER) IS SELECT empno, ename, sal FROM emp_demo 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模式可以写p_deptno IN NUMBER也可以给默认值p_deptno NUMBER DEFAULT 10。参数只在OPEN时绑定一次循环中不会重新求值。5.2 FOR UPDATE 更新游标当你需要在遍历的同时更新当前行用FOR UPDATE OF 列名锁定行再用WHERE CURRENT OF 游标名定位DECLARE CURSOR c_upd IS SELECT empno, ename, sal FROM emp_demo WHERE job SALESMAN FOR UPDATE OF sal; BEGIN FOR r IN c_upd LOOP IF r.sal 2000 THEN UPDATE emp_demo SET sal r.sal * 1.1 WHERE CURRENT OF c_upd; DBMS_OUTPUT.PUT_LINE(r.ename || 原工资 || r.sal || 调整后 || (r.sal * 1.1)); END IF; END LOOP; COMMIT; END; /WHERE CURRENT OF比用主键再查一次更高效因为它直接定位游标当前指向的物理行。但要注意FOR UPDATE会持有行锁直到COMMIT或ROLLBACK事务要尽量短。5.3 用计数器控制提取行数有时候你只想处理前 N 行比如“给资格最老的两个人升职”DECLARE CURSOR c_old IS SELECT ename, hiredate FROM emp_demo ORDER BY hiredate ASC; v_count NUMBER : 2; BEGIN FOR r IN c_old LOOP EXIT WHEN v_count 0; DBMS_OUTPUT.PUT_LINE(员工 || r.ename || 入职 || TO_CHAR(r.hiredate,YYYY-MM-DD)); v_count : v_count - 1; END LOOP; END; /EXIT WHEN v_count 0放在循环体开头保证只输出两行。如果放在末尾会多输出一行。6. 游标变量 REF CURSOR6.1 强类型与弱类型REF CURSOR是游标变量可以在运行时动态指向不同的查询。强类型REF CURSOR绑定了返回类型弱类型则用SYS_REFCURSORDECLARE TYPE t_emp_cur IS REF CURSOR RETURN emp_demo%ROWTYPE; v_cur t_emp_cur; v_row emp_demo%ROWTYPE; BEGIN OPEN v_cur FOR SELECT * FROM emp_demo WHERE deptno 10; 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; /弱类型写法更灵活适合返回给调用方DECLARE v_cur SYS_REFCURSOR; v_empno emp_demo.empno%TYPE; v_ename emp_demo.ename%TYPE; BEGIN OPEN v_cur FOR SELECT empno, ename FROM emp_demo WHERE deptno 30; LOOP FETCH v_cur INTO v_empno, v_ename; EXIT WHEN v_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno || : || v_ename); END LOOP; CLOSE v_cur; END; /6.2 存储过程返回游标REF CURSOR最常见的用途是存储过程把结果集返回给应用层CREATE 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_demo WHERE deptno p_deptno ORDER BY empno; END; /调用方式VAR rc REFCURSOR; EXEC get_emp_by_dept(20, :rc); PRINT rc;在 SQL*Plus 里PRINT rc会输出结果集。应用层JDBC、OCI则通过CallableStatement注册OUT参数为OracleTypes.CURSOR来接收。注意REF CURSOR打开后必须由打开它的那一层负责关闭跨层传递时容易泄漏。存储过程返回游标给应用应用读完必须close()。7. 常见报错与排查7.1 ORA-01000 打开游标数超限原因通常是显式游标在异常路径下没有关闭。比如OPEN之后FETCH抛异常CLOSE被跳过。修复方式是用BEGIN...EXCEPTION...END包住或在异常处理里补CLOSEDECLARE CURSOR c IS SELECT * FROM emp_demo; v c%ROWTYPE; BEGIN OPEN c; BEGIN LOOP FETCH c INTO v; EXIT WHEN c%NOTFOUND; END LOOP; EXCEPTION WHEN OTHERS THEN IF c%ISOPEN THEN CLOSE c; END IF; RAISE; END; CLOSE c; END; /更彻底的做法是优先用FOR循环游标它由 Oracle 自动管理关闭。7.2 ORA-06550 / PLS-00382 类型不匹配FETCH ... INTO的变量类型必须和游标返回列兼容。用%ROWTYPE或%TYPE声明变量可以避免大部分问题DECLARE CURSOR c IS SELECT empno, ename FROM emp_demo; v_empno emp_demo.empno%TYPE; v_ename emp_demo.ename%TYPE; BEGIN OPEN c; FETCH c INTO v_empno, v_ename; CLOSE c; END; /如果游标返回 3 列但你只INTO2 个变量会报PLS-00394: wrong number of values in the INTO list。7.3 ORA-01002 fetch out of sequence这个错误通常出现在FOR UPDATE游标里你在COMMIT之后继续FETCH。COMMIT会释放行锁并关闭游标上下文后续FETCH就报错。解决办法是把COMMIT放到循环结束后或者改用分批提交并重新打开游标。7.4 用 TaoToken 辅助定位游标报错往往只给一个错误码上下文需要你自己拼。我的做法是把报错码、游标定义、表 DDL 和调用栈整理成一段文本通过 TaoToken 的模型对话入口提交https://taotoken.net/model-chat?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentcursor_chat比如输入“ORA-01002 出现在 FOR UPDATE 游标循环中循环体内有 COMMIT如何改”模型会直接给出“把 COMMIT 移出循环”或“改用分批提交”的具体改法。如果你在写长期运行的编码任务或 Agent 流程Coding Plan 更适合持续调用https://taotoken.net/coding-plan?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentcursor_plan8. 把游标逻辑接进你的 AI 排查链路游标本身是数据库层的语法但排查过程经常需要跨工具SQL Developer 看执行计划、日志平台捞报错、文档站查属性含义。TaoToken 的价值在于把这些查询统一到一个 Key 和一个 API 入口下你不用为每个模型单独配置凭证也不用在多个控制台之间切换。具体操作路径先在控制台创建 Key把base_url设为https://taotoken.net/api然后在你的排查脚本或 IDE 插件里调用。模型对话入口适合交互式问“这段游标为什么死循环”Coding Plan 适合把游标审查做成自动化步骤API Keys 页面管理凭证轮换。https://taotoken.net/api-keys?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentcursor_apikeys接入文档里有完整的请求示例和参数说明https://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentcursor_doc我自己的习惯是写完一段带FOR UPDATE的游标后先把代码和表结构丢给模型做一次静态审查重点问“有没有在循环内 COMMIT”“异常路径是否关闭游标”“%ROWCOUNT 读取时机对不对”。这三个问题覆盖了八成以上的游标事故。审查通过再上测试库跑比直接在生产环境试错省事得多。