开发存储过程时，有时需要记录一些日志或是debug时需要一些提示debug信息，当然有时可以使用DBMS_OUTPUT.PUT_LINE来打印一些信息，但是信息多了就不是很方便；也可以借助LOG4PLSQL来实现，但是有一点重。于是是自己写了一个小程序，基本可以实现记录日志的要求。
1. 日志表
--日志表 CREATE TABLE T_LOG ( ID NUMBER PRIMARY KEY, --ID LDATE DATE DEFAULT SYSDATE,--日期 LLEVEL VARCHAR2(10) NOT NULL,--级别 message VARCHAR2(2000)--内容 ) NOLOGGING;
--SEQ，记录每条日志的ID CREATE SEQUENCE SEQ_LOG_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
2. 包声明
CREATE OR REPLACE PACKAGE PLOG IS --输出标志：TRUE时同时输出，FALSE关闭输出 C_OUTPUT_ON CONSTANT BOOLEAN := TRUE; -- 日志级别 C_LEVEL_DEBUG CONSTANT VARCHAR2(10) := 'DEBUG'; C_LEVEL_INFO CONSTANT VARCHAR2(10) := 'INFO'; C_LEVEL_WARN CONSTANT VARCHAR2(10) := 'WARN'; C_LEVEL_ERROR CONSTANT VARCHAR2(10) := 'ERROR'; C_LEVEL_FATAL CONSTANT VARCHAR2(10) := 'FATAL'; -- 调试 PROCEDURE DEBUG(P_MSG IN VARCHAR2); -- 信息 PROCEDURE INFO(P_MSG IN VARCHAR2); -- 警告 PROCEDURE WARN(P_MSG IN VARCHAR2); -- 错误 PROCEDURE ERROR(P_MSG IN VARCHAR2); -- 致命 PROCEDURE FATAL(P_MSG IN VARCHAR2); END PLOG;
3. 包体
CREATE OR REPLACE PACKAGE BODY PLOG IS FUNCTION FORMAT_MSG(V_MSG IN VARCHAR2) RETURN VARCHAR2 AS BEGIN RETURN SUBSTR(V_MSG, 0, 2000); END FORMAT_MSG; PROCEDURE INS_TLOG(P_LEVEL IN T_LOG.LLEVEL%TYPE, P_MSG IN T_LOG.MESSAGE%TYPE) IS PRAGMA AUTONOMOUS_TRANSACTION; LID T_LOG.ID%TYPE := SEQ_LOG_ID.NEXTVAL; LDATE DATE := SYSDATE; BEGIN IF C_OUTPUT_ON THEN DBMS_OUTPUT.PUT_LINE(LID || ',' || to_char(LDATE,'YYYY-MM-DD HH24:MI:SS') || ',' || P_LEVEL || P_MSG); END IF; INSERT INTO T_LOG (ID, LDATE, LLEVEL, MESSAGE) VALUES (LID, LDATE, P_LEVEL, P_MSG); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END INS_TLOG; PROCEDURE DEBUG(P_MSG IN VARCHAR2) AS BEGIN INS_TLOG(C_LEVEL_DEBUG, FORMAT_MSG(P_MSG)); EXCEPTION WHEN OTHERS THEN ERROR(FORMAT_MSG('PKG_LOGGER.DEBUG:' || SQLERRM(SQLCODE))); END DEBUG; PROCEDURE INFO(P_MSG IN VARCHAR2) AS BEGIN INS_TLOG(C_LEVEL_INFO, FORMAT_MSG(P_MSG)); EXCEPTION WHEN OTHERS THEN ERROR(FORMAT_MSG('PKG_LOGGER.INFO:' || SQLERRM(SQLCODE))); END INFO; PROCEDURE WARN(P_MSG IN VARCHAR2) AS BEGIN INS_TLOG(C_LEVEL_WARN, FORMAT_MSG(P_MSG)); EXCEPTION WHEN OTHERS THEN ERROR(FORMAT_MSG('PKG_LOGGER.WARN:' || SQLERRM(SQLCODE))); END WARN; PROCEDURE ERROR(P_MSG IN VARCHAR2) AS BEGIN INS_TLOG(C_LEVEL_ERROR, FORMAT_MSG(P_MSG)); EXCEPTION WHEN OTHERS THEN INS_TLOG(C_LEVEL_ERROR, 'PKG_LOGGER.ERROR:' || SQLERRM(SQLCODE)); END ERROR; PROCEDURE FATAL(P_MSG IN VARCHAR2) AS BEGIN INS_TLOG(C_LEVEL_FATAL, FORMAT_MSG(P_MSG)); EXCEPTION WHEN OTHERS THEN ERROR(FORMAT_MSG('PKG_LOGGER.FATAL:' || SQLERRM(SQLCODE))); END FATAL; END PLOG;
4. 简单测试
BEGIN PLOG.DEBUG('DEBUG MSG'); PLOG.INFO('INFO MSG'); PLOG.WARN('WARN MSG'); PLOG.ERROR('ERROR MSG'); PLOG.FATAL('FATAL MSG'); END; // 输出：控制台 17,2011-02-14 10:04:37,DEBUGDEBUG MSG 18,2011-02-14 10:04:37,INFOINFO MSG 19,2011-02-14 10:04:37,WARNWARN MSG 20,2011-02-14 10:04:37,ERRORERROR MSG 21,2011-02-14 10:04:37,FATALFATAL MSG // 输出：TLOG 1 17 2011-2-14 10:04:37 DEBUG DEBUG MSG 2 18 2011-2-14 10:04:37 INFO INFO MSG 3 19 2011-2-14 10:04:37 WARN WARN MSG 4 20 2011-2-14 10:04:37 ERROR ERROR MSG 5 21 2011-2-14 10:04:37 FATAL FATAL MSG
