Oracle存储过程，函数执行记录日志方式_oracle 存储过程被调用记录

link之家
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
步骤一：创建一个序列,用于标识[存储过程,函数,触发器]执行先后顺序

--步骤一：创建一个序列,用于标识[存储过程,函数,触发器]执行先后顺序
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;                                     --日志开启自治事务，不影响业务逻辑事务
  调用方式：
  1.正常日志：PROC_SAVELOG(V_PROC_NAME, 'Y', '执行成功。。。',V_BEGIN_TIME);
  2.异常日志：PROC_SAVELOG(V_PROC_NAME, 'N', SQLERRM,V_BEGIN_TIME);
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
  用于处理异常信息,方便后续扩展
      1.执行信息中添加ERR_MSG
      2.执行记录日志过程
      3.错误信息赋值给返回结果RESULT
  调用方式：
      SYS_PROCESS_ERRMSG(V_PROC_NAME, V_EXE_MSG,V_ERR_MSG,V_BEGIN_TIME,RESULT);
    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); 
                    1.步骤一：创建一个序列,用于标识[存储过程,函数,触发器]执行先后顺序--步骤一：创建一个序列,用于标识[存储过程,函数,触发器]执行先后顺序CREATE SEQUENCE SEQ_PROGRAM_EXECUTE_LOGMINVALUE 1MAXVALUE 1000000000000START WITH 1INCREMENT BY 1CACHE 100CYCLEORDER;...
				2、登陆数据库，执行如下语句，查看当前进行io操作的进程和模块名称：
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,
se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes
FROM v$session se,v$session_wait st,v$sess_io si,v$process pr
WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR and se.username is not null AND se.sid>6
AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC




    

				　　一般我们会将一些涉及到数据库的定时任务直接用存储过程搞定，省去了后端代码的开发、部署，简单、快速，但这种方式存在一个弊端——当存储过程执行出错了，我们无法感知。解决办法也简单，学代码那样去捕获异常、打印日志。
　　第一步，建日志表：
create table TBL_WLF_SYS_LOG
      S_TIME            VARCHAR2(32) not nul...
				项目中存储过程运行时记录出错日志方法。
步骤一：创建一个序列,用于标识[存储过程,函数,触发器]执行先后顺序
CREATE SEQUENCE SEQ_PROGRAM_EXECUTE_LOG
MINVALUE 1
MAXVALUE 1000000000000
START WITH 1
INCREMENT BY 1
CACHE 100
CYCLE
ORDER;
-- 删除序列
--DROP SEQU...
				1.1、查all_triggers表得到trigger_name
1.2、根据trigger_name查询出触发器详细信息
1.3、在Oracle数据库中查询所有触发器、存储过程、视图、表方法1：
				本包用于记录存储过程里面的日志，将日志信息写入日志表(mylog)。可以记录日志时间、过程名称、过程里面记录日志的行数、参数值。如果在异常处理部分记录日志，还可以记录异常代码、异常原因、异常产生的行数。
本人使用开发环境为oracle10g
				可以在 Oracle 存储过程中使用 DBMS_OUTPUT.PUT_LINE 函数来输出日志信息。将这些信息输出到数据库日志文件中，可以使用 Oracle 提供的日志记录机制，例如使用 UTL_FILE 包将日志信息写入到文件中。
下面是一个示例存储过程，其中使用了 DBMS_OUTPUT.PUT_LINE 函数来输出日志信息，并使用 UTL_FILE 包将日志信息写入到文件中：
CREATE OR REPLACE PROCEDURE my_procedure
   log_file UTL_FILE.FILE_TYPE;
BEGIN
   -- 打开日志文件
   log_file := UTL_FILE.FOPEN('MY_DIR', 'my_log.log', 'w');
   -- 输出日志信息到控制台和日志文件中
   DBMS_OUTPUT.PUT_LINE('Starting my_procedure');
   UTL_FILE.PUT_LINE(log_file, 'Starting my_procedure');
   -- 执行存储过程的主体逻辑
   -- 输出日志信息到控制台和日志文件中
   DBMS_OUTPUT.PUT_LINE('Finished my_procedure');
   UTL_FILE.PUT_LINE(log_file, 'Finished my_procedure');
   -- 关闭日志文件
   UTL_FILE.FCLOSE(log_file);
在上面的示例中，存储过程首先通过 UTL_FILE.FOPEN 函数打开了一个名为 'my_log.log' 的日志文件，然后通过 DBMS_OUTPUT.PUT_LINE 函数和 UTL_FILE.PUT_LINE 函数分别将日志信息输出到控制台和日志文件中，最后使用 UTL_FILE.FCLOSE 函数关闭日志文件。