Oracle日志表的使用方式_Oracle

1.日志表定义

日志一般会记录：同步的源表名，同步的目标表名，步骤名称，记录行数，状态，开始时间，结束时间，备注。

2.创建日志表

create table log_table
(
    source_table_name varchar2(100),
    target_table_name varchar2(100),
    step_name         varchar2(100),
    row_count         number,
    status            varchar2(30),
    start_dt          date,
    end_dt            date,
    mark              varchar2(100)
);

3.开发往log_table同步数据的存储过程

create or replace procedure p_log(
    p_source_table_name varchar2,
    p_target_table_name varchar2,
    p_step_name varchar2,
    p_row_count number,
    p_status varchar2,
    p_start_dt date,
    p_end_dt date,
    p_mark varchar2) is
begin
    insert into log_table
    values (p_source_table_name,
            p_target_table_name,
            p_step_name,
            p_row_count,
            p_status,
            p_start_dt,
            p_end_dt,
            p_mark);
    commit;

    ---异常
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;

-- 调用存储过程
begin
  p_log(p_source_table_name,
        p_target_table_name,
        p_step_name,
        p_row_count,
        p_status,
        p_start_dt,
        p_end_dt,
        p_mark);

end;

4.开发存储过程 emp同步数据到 emp_1135

drop table emp_1135;
create table emp_1135 as select * from emp where 1 = 2;

-- 给emp_1135表添加主键
alter table emp_1135 add constraint pk_emp_1135 primary key (empno);

-- 创建存储过程
create or replace procedure p_19
as
    v_source varchar2(20);
    v_target varchar2(20);
    v_st     date;
    v_dt     date;
    v_ct     number;
begin
    v_st := sysdate;
    v_source := 'emp';
    v_target := 'emp_1135';
    insert into emp_1135
    select *
    from emp;
    v_ct := sql%rowcount;
    commit;
    v_dt := sysdate;

    -- 调用日志表存储过程
    p_log(p_source_table_name=>v_source,
          p_target_table_name=>v_target,
          p_step_name=>v_source || ' to ' || v_target,
          p_row_count=>v_ct,
          p_status=>'成功',
          p_start_dt=>v_st,
          p_end_dt=>v_dt,
          p_mark=>'');
    -- 定义异常
exception
    when others then
        p_log(p_source_table_name=>v_source,
              p_target_table_name=>v_target,
              p_step_name=>v_source || ' to ' || v_target,
              p_row_count=>0,
              p_status=>'失败',
              p_start_dt=>v_st,
              p_end_dt=>null,
              p_mark=>sqlerrm);
end;

-- 调用存储过程
begin
    p_19;
end;

select * from emp_1135;

-- 查询日志表
select * from log_table;

再次调用存储过程

-- 调用存储过程
begin
    p_19;
end;

select * from emp_1135;

-- 查询日志表
select * from log_table;

5.开发一个存储过程

将emp表同步到 emp_1134，然后将通过emp_1134这个表数据计算每个部门总薪资，同步到 emp_sum_sal

create table emp_1134 as select * from emp where 1=2;

-- 添加主键
alter table emp_1134 
add constraint pk_emp_1134 primary key (empno);

create table emp_sum_sal (deptno number,sum_sal number);

create or replace procedure p_20 as
  v_st     date;
  v_dt     date;
  v_ct     number;
  v_source varchar2(50);
  v_dir    varchar2(50);
begin
  v_source := 'emp';
  v_dir    := 'emp_1134';
  v_st     := sysdate;
  insert into emp_1134
    select * from emp;
  v_ct := sql%rowcount;
  commit;
  v_dt := sysdate;

  p_log(p_source_table_name => v_source,
        p_target_table_name => v_dir,
        p_step_name         => v_source || ' to ' || v_dir,
        p_row_count         => v_ct,
        p_status            => '成功',
        p_start_dt          => v_st,
        p_end_dt            => v_dt,
        p_mark              => '');
  -------------------------------------------------------
  v_source := 'emp_1134';
  v_dir    := 'emp_sum_sal';
  v_st     := sysdate;
  insert into emp_sum_sal
    select deptno, sum(sal) from emp_1134 group by deptno;
  v_ct := sql%rowcount;
  commit;
  v_dt := sysdate;

  p_log(p_source_table_name => v_source,
        p_target_table_name => v_dir,
        p_step_name         => v_source || ' to ' || v_dir,
        p_row_count         => v_ct,
        p_status            => '成功',
        p_start_dt          => v_st,
        p_end_dt            => v_dt,
        p_mark              => '');
  ---异常处理
exception
  when others then
    -- dbms_output.put_line(sqlerrm);
    -- raise; 可以添加弹窗
    p_log(p_source_table_name => v_source,
          p_target_table_name => v_dir,
          p_step_name         => v_source || ' to ' || v_dir,
          p_row_count         => 0,
          p_status            => '失败',
          p_start_dt          => v_st,
          p_end_dt            => null,
          p_mark              => sqlerrm);
end;

begin
  p_20;
end;

-- 查询日志表
select * from log_table;

select * from emp_1134;

select * from emp_sum_sal;

再次调用存储过程

begin
  p_20;
end;

-- 查询日志表
select * from log_table;

select * from emp_1134;

select * from emp_sum_sal;

第二次调用存储过程时，因为emp_1134有主键，所以当第二次insert到emp_1134时检测到异常，直接抛出，不会往下走

6.日志表的功能

通过写日志表，能够记录存储过程哪一个步骤执行成功，哪一个步骤执行失败了，以及能记录每个步骤的执行时间，方便开发者后期对其优化，以及方便，检查。

日志的另一大功能点：程序报错的时候，记录程序报错的步骤以及错误的原因。
例如：存储过程的同步逻辑(比如源表有10条数据，日志表中记录，同步过去的行数有20条，说明sql中存在数据发散)
练习：全量同步 dept 表到 dept_1123，并记录详细的日志信息，以及出现异常，则抛出。

----创建目标表  
create table dept_1123 as select * from dept where 1 = 2;

----开发存储过程
create or replace procedure p_dept
    is
    v_rowcount number;
    v_start_dt date;
    v_end_dt   date;
begin
    v_start_dt := sysdate;
    ----清空目标表
    execute immediate 'truncate table dept_1123';
    -----插入数据
    insert into dept_1123
    select *
    from dept;

    v_rowcount := sql%rowcount;
    commit;
    v_end_dt := sysdate;

    p_log(p_source_table_name =>'dept',
          p_target_table_name => 'dept_1123',
          p_step_name =>'dept同步数据到dept_1123',
          p_row_count => v_rowcount,
          p_status => 'success',
          p_start_dt => v_start_dt,
          p_end_dt => v_end_dt,
          p_mark =>'执行成功');

    -------异常处理
exception
    when others
        then dbms_output.put_line(sqlerrm);
        p_log(p_source_table_name =>'dept',
              p_target_table_name => 'dept_1123',
              p_step_name =>'dept同步数据到dept_1123',
              p_row_count => 0,
              p_status => 'fail',
              p_start_dt => v_start_dt,
              p_end_dt => null,
              p_mark =>sqlerrm);
        raise;
end;

----调用
begin
    p_dept;
end;


----验证
select * from dept_1123;

select * from log_table;