Oracle 12C中SQL Server CURSOR STATIC的等效实现方式咨询

核心结论 #

Oracle 原生基于多版本并发控制（MVCC）的读一致性机制，默认显式游标行为就等价于SQL Server的CURSOR STATIC，打开游标时会自动生成查询结果集的快照，遍历过程中所有新插入的行（包括当前会话和其他会话提交的新增数据）都不会被遍历到。

实现方式 #

方式1：直接使用默认显式游标（推荐） #

无需额外添加特殊参数，普通游标声明即可满足需求，示例代码如下：

DECLARE
  -- 声明遍历游标，无需特殊参数
  CURSOR c_original_data IS SELECT id, col1, col2 FROM 你的表名;
  v_row c_original_data%ROWTYPE;
BEGIN
  -- 游标打开瞬间固化当前结果集快照
  OPEN c_original_data;
  LOOP
    FETCH c_original_data INTO v_row;
    EXIT WHEN c_original_data%NOTFOUND;
    -- 此处写你的数据复制逻辑，例：插入同表新行
    INSERT INTO 你的表名(id, col1, col2) VALUES (自定义主键生成规则, v_row.col1, v_row.col2);
  END LOOP;
  CLOSE c_original_data;
  COMMIT;
END;
/

如果需要显式和可更新游标做区分，避免意外行锁，可以在游标声明末尾添加FOR READ ONLY：

CURSOR c_original_data IS SELECT id, col1, col2 FROM 你的表名 FOR READ ONLY;

方式2：临时表固化快照（超大数据量场景适用） #

如果表数据量极大，不想游标快照占用过多PGA内存，可以先将查询瞬间的主键集合存入临时表，再基于临时表遍历处理，完全隔离新增数据：

1. 提前创建会话级临时表（仅需创建一次）

CREATE GLOBAL TEMPORARY TABLE tmp_snap_pk(
  主键列名 主键类型
) ON COMMIT DELETE ROWS;

2. 处理逻辑示例

DECLARE
  CURSOR c_original_data IS 
    SELECT t.id, t.col1, t.col2 
    FROM 你的表名 t
    JOIN tmp_snap_pk s ON t.主键列名 = s.主键列名;
  v_row c_original_data%ROWTYPE;
BEGIN
  -- 插入打开游标瞬间的所有主键，固化快照范围
  INSERT INTO tmp_snap_pk SELECT 主键列名 FROM 你的表名;
  OPEN c_original_data;
  LOOP
    FETCH c_original_data INTO v_row;
    EXIT WHEN c_original_data%NOTFOUND;
    -- 复制逻辑
    INSERT INTO 你的表名(id, col1, col2) VALUES (自定义主键生成规则, v_row.col1, v_row.col2);
  END LOOP;
  CLOSE c_original_data;
  COMMIT;
END;
/

注意事项 #

只要你没有手动修改会话的事务隔离级别为READ UNCOMMITTED（Oracle默认隔离级别为READ COMMITTED），上述两种方案都可以完全满足忽略遍历过程中新插入行的需求。

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