如下报错
[Error] ORA-00907 (54: 13): PL/SQL: ORA-00907: missing right parenthesis
[Error] ORA-00923 (210: 5): PL/SQL: ORA-00923: FROM keyword not found where expected
请协助修正，并提高执行效率和减少资源占用：
CREATE OR REPLACE PROCEDURE LPBD7.SP_LOG_ORT_INSERT_EVENT_T(
IN_SERIALS_BAND VARCHAR2,
IN_SERIALS_SP VARCHAR2,
IN_SERIALS_BG VARCHAR2,
IN_SERIALS_FG VARCHAR2,
IN_ID VARCHAR2,
IN_CREATED DATE,
IN_TEST_NAME VARCHAR2,
IN_EVENT VARCHAR2,
IN_JSON CLOB,
IN_SAMPLE_TYPE VARCHAR2
) AUTHID CURRENT_USER IS
V_MSG VARCHAR2(4000);
V_LINE VARCHAR2 (4000);
V_STEP VARCHAR2(10) := '0';
V_STATION_ID VARCHAR2(1000);
V_ADDR VARCHAR2(1000);
V_PLANT VARCHAR2(5);
V_LINE_ID VARCHAR2(100);
V_PROCESS VARCHAR2(50);
V_MODEL VARCHAR2(100);
V_TYPE_NAME VARCHAR2(50);
V_FRE VARCHAR2(50);
V_TEST_NAME_NORM VARCHAR2(1000);
V_LOWER_LIMIT VARCHAR2(50);
V_UPPER_LIMIT VARCHAR2(50);
V_VALUE VARCHAR2(50);
G_NEXT_REC_TIME SAJET.TC_ASSY_EVENT.NEXT_REC_TIME%TYPE;
V_LU_FLAG BOOLEAN; -- 标记上下限是否存在
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- [优化点1] 改写OR条件为UNION ALL + FETCH FIRST
BEGIN
SELECT ADDR, STATION_ID INTO V_ADDR, V_STATION_ID
FROM (
SELECT D.ADDR, A.STATION_ID, A.CREATED
FROM LPBD7.AGENCY_LOGS_JSON A
JOIN MESNEX.AGENCY_JOBS B ON B.AGENT = A.AGENT_ID
AND A.CREATED BETWEEN B.STARTED AND B.ENDED
JOIN MESNEX.AGENCY_PROCESS C ON C.ID = B.PROCESS
JOIN LP960.AGENCY_AGENTS D ON D.ID = A.AGENT_ID
CROSS APPLY (
SELECT DISTINCT VALUE2
FROM LPBD7.U_PARAMETER_FILE
WHERE CONDITION1 = 'ORT_EBORD'
AND ACTIVE = 'Y'
AND UPPER(C.NAME) = UPPER(VALUE2)
) E
WHERE A.SERIALS_BAND = IN_SERIALS_BAND
ORDER BY A.CREATED DESC
FETCH FIRST 1 ROW ONLY
UNION ALL
SELECT D.ADDR, A.STATION_ID, A.CREATED
FROM LPBD7.AGENCY_LOGS_JSON A
JOIN MESNEX.AGENCY_JOBS B ON B.AGENT = A.AGENT_ID
AND A.CREATED BETWEEN B.STARTED AND B.ENDED
JOIN MESNEX.AGENCY_PROCESS C ON C.ID = B.PROCESS
JOIN LP960.AGENCY_AGENTS D ON D.ID = A.AGENT_ID
CROSS APPLY (
SELECT DISTINCT VALUE2
FROM LPBD7.U_PARAMETER_FILE
WHERE CONDITION1 = 'ORT_EBORD'
AND ACTIVE = 'Y'
AND UPPER(C.NAME) = UPPER(VALUE2)
) E
WHERE A.SERIALS_SP = IN_SERIALS_SP
ORDER BY A.CREATED DESC
FETCH FIRST 1 ROW ONLY
UNION ALL
SELECT D.ADDR, A.STATION_ID, A.CREATED
FROM LPBD7.AGENCY_LOGS_JSON A
JOIN MESNEX.AGENCY_JOBS B ON B.AGENT = A.AGENT_ID
AND A.CREATED BETWEEN B.STARTED AND B.ENDED
JOIN MESNEX.AGENCY_PROCESS C ON C.ID = B.PROCESS
JOIN LP960.AGENCY_AGENTS D ON D.ID = A.AGENT_ID
CROSS APPLY (
SELECT DISTINCT VALUE2
FROM LPBD7.U_PARAMETER_FILE
WHERE CONDITION1 = 'ORT_EBORD'
AND ACTIVE = 'Y'
AND UPPER(C.NAME) = UPPER(VALUE2)
) E
WHERE A.SERIALS_BG = IN_SERIALS_BG
ORDER BY A.CREATED DESC
FETCH FIRST 1 ROW ONLY
UNION ALL
SELECT D.ADDR, A.STATION_ID, A.CREATED
FROM LPBD7.AGENCY_LOGS_JSON A
JOIN MESNEX.AGENCY_JOBS B ON B.AGENT = A.AGENT_ID
AND A.CREATED BETWEEN B.STARTED AND B.ENDED
JOIN MESNEX.AGENCY_PROCESS C ON C.ID = B.PROCESS
JOIN LP960.AGENCY_AGENTS D ON D.ID = A.AGENT_ID
CROSS APPLY (
SELECT DISTINCT VALUE2
FROM LPBD7.U_PARAMETER_FILE
WHERE CONDITION1 = 'ORT_EBORD'
AND ACTIVE = 'Y'
AND UPPER(C.NAME) = UPPER(VALUE2)
) E
WHERE A.SERIALS_FG = IN_SERIALS_FG
ORDER BY A.CREATED DESC
FETCH FIRST 1 ROW ONLY
)
WHERE ADDR IS NOT NULL
ORDER BY CREATED DESC
FETCH FIRST 1 ROW ONLY;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN;
WHEN OTHERS THEN RAISE;
END;
V_STEP := '1';
-- 获取厂区/机种信息
SELECT PLANT, LINE_ID, TYPE_NAME, PRODUCT
INTO V_PLANT, V_LINE_ID, V_PROCESS, V_MODEL
FROM SAJET.TC_ASSY_MACHINE
WHERE MACHINE_ID = V_STATION_ID AND IP = V_ADDR;
V_STEP := '2';
-- [优化点2] 单次JSON解析获取所有数据
DECLARE
TYPE json_rec IS RECORD (
test_name VARCHAR2(1000),
sub_test VARCHAR2(1000),
value NUMBER,
lower_limit NUMBER,
upper_limit NUMBER
);
v_json_data json_rec;
BEGIN
SELECT
JT.test_name,
JT.sub_test,
JT.value,
JT.lower_limit,
JT.upper_limit
INTO v_json_data
FROM JSON_TABLE(IN_JSON, '$.insight.results[0]'
COLUMNS (
test_name VARCHAR2 PATH '$.test',
sub_test VARCHAR2 PATH '$.sub_test',
value NUMBER PATH '$.value',
lower_limit NUMBER PATH '$.lower_limit',
upper_limit NUMBER PATH '$.upper_limit'
)
) JT;
-- 标记上下限是否存在
V_LU_FLAG := (v_json_data.lower_limit IS NOT NULL AND v_json_data.upper_limit IS NOT NULL);
-- 组合测试名称
V_TEST_NAME_NORM :=
CASE WHEN v_json_data.sub_test IS NOT NULL
THEN v_json_data.test_name || ',' || v_json_data.sub_test
ELSE v_json_data.test_name
END;
V_VALUE := ROUND(v_json_data.value, 2);
V_LOWER_LIMIT := CASE WHEN V_LU_FLAG THEN ROUND(v_json_data.lower_limit, 2) END;
V_UPPER_LIMIT := CASE WHEN V_LU_FLAG THEN ROUND(v_json_data.upper_limit, 2) END;
EXCEPTION
WHEN OTHERS THEN RETURN;
END;
V_STEP := '3';
-- [优化点3] 预计算正则避免重复调用
V_TEST_NAME_NORM := UPPER(REGEXP_REPLACE(V_TEST_NAME_NORM, '[^a-z,A-Z, ,0-9,.,-]'));
-- 获取频率参数
BEGIN
SELECT CONDITION2, VALUE2
INTO V_FRE, V_TYPE_NAME
FROM LPBD7.U_PARAMETER_FILE
WHERE CONDITION1 = 'ORT_EBORD'
AND ACTIVE = 'Y'
AND INSTR(
V_TEST_NAME_NORM,
UPPER(REGEXP_REPLACE(VALUE3, '[^a-z,A-Z, ,0-9,.,-]'))
) > 0
FETCH FIRST 1 ROW ONLY;
EXCEPTION
WHEN OTHERS THEN RETURN;
END;
V_STEP := '4';
-- [优化点4] 直接MERGE操作
G_NEXT_REC_TIME := SAJET.F_GET_NEXT_RECTIME(V_FRE, IN_CREATED);
MERGE INTO SAJET.TC_ASSY_EVENT T
USING (
SELECT
V_PLANT AS PLANT,
V_LINE_ID AS LINE_ID,
V_STATION_ID AS MACHINE_ID,
V_ADDR AS IP,
V_TYPE_NAME AS TYPE_NAME,
V_MODEL AS MODEL
) S ON (
T.PLANT = S.PLANT
AND T.MODEL = S.MODEL
AND T.LINE_ID = S.LINE_ID
AND T.TYPE_NAME = S.TYPE_NAME
AND UPPER(REGEXP_REPLACE(T.TEST_NAME, '[^a-z,A-Z, ,0-9,.,-]')) = V_TEST_NAME_NORM
)
WHEN MATCHED THEN UPDATE SET
SERIALS_BAND = IN_SERIALS_BAND,
SERIALS_SP = IN_SERIALS_SP,
SERIALS_BG = IN_SERIALS_BG,
SERIALS_FG = IN_SERIALS_FG,
CREATED = SYSDATE,
VALUE = V_VALUE,
EVENT = IN_EVENT,
LOWER_LIMIT = V_LOWER_LIMIT,
UPPER_LIMIT = V_UPPER_LIMIT,
REC_TIME = IN_CREATED,
NEXT_REC_TIME = G_NEXT_REC_TIME,
SAMPLE_TYPE = IN_SAMPLE_TYPE
WHEN NOT MATCHED THEN INSERT (
PLANT, LINE_ID, MACHINE_ID, IP, TYPE_NAME,
SERIALS_BAND, SERIALS_SP, SERIALS_BG, SERIALS_FG,
CREATED, TEST_NAME, VALUE, EVENT, LOWER_LIMIT,
UPPER_LIMIT, REC_TIME, NEXT_REC_TIME, MODEL,
PROCESS, SAMPLE_TYPE
) VALUES (
S.PLANT, S.LINE_ID, S.MACHINE_ID, S.IP, S.TYPE_NAME,
IN_SERIALS_BAND, IN_SERIALS_SP, IN_SERIALS_BG, IN_SERIALS_FG,
SYSDATE, V_TEST_NAME_NORM, V_VALUE, IN_EVENT, V_LOWER_LIMIT,
V_UPPER_LIMIT, IN_CREATED, G_NEXT_REC_TIME, V_MODEL,
V_PROCESS, IN_SAMPLE_TYPE
);
COMMIT;
V_STEP := '5';
EXCEPTION
WHEN OTHERS THEN
V_MSG := SQLERRM;
V_LINE := DBMS_UTILITY.FORMAT_ERROR_STACK || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
SAJET.SF_MES_Send_Mail(
'yujia.liu@tzlens.com,mes.it@tzlens.com,',
'LPBD7.SP_LOG_ORT_INSERT_EVENT ERROR: ' || V_STEP,
'SERIALS: ' || IN_SERIALS_BAND || ',' || IN_SERIALS_SP || ',' || IN_SERIALS_BG || ',' || IN_SERIALS_FG,
'ERROR: ' || SUBSTR(V_LINE, 1, 4000) || ',' || SUBSTR(V_MSG, 1, 4000),
''
);
RAISE;
END;
最新发布
当从MySQL切换到Oracle数据库时，使用UNION ALL语句并结合子查询中的ORDER BY子句会导致ORA-00907错误：缺失右括号。解决方法是将ORDER BY移出子查询，放在整个UNION ALL操作的外部。
扫一扫
2816
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
