oracle 复制一条记录只改变主键不写全部列名

场景：表TEST中有C1，C2，C3...字段，其中C1为主键，先需要复制表TEST中一条（C1='1'的）记录，修改主键列C1和需要变更的列后，再插入到表TEST中。

procedure P_TEST 执行过程：

create or replace procedure P_TEST(P_EXT_COLS IN VARCHAR2 --需要变更的列
                               ) IS
  VSQL   VARCHAR2(200); --动态SQL
  V_COLS VARCHAR2(200); --自动SELECT出来的列
begin
  DELETE FROM TEST WHERE C1 <> '1';--执行前 还原测试表数据
  --获取表TEST 除了需要变更的列以外的所有列
  SELECT WMSYS.WM_CONCAT(T.COLUMN_NAME) INTO V_COLS FROM USER_TAB_COLUMNS t WHERE t.table_name = 'TEST' AND INSTR(P_EXT_COLS, T.COLUMN_NAME) = 0;
  --SYS_GUID(),XXX对应需要变更的列
  VSQL := 'INSERT INTO TEST (' || P_EXT_COLS || ',' || V_COLS ||') SELECT SYS_GUID(),''XXX'','||V_COLS||' FROM TEST WHERE C1=''1''';
  EXECUTE IMMEDIATE VSQL;
  COMMIT;
end;

测试存储过程：

SQL> SELECT C1,C2,C3 FROM TEST;
 
C1                                         C2                                         C3
------------------------------------------ ------------------------------------------ ------------------------------------------
1                                          2                                          3
0038D118A2C84E288D1021DC2C9B6E76           2                                          3
 
SQL> EXEC P_TEST('C1,C2');
 
PL/SQL procedure successfully completed
 
SQL> SELECT C1,C2,C3 FROM TEST;
 
C1                                         C2                                         C3
------------------------------------------ ------------------------------------------ ------------------------------------------
1                                          2                                          3
56AEB018A375472BA34F5A3EE3E674C9           XXX                                        3
 
SQL> EXEC P_TEST('C1,C3');
 
PL/SQL procedure successfully completed
 
SQL> SELECT C1,C2,C3 FROM TEST;
 
C1                                         C2                                         C3
------------------------------------------ ------------------------------------------ ------------------------------------------
1                                          2                                          3
E4B7CF5D949542F0BA2A04AF21518AF5           2                                          XXX

 

转载于:https://www.cnblogs.com/kcypt/p/4000540.html