Oracle SQL多表复制插入：请求状态创建与值复制技术问询

针对你的需求，这里有两种实用的方案来完成步骤2，考虑到数据量仅500-750条，两种方案都能轻松满足性能要求：

方案一：使用临时表存储新创建的状态ID（最可靠） #

这个方案通过临时表记录步骤1生成的新request_status_id及其对应的request_id，能准确关联到原活跃状态的Values记录，避免重复或错误匹配。

步骤1：创建临时表 #

首先创建一个会话级临时表，用于存储步骤1插入的结果：

CREATE GLOBAL TEMPORARY TABLE Temp_New_Statuses (
    request_id NUMBER,
    new_status_id NUMBER
) ON COMMIT PRESERVE ROWS;

步骤2：修改步骤1的SQL，将插入结果写入临时表 #

通过Oracle的RETURNING子句结合PL/SQL块，把新生成的状态ID和对应的请求ID存入临时表：

DECLARE
    TYPE status_rec IS RECORD (
        req_id NUMBER,
        status_id NUMBER
    );
    TYPE status_tab IS TABLE OF status_rec;
    v_statuses status_tab;
BEGIN
    INSERT INTO Request_Statuses (fk_request_id, fk_enum_value_id /*, 其他无关字段 */)
    SELECT rs1.fk_request_id, 4 /*, 其他无关字段 */
    FROM Request_Statuses rs1
    LEFT JOIN Request_Statuses rs2 ON (rs1.fk_request_id = rs2.fk_request_id AND rs1.created_date < rs2.created_date)
    WHERE rs2.created_date IS NULL
      AND rs1.fk_request_id IN (
          SELECT r.request_id
          FROM Requests r
          WHERE r.fk_person_id IN (SELECT p.person_id FROM Persons p WHERE p.unique_code IN ('12345','67890'))
            AND r.year = 2017
      )
    RETURNING fk_request_id, request_status_id BULK COLLECT INTO v_statuses;

    FORALL i IN 1..v_statuses.COUNT
        INSERT INTO Temp_New_Statuses (request_id, new_status_id)
        VALUES (v_statuses(i).req_id, v_statuses(i).status_id);
    COMMIT;
END;
/

步骤3：执行步骤2的插入SQL #

关联临时表和原Values记录，复制并替换fk_request_status_id：

INSERT INTO Values (fk_request_status_id /*, 其他无关字段 */)
SELECT tns.new_status_id /*, v1.其他无关字段 */
FROM Values v1
JOIN Request_Statuses original_rs ON v1.fk_request_status_id = original_rs.request_status_id
JOIN Temp_New_Statuses tns ON original_rs.fk_request_id = tns.request_id
-- 确保关联的是原活跃状态（和步骤1的条件一致）
WHERE NOT EXISTS (
    SELECT 1 FROM Request_Statuses rs2 
    WHERE rs2.fk_request_id = original_rs.fk_request_id 
      AND rs2.created_date > original_rs.created_date
);

方案二：纯SQL关联查询（无需临时表） #

如果不想使用临时表，也可以通过直接关联Request_Statuses表，找到步骤1创建的新状态ID。这个方案依赖于步骤1中每个符合条件的request_id仅插入一条fk_enum_value_id=4的状态记录：

INSERT INTO Values (fk_request_status_id /*, 其他无关字段 */)
SELECT new_rs.request_status_id /*, v1.其他无关字段 */
FROM Values v1
-- 关联原活跃状态
JOIN Request_Statuses original_rs 
    ON v1.fk_request_status_id = original_rs.request_status_id
-- 关联步骤1创建的新状态（fk_enum_value_id=4）
JOIN Request_Statuses new_rs 
    ON original_rs.fk_request_id = new_rs.fk_request_id
    AND new_rs.fk_enum_value_id = 4
-- 过滤符合条件的Person和年份
JOIN Requests r ON original_rs.fk_request_id = r.request_id
JOIN Persons p ON r.fk_person_id = p.person_id
WHERE p.unique_code IN ('12345','67890')
  AND r.year = 2017
-- 确保原状态是该请求的最新活跃状态（和步骤1逻辑一致）
  AND NOT EXISTS (
      SELECT 1 FROM Request_Statuses rs2 
      WHERE rs2.fk_request_id = original_rs.fk_request_id 
        AND rs2.created_date > original_rs.created_date
  )
-- 确保新状态是步骤1创建的最新那条（避免重复插入的情况）
  AND NOT EXISTS (
      SELECT 1 FROM Request_Statuses rs3 
      WHERE rs3.fk_request_id = new_rs.fk_request_id 
        AND rs3.fk_enum_value_id = 4
        AND rs3.created_date > new_rs.created_date
  );

注意事项 #

• 因为Values表有fk_request_status_id+其他字段的唯一约束，复制原记录时仅替换fk_request_status_id，其他字段保持不变，不会违反约束。
• 方案一的临时表是会话级的，会话结束后会自动清空，不会留下冗余数据。

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