使用oracle备份数据,[ORACLE]通用备份数据存储过程，适合编辑/删除前需要备份数据时使用...-CSDN博客

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

查询结果如下：

完成