现有如下oracle的TRIGGER和SQL，
因在LPBD7.AGENCY_LOGS_JSON表有多个触发器造成数据同步延迟，请将TRIGGER和SQL的逻辑结合，重新整理出一个PROCEDURE，并且逻辑简单清晰，提高执行效率和减少资源占用：
CREATE OR REPLACE TRIGGER LPBD7.TRG_AGENCY_LOGS_ORT
AFTER INSERT ON LPBD7.AGENCY_LOGS_JSON
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
DISABLE
DECLARE
V_MSG VARCHAR2(1000);
V_CNT NUMBER;
V_CNT2 NUMBER;
V_PROCESS_NAME VARCHAR2(200);
V_TEST_NAME VARCHAR2(200);
V_RESULT_TEST VARCHAR2(200);
V_RESULT VARCHAR2(500);
V_SAMPLE_TYPE VARCHAR2(200);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF INSERTING
THEN
SELECT COUNT(*)
INTO V_CNT
FROM MESNEX.AGENCY_JOBS J
JOIN MESNEX.AGENCY_PROCESS P ON J.PROCESS = P.ID
WHERE AGENT = :NEW.AGENT_ID
AND DISABLED = 'False'
AND :NEW.CREATED BETWEEN STARTED AND ENDED;
IF V_CNT > 0
THEN
SELECT DISTINCT P.NAME
INTO V_PROCESS_NAME
FROM MESNEX.AGENCY_JOBS J
JOIN MESNEX.AGENCY_PROCESS P ON J.PROCESS = P.ID
WHERE AGENT = :NEW.AGENT_ID
AND DISABLED = 'False'
AND :NEW.Created BETWEEN STARTED AND ENDED;
--ort-raw写入关联资料，供ORT专案使用
IF UPPER(V_PROCESS_NAME) = UPPER('ort-raw')
THEN
SELECT UPPER(REGEXP_REPLACE(JSON_VALUE (:NEW.JSON, '$.insight.results.test'),'[^a-z,A-Z, ,0-9]')),JSON_VALUE (:NEW.JSON, '$.insight.results.test')
,JSON_VALUE (:NEW.JSON, '$.insight.uut_attributes.sample_type')
INTO V_RESULT_TEST,V_TEST_NAME,V_SAMPLE_TYPE
FROM DUAL;
SELECT COUNT (1)
INTO V_CNT2
FROM (SELECT DISTINCT VALUE1,VALUE2 FROM LPBD7.U_PARAMETER_FILE A WHERE A.CONDITION1 = 'ORT_EBORD' AND A.ACTIVE = 'Y')
WHERE INSTR (V_RESULT_TEST, UPPER(REGEXP_REPLACE(VALUE1,'[^a-z,A-Z, ,0-9]'))) > 0;
IF V_CNT2 > 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)
VALUES
(:NEW.EVENT,
:NEW.AGENT_ID,
:NEW.SERIALS_BAND,
:NEW.SERIALS_SP,
:NEW.SERIALS_BG,
:NEW.SERIALS_FG,
V_TEST_NAME,
:NEW.CREATED,
:NEW.INSERTD,
:NEW.STATION_ID,
:NEW.ID);
COMMIT;
LPBD7.SP_LOG_ORT_INSERT_EVENT(:NEW.SERIALS_BAND, :NEW.SERIALS_SP, :NEW.SERIALS_BG, :NEW.SERIALS_FG, :NEW.ID,
:NEW.CREATED, V_TEST_NAME, :NEW.EVENT, :NEW.JSON, V_SAMPLE_TYPE);
END IF;
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
V_MSG := SQLERRM;
SELECT SAJET.SF_MES_Send_Mail('mes.it@tzlens.com,yujia.liu@tzlens.com',
:NEW.ID || ', AGENT:' || :NEW.AGENT_ID || ', CREATED:' || :NEW.CREATED || ', ' || :NEW.SERIALS_BAND || ', ' || :NEW.SERIALS_SP || ' ' || SUBSTR(V_MSG, 1, 4000),
'LPBD7 TRG_AGENCY_LOGS_ORT ERROR', '', '', '')
INTO V_RESULT
FROM DUAL;
--raise_application_error(-20101, V_MSG);
END;
/
SHOW ERRORS;
--ORT量测数据查询并写入AGENCY_LOGS_ORT表
DECLARE
TYPE t_param_values IS TABLE OF VARCHAR2(4000);
v_params t_param_values;
v_exists NUMBER;
v_found BOOLEAN := FALSE; -- 修复点：声明匹配标志变量
BEGIN
-- 预加载参数值（减少循环中重复查询）
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:] ]', ''); -- 直接去重
FOR rec IN (
SELECT
H.EVENT, H.AGENT_ID, H.SERIALS_BAND, H.SERIALS_SP,
H.SERIALS_BG, H.SERIALS_FG, H.JSON,
JSON_VALUE(H.JSON, '$.insight.results.test') TEST_NAME,
H.CREATED, H.INSERTD, H.STATION_ID, H.ID,
JSON_VALUE(H.JSON, '$.insight.uut_attributes.sample_type') SAMPLE_TYPE,
-- 预计算正则处理值（提升性能）
UPPER(REGEXP_REPLACE(JSON_VALUE(H.JSON, '$.insight.results.test'), '[^[:alnum:] ]', '')) CLEAN_TEST
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.CREATED >= TRUNC(SYSDATE) + 8.5/24
) LOOP
-- 重置匹配标志
v_found := FALSE;
-- 参数匹配检查（使用预加载集合）
FOR i IN 1..v_params.COUNT LOOP
CONTINUE WHEN v_params(i) IS NULL;
IF INSTR(rec.CLEAN_TEST, UPPER(v_params(i))) > 0 THEN
v_found := TRUE;
EXIT; -- 找到匹配立即退出
END IF;
END LOOP;
IF v_found THEN -- 修复点：使用显式声明的标志变量
-- 检查目标表是否存在重复记录
SELECT COUNT(1) INTO v_exists
FROM LPBD7.AGENCY_LOGS_ORT
WHERE EVENT = rec.EVENT
AND AGENT_ID = rec.AGENT_ID
AND SERIALS_BAND = rec.SERIALS_BAND
AND SERIALS_SP = rec.SERIALS_SP
AND SERIALS_BG = rec.SERIALS_BG
AND SERIALS_FG = rec.SERIALS_FG
AND TEST_NAME = rec.TEST_NAME
AND CREATED = rec.CREATED
AND INSERTD = rec.INSERTD
AND STATION_ID = rec.STATION_ID;
IF v_exists = 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
) VALUES (
rec.EVENT, rec.AGENT_ID, rec.SERIALS_BAND, rec.SERIALS_SP,
rec.SERIALS_BG, rec.SERIALS_FG, rec.TEST_NAME,
rec.CREATED, rec.INSERTD, rec.STATION_ID, rec.ID
);
-- 调用存储过程（确保事务一致性）
LPBD7.SP_LOG_ORT_INSERT_EVENT(
rec.SERIALS_BAND, rec.SERIALS_SP, rec.SERIALS_BG,
rec.SERIALS_FG, rec.ID, rec.CREATED,
rec.TEST_NAME, rec.EVENT, rec.JSON, rec.SAMPLE_TYPE
);
END IF;
END IF;
END LOOP;
COMMIT; -- 统一提交（减少I/O开销）
EXCEPTION -- 新增异常处理
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
最新发布
本文介绍了如何在Oracle PLSQL中将字符串转换为数组，利用BULK COLLECT INTO关键字将字符串拆分成表格形式，便于进行随机访问。通过示例展示了具体的转换过程。
最低0.47元/天 解锁文章
扫一扫
2691
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
