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