Oracle日志表的使用方式
1.日志表定義
日志一般會記錄:同步的源表名,同步的目標表名,步驟名稱,記錄行數(shù),狀態(tài),開始時間,結束時間,備注。
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ā)散)
- 練習:全量同步 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;
7.日志表總結
日志表的模板 以及 調(diào)用寫日志存儲過程 在項目組中已經(jīng)落地好了,我們直接開發(fā)存儲過程里面的同步邏輯,然后對照著套著寫日志就可以了。
日志的核心功能點:
- 1.記錄存儲過程每個步驟的 開始時間 & 結束時間,可以分析寫的SQL執(zhí)行的效率高與低
- 2.記錄每個步驟的執(zhí)行狀態(tài),成功與否,方便我們快速找到報錯的步驟
- 3.記錄每個步驟的影響行數(shù),驗證程序能夠準確跑出數(shù)據(jù)(如果行數(shù)為0,則說明沒有跑出來數(shù)據(jù))
- 4.記錄詳細的報錯步驟以及錯誤原因,方便我們快速定位問題,解決問題
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關文章
利用Oracle數(shù)據(jù)庫發(fā)送郵件的實例代碼
本文給大家利用oracle數(shù)據(jù)庫發(fā)送郵件的實例,代碼簡單易懂,試用性非常高,對此文感興趣的朋友一起學習吧2016-09-09Oracle GoldenGate同步服務歸檔空間維護【推薦】
這篇文章主要介紹了Oracle GoldenGate同步服務歸檔空間維護的相關知識,非常不錯,具有一定的參考借鑒價值,需要的朋友可以參考下2018-08-08Oracle導dmp出現(xiàn)文件ORA-12154: TNS: 無法解析指定的連接標識符問題的解決方案
這篇文章主要介紹了Oracle導dmp出現(xiàn)文件ORA-12154: TNS: 無法解析指定的連接標識符問題的解決方案,需要的朋友可以參考下2017-03-03Windows server 2008 R2(win7)登陸sqlplus錯誤ORA-12560和ORA-12557的解
這篇文章主要為大家詳細介紹了Windows server 2008 R2(win7)登陸sqlplus錯誤ORA-12560和ORA-12557的解決方法,具有一定的參考價值,感興趣的小伙伴們可以參考一下2017-05-05oracle官網(wǎng)下載資源報錯400?Bad?Request?Request?Header?Or?Cookie
這篇文章主要介紹了oracle官網(wǎng)下載資源報錯400?Bad?Request?Request?Header?Or?Cookie?Too?Large解決辦法,這通常是因為您的瀏覽器發(fā)送的請求頭或Cookie過大,超出了服務器允許的限制,需要的朋友可以參考下2025-02-02