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