如下oracle的PROCEDURE报错，[Error] ORA-00942 (62: 45): PL/SQL: ORA-00942: table or view does not exist
请协助修正
CREATE OR REPLACE PROCEDURE LPBD7.SP_INSERT_AGENCY_LOGS_ORT AS
TYPE t_rec IS RECORD (
event AGENCY_LOGS_JSON.EVENT%TYPE,
agent_id AGENCY_LOGS_JSON.AGENT_ID%TYPE,
serials_band AGENCY_LOGS_JSON.SERIALS_BAND%TYPE,
serials_sp AGENCY_LOGS_JSON.SERIALS_SP%TYPE,
serials_bg AGENCY_LOGS_JSON.SERIALS_BG%TYPE,
serials_fg AGENCY_LOGS_JSON.SERIALS_FG%TYPE,
test_name VARCHAR2(200),
created AGENCY_LOGS_JSON.CREATED%TYPE,
insertd AGENCY_LOGS_JSON.INSERTD%TYPE,
station_id AGENCY_LOGS_JSON.STATION_ID%TYPE,
id AGENCY_LOGS_JSON.ID%TYPE,
json AGENCY_LOGS_JSON.JSON%TYPE,
sample_type VARCHAR2(200),
clean_test VARCHAR2(500)
);
TYPE t_rec_tab IS TABLE OF t_rec;
v_data t_rec_tab;
v_params SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
v_last_id NUMBER;
v_new_last_id NUMBER := 0;
v_msg VARCHAR2(4000);
V_RESULT VARCHAR2(500);
BEGIN
-- 获取上次处理位置
BEGIN
SELECT last_id INTO v_last_id
FROM LPBD7.PROC_SYNC_STATUS
WHERE proc_name = 'PROC_SYNC_AGENCY_LOGS_ORT';
EXCEPTION
WHEN NO_DATA_FOUND THEN v_last_id := 0;
END;
-- 预加载参数值
SELECT REGEXP_REPLACE(VALUE1, '[^[:alnum:] ]', '')
BULK COLLECT INTO v_params
FROM LPBD7.U_PARAMETER_FILE
WHERE CONDITION1 = 'ORT_EBORD'
AND ACTIVE = 'Y'
GROUP BY REGEXP_REPLACE(VALUE1, '[^[:alnum:] ]', '');
-- 批量获取待处理数据
SELECT
h.EVENT, h.AGENT_ID, h.SERIALS_BAND, h.SERIALS_SP,
h.SERIALS_BG, h.SERIALS_FG,
JSON_VALUE(h.JSON, '$.insight.results.test'),
h.CREATED, h.INSERTD, h.STATION_ID, h.ID,
h.JSON,
JSON_VALUE(h.JSON, '$.insight.uut_attributes.sample_type'),
UPPER(REGEXP_REPLACE(JSON_VALUE(h.JSON, '$.insight.results.test'), '[^[:alnum:] ]', ''))
BULK COLLECT INTO v_data
FROM LPBD7.AGENCY_LOGS_JSON h
JOIN MESNEX.AGENCY_JOBS j ON h.AGENT_ID = j.AGENT
JOIN MESNEX.AGENCY_PROCESS p ON j.PROCESS = p.ID
WHERE UPPER(p.NAME) = 'ORT-RAW'
AND j.DISABLED = 'False'
AND h.CREATED BETWEEN j.STARTED AND j.ENDED
AND h.ID > v_last_id
AND h.CREATED >= TO_DATE((SELECT MAX(TO_CHAR(A.LAST_DATETIME,'yyyy/mm/dd hh24:mi:ss'))
FROM LP960.TC_AGENCY_JOB_LOG A
WHERE A.EXECUTETIME > SYSDATE-0.5
AND A.JOBNAME = 'AgencyAPI_Lobs_Json'),'yyyy/mm/dd hh24:mi:ss')
- 10 / 1440 -- 处理TRACE同步最新时间内数据
ORDER BY h.ID;
IF v_data.COUNT > 0 THEN
v_new_last_id := v_data(v_data.LAST).id;
-- 批量插入处理
FOR i IN 1..v_data.COUNT LOOP
FOR j IN 1..v_params.COUNT LOOP
CONTINUE WHEN v_params(j) IS NULL;
IF INSTR(v_data(i).clean_test, UPPER(v_params(j))) > 0 THEN
-- 查重后插入
INSERT INTO LPBD7.AGENCY_LOGS_ORT (
EVENT, AGENT_ID, SERIALS_BAND, SERIALS_SP,
SERIALS_BG, SERIALS_FG, TEST_NAME,
CREATED, INSERTD, STATION_ID, ID
)
SELECT
v_data(i).event, v_data(i).agent_id,
v_data(i).serials_band, v_data(i).serials_sp,
v_data(i).serials_bg, v_data(i).serials_fg,
v_data(i).test_name, v_data(i).created,
v_data(i).insertd, v_data(i).station_id,
v_data(i).id
FROM DUAL
WHERE NOT EXISTS (
SELECT 1 FROM LPBD7.AGENCY_LOGS_ORT o
WHERE o.ID = v_data(i).id
AND o.TEST_NAME = v_data(i).test_name
);
-- 调用存储过程
LPBD7.SP_LOG_ORT_INSERT_EVENT(
v_data(i).serials_band,
v_data(i).serials_sp,
v_data(i).serials_bg,
v_data(i).serials_fg,
v_data(i).id,
v_data(i).created,
v_data(i).test_name,
v_data(i).event,
v_data(i).json,
v_data(i).sample_type
);
EXIT; -- 匹配后跳出参数循环
END IF;
END LOOP;
END LOOP;
-- 更新处理位置
MERGE INTO LPBD7.PROC_SYNC_STATUS dst
USING (SELECT 'PROC_SYNC_AGENCY_LOGS_ORT' proc_name FROM DUAL) src
ON (dst.proc_name = src.proc_name)
WHEN MATCHED THEN
UPDATE SET last_id = v_new_last_id
WHEN NOT MATCHED THEN
INSERT (proc_name, last_id) VALUES (src.proc_name, v_new_last_id);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_msg := SQLERRM;
SELECT SAJET.SF_MES_Send_Mail(
'mes.it@tzlens.com,yujia.liu@tzlens.com',
'SP_INSERT_AGENCY_LOGS_ORT ERROR: ' || SUBSTR(v_msg, 1, 4000),
'STORED PROCEDURE ERROR', '', '', ''
)INTO V_RESULT
FROM DUAL;
RAISE;
END;
/
本文介绍三种在SQL中进行日期查询的方法：使用between...and...函数结合trunc进行时间段查询，直接利用trunc函数精确到指定日期，以及不使用任何函数直接比较日期。文章通过实例展示了如何灵活运用这些技巧，以提高查询效率。
Y
扫一扫
560
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
