[ORACLE]通用备份数据存储过程，适合编辑/删除前需要备份数据时使用

设计目的：在数据操作和数据编辑前，往往需要把数据给备份起来，但是如果每个表都要写一遍备份操作，会造成代码冗余。该过程能适用任何表，自动创建备份表并备份需要删除的记录。支持一次传入多个主键ID

不多说，直接上代码。

CREATE OR REPLACE PROCEDURE PRO_TABLE_BACKUP(I_TABLE_NAME    VARCHAR2, --操作的表名
                                             I_PK_COLUNM     VARCHAR2, --操作的主键列名
                                             I_PK_ID_VALUE   VARCHAR2, --操作的主键值,多个用英文逗号分隔
                                             I_EDIT_USERID   VARCHAR2, --操作的工号
                                             I_EDIT_USERNAME VARCHAR2, --操作的姓名
                                             I_EDIT_REMARK   VARCHAR2) IS
  V_COLUMNS_COUNT1   NUMBER; --列数
  V_COLUMNS_COUNT2   NUMBER; --列数
  V_TEMP_TABLENAME   VARCHAR2(200); --临时表名
  V_BACKUP_TABLENAME VARCHAR2(200); --创建的备份表名
  V_TABLE_COUNT      NUMBER; --表是否存在
  V_COLUMNSSTR       VARCHAR2(2000); --列字符串
  V_PK_COLUMNNAME    VARCHAR2(100); --主键ID名称
  V_SQLTEXT          VARCHAR2(2000);
  V_PK_SQL           VARCHAR2(200);--操作的主键值(对于多个时用in连接)
BEGIN
  /**自动创建备份表
  ADD BY ZHENGYANGBO 20130730
  */
  --检查备份表跟需要备份的表结构是否一致。不一致则重新创建后再备份数据
  
  --==========准备工作开始============--
  
  V_BACKUP_TABLENAME := 'BACKUP_' || I_TABLE_NAME;
  --如果是传入了多个ID值，则把查询语句拼接成IN的方式
  IF INSTR(I_PK_ID_VALUE,',') > 0 THEN
     V_PK_SQL := ' IN ( ''' || REPLACE(RTRIM(LTRIM(I_PK_ID_VALUE,','),','),',',''',''') || ''')';
  ELSE
     V_PK_SQL := ' = ''' || I_PK_ID_VALUE || '''';
  END IF;
  
  SELECT 'TEMP_TABLE_' || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
    INTO V_TEMP_TABLENAME
    FROM DUAL; --临时表名
    
  --==========准备工作完成============--
  SELECT COUNT(*)
    INTO V_TABLE_COUNT
    FROM USER_TABLES
   WHERE TABLE_NAME = UPPER(V_BACKUP_TABLENAME); --查看是否存在表
  IF V_TABLE_COUNT > 0 THEN
    SELECT COUNT(*)
      INTO V_COLUMNS_COUNT1
      FROM USER_TAB_COLUMNS
     WHERE TABLE_NAME = I_TABLE_NAME; --统计临时表的列数
    SELECT COUNT(*)
      INTO V_COLUMNS_COUNT2
      FROM USER_TAB_COLUMNS
     WHERE TABLE_NAME = V_BACKUP_TABLENAME; --统计需要备份的表的列数
    IF V_COLUMNS_COUNT1 <> V_COLUMNS_COUNT2 - 3 THEN
      DBMS_OUTPUT.put_line('正在重建表');
      --先备份数据
      EXECUTE IMMEDIATE 'CREATE TABLE ' || V_TEMP_TABLENAME ||
                        ' AS SELECT * FROM BACKUP_' || I_TABLE_NAME;
      --删除表
      EXECUTE IMMEDIATE 'DROP TABLE ' || V_BACKUP_TABLENAME;
      --重新创建备份表
      EXECUTE IMMEDIATE 'CREATE TABLE ' || V_BACKUP_TABLENAME ||
                        ' AS SELECT SYSDATE EDIT_DATE,''' || I_EDIT_USERID ||
                        ''' EDIT_USERID,''' || I_EDIT_REMARK ||
                        ''' EDIT_REMARK,T.* FROM ' || I_TABLE_NAME ||
                        ' T WHERE 1 > 2';
      EXECUTE IMMEDIATE 'ALTER TABLE ' || V_BACKUP_TABLENAME ||
                        ' MODIFY (EDIT_REMARK VARCHAR2(200),EDIT_USERID VARCHAR2(40))';
      SELECT FN_QUERY_TABLE_COLUMNSTR(V_TEMP_TABLENAME)
        INTO V_COLUMNSSTR
        FROM DUAL;
      --备份数据插入
      EXECUTE IMMEDIATE 'INSERT INTO ' || V_BACKUP_TABLENAME || '(' ||
                        V_COLUMNSSTR || ') SELECT ' || V_COLUMNSSTR ||
                        ' FROM ' || V_TEMP_TABLENAME;
      --删除临时表
      EXECUTE IMMEDIATE 'DROP TABLE ' || V_TEMP_TABLENAME;
    END IF;
    --备份该条数据
    EXECUTE IMMEDIATE ' INSERT INTO ' || V_BACKUP_TABLENAME ||
                      ' SELECT SYSDATE,''' || I_EDIT_USERID || ''',''[' ||
                      I_EDIT_USERNAME || ']' || I_EDIT_REMARK ||
                      ''' ,T.* FROM ' || I_TABLE_NAME || ' T WHERE ' ||
                      I_PK_COLUNM || V_PK_SQL;
    --DBMS_OUTPUT.put_line(V_SQLTEXT);
    --EXECUTE IMMEDIATE V_SQLTEXT;
  ELSE
    EXECUTE IMMEDIATE 'CREATE TABLE ' || V_BACKUP_TABLENAME ||
                      ' AS SELECT SYSDATE EDIT_DATE,''' || I_EDIT_USERID ||
                      ''' EDIT_USERID,''' || I_EDIT_REMARK ||
                      ''' EDIT_REMARK,T.* FROM ' || I_TABLE_NAME ||
                      ' T  WHERE 1 > 2';
    EXECUTE IMMEDIATE 'ALTER TABLE ' || V_BACKUP_TABLENAME ||
                      ' MODIFY (EDIT_REMARK VARCHAR2(200),EDIT_USERID VARCHAR2(40))';
    EXECUTE IMMEDIATE ' INSERT INTO ' || V_BACKUP_TABLENAME ||
                      ' SELECT SYSDATE,''' || I_EDIT_USERID || ''',''[' ||
                      I_EDIT_USERNAME || ']' || I_EDIT_REMARK ||
                      ''' ,T.* FROM ' || I_TABLE_NAME || ' T WHERE ' ||
                      I_PK_COLUNM || V_PK_SQL;
  END IF;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace);
    DBMS_OUTPUT.put_line(sqlcode || ',    ' || SQLERRM);
  
END PRO_TABLE_BACKUP;

使用到的函数

CREATE OR REPLACE FUNCTION FN_QUERY_TABLE_COLUMNSTR(I_TABLE_NAME IN VARCHAR2) RETURN VARCHAR2 AS
/**
查询某个表的所有列拼接成字符串的形式
ZHENGYANGBO 20130730
*/
BEGIN
  DECLARE
    COLUMNS_NAME VARCHAR2(2000) := '';
  BEGIN
    FOR X IN (SELECT COLUMN_NAME COL_NAME
                FROM USER_TAB_COLUMNS
               WHERE TABLE_NAME = UPPER(I_TABLE_NAME)) LOOP
      COLUMNS_NAME := COLUMNS_NAME || ',' || X.COL_NAME;
    END LOOP;
    COLUMNS_NAME := LTRIM(COLUMNS_NAME, ',');
    --DBMS_OUTPUT.PUT_LINE(COLUMNS_NAME);
    RETURN COLUMNS_NAME;
  EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
  END;
END;

查询结果如下：

完成