Oracle中如何不罗列全列名，实现查询后修改单字段值并快速插入记录

你可以直接利用结构化行对象的特性实现需求，全程不需要手动罗列全量列名，完全适配几百列的大宽表场景，具体实现方式如下：

方式1：PL/SQL 行变量操作（生产环境首选） #

借助Oracle的%ROWTYPE自动绑定表结构，不需要手动列字段：

DECLARE
  -- 自动对齐目标表所有字段的结构和类型，零手写列名成本
  v_row tt%ROWTYPE;
BEGIN
  -- 查询待复制的源记录，完整存入行变量
  SELECT * INTO v_row FROM tt WHERE id = 1;
  -- 仅修改需要调整的单个字段，其余字段完全保留源值
  v_row.name := 'x1';
  -- 整行插入目标表，不需要写任何列名
  INSERT INTO tt VALUES v_row;
  COMMIT;
END;
/

哪怕表有上百个列、后续表结构调整，这段代码也不需要改动，只需要调整需要赋值的那个字段即可，从根源上避免列值错位、类型不匹配的问题。

方式2：基于数据字典的动态SQL（适合临时执行场景） #

如果不想写完整的PL/SQL块，可以直接读取数据字典自动获取列清单，动态拼接SQL，不需要手动查列名：

DECLARE
  v_dynamic_sql CLOB;
  v_rest_cols VARCHAR2(4000);
BEGIN
  -- 按表定义顺序，自动取出除待修改的name字段之外的所有列名
  SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_id)
  INTO v_rest_cols
  FROM user_tab_columns
  WHERE table_name = 'TT'
    AND column_name != 'NAME';
  -- 拼接出可直接执行的插入语句，把待修改字段的值设为目标取值
  v_dynamic_sql := 'INSERT INTO tt (name, ' || v_rest_cols || ') '
               || 'SELECT ''x1'', ' || v_rest_cols || ' FROM tt WHERE id = 1';
  EXECUTE IMMEDIATE v_dynamic_sql;
  COMMIT;
END;
/

实操注意点 #

• 不要靠手写SELECT *的结果手动改单字段值来实现逻辑，一旦后续表新增列、调整列顺序，很容易出现插入值和列不匹配的问题
• 如果表的主键是序列生成、或者有触发器自动补全字段值的场景，用行变量插入前需要把对应字段置空（比如v_row.id := NULL;），避免带源行的主键值造成插入冲突

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