执行时 打印：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. 创建存储过程执行错误记录表和记录错误的存储过程；
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 '报错的具体行';
CREATE sequence SEQ_RECORD_PROC_ERR minvalue 1 maxvalue 9999999999999999999999999999 START WITH 21 INCREMENT BY 1 cache 20;
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;
select to_char(TRUNC(SYSDATE -7), 'YYYYMMDD')
into v_seven
from dual;
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
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
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' );
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. 创建定时任务 执行存储过程；
declare duplicate_data_job number;
begin
sys.dbms_job.submit(
duplicate_data_job,
'PROCESS_DUPLICATE_DATA;',
sysdate,
'TRUNC(SYSDATE + 1)'
);
COMMIT;
end;
5. 查看任务的一些sql 命令（不需要执行，即参考）；
SELECT * FROM user_jobs;
BEGIN
dbms_job.run(25);
END;
alter system set job_queue_processes =10;
DROP PROCEDURE PROCESS_DUPLICATE_DATA;
BEGIN
dbms_job.broken(25,true);
END;
begin
dbms_job.remove(45);
end;
三、嵌套begin end 里面 begin
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
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
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;
|