1. Oracle ref cursor 游标变量到底解决什么问题:从静态游标到动态结果集的场景拆解
先说结论:ref cursor(游标变量)是 Oracle PL/SQL 里唯一能把「一个还没确定 SQL 的结果集句柄」在存储过程、函数、包之间传来传去的机制。它和普通静态游标最大的区别在于——静态游标在声明那一刻就把 SQL 焊死了,而游标变量只声明「我要返回什么形状的数据」,具体查哪张表、带什么条件,等 OPEN ... FOR 那一刻才决定。
我见过太多人第一次写分页查询或者报表导出时,被静态游标卡住:一个存储过程要支持按部门查、按姓名查、按入职时间查,如果每个条件写一个游标,代码会膨胀成十几个 CURSOR c1 IS SELECT ...。而用 SYS_REFCURSOR,一个出参就能覆盖所有分支,调用方只管 FETCH,不用关心底层 SQL 长什么样。
游标变量适合谁?三类人最该掌握:一是写存储过程给 Java/MyBatis 调用的后端开发,OUT SYS_REFCURSOR 几乎是 Oracle 返回多结果集的标准姿势;二是做批量数据迁移、报表汇总的 DBA,需要把结果集 BULK COLLECT 进集合再加工;三是维护老系统的同学,看到 ORA-01000、ORA-24338 这类报错时能快速定位是游标没关还是句柄失效。
它有几个必须记住的属性:%FOUND、%NOTFOUND、%ISOPEN、%ROWCOUNT,用法和普通游标一致,但注意 %ROWCOUNT 在 BULK COLLECT 之后统计的是「已取出的行数」,不是总行数。还有一个容易踩的坑:游标变量不能用 = 比较相等或判空,也不能存进表列、嵌套表、关联数组里,这些限制在官方文档里写得很清楚,但实际写代码时经常忘。
下面这段是最小可运行骨架,你可以直接在 SQL*Plus 或 SQL Developer 里跑:
DECLARE
TYPE emp_cur_typ IS REF CURSOR RETURN employees%ROWTYPE; -- 强类型
emp_cv emp_cur_typ;
emp_rec employees%ROWTYPE;
BEGIN
OPEN emp_cv FOR SELECT * FROM employees WHERE ROWNUM <= 5;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ' - ' || emp_rec.last_name);
END LOOP;
CLOSE emp_cv;
END;
/
这段代码跑通,你就理解了「声明类型 → 声明变量 → OPEN FOR → FETCH → 关闭」这条主线。接下来所有复杂用法,都是在这条主线上加参数、加动态 SQL、加包封装。
需要区分强类型和弱类型:强类型带 RETURN 记录类型,编译期就能校验 FETCH INTO 的字段是否匹配,适合字段固定的业务表;弱类型不带返回类型(TYPE t IS REF CURSOR; 或直接用 SYS_REFCURSOR),灵活但把校验推迟到运行时,动态 SQL 场景基本只能用弱类型。选哪个不是风格问题,是「你要编译期安全还是运行时灵活」的取舍。
2. 接入前的环境准备:TaoToken 控制台拿 Key 与模型 ID 的完整路径
这一节解决「代码写好了,但调用链路上还缺一个能跑通的大模型/API 入口」的问题。很多同学在本地把 ref cursor 脚本调通后,想接一个 AI 辅助生成 SQL 或者做结果集语义分析的服务,卡在鉴权和模型 ID 配置上。下面按可跟做的顺序走一遍。
第一步,打开官网 https://taotoken.net/?utm_source=taotoken_aicg_blog_end&utm_medium=csdn&utm_campaign=rewrite&utm_content= ,注册并登录后进入控制台。控制台地址是 https://taotoken.net/console?utm_source=taotoken_aicg_blog_end&utm_content=console&utm_campaign=rewrite ,进去后左侧菜单找「API Keys」。
第二步,创建 Key。点击「新建 API Key」,起一个能区分用途的名字,比如 oracle-refcursor-dev,权限按最小化原则给,只勾选你实际要调的模型范围。创建完立刻复制保存——页面刷新后完整 Key 不再显示,只能看到前缀。这一步踩过的坑是:有人把 Key 贴进 Git 仓库的配置文件里,第二天收到泄露告警,所以养成用环境变量或本地 .env(加 .gitignore)的习惯。
第三步,确认模型 ID。在「模型对话」页面 https://taotoken.net/models?utm_source=taotoken_aicg_blog_end&utm_content=model_chat&utm_campaign=rewrite 可以看到当前可用模型列表,每个模型旁边标注了 Model ID,形如 xxx-chat 这种字符串。这个 ID 必须一字不差地写进请求体,写错就是 404 或 model not found。
第四步,如果你要做的是长期编码辅助(比如让模型帮你批量生成 PL/SQL 存储过程、审查游标关闭逻辑),建议直接看 Coding Plan 页面 https://taotoken.net/coding-plan?utm_source=taotoken_aicg_blog_end&utm_content=coding_plan&utm_campaign=rewrite ,它面向的是持续编码场景,比按次调用更适合日常开发。
第五步,接口文档在 https://taotoken.net/doc?utm_source=taotoken_aicg_blog_end&utm_content=doc&utm_campaign=rewrite ,Base URL 统一用 https://taotoken.net/api ,注意这个地址不带任何查询参数,不要自己拼 UTM 上去,否则可能被网关判为异常请求。
把三件套记牢:Base URL = https://taotoken.net/api,API Key = 控制台生成的那串,Model ID = 模型列表里复制的那个。后面第 3 节的配置文件里会原样用到这三个值。
这里补一句安全提醒:Key 只放在服务端环境变量里,前端代码、日志、报错堆栈里都不要出现完整 Key。如果怀疑泄露,控制台可以立即吊销重建,旧 Key 失效后所有请求返回 401,这也是第 5 节排障的第一个检查点。
3. 可复制配置:ref cursor 脚本 + 客户端 settings 片段一次配齐
这一节给两份东西:一份是完整的 PL/SQL 游标变量实践脚本(强类型、弱类型、动态 SQL 绑定、包封装、BULK COLLECT 全覆盖),一份是调用侧客户端的配置文件片段,路径和字段名按真实工具来,复制改值即可。
先看 PL/SQL 侧。下面这个脚本建一张测试表,然后演示五种典型用法,每段都能单独跑:
-- 准备测试数据
CREATE TABLE emp_demo (
emp_id NUMBER(6),
emp_name VARCHAR2(30),
dept_no NUMBER(4),
salary NUMBER(8,2)
);
INSERT INTO emp_demo VALUES (1,'Dan Morgan',10,5000);
INSERT INTO emp_demo VALUES (2,'Hans Forbrich',20,6200);
INSERT INTO emp_demo VALUES (3,'Caleb Small',10,4800);
INSERT INTO emp_demo VALUES (4,'Lisa Ray',30,7100);
COMMIT;
-- 用法一:强类型游标变量 + 过程传参
CREATE OR REPLACE PACKAGE emp_pkg AS
TYPE emp_cur_typ IS REF CURSOR RETURN emp_demo%ROWTYPE;
PROCEDURE show_by_dept(p_dept IN NUMBER, p_cv IN OUT emp_cur_typ);
END emp_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
PROCEDURE show_by_dept(p_dept IN NUMBER, p_cv IN OUT emp_cur_typ) IS
r emp_demo%ROWTYPE;
BEGIN
OPEN p_cv FOR SELECT * FROM emp_demo WHERE dept_no = p_dept;
LOOP
FETCH p_cv INTO r;
EXIT WHEN p_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(r.emp_id || ' | ' || r.emp_name || ' | ' || r.salary);
END LOOP;
CLOSE p_cv;
END;
END emp_pkg;
/
-- 用法二:弱类型 + 动态 SQL 绑定变量(防注入)
DECLARE
cv SYS_REFCURSOR;
r emp_demo%ROWTYPE;
v_dept NUMBER := 10;
BEGIN
OPEN cv FOR 'SELECT * FROM emp_demo WHERE dept_no = :1' USING v_dept;
LOOP
FETCH cv INTO r;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('dynamic: ' || r.emp_name);
END LOOP;
CLOSE cv;
END;
/
-- 用法三:BULK COLLECT 批量取到集合
DECLARE
TYPE name_tab IS TABLE OF emp_demo.emp_name%TYPE;
TYPE sal_tab IS TABLE OF emp_demo.salary%TYPE;
cv SYS_REFCURSOR;
names name_tab;
sals sal_tab;
BEGIN
OPEN cv FOR SELECT emp_name, salary FROM emp_demo WHERE dept_no = 10;
FETCH cv BULK COLLECT INTO names, sals;
CLOSE cv;
FOR i IN names.FIRST .. names.LAST LOOP
DBMS_OUTPUT.PUT_LINE(names(i) || ' => ' || sals(i));
END LOOP;
END;
/
调用侧配置文件,以常见的 Claude Code / Cline 这类客户端为例,settings.json 里需要写全三件套:
{
"env": {
"ANTHROPIC_BASE_URL": "https://taotoken.net/api",
"ANTHROPIC_AUTH_TOKEN": "sk-你的Key粘贴在这里",
"ANTHROPIC_MODEL": "你的ModelID"
}
}
如果你用的是 Codex 风格的 auth.json,结构类似:
{
"base_url": "https://taotoken.net/api",
"api_key": "sk-你的Key粘贴在这里",
"model": "你的ModelID"
}
注意 ANTHROPIC_BASE_URL 后面不要加 /v1 之类的后缀,也不要带 UTM 参数,网关按原样匹配。Key 建议从环境变量注入,配置文件里写占位符,提交前用 git diff 检查一遍。
4. 验证请求与成功结果:从 SQL*Plus 输出到 API 返回逐项对照
配置写完必须验证,不然你永远不知道是 SQL 写错了还是 Key 配错了。分两层验证:先验 PL/SQL 脚本,再验 API 连通性。
第一层,SQL*Plus 里执行:
SET SERVEROUTPUT ON;
EXEC emp_pkg.show_by_dept(10, :dummy);
等等,IN OUT 参数不能直接 EXEC 传字面量,正确写法是匿名块:
SET SERVEROUTPUT ON;
DECLARE
cv emp_pkg.emp_cur_typ;
BEGIN
emp_pkg.show_by_dept(10, cv);
END;
/
预期输出:
1 | Dan Morgan | 5000
3 | Caleb Small | 4800
如果只看到 PL/SQL procedure successfully completed. 但没有数据行,先检查 SET SERVEROUTPUT ON 是否生效,再检查 dept_no = 10 是否真有数据。动态 SQL 那段预期输出 dynamic: Dan Morgan 和 dynamic: Caleb Small,BULK COLLECT 段预期两行 名字 => 薪资。
第二层,验证 API 连通性。用 curl 发一个最小请求:
curl -s https://taotoken.net/api/v1/messages \
-H "Content-Type: application/json" \
-H "x-api-key: $ANTHROPIC_AUTH_TOKEN" \
-H "anthropic-version: 2023-06-01" \
-d '{
"model": "'"$ANTHROPIC_MODEL"'",
"max_tokens": 64,
"messages": [{"role":"user","content":"回复 OK 两个字母"}]
}'
成功时返回 JSON 里 content[0].text 字段有内容,stop_reason 为 end_turn。如果返回 401,说明 Key 不对或没带 x-api-key 头;返回 404 且提示 model 不存在,说明 Model ID 抄错了;返回 local proxy failed 这类字样,说明 Base URL 写成了本地代理地址,改回 https://taotoken.net/api。
再补一个「结果集语义校验」的实用场景:把 FETCH 出来的前几行拼成文本,发给模型让它判断字段映射是否符合预期。请求体里 messages 的 content 直接放 emp_id=1, emp_name=Dan Morgan, salary=5000 这种字符串,模型返回「字段顺序与表定义一致」就说明你的 FETCH INTO 变量顺序没写反。这一步能提前发现「强类型游标 %ROWTYPE 和手写 RECORD 字段顺序不一致」的隐蔽 bug。
验证通过的标准很明确:SQL 侧看到预期行数和内容,API 侧拿到 200 且响应体结构完整。两个都过,才进入下一节排错。
5. 常见报错排查:ORA-01000、ORA-24338、401 与 reading choices 逐条对照
这一节按「报错原文 → 根因 → 修法」组织,都是真实会撞上的。
ORA-01000: maximum open cursors exceeded。根因是游标变量 OPEN 了没 CLOSE,尤其在异常分支里。修法:把 CLOSE 放进 EXCEPTION 块或者用 BEGIN ... EXCEPTION WHEN OTHERS THEN IF cv%ISOPEN THEN CLOSE cv; END IF; RAISE; END; 包起来。另外检查 OPEN_FOR 循环里是否重复 OPEN 同一个变量——游标变量重复 OPEN 而不先 CLOSE 会直接报 ORA-06511。
ORA-24338: statement handle not executed。典型场景是对一个没 OPEN 的游标变量执行 FETCH,或者 OPEN 之后已经 CLOSE 了又 FETCH。修法:FETCH 前用 IF cv%ISOPEN THEN 判断,或者检查控制流是否在异常里提前 CLOSE 了。还有一种隐蔽情况:把游标变量作为 OUT 参数从子过程返回,子过程里 OPEN 了但父过程以为没开,直接 FETCH 报这个错。
ORA-06502: PL/SQL: numeric or value error。FETCH INTO 的变量类型和查询列不匹配,比如 salary NUMBER(8,2) 塞进 VARCHAR2(10)。强类型游标用 %ROWTYPE 能避免大部分,弱类型 + 手写 RECORD 最容易中招。修法:FETCH INTO 的变量用 表名%ROWTYPE 或 列名%TYPE 声明。
401 Unauthorized。API 侧鉴权失败。检查顺序:Key 是否完整(有没有复制时漏字符)、请求头字段名是否是 x-api-key(不是 Authorization: Bearer)、Key 是否已被吊销。控制台重新生成一个对比测试最快。
local proxy failed / connection refused。Base URL 被写成了 http://127.0.0.1:xxxx 之类的本地地址,或者环境变量里残留了旧代理配置。修法:echo $ANTHROPIC_BASE_URL 确认输出是 https://taotoken.net/api,检查 shell 的 http_proxy/https_proxy 是否指向了不可达地址,清掉再试。
reading choices / unexpected response shape。客户端解析响应体时字段对不上,常见于把 OpenAI 格式的响应喂给 Anthropic 格式的解析器。修法:确认请求路径是 /v1/messages 而不是 /v1/chat/completions,请求体用 messages + max_tokens 结构,响应解析取 content[0].text。
OAuth 相关报错(invalid_grant / token expired)。如果你用的是带 OAuth 流程的客户端,token 过期后需要重新授权。检查客户端配置里是否同时填了 API Key 和 OAuth token,两者冲突时优先走 OAuth 会失败。修法:清掉 OAuth 缓存,只用 API Key 模式。
排查顺序建议固定成:先看报错码属于 SQL 层还是 HTTP 层,SQL 层查 %ISOPEN 和变量类型,HTTP 层查三件套(Base URL、Key、Model ID)。每次只改一个变量,改完立刻重跑验证命令,别一次改三处然后不知道是哪处生效。
6. 长期编码与 Agent 场景的落地建议:把 ref cursor 脚本沉淀成可复用资产
脚本跑通、报错排完,最后一步是让它变成团队能复用的东西,而不是躺在某个人的 SQL 窗口里。
第一,把第 3 节的包封装抽成模板。emp_pkg 的结构可以泛化成「入参过滤条件 + OUT SYS_REFCURSOR」的标准签名,新业务表照着改表名和字段即可。强类型游标适合字段稳定的核心表,弱类型留给动态报表。模板里固定带上异常块关闭游标的写法,从源头堵住 ORA-01000。
第二,动态 SQL 一律用绑定变量。第 3 节用法二里的 :1 不是可选项,字符串拼接 '... WHERE dept_no = ' || v_dept 在部门号来自外部输入时就是注入入口。绑定变量还能让 Oracle 复用执行计划,批量场景下性能差异很明显。
第三,如果你要让 AI 辅助生成或审查这类 PL/SQL,走 Coding Plan 比单次对话更划算,地址是 https://taotoken.net/coding-plan?utm_source=taotoken_aicg_blog_end&utm_content=coding_plan&utm_campaign=rewrite 。把「游标必须关闭」「动态 SQL 必须绑定」「强类型优先」写成提示词里的硬约束,生成出来的代码质量会稳定很多。
第四,Key 管理做成流程。开发、测试、生产各用独立 Key,权限按模型范围最小化,泄露时只吊销对应环境的那把。接入文档在 https://taotoken.net/doc?utm_source=taotoken_aicg_blog_end&utm_content=doc&utm_campaign=rewrite 有完整的鉴权和错误码说明,排障时先翻文档比瞎试快。
最后给一个我实际用过的检查清单,贴在团队 wiki 里:每个 OPEN 是否配对 CLOSE、FETCH 前是否判 %ISOPEN、动态 SQL 是否全绑定、FETCH INTO 变量是否用 %TYPE/%ROWTYPE、API 三件套是否从环境变量读取。这五条过一遍,ref cursor 相关的线上问题能少八成。



被折叠的 条评论
为什么被折叠?



