Oracle批量获取行数据用于跨库迁移的SQL查询问题

你遇到的问题是Oracle中ROWNUM的特性导致的——ROWNUM是Oracle在返回结果集的过程中逐行分配的序号，从1开始递增。当你写ROWNUM >= 10000时，第一条符合条件的数据会被分配ROWNUM=1，不满足>=10000的条件，会被过滤掉；后续的数据也永远不会被分配到>=10000的序号，所以查询结果为空。

下面给你几种可行的批量取数方案，适配你的Node.js迁移场景：

1. 基于子查询的ROWNUM分页（兼容所有Oracle版本） #

通过嵌套子查询先给排序后的结果分配ROWNUM，再在外层过滤范围，这是最经典的兼容写法：

SELECT * 
FROM (
    -- 中间层：给排序后的结果分配ROWNUM
    SELECT t.*, ROWNUM AS rn 
    FROM (
        -- 内层：先对数据排序（必须加，否则分页结果可能不稳定）
        SELECT * FROM table_name ORDER BY id -- id替换为你的主键/唯一排序键
    ) t
) 
-- 外层：过滤目标批次的行号范围
WHERE rn >= :start_row AND rn < :end_row;

比如第2批（10000-20000行）的参数就是start_row=10001，end_row=20001（因为ROWNUM从1开始计数）。

2. Oracle 12c+ 简洁分页语法（推荐） #

如果你的Oracle版本是12c及以上，可以用OFFSET/FETCH语法，写法更直观：

SELECT * FROM table_name 
ORDER BY id -- 同样需要排序保证批次一致性
OFFSET :offset ROWS FETCH NEXT :batch_size ROWS ONLY;

比如第2批的参数是offset=10000，batch_size=10000，表示跳过前10000行，取接下来的10000行。

3. 大表优化：主键范围分页（效率更高） #

如果你的表数据量很大（百万/千万级），ROWNUM分页可能会因为全表扫描导致性能问题。这时优先用主键范围查询，利用主键索引快速定位数据：

-- 先查询主键的最小和最大值
SELECT MIN(id), MAX(id) FROM table_name;

-- 然后分批次查询，比如第n批的id范围是 [start_id, end_id]
SELECT * FROM table_name 
WHERE id BETWEEN :start_id AND :end_id;

比如主键id是连续或近似连续的，你可以把整个id范围分成每个批次10000条的区间，每次查询一个区间的数据。这种方式的查询效率远高于ROWNUM分页。

Node.js应用中的实践建议 #

• 必须加稳定排序：无论用哪种分页方式，都要基于主键或唯一列排序，否则不同批次可能出现数据重复或遗漏的情况。
• 参数化查询：在Node.js中使用参数绑定（比如上面例子中的:start_row、:offset），避免SQL注入，同时提升查询性能。
• 循环终止条件：每次查询后检查返回的行数，如果为0则停止循环，表示没有更多数据。

附一段Node.js伪代码示例（基于oracledb驱动）：

const oracledb = require('oracledb');
const batchSize = 10000;
let currentOffset = 0;

// 假设已初始化数据库连接
async function fetchAndMigrateBatch() {
  const sql = `
    SELECT * 
    FROM (
        SELECT t.*, ROWNUM AS rn 
        FROM (
            SELECT * FROM table_name ORDER BY id
        ) t
    ) 
    WHERE rn >= :start AND rn < :end
  `;

  const result = await connection.execute(sql, {
    start: currentOffset + 1,
    end: currentOffset + batchSize + 1
  }, { outFormat: oracledb.OUT_FORMAT_OBJECT });

  if (result.rows.length === 0) {
    console.log('所有数据已取完');
    return false;
  }

  // 这里替换为你的数据迁移逻辑
  console.log(`处理第${Math.floor(currentOffset/batchSize)+1}批，共${result.rows.length}条数据`);
  await migrateToTargetDB(result.rows);

  currentOffset += batchSize;
  return true;
}

// 启动批量取数循环
(async () => {
  while (await fetchAndMigrateBatch()) {
    // 循环继续
  }
  await connection.close();
})();

内容的提问来源于stack exchange，提问作者scriptAddict