oracle存储过程实现定时备份表和处理重复数据

最新推荐文章于 2024-05-29 09:29:20 发布

weijx_

最新推荐文章于 2024-05-29 09:29:20 发布

阅读量1.5k

点赞数 2

分类专栏： JAVA 数据库文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/weijx_/article/details/125557652

版权

JAVA 同时被 2 个专栏收录

31 篇文章 0 订阅

订阅专栏

数据库

10 篇文章 0 订阅

订阅专栏

执行时打印：dbms_output.put_line ( ‘11111111111’ );
拼接的动态sql 可以用 execute immediate 后面加拼接的语句，下面有类似；
可以定义变量，然后使用 into + 变量来使用；
异常可以使用：EXCEPTION WHEN others THEN

一、整体流程说明

1. 首先创建了个日志错误表记录错误信息；

2. 创建一个通用的记录存储过程执行的错误记录（record_proc_err_log）

3. 创建一个处理备份表的存储过程：

删除7天以前的备份表，创建一个当天新的备份表（DAILY_BACKUP）

4. 创建存储过程：处理备份表和处理重复数据（PROCESS_DUPLICATE_DATA）

4.1 查看重复数据，

4.2 如果有重复数据，则删除存在的备份表，创建新的备份表 ;无重复数据则不执行任何处理

4.3 查询备份表是否和原表一致(一致说明备份数据完整，可以处理重复数据了) ，不一致则不处理（说明数据备份不完整）

5. 捕获异常信息，记录到存储过程执行的日志表中

6. 创建一个定时任务去执行处理重复数据的存储过程（配置的每日凌晨 12点执行）

二、脚本说明

1. 创建存储过程执行错误记录表和记录错误的存储过程；

--1.建立执行错误日志表
CREATE TABLE SYS_PUB_PROC_ERR_LOG (
	LOG_ID NUMBER,
	MODULE_NAME VARCHAR2 ( 100 ),
	PROC_NAME VARCHAR2 ( 100 ),
	TIME DATE,
	SQL_CODE VARCHAR2 ( 50 ),
	SQL_ERRM VARCHAR2 ( 100 ),
	ERR_CONTENT VARCHAR2 ( 500 ) 
);
COMMENT ON COLUMN SYS_PUB_PROC_ERR_LOG.LOG_ID IS '主键';
COMMENT ON COLUMN SYS_PUB_PROC_ERR_LOG.MODULE_NAME IS '模块名称';
COMMENT ON COLUMN SYS_PUB_PROC_ERR_LOG.PROC_NAME IS '存储过程名称';
COMMENT ON COLUMN SYS_PUB_PROC_ERR_LOG.TIME IS '时间';
COMMENT ON COLUMN SYS_PUB_PROC_ERR_LOG.SQL_CODE IS 'SQLCODE';
COMMENT ON COLUMN SYS_PUB_PROC_ERR_LOG.SQL_ERRM IS 'SQLERRM';
COMMENT ON COLUMN SYS_PUB_PROC_ERR_LOG.ERR_CONTENT IS '报错的具体行';

--2.表主键的序列
CREATE sequence SEQ_RECORD_PROC_ERR minvalue 1 maxvalue 9999999999999999999999999999 START WITH 21 INCREMENT BY 1 cache 20;

--3.通用记录错误存储过程
CREATE 
	OR REPLACE PROCEDURE record_proc_err_log ( module_name VARCHAR2, proc_name VARCHAR2, v_SQLCODE VARCHAR2, v_SQLERRM VARCHAR2, v_err_line VARCHAR2 ) IS PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
		INSERT INTO sys_pub_proc_err_log ( log_id, module_name, proc_name, time, sql_code, sql_errm, err_content )
	VALUES
		( seq_record_proc_err.nextval, module_name, proc_name, SYSDATE, v_SQLCODE, v_SQLERRM, v_err_line );
	commit;
	
END record_proc_err_log;

2. 处理备份表：定期删除备份表（7天），并创建新的备份表

create or replace PROCEDURE DAILY_BACKUP AS

v_now VARCHAR2(8);

v_seven varchar2(8);

v_7tab1_has int;

v_table_name_1 CONSTANT VARCHAR2(50) := 'eval_sum_newest_info_bak_';

BEGIN

--获取当天日期

select to_char(SYSDATE, 'YYYYMMDD')

into v_now

from dual;

--获取7天前日期

select to_char(TRUNC(SYSDATE -7), 'YYYYMMDD')

into v_seven

from dual;

--删除7天前的表

select count(1) into v_7tab1_has from user_tables where TABLE_NAME = upper(v_table_name_1  || v_seven);

if v_7tab1_has=1 then

--如果存在，则执行drop table  (drop 是不能回滚的)
dbms_output.put_line ( '删除的表, '|| v_table_name_1  || v_seven );
execute immediate 'drop table '|| v_table_name_1  || v_seven;

end if;

--创建当天的数据备份
dbms_output.put_line ( 'CREATE TABLE ' || v_table_name_1  || v_now || ' AS SELECT * FROM eval_sum_newest_info');
EXECUTE IMMEDIATE 'CREATE TABLE ' || v_table_name_1  || v_now || ' AS SELECT * FROM eval_sum_newest_info';

COMMIT;
			EXCEPTION 
				WHEN others THEN
				record_proc_err_log ( 'process', 'data', SQLCODE, SQLERRM, substr( dbms_utility.format_error_backtrace, 1, 400 ) );
			ROLLBACK;
			
END DAILY_BACKUP;

3. 处理重复数据的存储过程；

-- 创建存储过程 先调用处理备份表，然后 处理重复数据
CREATE 
	OR REPLACE PROCEDURE PROCESS_DUPLICATE_DATA IS -- 定义变量
row1 NUMBER;
row2 NUMBER;
v_now VARCHAR2 ( 8 );
v_table_name_1 CONSTANT VARCHAR2 ( 50 ) := 'eval_sum_newest_info_bak_';
BEGIN--获取当天日期
	SELECT
		to_char( SYSDATE, 'YYYYMMDD' ) INTO v_now 
	FROM
		dual;
-- 查看重复数据
	SELECT
		sum( count( * ) ) INTO row1 
	FROM
		EVAL_SUM_NEWEST_INFO 
	GROUP BY
		eval_basic_information_id,
		eval_sum_risk_info_id,
		risk_dept_id 
	HAVING
		count( * ) > 1;
	IF
		( row1 > 0 ) THEN-- 如果存在重复数据，则删除存在的备份表(7天)，创建新的备份表
			DAILY_BACKUP ( );
		BEGIN-- 查询 备份表 是否和 原表一致(说明备份数据完整，可以处理重复数据了)
			
			dbms_output.put_line ( '创建的表： ' || v_table_name_1 || v_now );
			dbms_output.put_line ( 'SELECT count( * ) FROM ( SELECT * FROM EVAL_SUM_NEWEST_INFO MINUS SELECT * FROM ' || v_table_name_1 || v_now || ' ) t' );
			-- 把拼接的语句 作比较，然后把数量 赋值给row2 
			execute IMMEDIATE 'SELECT count( * ) FROM ( SELECT * FROM EVAL_SUM_NEWEST_INFO MINUS SELECT * FROM ' || v_table_name_1 || v_now || ' ) t' INTO row2;
			
			IF
				( row2 > 0 ) THEN-- 这里是不一致(不做清理数据)
					
					dbms_output.put_line ( '00000000000' );
				
				ELSE dbms_output.put_line ( '11111111111' );
				
				-- 删除重复数据
				execute IMMEDIATE 'delete  EVAL_SUM_NEWEST_INFO WHERE id in  
				(
				select id from (
				select t.*, row_number() over(partition by eval_basic_information_id,eval_sum_risk_info_id,risk_dept_id order by id ) rn from EVAL_SUM_NEWEST_INFO t 
				) where rn > 1
				)';
				
			END IF;
			COMMIT;
			EXCEPTION 
				WHEN others THEN
				record_proc_err_log ( 'process', 'data', SQLCODE, SQLERRM, substr( dbms_utility.format_error_backtrace, 1, 400 ) );
			ROLLBACK;
			
		END;
		
	END IF;
END;

4. 创建定时任务执行存储过程；

-- 1.创建任务
declare duplicate_data_job number;  --duplicate_data_job定时任务名称

begin

sys.dbms_job.submit(

duplicate_data_job, --任务名称

'PROCESS_DUPLICATE_DATA;',--执行的存储过程

sysdate,--执行时间

--'TRUNC(SYSDATE + 1) + (15*60+52)/(24*60)'--下次执行时间 11:55
'TRUNC(SYSDATE + 1)'  --每天午夜12点
--'sysdate+2/(24*60)'   --每2分钟执行一次

);

COMMIT;

end;

5. 查看任务的一些sql 命令（不需要执行，即参考）；


-- 以下不需要执行，一些sql做参考 的查看命令


-- 查看定时任务
SELECT * FROM user_jobs;

-- 手动启动，执行完后再执行3 查看，如果next_date 没有自动变成明天这个时间的话，那么就需要配置下5 
BEGIN

dbms_job.run(25);

END;

-- ********* 
--在 90上试验之后是可以的 （通过查阅资料发现原来有一个参数job_queue_processes数字为0是定时任务都不会执行）
alter system set job_queue_processes =10;
-- ********* 



-- 删除存储过程
DROP PROCEDURE PROCESS_DUPLICATE_DATA;


-- 停止任务
BEGIN

dbms_job.broken(25,true);

END;


-- 删除任务
begin

dbms_job.remove(45);--ALL_JOBS.job的值

end;

三、嵌套begin end 里面 begin

-- 1.创建表 参数：表名，建表语句
CREATE 
	OR REPLACE PROCEDURE PROCESS_DUPLICATE_DATA2 IS row1 NUMBER;
row2 NUMBER;
BEGIN
	SELECT
		count( table_name ) INTO row1 
	FROM
		all_tables 
	WHERE
		table_name = upper( 'eval_sum_newest_info_bak' );
	IF
		( row1 = 1 ) THEN-- 删除存在的备份表，创建新的备份表
--execute immediate 'drop table EVAL_SUM_NEWEST_INFO_BAK';
--execute immediate 'create table eval_sum_newest_info_bak as select * from eval_sum_newest_info';
			dbms_output.put_line ( '00000000000' );
		BEGIN
			SELECT
				count( * ) INTO row2 
			FROM
				( SELECT * FROM EVAL_SUM_NEWEST_INFO MINUS SELECT * FROM EVAL_SUM_NEWEST_INFO_BAK ) t;
			IF
				( row2 > 0 ) THEN
					dbms_output.put_line ( '11111111111' );
				ELSE dbms_output.put_line ( '222222222222' );
				
			END IF;
			
		END;
		ELSE dbms_output.put_line ( '33333333333' );
		
	END IF;
END;

四、多个begin end 并行

CREATE 
	OR REPLACE PROCEDURE test IS row1 NUMBER;--第一个游标
row2 NUMBER;--第二个游标
BEGIN
BEGIN
	SELECT
		count( table_name ) INTO row1 
	FROM
		all_tables 
	WHERE
		table_name = upper( 'eval_sum_newest_info_bak' );
	IF
		( row1 = 1 ) THEN-- 删除存在的备份表，创建新的备份表
--execute immediate 'drop table EVAL_SUM_NEWEST_INFO_BAK';
--execute immediate 'create table eval_sum_newest_info_bak as select * from eval_sum_newest_info';
			dbms_output.put_line ( '0000000000' );
		ELSE dbms_output.put_line ( '33333333333' );
		
	END IF;
	
END;


--分开
BEGIN
SELECT
	count( * ) INTO row2 
FROM
	( SELECT * FROM EVAL_SUM_NEWEST_INFO MINUS SELECT * FROM EVAL_SUM_NEWEST_INFO_BAK ) t;
IF
	( row2 > 0 ) THEN
		dbms_output.put_line ( '11111111111' );
		ELSE dbms_output.put_line ( '222222222222' );
		
	END IF;
END;

END;