day13_自动抽取数据——监控存储过程
精简版：
create or replace procedure P_INSERT_LOG(BEGIN_DATE in DATE,
PROCEDURE_ID in VARCHAR2,
PARAMETER in VARCHAR2,
LOG_DETAIL IN VARCHAR2) is
begin
--存储过程执行时间存储日志
insert into t_procedure_log
values
(PROCEDURE_ID,
PARAMETER,
BEGIN_DATE,
LOG_DETAIL);
commit;
end P_INSERT_LOG;
/
-- Create table
create table T_PROCEDURE_LOG
(
PROCEDURE_ID VARCHAR2(32) not null,
PARAMETER VARCHAR2(100),
BEGIN_TIME DATE not null,
DETAIL VARCHAR2(1024)
);
comment on column T_PROCEDURE_LOG.PROCEDURE_ID
is '存储过程编号';
comment on column T_PROCEDURE_LOG.PARAMETER
is '存储过程执行参数';
comment on column T_PROCEDURE_LOG.BEGIN_TIME
is '开始执行时间，格式yyyy-mm-dd hh24:mm:ss';
comment on column T_PROCEDURE_LOG.DETAIL
is '存储过程具体执行日志';
grant select on T_PROCEDURE_LOG to 用户;
grant execute on P_INSERT_LOG to 用户;
生产版：
create or replace procedure P_INSERT_LOG(BEGIN_DATE in DATE,
PROCEDURE_ID in VARCHAR2,
PARAMETER in VARCHAR2,
LOG_DETAIL IN VARCHAR2) is
begin
--存储过程执行时间存储日志
insert into t_procedure_log
values
(PROCEDURE_ID,
PARAMETER,
BEGIN_DATE,
sysdate,
trunc(sysdate),
(sysdate - BEGIN_DATE) * 24,
(sysdate - BEGIN_DATE) * 24 * 60,
(sysdate - BEGIN_DATE) * 24 * 60 * 60,
LOG_DETAIL);
commit;
end P_INSERT_LOG;
/
两个时间相减，结果天数
-- Create table
create table T_PROCEDURE_LOG
(
PROCEDURE_ID VARCHAR2(32) not null,
PARAMETER VARCHAR2(100),
BEGIN_TIME DATE not null,
DONE_TIME DATE not null,
SYS_DATE DATE not null,
USE_HOUR NUMBER(10,2) not null,
USE_MINITE NUMBER(10,2) not null,
USE_SECOND NUMBER not null,
DETAIL VARCHAR2(1024)
);
-- Add comments to the columns
comment on column T_PROCEDURE_LOG.PROCEDURE_ID
is '存储过程编号';
comment on column T_PROCEDURE_LOG.PARAMETER
is '存储过程执行参数';
comment on column T_PROCEDURE_LOG.BEGIN_TIME
is '开始执行时间，格式yyyy-mm-dd hh24:mm:ss';
comment on column T_PROCEDURE_LOG.DONE_TIME
is '执行完成时间，格式yyyy-mm-dd hh24:mm:ss';
comment on column T_PROCEDURE_LOG.SYS_DATE
is '执行日期，格式yyyy-mm-dd';
comment on column T_PROCEDURE_LOG.USE_HOUR
is '小时';
comment on column T_PROCEDURE_LOG.USE_MINITE
is '分钟';
comment on column T_PROCEDURE_LOG.USE_SECOND
is '秒';
comment on column T_PROCEDURE_LOG.DETAIL
is '存储过程具体执行日志';
-- Create/Recreate indexes
create index IDX_PROCEDURE_LOG on T_PROCEDURE_LOG (BEGIN_TIME);
grant select on T_PROCEDURE_LOG to 用户;
grant execute on P_INSERT_LOG to 用户;
