如何在Oracle中基于现有行批量生成指定数量新行并修改部分字段

方法1：Oracle 12cR2+ 简洁写法（支持SELECT * EXCEPT） #

如果你的Oracle版本是12cR2及以上，可以利用SELECT * EXCEPT语法快速排除不需要复制的字段，结合CONNECT BY生成指定数量的行：

-- 生成100条新记录，基于指定模板记录
INSERT INTO card_table
SELECT 
  -- 自定义ID生成规则：这里用序列+行号，也可以用MAX(id)+LEVEL或其他逻辑
  your_id_sequence.NEXTVAL + LEVEL AS id,
  SYSTIMESTAMP AS inserted, -- 字段是DATE类型则替换为SYSDATE
  SYSTIMESTAMP AS lastModified,
  -- 排除要修改的字段，复制其余所有字段
  t.* EXCEPT (id, inserted, lastModified)
FROM card_table t
-- 选择一条模板记录（可根据需求修改WHERE条件，比如选最新记录、特定条件记录）
WHERE t.id = '模板记录ID' 
-- 指定要生成的新记录数量
CONNECT BY LEVEL <= 100;

COMMIT;

方法2：低版本Oracle（无SELECT * EXCEPT）：动态SQL存储过程 #

如果Oracle版本低于12cR2，无法使用SELECT * EXCEPT，可以通过动态SQL自动生成字段列表，避免手动逐个输入大量字段：

CREATE OR REPLACE PROCEDURE generate_new_card_records(
  p_target_count IN NUMBER, -- 要生成的新记录数量
  p_template_condition IN VARCHAR2 -- 模板记录的筛选条件，比如'id = ''ABC123'''
) AS
  v_insert_sql VARCHAR2(4000);
BEGIN
  -- 动态拼接字段列表：排除id、inserted、lastModified，取其余所有字段
  SELECT 'INSERT INTO card_table (id, inserted, lastModified, ' 
         || LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id) 
         || ') SELECT your_id_sequence.NEXTVAL + LEVEL, SYSTIMESTAMP, SYSTIMESTAMP, '
         || LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id)
         || ' FROM card_table WHERE ' || p_template_condition 
         || ' CONNECT BY LEVEL <= ' || p_target_count
  INTO v_insert_sql
  FROM user_tab_columns
  WHERE table_name = 'CARD_TABLE'
    AND UPPER(column_name) NOT IN ('ID', 'INSERTED', 'LASTMODIFIED');

  -- 执行动态SQL
  EXECUTE IMMEDIATE v_insert_sql;
  COMMIT;
END;
/

调用示例：

-- 生成50条新记录，基于id为'ABC123'的模板记录
EXEC generate_new_card_records(50, 'id = ''ABC123''');

关键注意事项 #

• ID生成逻辑：
  • 推荐使用序列（your_id_sequence.NEXTVAL）保证ID唯一性，避免并发插入时重复；
  • 如果不用序列，也可以用(SELECT MAX(id) FROM card_table) + LEVEL，但需注意并发场景下的冲突风险。
• 模板记录选择：
  • 可以修改WHERE条件选择任意符合需求的模板记录，比如ROWNUM = 1取第一条记录，或者lastModified = (SELECT MAX(lastModified) FROM card_table)取最新记录；
  • 如果需要基于多条模板记录生成指定总数的新行，可调整为SELECT ... FROM card_table t, (SELECT LEVEL FROM dual CONNECT BY LEVEL <= p_target_count) nr WHERE ... AND ROWNUM <= p_target_count。
• 时间字段适配：
  • 如果inserted和lastModified是DATE类型，替换为SYSDATE；如果是TIMESTAMP类型，用SYSTIMESTAMP。
• 避免批量复制所有记录：
  • 利用CONNECT BY LEVEL <= 指定数量严格控制生成的行数，不会像之前的循环逻辑那样每次复制所有符合条件的记录。

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