1. 大表取数为什么总在 PL/SQL 里卡住如果你写过 Oracle 的存储过程大概率遇到过这种场景一张几百万行的表用显式游标LOOP ... FETCH ... END LOOP一行一行捞跑起来像老牛拉车日志刷得慢内存还时不时报警。问题不在数据库本身而在 PL/SQL 引擎和 SQL 引擎之间的上下文切换——每 FETCH 一行就要来回切一次行数一多开销全耗在切换上。BULK COLLECT就是来解决这个的。它把查询结果一次性或分批灌进集合collection里让 PL/SQL 一次拿到一批数据而不是一行一行要。配合LIMIT还能控制每批大小避免一次性把几百万行塞进 PGA 把内存撑爆。适合谁做数据迁移、报表预聚合、批量对账、ETL 中间层的同学基本都会用到。这篇我按「建表造数 → 三种写法 → 用 TaoToken 统一 Key 让 AI 生成和校验脚本 → 执行计划与耗时对比」的顺序走一遍代码都能直接复制跑。TaoToken 在这里的作用是你手头有多个 AI 工具对话、编码插件、Agent不用每个都配一套 Key统一走一个 API 通道生成和校验 PL/SQL 脚本时省去反复切配置的麻烦。2. 前置准备TaoToken 统一 Key 与 API 通道先说清楚 TaoToken 是什么它是一个大模型 API 的统一接入层把不同模型的调用收敛到一个 Key、一个 Base URL 上。对写 Oracle 脚本的人来说价值在于——你让 AI 帮你生成BULK COLLECT模板、检查FORALL语法、解释执行计划时不用在多个工具里维护多份密钥。接入信息如下官网入口https://taotoken.net/?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentAPI Base URLhttps://taotoken.net/api模型对话页https://taotoken.net/api/model-chat?utm_sourcetaotoken_aicg_blog_endutm_contentmodel_chatutm_campaignrewriteAPI Keys 管理https://taotoken.net/api/api-keys?utm_sourcetaotoken_aicg_blog_endutm_contentapi_keysutm_campaignrewrite接入文档https://taotoken.net/api/doc?utm_sourcetaotoken_aicg_blog_endutm_contentdocutm_campaignrewrite操作顺序先进 API Keys 页面创建一个 Key复制出来然后在你的 AI 工具比如支持自定义 Base URL 的编码助手里把 Base URL 填成https://taotoken.net/apiKey 填进去。这样无论你后面换哪个模型配置只改一处。注意Key 只创建一次就够别在多个工具里重复建否则后面轮换时容易漏改。建议命名成oracle-plsql-debug这种带用途的方便识别。3. 可复制配置建表、造数、三种 bulk collect 写法3.1 建表与测试数据先造一张有足够行数的表方便观察分批效果。下面脚本建一张 200 万行的订单表-- 建表 CREATE TABLE t_order ( order_id NUMBER PRIMARY KEY, user_id NUMBER, amount NUMBER(12,2), status VARCHAR2(20), created_at DATE ); -- 造 200 万行测试数据 BEGIN FOR i IN 1 .. 2000000 LOOP INSERT INTO t_order VALUES ( i, MOD(i, 10000) 1, ROUND(DBMS_RANDOM.VALUE(1, 9999), 2), CASE MOD(i, 4) WHEN 0 THEN PAID WHEN 1 THEN PENDING WHEN 2 THEN SHIPPED ELSE CLOSED END, SYSDATE - DBMS_RANDOM.VALUE(0, 365) ); IF MOD(i, 10000) 0 THEN COMMIT; END IF; END LOOP; COMMIT; END; / -- 收集统计信息否则执行计划不准 BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER, T_ORDER); END; /3.2 写法一BULK COLLECT INTO 一次性加载适合结果集不大几千到几万行的场景一次全灌进集合SET SERVEROUTPUT ON DECLARE TYPE t_order_tab IS TABLE OF t_order%ROWTYPE; v_orders t_order_tab; v_start NUMBER; BEGIN v_start : DBMS_UTILITY.GET_TIME; SELECT * BULK COLLECT INTO v_orders FROM t_order WHERE status PAID; DBMS_OUTPUT.PUT_LINE(行数: || v_orders.COUNT); DBMS_OUTPUT.PUT_LINE(耗时(厘秒): || (DBMS_UTILITY.GET_TIME - v_start)); END; /DBMS_UTILITY.GET_TIME返回的是厘秒1/100 秒用来做相对对比够用。注意这里没有LIMIT如果statusPAID命中几十万行PGA 会明显上涨。3.3 写法二LIMIT 分批 FETCH大结果集的标准做法用游标 FETCH ... BULK COLLECT INTO ... LIMIT nSET SERVEROUTPUT ON DECLARE CURSOR c_order IS SELECT * FROM t_order WHERE status PAID; TYPE t_order_tab IS TABLE OF t_order%ROWTYPE; v_orders t_order_tab; v_total NUMBER : 0; v_start NUMBER; BEGIN v_start : DBMS_UTILITY.GET_TIME; OPEN c_order; LOOP FETCH c_order BULK COLLECT INTO v_orders LIMIT 5000; EXIT WHEN v_orders.COUNT 0; v_total : v_total v_orders.COUNT; -- 这里可以做逐批处理比如写日志、聚合 END LOOP; CLOSE c_order; DBMS_OUTPUT.PUT_LINE(总行数: || v_total); DBMS_OUTPUT.PUT_LINE(耗时(厘秒): || (DBMS_UTILITY.GET_TIME - v_start)); END; /LIMIT 5000是每批行数实测下来 1000 到 10000 之间比较稳太小切换次数多太大内存吃紧。你可以按 PGA 大小和行宽调。3.4 写法三FORALL 批量回写取出来处理完要写回时别用循环一条条 INSERT/UPDATE用FORALLSET SERVEROUTPUT ON DECLARE TYPE t_id_tab IS TABLE OF t_order.order_id%TYPE; TYPE t_status_tab IS TABLE OF t_order.status%TYPE; v_ids t_id_tab; v_status t_status_tab; v_start NUMBER; BEGIN v_start : DBMS_UTILITY.GET_TIME; -- 先批量取待更新行的主键 SELECT order_id BULK COLLECT INTO v_ids FROM t_order WHERE status PENDING AND ROWNUM 100000; -- 构造新状态集合 v_status : t_status_tab(); v_status.EXTEND(v_ids.COUNT); FOR i IN 1 .. v_ids.COUNT LOOP v_status(i) : PROCESSED; END LOOP; -- 批量回写 FORALL i IN 1 .. v_ids.COUNT UPDATE t_order SET status v_status(i) WHERE order_id v_ids(i); COMMIT; DBMS_OUTPUT.PUT_LINE(更新行数: || SQL%ROWCOUNT); DBMS_OUTPUT.PUT_LINE(耗时(厘秒): || (DBMS_UTILITY.GET_TIME - v_start)); END; /FORALL把整个集合的 DML 一次性发给 SQL 引擎比循环单条执行快一个量级。注意FORALL里不能写COMMIT要放在外面。4. 用 TaoToken 生成与校验脚本的实操前面三段代码我实际是用 TaoToken 的模型对话通道先出草稿、再人工核对语法细节的。流程是这样第一步在模型对话页把需求描述清楚比如「Oracle 19c写一个 PL/SQL 块用游标 BULK COLLECT LIMIT 5000 分批读取 t_order 表 statusPAID 的行统计总行数并输出耗时」。模型会给出结构但%ROWTYPE集合类型声明、EXIT WHEN位置这些容易写错需要你对着文档核。第二步把生成的脚本贴回对话里让它检查「FORALL中能否包含COMMIT」「BULK COLLECT能否用在RETURNING INTO」这类边界问题。这一步能省不少翻文档的时间。第三步如果你用的是支持自定义 Base URL 的编码工具把 Base URL 指向https://taotoken.net/apiKey 用前面创建的就能在编辑器里直接让 AI 补全 PL/SQL不用切窗口。提示AI 生成的 PL/SQL 一定要在测试库跑一遍再上生产。尤其是LIMIT数值和集合类型声明不同 Oracle 版本对%ROWTYPE集合的支持细节有差异。5. 验证请求与成功结果执行计划与耗时对比脚本跑通只是第一步得看它到底快在哪。用下面两步验证。5.1 看执行计划对分批查询的 SQL 单独跑一次执行计划EXPLAIN PLAN FOR SELECT * FROM t_order WHERE status PAID; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);重点看TABLE ACCESS是FULL还是走了索引。如果status选择性差比如 PAID 占 1/4全表扫描反而合理别盲目加索引。5.2 耗时对比把「逐行 FETCH」和「BULK COLLECT LIMIT」放一起对比用同一张表、同一条件-- 逐行处理对照组 DECLARE CURSOR c IS SELECT * FROM t_order WHERE status PAID; v_row t_order%ROWTYPE; v_cnt NUMBER : 0; v_start NUMBER; BEGIN v_start : DBMS_UTILITY.GET_TIME; OPEN c; LOOP FETCH c INTO v_row; EXIT WHEN c%NOTFOUND; v_cnt : v_cnt 1; END LOOP; CLOSE c; DBMS_OUTPUT.PUT_LINE(逐行 行数: || v_cnt || 耗时: || (DBMS_UTILITY.GET_TIME - v_start)); END; /实测下来50 万行量级逐行 FETCH 通常在几千厘秒BULK COLLECT LIMIT 5000能压到几百厘秒差距在 5 到 10 倍。具体数字取决于机器和 PGA 配置你按自己环境跑一遍最准。成功结果长这样总行数: 500000 耗时(厘秒): 412如果耗时没降下来先查是不是LIMIT设得太小或者集合类型用了%ROWTYPE导致每行拷贝开销大——可以只取需要的列用TYPE ... IS TABLE OF t_order.order_id%TYPE这种窄类型。6. 本篇常见错排查ORA-06550 / PLS-00382表达式类型错误。多半是BULK COLLECT INTO后面的变量不是集合类型。检查TYPE ... IS TABLE OF ...声明有没有漏或者集合和查询列数对不上。ORA-21700对象不存在或已标记删除。集合没初始化就EXTEND或者FORALL里索引越界。v_status : t_status_tab();这行别省。PGA 内存告警 / ORA-04030。一次性BULK COLLECT没加LIMIT结果集太大。改成游标 LIMIT分批或者调大pga_aggregate_target。FORALL 里报 ORA-06502。集合下标不连续FORALL i IN 1 .. n要求 1 到 n 都有值。用INDICES OF或VALUES OF处理稀疏集合。执行计划没走索引。统计信息过期重新DBMS_STATS.GATHER_TABLE_STATS。或者status选择性太差全表扫描本来就是最优解。排障时如果拿不准语法可以把报错原文贴到模型对话页让 AI 解释但记得把表名、字段名脱敏。接入配置统一走 API Keys 页面管理别散落在各个工具里。7. 下一步把统一 Key 用到长期编码里如果你只是偶尔写几个 PL/SQL 块模型对话页够用。但要是你长期做 Oracle 数据层开发每天都要生成、校验、优化脚本建议把 TaoToken 的 Coding Plan 用起来配合编码工具做持续补全和审查Key 和通道统一管理省得每次换工具重配。长期编码 / Agent 场景https://taotoken.net/api/coding-plan?utm_sourcetaotoken_aicg_blog_endutm_contentcoding_planutm_campaignrewrite控制台总入口https://taotoken.net/api/console?utm_sourcetaotoken_aicg_blog_endutm_contentconsoleutm_campaignrewrite我自己的习惯是建表造数脚本让 AI 出初稿BULK COLLECT和FORALL的核心逻辑自己写执行计划和耗时对比一定在测试库实跑。AI 能帮你省掉查语法的时间但内存和性能的坑还得靠LIMIT数值和 PGA 监控来兜。
Oracle 学习总结三：用 TaoToken 统一 Key 调试 bulk collect 批量取数脚本
1. 大表取数为什么总在 PL/SQL 里卡住如果你写过 Oracle 的存储过程大概率遇到过这种场景一张几百万行的表用显式游标LOOP ... FETCH ... END LOOP一行一行捞跑起来像老牛拉车日志刷得慢内存还时不时报警。问题不在数据库本身而在 PL/SQL 引擎和 SQL 引擎之间的上下文切换——每 FETCH 一行就要来回切一次行数一多开销全耗在切换上。BULK COLLECT就是来解决这个的。它把查询结果一次性或分批灌进集合collection里让 PL/SQL 一次拿到一批数据而不是一行一行要。配合LIMIT还能控制每批大小避免一次性把几百万行塞进 PGA 把内存撑爆。适合谁做数据迁移、报表预聚合、批量对账、ETL 中间层的同学基本都会用到。这篇我按「建表造数 → 三种写法 → 用 TaoToken 统一 Key 让 AI 生成和校验脚本 → 执行计划与耗时对比」的顺序走一遍代码都能直接复制跑。TaoToken 在这里的作用是你手头有多个 AI 工具对话、编码插件、Agent不用每个都配一套 Key统一走一个 API 通道生成和校验 PL/SQL 脚本时省去反复切配置的麻烦。2. 前置准备TaoToken 统一 Key 与 API 通道先说清楚 TaoToken 是什么它是一个大模型 API 的统一接入层把不同模型的调用收敛到一个 Key、一个 Base URL 上。对写 Oracle 脚本的人来说价值在于——你让 AI 帮你生成BULK COLLECT模板、检查FORALL语法、解释执行计划时不用在多个工具里维护多份密钥。接入信息如下官网入口https://taotoken.net/?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentAPI Base URLhttps://taotoken.net/api模型对话页https://taotoken.net/api/model-chat?utm_sourcetaotoken_aicg_blog_endutm_contentmodel_chatutm_campaignrewriteAPI Keys 管理https://taotoken.net/api/api-keys?utm_sourcetaotoken_aicg_blog_endutm_contentapi_keysutm_campaignrewrite接入文档https://taotoken.net/api/doc?utm_sourcetaotoken_aicg_blog_endutm_contentdocutm_campaignrewrite操作顺序先进 API Keys 页面创建一个 Key复制出来然后在你的 AI 工具比如支持自定义 Base URL 的编码助手里把 Base URL 填成https://taotoken.net/apiKey 填进去。这样无论你后面换哪个模型配置只改一处。注意Key 只创建一次就够别在多个工具里重复建否则后面轮换时容易漏改。建议命名成oracle-plsql-debug这种带用途的方便识别。3. 可复制配置建表、造数、三种 bulk collect 写法3.1 建表与测试数据先造一张有足够行数的表方便观察分批效果。下面脚本建一张 200 万行的订单表-- 建表 CREATE TABLE t_order ( order_id NUMBER PRIMARY KEY, user_id NUMBER, amount NUMBER(12,2), status VARCHAR2(20), created_at DATE ); -- 造 200 万行测试数据 BEGIN FOR i IN 1 .. 2000000 LOOP INSERT INTO t_order VALUES ( i, MOD(i, 10000) 1, ROUND(DBMS_RANDOM.VALUE(1, 9999), 2), CASE MOD(i, 4) WHEN 0 THEN PAID WHEN 1 THEN PENDING WHEN 2 THEN SHIPPED ELSE CLOSED END, SYSDATE - DBMS_RANDOM.VALUE(0, 365) ); IF MOD(i, 10000) 0 THEN COMMIT; END IF; END LOOP; COMMIT; END; / -- 收集统计信息否则执行计划不准 BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER, T_ORDER); END; /3.2 写法一BULK COLLECT INTO 一次性加载适合结果集不大几千到几万行的场景一次全灌进集合SET SERVEROUTPUT ON DECLARE TYPE t_order_tab IS TABLE OF t_order%ROWTYPE; v_orders t_order_tab; v_start NUMBER; BEGIN v_start : DBMS_UTILITY.GET_TIME; SELECT * BULK COLLECT INTO v_orders FROM t_order WHERE status PAID; DBMS_OUTPUT.PUT_LINE(行数: || v_orders.COUNT); DBMS_OUTPUT.PUT_LINE(耗时(厘秒): || (DBMS_UTILITY.GET_TIME - v_start)); END; /DBMS_UTILITY.GET_TIME返回的是厘秒1/100 秒用来做相对对比够用。注意这里没有LIMIT如果statusPAID命中几十万行PGA 会明显上涨。3.3 写法二LIMIT 分批 FETCH大结果集的标准做法用游标 FETCH ... BULK COLLECT INTO ... LIMIT nSET SERVEROUTPUT ON DECLARE CURSOR c_order IS SELECT * FROM t_order WHERE status PAID; TYPE t_order_tab IS TABLE OF t_order%ROWTYPE; v_orders t_order_tab; v_total NUMBER : 0; v_start NUMBER; BEGIN v_start : DBMS_UTILITY.GET_TIME; OPEN c_order; LOOP FETCH c_order BULK COLLECT INTO v_orders LIMIT 5000; EXIT WHEN v_orders.COUNT 0; v_total : v_total v_orders.COUNT; -- 这里可以做逐批处理比如写日志、聚合 END LOOP; CLOSE c_order; DBMS_OUTPUT.PUT_LINE(总行数: || v_total); DBMS_OUTPUT.PUT_LINE(耗时(厘秒): || (DBMS_UTILITY.GET_TIME - v_start)); END; /LIMIT 5000是每批行数实测下来 1000 到 10000 之间比较稳太小切换次数多太大内存吃紧。你可以按 PGA 大小和行宽调。3.4 写法三FORALL 批量回写取出来处理完要写回时别用循环一条条 INSERT/UPDATE用FORALLSET SERVEROUTPUT ON DECLARE TYPE t_id_tab IS TABLE OF t_order.order_id%TYPE; TYPE t_status_tab IS TABLE OF t_order.status%TYPE; v_ids t_id_tab; v_status t_status_tab; v_start NUMBER; BEGIN v_start : DBMS_UTILITY.GET_TIME; -- 先批量取待更新行的主键 SELECT order_id BULK COLLECT INTO v_ids FROM t_order WHERE status PENDING AND ROWNUM 100000; -- 构造新状态集合 v_status : t_status_tab(); v_status.EXTEND(v_ids.COUNT); FOR i IN 1 .. v_ids.COUNT LOOP v_status(i) : PROCESSED; END LOOP; -- 批量回写 FORALL i IN 1 .. v_ids.COUNT UPDATE t_order SET status v_status(i) WHERE order_id v_ids(i); COMMIT; DBMS_OUTPUT.PUT_LINE(更新行数: || SQL%ROWCOUNT); DBMS_OUTPUT.PUT_LINE(耗时(厘秒): || (DBMS_UTILITY.GET_TIME - v_start)); END; /FORALL把整个集合的 DML 一次性发给 SQL 引擎比循环单条执行快一个量级。注意FORALL里不能写COMMIT要放在外面。4. 用 TaoToken 生成与校验脚本的实操前面三段代码我实际是用 TaoToken 的模型对话通道先出草稿、再人工核对语法细节的。流程是这样第一步在模型对话页把需求描述清楚比如「Oracle 19c写一个 PL/SQL 块用游标 BULK COLLECT LIMIT 5000 分批读取 t_order 表 statusPAID 的行统计总行数并输出耗时」。模型会给出结构但%ROWTYPE集合类型声明、EXIT WHEN位置这些容易写错需要你对着文档核。第二步把生成的脚本贴回对话里让它检查「FORALL中能否包含COMMIT」「BULK COLLECT能否用在RETURNING INTO」这类边界问题。这一步能省不少翻文档的时间。第三步如果你用的是支持自定义 Base URL 的编码工具把 Base URL 指向https://taotoken.net/apiKey 用前面创建的就能在编辑器里直接让 AI 补全 PL/SQL不用切窗口。提示AI 生成的 PL/SQL 一定要在测试库跑一遍再上生产。尤其是LIMIT数值和集合类型声明不同 Oracle 版本对%ROWTYPE集合的支持细节有差异。5. 验证请求与成功结果执行计划与耗时对比脚本跑通只是第一步得看它到底快在哪。用下面两步验证。5.1 看执行计划对分批查询的 SQL 单独跑一次执行计划EXPLAIN PLAN FOR SELECT * FROM t_order WHERE status PAID; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);重点看TABLE ACCESS是FULL还是走了索引。如果status选择性差比如 PAID 占 1/4全表扫描反而合理别盲目加索引。5.2 耗时对比把「逐行 FETCH」和「BULK COLLECT LIMIT」放一起对比用同一张表、同一条件-- 逐行处理对照组 DECLARE CURSOR c IS SELECT * FROM t_order WHERE status PAID; v_row t_order%ROWTYPE; v_cnt NUMBER : 0; v_start NUMBER; BEGIN v_start : DBMS_UTILITY.GET_TIME; OPEN c; LOOP FETCH c INTO v_row; EXIT WHEN c%NOTFOUND; v_cnt : v_cnt 1; END LOOP; CLOSE c; DBMS_OUTPUT.PUT_LINE(逐行 行数: || v_cnt || 耗时: || (DBMS_UTILITY.GET_TIME - v_start)); END; /实测下来50 万行量级逐行 FETCH 通常在几千厘秒BULK COLLECT LIMIT 5000能压到几百厘秒差距在 5 到 10 倍。具体数字取决于机器和 PGA 配置你按自己环境跑一遍最准。成功结果长这样总行数: 500000 耗时(厘秒): 412如果耗时没降下来先查是不是LIMIT设得太小或者集合类型用了%ROWTYPE导致每行拷贝开销大——可以只取需要的列用TYPE ... IS TABLE OF t_order.order_id%TYPE这种窄类型。6. 本篇常见错排查ORA-06550 / PLS-00382表达式类型错误。多半是BULK COLLECT INTO后面的变量不是集合类型。检查TYPE ... IS TABLE OF ...声明有没有漏或者集合和查询列数对不上。ORA-21700对象不存在或已标记删除。集合没初始化就EXTEND或者FORALL里索引越界。v_status : t_status_tab();这行别省。PGA 内存告警 / ORA-04030。一次性BULK COLLECT没加LIMIT结果集太大。改成游标 LIMIT分批或者调大pga_aggregate_target。FORALL 里报 ORA-06502。集合下标不连续FORALL i IN 1 .. n要求 1 到 n 都有值。用INDICES OF或VALUES OF处理稀疏集合。执行计划没走索引。统计信息过期重新DBMS_STATS.GATHER_TABLE_STATS。或者status选择性太差全表扫描本来就是最优解。排障时如果拿不准语法可以把报错原文贴到模型对话页让 AI 解释但记得把表名、字段名脱敏。接入配置统一走 API Keys 页面管理别散落在各个工具里。7. 下一步把统一 Key 用到长期编码里如果你只是偶尔写几个 PL/SQL 块模型对话页够用。但要是你长期做 Oracle 数据层开发每天都要生成、校验、优化脚本建议把 TaoToken 的 Coding Plan 用起来配合编码工具做持续补全和审查Key 和通道统一管理省得每次换工具重配。长期编码 / Agent 场景https://taotoken.net/api/coding-plan?utm_sourcetaotoken_aicg_blog_endutm_contentcoding_planutm_campaignrewrite控制台总入口https://taotoken.net/api/console?utm_sourcetaotoken_aicg_blog_endutm_contentconsoleutm_campaignrewrite我自己的习惯是建表造数脚本让 AI 出初稿BULK COLLECT和FORALL的核心逻辑自己写执行计划和耗时对比一定在测试库实跑。AI 能帮你省掉查语法的时间但内存和性能的坑还得靠LIMIT数值和 PGA 监控来兜。
