步骤一:创建一个序列,用于标识[存储过程,函数,触发器]执行先后顺序
-- 步骤一:创建一个序列, 用于标识[ 存储过程, 函数, 触发器] 执行先后顺序
CREATE SEQUENCE SEQ_PROGRAM_EXECUTE_LOG
MINVALUE 1
MAXVALUE 1000000000000
START WITH 1
INCREMENT BY 1
CACHE 100
CYCLE
ORDER ;
-- 删除序列
-- DROP SEQUENCE SEQ_PROGRAM_EXECUTE_LOG ;
步骤二:创建日志表,用于存储[存储过程,函数,触发器]执行日志
-- 步骤二:创建日志表, 用于存储[ 存储过程, 函数, 触发器] 执行日志
CREATE TABLE PROGRAM_EXECUTE_LOG
(
PROGRAM_NAME VARCHAR2 ( 1000 ) ,
IS_SUCCEED CHAR ( 1 ) ,
LOG_DATE DATE ,
EXECUTE_MSG CLOB ,
TIME_CONSUMING NUMBER ,
EXECUTE_ORDER NUMBER ,
EXECUTE_BEGINTIME TIMESTAMP ( 6 ) ,
EXECUTE_ENDTIME TIMESTAMP ( 6 ) ,
PROCESS_FLAG VARCHAR2 ( 10 ) DEFAULT 'N'
) ;
-- 添加注释
comment on table PROGRAM_EXECUTE_LOG is '过程、函数、触发器执行性能日志' ;
-- 添加字段注释
comment on column PROGRAM_EXECUTE_LOG . program_name is '程序名称' ;
comment on column PROGRAM_EXECUTE_LOG . is_succeed is '执行结果(成功Y,失败N)' ;
comment on column PROGRAM_EXECUTE_LOG . log_date is '日志时间' ;
comment on column PROGRAM_EXECUTE_LOG . execute_msg is '执行详细信息' ;
comment on column PROGRAM_EXECUTE_LOG . time_consuming is '耗时' ;
comment on column PROGRAM_EXECUTE_LOG . execute_order is '流水号-SEQ_PROGRAM_EXECUTE_LOG' ;
comment on column PROGRAM_EXECUTE_LOG . execute_begintime is '执行开始时间' ;
comment on column PROGRAM_EXECUTE_LOG . execute_endtime is '执行结束时间' ;
comment on column PROGRAM_EXECUTE_LOG . process_flag is '处理标识(已处理Y,未处理N)' ;
-- 存储过程
CREATE INDEX PROGRAM_EXECUTE_LOG_IDX1 ON PROGRAM_EXECUTE_LOG ( EXECUTE_ORDER ) ;
CREATE INDEX PROGRAM_EXECUTE_LOG_IDX2 ON PROGRAM_EXECUTE_LOG ( TIME_CONSUMING ) ;
-- select to_char ( SYSTIMESTAMP , 'MM/DD/YYYY HH24:MI:SS:FF3' ) from dual;
-- select SYSTIMESTAMP - 1 / 24 next_hour from dual;
select * from PROGRAM_EXECUTE_LOG ; -- 下一个小时 + -
步骤三:创建计算耗时的函数
-- 步骤三:创建计算耗时的函数
CREATE OR REPLACE FUNCTION F_TIMESTAMP_DIFF ( ENDTIME IN TIMESTAMP ,
STARTTIME IN TIMESTAMP )
RETURN INTEGER AS
STR VARCHAR2 ( 50 ) ;
MISECOND INTEGER ;
SECONDS INTEGER ;
MINUTES INTEGER ;
HOURS INTEGER ;
DAYS INTEGER ;
BEGIN
STR : = TO_CHAR ( ENDTIME - STARTTIME ) ;
MISECOND : = TO_NUMBER ( SUBSTR ( STR , INSTR ( STR , ' ' ) + 10 , 3 ) ) ;
SECONDS : = TO_NUMBER ( SUBSTR ( STR , INSTR ( STR , ' ' ) + 7 , 2 ) ) ;
MINUTES : = TO_NUMBER ( SUBSTR ( STR , INSTR ( STR , ' ' ) + 4 , 2 ) ) ;
HOURS : = TO_NUMBER ( SUBSTR ( STR , INSTR ( STR , ' ' ) + 1 , 2 ) ) ;
DAYS : = TO_NUMBER ( SUBSTR ( STR , 1 , INSTR ( STR , ' ' ) ) ) ;
RETURN DAYS * 24 * 60 * 60 * 1000 + HOURS * 60 * 60 * 1000 + MINUTES * 60 * 1000 + SECONDS * 1000 + MISECOND ;
END ;
-- instr函数测试
-- SELECT instr ( 'hellowoDAADSFADFSSFSArld' , 'A' ) FROM DUAL ;
-- select SUBSTR ( 'hellowoDAADSFADFSSFSArld' , instr ( 'hellowoDAADSFADFSSFSArld' , ' ' ) + 10 , 3 ) from dual;
-- 测试F_TIMESTAMP_DIFF 耗时函数
DECLARE
V_ENDTIME TIMESTAMP : = SYSTIMESTAMP + 1 / 24 ;
V_STARTTIME TIMESTAMP : = SYSTIMESTAMP ;
V_RESULT varchar2 ( 200 ) ;
BEGIN
-- V_RESULT : = TO_CHAR ( F_TIMESTAMP_DIFF ( V_ENDTIME , V_STARTTIME ) ) ;
V_RESULT : = TO_CHAR ( F_TIMESTAMP_DIFF ( V_ENDTIME , V_STARTTIME ) / ( 1000 * 60 ) ) ;
-- + TO_DATE ( '1970-01-01 08:00:00' , 'YYYY-MM-DD HH:MI:SS' ) , 'YYYY-MM-DD HH24:MI:SS' ) ;
DBMS_OUTPUT . PUT_LINE ( '结果:' || V_RESULT ) ;
END ;
步骤四:创建存储过程,用于存储日志记录
-- 步骤四:创建存储过程,用于存储日志记录
CREATE OR REPLACE PROCEDURE SYS_SAVELOG
(
PROGRAM_NAME VARCHAR2 , -- 过程名称
IS_SUCCEED VARCHAR2 , -- 是否执行成功 Y | N
EXECUTE_MSG CLOB , -- 过程执行信息
BEGIN_TIME TIMESTAMP -- 过程开始执行TIMESTAMP
) AS
V_PROGRAM_NAME VARCHAR2 ( 1000 ) : = SUBSTRB ( PROGRAM_NAME , 1 , 1000 ) ; -- 过程名称
V_IS_SUCCEED CHAR ( 1 ) : = SUBSTR ( IS_SUCCEED , 1 , 1 ) ; -- 过程执行成功表示 Y N
V_BEGIN_TIME TIMESTAMP : = BEGIN_TIME ; -- 执行过程开始时间
PRAGMA AUTONOMOUS_TRANSACTION ; -- 日志开启自治事务,不影响业务逻辑事务
BEGIN
INSERT INTO PROGRAM_EXECUTE_LOG
(
PROGRAM_NAME , -- 过程名称
IS_SUCCEED , -- 执行结果 ( 成功Y , 失败N )
LOG_DATE , -- 日志时间
EXECUTE_MSG , -- 执行详细信息(CLOB 类型)
TIME_CONSUMING , -- 耗时
-- EXECUTE_ORDER , -- 流水号
EXECUTE_BEGINTIME , -- 执行开始时间
EXECUTE_ENDTIME -- 执行结束时间
-- PROCESS_FLAG -- 处理标识 ( 已处理Y , 未处理N )
)
VALUES
(
V_PROGRAM_NAME ,
V_IS_SUCCEED ,
SYSDATE ,
EXECUTE_MSG ,
F_TIMESTAMP_DIFF ( SYSTIMESTAMP , V_BEGIN_TIME ) ,
-- PROGRAM_EXECUTE_LOG_SEQ . NEXTVAL ,
V_BEGIN_TIME ,
SYSTIMESTAMP
) ;
COMMIT ;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
DBMS_OUTPUT . put_line ( 'ROLLBACK SYS_SAVELOG ......' ) ;
END ;
步骤五:异常日志处理存储过程
-- 步骤五:异常日志处理存储过程
CREATE OR REPLACE PROCEDURE SYS_PROCESS_ERRMSG
(
PROC_NAME VARCHAR2 , -- 过程信息
EXE_MSG CLOB , -- 执行信息
ERR_MSG VARCHAR2 , -- 错误信息
BEGIN_TIME TIMESTAMP , -- 过程开始执行TIMESTAMP
RESULT OUT VARCHAR2
) AS
V_EXE_MSG CLOB : = EXE_MSG ;
BEGIN
RESULT : = 'OK' ;
-- 如果错误信息长度为0 , 返回OK
IF LENGTH ( ERR_MSG ) = 0 OR ERR_MSG IS NULL THEN
RETURN ;
END IF ;
DBMS_LOB . APPEND ( V_EXE_MSG , ERR_MSG || CHR ( 10 ) ) ; -- 错误信息 赋值到 V_EXE_MSG 中(V_EXE_MSG 中原来没有东西)
SYS_SAVELOG ( PROC_NAME , 'I' , V_EXE_MSG , BEGIN_TIME ) ;
ROLLBACK ;
RESULT : = ERR_MSG ;
EXCEPTION
WHEN OTHERS THEN
RESULT : = '处理异常信息发生错误!' ;
END ;
测试存储过程SYS_SAVELOG
-- 测试一下存储过程1
CREATE OR REPLACE PROCEDURE SYS_SAVELOG_EXAMPLE ( PLAN_DATE IN DATE , -- 时间
CONFIRM IN VARCHAR2 , -- Y
RESULT OUT VARCHAR2 ) AS -- 结果
V_PLAN_DATE DATE : = PLAN_DATE ;
V_CONFIRM VARCHAR2 ( 10 ) : = CONFIRM ; -- CONFIRM 标识, Y : 报工确认 N :报工解锁
V_ERR_MSG VARCHAR2 ( 1000 ) ;
V_BEGIN_TIME TIMESTAMP : = SYSTIMESTAMP ;
V_PROC_NAME VARCHAR2 ( 1000 ) : = 'SYS_SAVELOG_EXAMPLE/' || TO_CHAR ( V_PLAN_DATE , 'YYYY-MM-DD HH24:MI:SS' ) || '/' || V_CONFIRM ;
V_EXE_MSG CLOB ;
BEGIN
DBMS_LOB . CREATETEMPORARY ( V_EXE_MSG , TRUE ) ; -- 初始化CLOB
RESULT : = 'OK' ;
IF V_CONFIRM = 'Y' THEN
V_ERR_MSG : = '不允许确认今天或者今天之后的数据!' ; -- 今天或者
GOTO PROCESS_ERRMSG ;
END IF ;
COMMIT ;
DBMS_LOB . APPEND ( V_EXE_MSG , '执行成功!' ) ;
SYS_SAVELOG ( V_PROC_NAME , 'Y' , V_EXE_MSG , V_BEGIN_TIME ) ;
-- 异常处理
RETURN ; -- 若为顺序执行到此位置,则直接返回
<< PROCESS_ERRMSG >>
SYS_PROCESS_ERRMSG ( V_PROC_NAME ,
V_EXE_MSG , -- 没有东西
V_ERR_MSG , -- 错误信息(有)
V_BEGIN_TIME ,
RESULT ) ;
EXCEPTION
WHEN OTHERS THEN
V_ERR_MSG : = SUBSTR ( SQLERRM , 1 , 160 ) ;
DBMS_LOB . APPEND ( V_EXE_MSG , V_ERR_MSG ) ;
SYS_SAVELOG ( V_PROC_NAME , 'N' , V_EXE_MSG , V_BEGIN_TIME ) ;
RESULT : = V_ERR_MSG ; -- 错误号对应的信息
END ;
-- 测试一下存储过程2
CREATE OR REPLACE PROCEDURE SYS_SAVELOG_EXAMPLE ( PLAN_DATE IN DATE , -- 时间
CONFIRM IN VARCHAR2 , -- Y
RESULT OUT VARCHAR2 ) AS -- 结果
V_PLAN_DATE DATE : = PLAN_DATE ;
V_CONFIRM VARCHAR2 ( 10 ) : = CONFIRM ; -- CONFIRM 标识, Y : 报工确认 N :报工解锁
V_ERR_MSG VARCHAR2 ( 1000 ) ;
V_BEGIN_TIME TIMESTAMP : = SYSTIMESTAMP ;
V_PROC_NAME VARCHAR2 ( 1000 ) : = 'SYS_SAVELOG_EXAMPLE/' || TO_CHAR ( V_PLAN_DATE , 'YYYY-MM-DD HH24:MI:SS' ) || '/' || V_CONFIRM ;
V_EXE_MSG CLOB ;
BEGIN
DBMS_LOB . CREATETEMPORARY ( V_EXE_MSG , TRUE ) ; -- 初始化CLOB
RESULT : = 'OK' ;
IF V_CONFIRM = 'N' THEN
V_ERR_MSG : = '错误信息内容' ;
SYS_PROCESS_ERRMSG ( V_PROC_NAME ,
V_EXE_MSG , -- 没有东西
V_ERR_MSG , -- 错误信息(有)
V_BEGIN_TIME ,
RESULT ) ;
ELSIF V_CONFIRM = 'Y' THEN
DBMS_LOB . APPEND ( V_EXE_MSG , '执行成功内容1!' ) ;
DBMS_LOB . APPEND ( V_EXE_MSG , '执行成功内容2!' ) ;
SYS_SAVELOG ( V_PROC_NAME , 'Y' , V_EXE_MSG , V_BEGIN_TIME ) ;
END IF ;
COMMIT ;
EXCEPTION
WHEN OTHERS THEN
V_ERR_MSG : = SUBSTR ( SQLERRM , 1 , 160 ) ;
DBMS_LOB . APPEND ( V_EXE_MSG , V_ERR_MSG ) ;
SYS_SAVELOG ( V_PROC_NAME , 'N' , V_EXE_MSG , V_BEGIN_TIME ) ;
RESULT : = V_ERR_MSG ; -- 错误号对应的信息
END ;
测试执行SYS_SAVELOG_EXAMPLE
DECLARE
V_RESULT VARCHAR2 ( 100 ) ;
BEGIN
SYS_SAVELOG_EXAMPLE ( sysdate, 'N' , V_RESULT ) ;
DBMS_OUTPUT . put_line ( 'V_RESULT:' || V_RESULT ) ;
END ;
最后查询记录日志结果
-- 查询
SELECT * FROM PROGRAM_EXECUTE_LOG ;
DELETE FROM PROGRAM_EXECUTE_LOG ;
-- 查询日期是星期几?
Select to_char(sysdate,'day') from dual;
-- dbms_output.put_line(sqlcode||','||sqlerrm);