Oracle日志表的使用方式

更新時(shí)間：2025年06月09日 09:55:03 作者：雷神樂(lè)樂(lè)

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

1.日志表定義
2.創(chuàng)建日志表
3.開(kāi)發(fā)往log_table同步數(shù)據(jù)的存儲(chǔ)過(guò)程
4.開(kāi)發(fā)存儲(chǔ)過(guò)程 emp同步數(shù)據(jù)到 emp_1135
5.開(kāi)發(fā)一個(gè)存儲(chǔ)過(guò)程
6.日志表的功能
7.日志表總結(jié)

1.日志表定義

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

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

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)用存儲(chǔ)過(guò)程
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.開(kāi)發(fā)存儲(chǔ)過(guò)程 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)建存儲(chǔ)過(guò)程
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)用日志表存儲(chǔ)過(guò)程
    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)用存儲(chǔ)過(guò)程
begin
    p_19;
end;

select * from emp_1135;

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

再次調(diào)用存儲(chǔ)過(guò)程

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

select * from emp_1135;

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

5.開(kāi)發(fā)一個(gè)存儲(chǔ)過(guò)程

將EMP表同步到 EMP_1134，然后將通過(guò)EMP_1134這個(gè)表數(shù)據(jù)計(jì)算每個(gè)部門(mén)總薪資，同步到 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)用存儲(chǔ)過(guò)程

begin
  p_20;
end;

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

select * from emp_1134;

select * from EMP_SUM_SAL;

第二次調(diào)用存儲(chǔ)過(guò)程時(shí)，因?yàn)閑mp_1134有主鍵，所以當(dāng)?shù)诙蝘nsert到emp_1134時(shí)檢測(cè)到異常，直接拋出，不會(huì)往下走

6.日志表的功能

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

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

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

----開(kāi)發(fā)存儲(chǔ)過(guò)程
CREATE OR REPLACE PROCEDURE p_dept
    IS
    v_rowcount NUMBER;
    v_start_dt DATE;
    v_end_dt   DATE;
BEGIN
    v_start_dt := SYSDATE;
    ----清空目標(biāo)表
    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;


----驗(yàn)證
SELECT * FROM dept_1123;

SELECT * FROM log_table;

7.日志表總結(jié)

日志表的模板以及調(diào)用寫(xiě)日志存儲(chǔ)過(guò)程在項(xiàng)目組中已經(jīng)落地好了，我們直接開(kāi)發(fā)存儲(chǔ)過(guò)程里面的同步邏輯，然后對(duì)照著套著寫(xiě)日志就可以了。

日志的核心功能點(diǎn)：

1.記錄存儲(chǔ)過(guò)程每個(gè)步驟的開(kāi)始時(shí)間 & 結(jié)束時(shí)間，可以分析寫(xiě)的SQL執(zhí)行的效率高與低
2.記錄每個(gè)步驟的執(zhí)行狀態(tài)，成功與否，方便我們快速找到報(bào)錯(cuò)的步驟
3.記錄每個(gè)步驟的影響行數(shù)，驗(yàn)證程序能夠準(zhǔn)確跑出數(shù)據(jù)(如果行數(shù)為0，則說(shuō)明沒(méi)有跑出來(lái)數(shù)據(jù))
4.記錄詳細(xì)的報(bào)錯(cuò)步驟以及錯(cuò)誤原因，方便我們快速定位問(wèn)題，解決問(wèn)題

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: