注册

创建好了存储过程,及作业定时执行,但不执行

blueberry 2024/12/19 485 24

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM8
【操作系统】:
【CPU】:
【问题描述】*:创建了一个存储过程,也创建了作业来执行这个存储过程,但测试发现,一直没有该作业执行记录,按理说正常执行,就算失败也有作业日志记录;有大佬帮我看下,下面是我的存储过程,以及作业调度:
CREATE OR REPLACE PROCEDURE "TV_FOREST_TEST"."getFalseRate"
AS
-- 声明变量
dataCnt INT;
falseRate FLOAT;
trueCnt INT;
noRepeatCnt INT;
repeatTrueCnt INT;
shieldCnt INT;
repeatFalseCnt INT;
totalCnt INT;
thresConfirmCnt INT;
checkCnt INT;
BEGIN

select count(a.id) into trueCnt from tb_alter a
where a.alarmTime > CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 00:00:00') and a.alarmTime < CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 23:59:59');

select count(a.id) into thresConfirmCnt from tb_alter a
where a.thresConfirm = 1 and a.alarmTime > CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 00:00:00') and a.alarmTime < CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 23:59:59');

select count(ah.id) into noRepeatCnt from tb_alter_temp ah
where ah.alarmTime > CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 00:00:00') and ah.alarmTime <= CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 23:59:59');

select count(ar.id) into repeatTrueCnt from tb_algoRepeat ar
where ar.alarm_time > CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 00:00:00') and ar.alarm_time <= CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 23:59:59') and ar.type = 1;

select count(ar.id) into shieldCnt from tb_algoRepeat ar
where ar.alarm_time > CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 00:00:00') and ar.alarm_time <= CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 23:59:59') and ar.type = 2;

select count(arf.id) into repeatFalseCnt from tb_algoRepeatFalse arf
where arf.alarm_time > CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 00:00:00') and arf.alarm_time <= CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 23:59:59');

totalCnt := noRepeatCnt + repeatTrueCnt + shieldCnt + repeatFalseCnt;

falseRate := (noRepeatCnt - trueCnt) / noRepeatCnt * 100;

select count(tf.id) into dataCnt from TestModel_falseRate tf where tf.date = DATE_FORMAT(NOW(),'%Y-%m-%d');

checkCnt := noRepeatCnt - thresConfirmCnt;

IF dataCnt > 0 THEN
UPDATE
TestModel_falseRate
SET
"time" = TO_CHAR(SYSDATE, 'HH24:MI:SS'),
falseRate = TO_CHAR(ROUND(falseRate, 2), '999999.99') || '%',
trueCnt = trueCnt,
thresConfirmCnt = thresConfirmCnt,
checkCnt = checkCnt,
noRepeatCnt = noRepeatCnt,
repeatTrueCnt = repeatTrueCnt,
shieldCnt = shieldCnt,
repeatFalseCnt = repeatFalseCnt,
totalCnt = totalCnt
WHERE
"date" = TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD');
ELSE
INSERT INTO TestModel_falseRate("date", "time", falseRate, trueCnt, thresConfirmCnt,
checkCnt, noRepeatCnt,repeatTrueCnt, shieldCnt, repeatFalseCnt, totalCnt)
VALUES (TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD'),
TO_CHAR(SYSDATE, 'HH24:MI:SS'),
TO_CHAR(ROUND(falseRate, 2), '999999.99') || '%',
trueCnt,
thresConfirmCnt,
checkCnt,
noRepeatCnt,
repeatTrueCnt,
shieldCnt,
repeatFalseCnt,
totalCnt);
END IF;

END;

call SP_CREATE_JOB('置信度自动确认函数',1,0,'',0,0,'',0,'统计告警表中通过置信度自动确认的告警');

call SP_JOB_CONFIG_START('置信度自动确认函数');

call SP_ADD_JOB_STEP('置信度自动确认函数', 'STP2', 0, 'call "TV_FOREST_TEST".getFalseRate();', 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('置信度自动确认函数', '置信度自动确认告警统计函数', 1, 1, 1, 0, 0, '10:26:00', NULL, '2024-12-18 17:29:46', NULL, '每日23:59:00执行一次');

call SP_JOB_CONFIG_COMMIT('置信度自动确认函数');

回答 0
暂无回答
扫一扫
联系客服