场景:表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