如下oracle的PROCEDURE测试时报错，[Error] (0: 0): PL/SQL: Compilation unit analysis terminated
[Error] PLS-00208 (24: 76): PLS-00208: identifier 'TYPE' is not a legal cursor attribute
请协助修正
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 AGENCY_LOGS_JSON.ID%TYPE; -- 使用%TYPE确保类型一致
v_new_last_id AGENCY_LOGS_JSON.ID%TYPE := CASE WHEN AGENCY_LOGS_JSON.ID%TYPE = 'NUMBER' THEN 0 ELSE '0' END;
v_msg VARCHAR2(4000);
V_RESULT VARCHAR2(500);
v_max_datetime DATE; -- 用于安全处理日期
BEGIN
-- 修复1：确保状态表查询使用正确的过程名
BEGIN
SELECT last_id INTO v_last_id
FROM LPBD7.PROC_SYNC_STATUS
WHERE proc_name = 'SP_INSERT_AGENCY_LOGS_ORT'; -- 名称保持一致
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_last_id := CASE WHEN AGENCY_LOGS_JSON.ID%TYPE = 'NUMBER' THEN 0 ELSE '0' END;
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:] ]', '');
-- 修复2：安全的日期处理
BEGIN
SELECT MAX(LAST_DATETIME) INTO v_max_datetime
FROM LP960.TC_AGENCY_JOB_LOG
WHERE EXECUTETIME > SYSDATE - 0.5
AND JOBNAME = 'AgencyAPI_Lobs_Json';
EXCEPTION
WHEN NO_DATA_FOUND THEN v_max_datetime := CASE
WHEN SYSDATE BETWEEN TRUNC (SYSDATE) + 8.5/24 AND TRUNC (SYSDATE) + 20.5/24
THEN TRUNC (SYSDATE) + 8.5/24
WHEN SYSDATE BETWEEN TRUNC (SYSDATE) + 20.5/24 AND TRUNC (SYSDATE+1)
THEN TRUNC (SYSDATE) + 20.5/24
WHEN SYSDATE BETWEEN TRUNC (SYSDATE-1) AND TRUNC (SYSDATE) + 8.5/24
THEN TRUNC (SYSDATE - 1) + 20.5 / 24
END;
END;
-- 批量获取待处理数据（使用安全的日期变量）
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 >= NVL(v_max_datetime, SYSDATE - 30) - INTERVAL '20' MINUTE -- 安全日期运算
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;
-- 修复3：更新状态时使用正确过程名
MERGE INTO LPBD7.PROC_SYNC_STATUS dst
USING (SELECT 'SP_INSERT_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;
最新发布
PL/SQL循环变量溢出解决方案
本文介绍了在PL/SQL中遇到for循环变量溢出的问题，并提供了将for循环替换为while循环的解决方法。文章详细解释了PL/SQL中循环变量的范围限制及其原因。
扫一扫
3364
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
