Oracle日志表的使用方式

更新時間：2025年06月09日 09:55:03 作者：雷神樂樂

這篇文章主要介紹了Oracle日志表的使用方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

1.日志表定義

日志一般會記錄：同步的源表名，同步的目標表名，步驟名稱，記錄行數(shù)，狀態(tài)，開始時間，結(jié)束時間，備注。

2.創(chuàng)建日志表

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.開發(fā)往log_table同步數(shù)據(jù)的存儲過程

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;

-- 調(diào)用存儲過程
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.開發(fā)存儲過程 emp同步數(shù)據(jù)到 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);

-- 創(chuàng)建存儲過程
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;

    -- 調(diào)用日志表存儲過程
    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;

-- 調(diào)用存儲過程
begin
    p_19;
end;

select * from emp_1135;

-- 查詢?nèi)罩颈?
select * from log_table;

再次調(diào)用存儲過程

-- 調(diào)用存儲過程
begin
    p_19;
end;

select * from emp_1135;

-- 查詢?nèi)罩颈?
select * from log_table;

5.開發(fā)一個存儲過程

將EMP表同步到 EMP_1134，然后將通過EMP_1134這個表數(shù)據(jù)計算每個部門總薪資，同步到 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;

-- 查詢?nèi)罩颈?
select * from log_table;

select * from emp_1134;

select * from EMP_SUM_SAL;

再次調(diào)用存儲過程

begin
  p_20;
end;

-- 查詢?nèi)罩颈?
select * from log_table;

select * from emp_1134;

select * from EMP_SUM_SAL;

第二次調(diào)用存儲過程時，因為emp_1134有主鍵，所以當?shù)诙蝘nsert到emp_1134時檢測到異常，直接拋出，不會往下走

6.日志表的功能

通過寫日志表，能夠記錄存儲過程哪一個步驟執(zhí)行成功，哪一個步驟執(zhí)行失敗了，以及能記錄每個步驟的執(zhí)行時間，方便開發(fā)者后期對其優(yōu)化，以及方便，檢查。

日志的另一大功能點：程序報錯的時候，記錄程序報錯的步驟以及錯誤的原因。
例如：存儲過程的同步邏輯(比如源表有10條數(shù)據(jù)，日志表中記錄，同步過去的行數(shù)有20條，說明SQL中存在數(shù)據(jù)發(fā)散)
練習(xí)：全量同步 DEPT 表到 DEPT_1123，并記錄詳細的日志信息，以及出現(xiàn)異常，則拋出。

----創(chuàng)建目標表  
CREATE TABLE dept_1123 AS SELECT * FROM dept WHERE 1 = 2;

----開發(fā)存儲過程
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';
    -----插入數(shù)據(jù)
    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同步數(shù)據(jù)到dept_1123',
          p_ROW_COUNT => v_rowcount,
          p_status => 'success',
          p_start_dt => v_start_dt,
          p_end_dt => v_end_dt,
          p_mark =>'執(zhí)行成功');

    -------異常處理
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同步數(shù)據(jù)到dept_1123',
              p_ROW_COUNT => 0,
              p_status => 'fail',
              p_start_dt => v_start_dt,
              p_end_dt => NULL,
              p_mark =>SQLERRM);
        RAISE;
END;

----調(diào)用
BEGIN
    p_dept;
END;


----驗證
SELECT * FROM dept_1123;

SELECT * FROM log_table;