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;