oracle存储过程代码日志记录
prompt 创建函数 f_getprocname
CREATE
OR
REPLACE
FUNCTION f_getprocname RETURN
VARCHAR2
--获取函数或者存储过程自身的名称及调用者
IS
l_owner VARCHAR2(30);
l_name VARCHAR2(30);
l_lineno NUMBER;
l_type VARCHAR2(30);
BEGIN
OWA_UTIL.who_called_me(l_owner, l_name, l_lineno, l_type);
RETURN l_owner ||
'.'
|| l_name;
END;
/
prompt 正在执行
创建表 T_PROC_LOGS
declare
v_count number
:=
0;
v_tb_name varchar2(1000)
:=
'T_PROC_LOGS';
begin
SELECT
count(*)
INTO v_count FROM USER_OBJECTS WHERE
OBJECT_NAME
=
UPPER(v_tb_name);
if v_count >
0
then
execute
immediate
'drop table '
||
UPPER(v_tb_name);
end
if;
end;
/
create
table T_PROC_LOGS
(
LOG_ID NUMBER(10)
default
0
not
null,
PROC_MC VARCHAR2(100)
default
' '
not
null,
TITLE VARCHAR2(4000)
default
' '
not
null,
CONTENT VARCHAR2(4000)
default
' '
not
null,
LOG_TYPE VARCHAR2(10)
default
' '
not
null,
LOG_TIME DATE
);
prompt 正在执行
创建序列 seq_log_id
declare
v_count number;
v_sequence_name varchar2(1000)
:=
'seq_log_id';
begin
select
count(*)
into v_count from user_sequences a where a.sequence_name =
upper(v_sequence_name);
if
(v_count =
0)
then
execute
immediate
'create sequence '
|| v_sequence_name ||
' increment by 1 start with 1'
||
' maxvalue 2100000000 minvalue 1'
||
' cycle nocache noorder';
end
if;
end;
/
create or replace package PKG_LOG is
PROCEDURE INFO(p_proc_name varchar2, title varchar2, content varchar2);
PROCEDURE DEBUG(p_proc_name varchar2, title varchar2, content varchar2);
PROCEDURE ERROR(p_proc_name varchar2, title varchar2, content varchar2);
end PKG_LOG;
create or replace package body PKG_LOG is
PROCEDURE DEBUG(p_proc_name varchar2, title varchar2, content varchar2) is
sLevel varchar2(10);
begin
select t.param_value into sLevel from tsysparam t where t.sys_param_id = 50001;
if sLevel = '1' then
INSERT INTO t_proc_logs(log_id,proc_mc,title,content, log_type,log_time)
VALUES (seq_log_id.NEXTVAL, upper(p_proc_name), substr(title,1,4000), substr(content,1,4000), 'DEBUG',sysdate);
COMMIT;
end if;
EXCEPTION WHEN OTHERS THEN
NULL;
ROLLBACK;
END;
PROCEDURE INFO(p_proc_name varchar2, title varchar2, content varchar2) is
sLevel varchar2(10);
begin
select t.param_value into sLevel from tsysparam t where t.sys_param_id = 50001;
if sLevel = '2' or sLevel = '1' then
INSERT INTO t_proc_logs(log_id,proc_mc,title,content, log_type,log_time)
VALUES (seq_log_id.NEXTVAL, upper(p_proc_name), substr(title,1,4000), substr(content,1,4000), 'INFO',sysdate);
COMMIT;
end if;
EXCEPTION WHEN OTHERS THEN
NULL;
ROLLBACK;
END;
PROCEDURE ERROR(p_proc_name varchar2, title varchar2, content varchar2) is
sLevel varchar2(10);
begin
select t.param_value into sLevel from tsysparam t where t.sys_param_id = 50001;
if sLevel = '3' or sLevel = '2' or sLevel = '1' then
INSERT INTO t_proc_logs(log_id,proc_mc,title,content, log_type,log_time)
VALUES (seq_log_id.NEXTVAL, upper(p_proc_name), substr(title,1,4000), substr(content,1,4000), 'ERROR',sysdate);
COMMIT;
end if;
EXCEPTION WHEN OTHERS THEN
NULL;
ROLLBACK;
END;
end PKG_LOG;
