★ FEATURED ARTICLE 1. 从一次批量更新说起Oracle 游标到底解决什么问题刚接触 Oracle PL/SQL 的时候很多人会卡在同一个地方SQL 一次只能处理一个结果集但业务逻辑偏偏要一行一行地判断、计算、再写回去。比如你有一张订单表需要根据每笔订单的金额决定是否打标、是否发券、是否写日志——这些动作没法用一条 UPDATE 搞定必须把结果集拿在手里逐行处理。这时候游标Cursor就登场了。游标本质上是一个指向查询结果集的指针。你可以把它想象成一根手指按在查询结果的第一行上然后一行一行往下滑。每滑一行你就能把当前行的字段读进变量里做任意逻辑判断。Oracle 里的游标分三类显式游标、隐式游标、游标 FOR 循环。显式游标需要你手动声明、打开、取值、关闭控制力最强隐式游标由 Oracle 自动管理适合单行操作游标 FOR 循环则是语法糖把打开、取值、关闭全包了写起来最省心。这篇文章面向刚上手 Oracle PL/SQL 的开发者我会用一张真实的测试表把三类游标的完整链路走一遍。每一步都给可复制的建表语句和 PL/SQL 块并且告诉你执行后应该看到什么输出。更重要的是我会讲清楚什么时候该用游标、什么时候该改用 BULK COLLECT 批量绑定——因为游标用错了场景性能会差出几十倍。如果你正在写存储过程做批量数据处理这篇内容可以直接对照着改代码。先明确一个检索词Oracle 游标遍历结果集。你在搜索时可能用的是Oracle 游标用法PL/SQL 游标 FOR 循环显式游标 fetch这类词核心都是同一件事——怎么把查询结果一行行取出来处理。下面从建表开始。2. 显式游标声明、打开、取值、关闭的完整链路2.1 建一张测试表并灌入数据在 SQL*Plus 或 SQL Developer 里执行CREATE TABLE testA ( id NUMBER, name VARCHAR2(20) ); INSERT INTO testA VALUES (1, zhangsan); INSERT INTO testA VALUES (2, lisi); INSERT INTO testA VALUES (3, wangwu); COMMIT;这张表只有三行方便你对照输出。实际业务表可能几百万行但游标的操作逻辑完全一样。2.2 显式游标的四个动作显式游标的标准写法分四步DECLARE 声明、OPEN 打开、FETCH 取值、CLOSE 关闭。看一个完整例子DECLARE CURSOR c_test IS SELECT id, name FROM testA; v_id testA.id%TYPE; v_name testA.name%TYPE; BEGIN OPEN c_test; LOOP FETCH c_test INTO v_id, v_name; EXIT WHEN c_test%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_id || || v_name); END LOOP; CLOSE c_test; END; /执行前记得打开输出SET SERVEROUTPUT ON;。你会看到1 zhangsan 2 lisi 3 wangwu这里有几个关键点。%TYPE让变量类型自动跟随列类型改表结构时不用改代码。%NOTFOUND是游标属性当 FETCH 没有取到数据时返回 TRUE用来退出循环。注意 FETCH 和 EXIT WHEN 的顺序——必须先 FETCH 再判断否则会多输出一行或漏掉最后一行。2.3 游标的四个属性属性含义典型用途%FOUND上一次 FETCH 是否取到数据判断是否继续循环%NOTFOUND上一次 FETCH 是否没取到数据退出循环%ROWCOUNT到目前为止取了多少行统计处理条数%ISOPEN游标是否处于打开状态关闭前判断避免异常%ROWCOUNT在批量处理时特别有用。比如你想每处理 1000 行提交一次就可以用IF c_test%ROWCOUNT MOD 1000 0 THEN COMMIT; END IF;。2.4 带参数的显式游标实际业务里查询条件往往是动态的。显式游标支持参数DECLARE CURSOR c_test(p_min_id NUMBER) IS SELECT id, name FROM testA WHERE id p_min_id; v_id testA.id%TYPE; v_name testA.name%TYPE; BEGIN OPEN c_test(2); LOOP FETCH c_test INTO v_id, v_name; EXIT WHEN c_test%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_id || || v_name); END LOOP; CLOSE c_test; END; /输出只有2 lisi和3 wangwu。参数化游标的好处是同一个游标定义可以复用于不同条件不用为每个查询写一遍声明。2.5 什么时候该用显式游标显式游标适合这些场景需要在循环中间做复杂逻辑判断、需要手动控制提交频率、需要根据%ROWCOUNT做分批处理、或者需要在打开游标前做动态 SQL 拼接。如果你只是简单遍历往下看游标 FOR 循环会更省事。3. 游标 FOR 循环最省心的遍历方式3.1 基本写法游标 FOR 循环把声明、打开、取值、关闭全自动化了。你只需要定义游标然后FOR 变量 IN 游标 LOOPDECLARE CURSOR c_test IS SELECT id, name FROM testA; BEGIN FOR v_test IN c_test LOOP DBMS_OUTPUT.PUT_LINE(v_test.id || || v_test.name); END LOOP; END; /输出和显式游标一样。注意v_test不需要你声明Oracle 自动把它定义成c_test%ROWTYPE。你直接用v_test.id、v_test.name访问字段。3.2 隐式游标 FOR 循环更省事的写法是连游标声明都省掉直接把 SELECT 写在 FOR 里BEGIN FOR v_test IN (SELECT id, name FROM testA) LOOP DBMS_OUTPUT.PUT_LINE(v_test.id || || v_test.name); END LOOP; END; /这种写法叫隐式游标 FOR 循环。Oracle 在背后帮你做了所有脏活。适合一次性遍历、不需要复用游标定义的场景。3.3 游标 FOR 循环的注意事项第一循环变量是只读的。你不能在循环体里给v_test.id赋值想改数据得用 UPDATE 语句。第二游标 FOR 循环只适用于静态 SQL动态 SQL 得用显式游标加OPEN ... FOR。第三循环结束后游标自动关闭你不需要也不能手动 CLOSE。3.4 动态 SQL 与显式游标的配合当 SQL 语句本身是运行时拼出来的就得用动态游标。Oracle 提供SYS_REFCURSORDECLARE v_cur SYS_REFCURSOR; v_id testA.id%TYPE; v_name testA.name%TYPE; v_sql VARCHAR2(200); BEGIN v_sql : SELECT id, name FROM testA WHERE id :1; OPEN v_cur FOR v_sql USING 1; LOOP FETCH v_cur INTO v_id, v_name; EXIT WHEN v_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_id || || v_name); END LOOP; CLOSE v_cur; END; /OPEN ... FOR支持绑定变量用USING传参能有效防止 SQL 注入。动态游标必须手动关闭否则会耗尽OPEN_CURSORS参数限制。3.5 执行计划验证想确认游标查询有没有走索引可以在 SQL Developer 里按 F5 看执行计划或者EXPLAIN PLAN FOR SELECT id, name FROM testA WHERE id 1; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);如果testA数据量大且id有索引应该看到 INDEX RANGE SCAN。全表扫描在几百万行时会拖慢游标遍历。4. 隐式游标与批量绑定什么时候该放弃逐行 FETCH4.1 隐式游标的 SQL 属性每次执行 DML 语句INSERT/UPDATE/DELETE或单行 SELECT INTOOracle 都会自动创建一个隐式游标。你可以用SQL%FOUND、SQL%ROWCOUNT等属性获取上次执行的信息BEGIN UPDATE testA SET name zhaoliu WHERE id 1; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(更新了 || SQL%ROWCOUNT || 行); END IF; END; /输出更新了 1 行。隐式游标不需要声明和关闭适合单行操作。但如果你在循环里反复执行单行 DML性能会很差——每次都有上下文切换开销。4.2 逐行 FETCH 的性能陷阱假设testA有 100 万行你用显式游标逐行 FETCH 再逐行 UPDATE会发生什么每次 FETCH 是一次 PL/SQL 到 SQL 引擎的切换每次 UPDATE 又是一次。100 万次切换耗时可能几分钟甚至更久。我试过在一张 50 万行的表上做逐行更新跑了将近 4 分钟。改成 BULK COLLECT 后降到 3 秒以内。4.3 BULK COLLECT 批量取值BULK COLLECT 一次性把结果集批量取进集合DECLARE TYPE t_id IS TABLE OF testA.id%TYPE; TYPE t_name IS TABLE OF testA.name%TYPE; v_ids t_id; v_names t_name; CURSOR c_test IS SELECT id, name FROM testA; BEGIN OPEN c_test; FETCH c_test BULK COLLECT INTO v_ids, v_names; CLOSE c_test; FOR i IN 1 .. v_ids.COUNT LOOP DBMS_OUTPUT.PUT_LINE(v_ids(i) || || v_names(i)); END LOOP; END; /BULK COLLECT INTO一次把所有行取进内存集合。对于大结果集可以配合LIMIT分批LOOP FETCH c_test BULK COLLECT INTO v_ids, v_names LIMIT 1000; EXIT WHEN v_ids.COUNT 0; -- 处理这 1000 行 END LOOP;4.4 FORALL 批量 DML取值用 BULK COLLECT写回用 FORALLDECLARE TYPE t_id IS TABLE OF testA.id%TYPE; v_ids t_id : t_id(1, 2, 3); BEGIN FORALL i IN 1 .. v_ids.COUNT UPDATE testA SET name batch_ || v_ids(i) WHERE id v_ids(i); COMMIT; END; /FORALL 把多条 DML 打包成一次发送减少上下文切换。注意 FORALL 里不能写复杂逻辑只能跟单条 DML。4.5 选型对照表场景推荐方式原因单行查询赋值SELECT INTO隐式游标自动管理小结果集遍历1万行游标 FOR 循环代码简洁可读性好需要复杂逐行逻辑显式游标控制力强可手动提交大结果集批量处理BULK COLLECT FORALL减少上下文切换性能最优动态 SQL 遍历SYS_REFCURSOR支持运行时拼接5. 常见报错排查从 ORA-01001 到 ORA-065505.1 ORA-01001: invalid cursor这个错通常是因为你 FETCH 了一个没打开的游标或者 CLOSE 了两次。检查 OPEN 和 CLOSE 是否配对。用%ISOPEN判断IF c_test%ISOPEN THEN CLOSE c_test; END IF;5.2 ORA-06550: line X, column YPL/SQL 编译错误通常是语法问题。比如EXIT WHEN c_test%NOTFOUND写成了EXIT WHEN c_test.NOTFOUND或者漏了分号。把错误行号对应到代码里逐行检查。5.3 ORA-01403: no data foundSELECT INTO 没查到数据时抛这个错。用隐式游标属性判断BEGIN SELECT name INTO v_name FROM testA WHERE id 999; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(没找到记录); END; /5.4 ORA-01422: exact fetch returns more than requested number of rowsSELECT INTO 返回多行。要么加 WHERE 条件限定一行要么改用游标遍历。5.5 ORA-01000: maximum open cursors exceeded打开的游标没关闭累积超过OPEN_CURSORS限制。检查所有OPEN是否都有对应的CLOSE。用这个查询看当前打开的游标SELECT COUNT(*) FROM v$open_cursor WHERE user_name USER;5.6 游标 FOR 循环里改数据不生效游标 FOR 循环的循环变量是只读快照你在循环体里 UPDATE 了表但循环变量不会刷新。如果需要看到最新值得重新查询。5.7 动态游标绑定变量类型不匹配OPEN ... FOR ... USING时USING 的变量类型要和 SQL 里的占位符匹配。比如:1是 NUMBER你传了字符串就会报 ORA-01722。检查绑定变量类型。6. 把游标用对从能跑到跑得快的实践建议游标本身不难难的是选对场景。我的经验是能用一条 SQL 解决的绝不用游标必须逐行处理的优先游标 FOR 循环数据量超过一万行的直接上 BULK COLLECT FORALL。显式游标留给需要手动控制提交频率或动态 SQL 的场景。另外几个实用技巧。第一游标查询尽量走索引用EXPLAIN PLAN确认执行计划。第二批量处理时用LIMIT分批 FETCH避免一次性把几百万行读进 PGA 导致内存溢出。第三循环里的 COMMIT 频率别太高每 1000 到 5000 行提交一次比较平衡。第四动态 SQL 一定要用绑定变量别用字符串拼接既防注入又提升性能。如果你在写存储过程时拿不准该用哪种游标可以先按最简单的方式写出来跑通逻辑后再用 BULK COLLECT 优化性能瓶颈。代码正确性永远优先于性能先跑对再跑快。
阅读完成 · 觉得有帮助？
