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

    执行时 打印：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;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    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;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40

    三、嵌套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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33

    四、多个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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
  • 相关阅读:
    马原复习知识点背诵-《马克思主义基本原理概论》
    微信小程序，配置web-view业务域名，文件检测失败
    华为性格测试通关指南
    OceanBase单机版安装体验
    广州穗雅医院健康汇：为什么口腔溃疡会反反复复？
    java毕业设计春之梦理发店管理Mybatis+系统+数据库+调试部署
    计算机毕业设计ssm大学生身心健康管理系统的设计与实现d223r系统+程序+源码+lw+远程部署
    【ECMAScript6】其它新增接口特性
    Unet医学细胞分割实战
    Elasticsearch 8.X 路径检索的企业级玩法
  • 原文地址：https://blog.csdn.net/weijx_/article/details/125557652