传统数据仓库日志系统详解

传统数据仓库日志系统详解

一、目的

当我们编写存储过程/自定义函数等SQL代码比较多的时候，添加日志是非常必要的，有助于我们进行Debug。

二、实现逻辑

日志记录，需要三部分组成：日志表(表分区/表空间/索引空间)、记录日志条数的序列、存储过程实现日志逻辑
1

1. 日志表的创建

create table t_sys_log(
	task_id 	number not null,	--使用序列记录主键值
	task_name 	varchar2(50),
	table_name	varchar2(50),
	start_time	timestamp,
	end_time	timestamp,
	task_status	number,
	task_log	varchar2(2000),
	task_pos	varchar2(1000),
	task_tmp	varchar2(100)	--备用字段
)
nologging	--减少日志记录
partition by range(start_time) interval (numtoyminterval(1,'month'))
(
partition part_mm_197001 values less then(to_timestamp('19700101','yyyymmdd'))
)tablespace tbs_sys_log;--创建分区表，tbs_sys_log为表分区名
create index idx_sys_log on t_sys_log(task_id) loacl tablespace tbs_sys_log_idx;--创建索引，tbs_sys_log_idx索引分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2. 序列的创建

create sequence seq_sys_log
	minvalue 1
	maxvalue 10000000000000000
	start with 1
	increment by 1
	cache 20;

1
2
3
4
5
6
7

3. 存储过程逻辑简述

1. 记录日志的SQL，我们大致可以分为两种：执行成功、执行失败。(使用字段：task_status 记录程序执行情况)。
2. 使用if函数来区分这两种状态，同时以全量或增量的形式将程序的当前状态写入到t_sys_log这张表中
3. 报错记录，借助Oracle内置函数：sqlcode和sqlerrm来实现
1
2
3

三、具体实现代码

create or replace procedure prc_sys_log
(
	oi_task_id		in out	integer,	
    iv_task_name	in		varchar2,	--存储过程/自定义函数名
    iv_table_name	in		varchar2,	--同步数据的目标表名
    ii_task_status	in		integer,	
    iv_task_log		in		varchar2,	
    iv_task_pos		in		varchar2
)
is
begin
	if(ii_task_status = 1) then
		select seq_sys_log.nextval into oi_task_id from dual;	--自增序列，给日志唯一id
		
		insert into t_o_sys_log(
        	task_id,		--日志记录数，创建序列记录
            task_name,		--存储过程/自定义函数名
            table_name,		--同步数据涉及目标表名
            start_time,		--开始时间
            end_time,		--结束时间
            task_status,	--标注位，可以定义有值程序正常执行，为null程序抛出异常...
            task_log,		--程序执行情况
            task_pos,		--详细报错信息
        )
        values(
        	oi_task_id,
            iv_task_name,
            iv_table_name,
            sysdate,
            null,
            1,
            'program processing',
            null
        );
	elsif ii_task_status = 0 then
		update t_sys_log
			set end_time	=	sysdate,
				task_status =	2,
				task_log	=	'program completed'
		where task_log <> 'performance debug' and task_id = oi_task_id;	--用于程序中，可以将程序进行分段定位
	else
		if oi_task_id is null then
			select seq_sys_log.nextval into oi_task_id from dual;
			insert into t_sys_log(
            	task_id,
                task_name,
                table_name,
                start_time,
                end_time,
                task_status,
                task_log,
                task_pos
            )
            values(
            	oi_task_id,
                iv_task_name,
                null,
                sysdate,
                null,
                ii_task_status,
                substr(iv_task_log,1,5000),
                substr(iv_task_pos,1,1000)
            );
        else
        	update t_sys_log
        		set end_time	=	sysdate,
        			task_status	=	ii_task_status,
        			task_log	=	substr(iv_task_log,1,2000),
        			task_pos	=	substr(iv_task_pos,1,200)
        	where task_log <> 'performance debug' and task_id = oi_task_id;
        end if;
    end if;
    commit;
    
exception	--异常处理
	when others then
    	rollback;
    end;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

四、调用方式

create or replace procedure/function Helloworld(
	vv_person_name	varchar2,
	
)
is
	/*
	进行参数定义
	*/
	vi_task_id		integer;
	vv_task_name	varchar2(50);	--记录任务名
	vv_err_code		number;		--记录异常编码
	vv_err_log		varchar2(500);	--记录异常内容
-----------------------
begin
	vv_task_name := 'Helloworld';
	/*
	记录开始日志
	*/
	prc_sys_log(
		vi_task_id,
		vv_task_name,
		null,
		1,
		null,
		null
	);
	----------------------------------------
	--程序主题部分
	-------------------------------
	/*
	记录结束日志
	*/
	prc_sys_log(
		vi_task_id,
		vv_task_name,
		null,
		0,
		null,
		null
	);
--异常处理部分
/*
当程序出现异常时，我们需要将异常信息写入到日志表中
*/
exception
	when others then
		vv_task_name := 'Helloworld';
		vi_err_code := sqlcode;
		vv_err_log := substr(sqlerrm,1,200)
		rollback;
		prc_sys_log(
			vi_task_id,
			vv_task_name,
			null,
			null,
			vi_err_code,
			vv_err_log
		);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

五、报错日志记录与查看

--当我们执行程序的时候出现错误，难以定位时日志的存在能够帮我们快速解决问题
select * from t_sys_log order by start_time desc;
--执行上面的语句即可快速查看我们刚刚执行的程序的问题所在了，快速定位快速解决......
1
2
3