Long run sql:
MERGE INTO INTITMRTNPARAM D
USING (
SELECT A.INRFILENM,A.INRSTAT,A.INRDEPCD,A.INRITMCD,A.INRSUBCD,A.INRSUPCD,A.INRSTOCD
FROM INTITMRTNPARAM A,INTITMRTNPARAM B
WHERE 1=1
AND A.ROWID <> B.ROWID
AND A.INRFILENM = B.INRFILENM
AND A.INRSTAT = 0
AND B.INRSTAT = 0
AND A.INRFILENM = p_filenm
AND B.INRFILENM = p_filenm
AND A.INRDEPCD = B.INRDEPCD
AND A.INRITMCD = B.INRITMCD
AND PKCRS.CHECKINCLUDECUSTTYPE(NVL(A.INRSUBCD,'ALL'),NVL(B.INRSUBCD,'ALL')) = 1
AND PKCRS.CHECKINCLUDECUSTTYPE(NVL(A.INRSUPCD,'ALL'),NVL(B.INRSUPCD,'ALL')) = 1
AND PKCRS.CHECKINCLUDECUSTTYPE(NVL(A.INRSTOCD,'ALL'),NVL(B.INRSTOCD,'ALL')) = 1
GROUP BY A.INRFILENM,A.INRSTAT,A.INRDEPCD,A.INRITMCD,A.INRSUBCD,A.INRSUPCD,A.INRSTOCD
) E
ON ( D.INRFILENM = E.INRFILENM
AND D.INRSTAT = E.INRSTAT
AND D.INRDEPCD = E.INRDEPCD
AND D.INRITMCD = E.INRITMCD
AND DECODE(TRIM(NVL(D.INRSUBCD,'')),'','NULL',TRIM(D.INRSUBCD)) = DECODE(TRIM(NVL(E.INRSUBCD,'')),'','NULL',TRIM(E.INRSUBCD))
AND DECODE(TRIM(NVL(D.INRSUPCD,'')),'','NULL',TRIM(D.INRSUPCD)) = DECODE(TRIM(NVL(E.INRSUPCD,'')),'','NULL',TRIM(E.INRSUPCD))
AND DECODE(TRIM(NVL(D.INRSTOCD,'')),'','NULL',TRIM(D.INRSTOCD)) = DECODE(TRIM(NVL(E.INRSTOCD,'')),'','NULL',TRIM(E.INRSTOCD)) )
WHEN MATCHED THEN
UPDATE SET D.INRERRNO = 2,
D.INRERRMES = D.INRERRMES || ', Dupplicate item',
D.INRUPD = p_date,
D.INRUSR = p_user;
Long sql 中调用的function（checkIncludeCustType）：
FUNCTION checkIncludeCustType(p_custType1 PRITSPHDR.TSPHCUSTYPE%TYPE,p_custType2 PRITSPHDR.TSPHCUSTYPE%TYPE) RETURN NUMBER IS
v_check NUMBER(1);
BEGIN
IF p_custType2 IS NULL OR p_custType1 IS NULL THEN
RETURN 1;
END IF;
BEGIN
SELECT 1
INTO v_check
FROM (SELECT LEVEL,
SUBSTR (
STRING_TO_TOKENIZE,
DECODE(LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL-1)+1),
INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL) - DECODE(LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL-1)+1)
) AS Token
FROM ( SELECT p_custType1||',' AS STRING_TO_TOKENIZE, ',' AS DELIMITER FROM DUAL)
CONNECT BY INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL)>0
ORDER BY LEVEL ASC
) custtype1,
(SELECT LEVEL,
SUBSTR ( STRING_TO_TOKENIZE,
DECODE(LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL-1)+1),
INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL) - DECODE(LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL-1)+1)
) AS Token
FROM ( SELECT p_custType2||',' AS STRING_TO_TOKENIZE, ',' AS DELIMITER FROM DUAL)
CONNECT BY INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL)>0
ORDER BY LEVEL ASC
) custtype2
WHERE custtype1.token = custtype2.token
AND rownum = 1;
EXCEPTION WHEN NO_DATA_FOUND THEN
v_check := 0;
END;
RETURN v_check;
END checkIncludeCustType;
优化后：
UPDATE INTITMRTNCON D
SET D.INRERRNO = 2, D.INRERRMES = D.INRERRMES || ', Dupplicate item' , D.INRUPD = p_date, D.INRUSR = p_user
WHERE INRFILENM = p_filenm AND INRSTAT = 0
AND (INRFILENM, INRSTAT, INRDPCD, INRITMCD, NVL(INRSUBCD, 'ALL'),
NVL(INRSUPCD, 'ALL')) IN
(SELECT INRFILENM, INRSTAT, INRDPCD, INRITMCD, NVL(INRSUBCD, 'ALL'), NVL(INRSUPCD, 'ALL')
FROM (SELECT T.INRFILENM, T.INRSTAT, T.INRDPCD, T.INRITMCD, T.INRSUBCD, T.INRSUPCD
FROM INTITMRTNCON T
WHERE INSTR(nvl(trim(INRSUBCD), 'ALL'), ',') = 0
and INSTR(nvl(trim(INRSUPCD), 'ALL'), ',') = 0
AND INRFILENM = p_filenm AND INRSTAT = 0
UNION ALL
select a.INRFILENM, a.INRSTAT, a.INRDPCD, a.INRITMCD, a.INRSUBCD, b.INRSUPCD
from (SELECT SUBSTR(STRING_TO_TOKENIZE,
DECODE(LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER,
1, LEVEL - 1) + 1),
INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL) -
DECODE(LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER,
1, LEVEL - 1) + 1))
AS INRSUBCD,
V_ROWID, INRFILENM, INRSTAT, INRDPCD, INRITMCD
FROM (SELECT T.INRSUBCD || ',' AS STRING_TO_TOKENIZE,
',' AS DELIMITER, T.ROWID V_ROWID, INRFILENM,
INRSTAT, INRDPCD, INRITMCD
FROM INTITMRTNCON T
WHERE INRFILENM = p_filenm AND INRSTAT = 0 AND (INSTR(INRSUBCD, ',') >= 1
or INSTR(INRSUPCD, ',') >= 1))
CONNECT BY INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL) > 0) a,
(SELECT SUBSTR(STRING_TO_TOKENIZE,
DECODE(LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL - 1) + 1),
INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL) -
DECODE(LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL - 1) + 1)) AS INRSUPCD,
V_ROWID, INRFILENM, INRSTAT, INRDPCD, INRITMCD
FROM (SELECT T.INRSUPCD || ',' AS STRING_TO_TOKENIZE, ',' AS DELIMITER,
T.ROWID V_ROWID, INRFILENM, INRSTAT, INRDPCD, INRITMCD
FROM INTITMRTNCON T
WHERE INRFILENM = p_filenm AND INRSTAT = 0 AND (INSTR(INRSUBCD, ',') >= 1
or INSTR(INRSUPCD, ',') >= 1 ))
CONNECT BY INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL) > 0) b
where a.v_rowid = b.v_rowid)
GROUP BY INRFILENM, INRSTAT, INRDPCD, INRITMCD, INRSUBCD, INRSUPCD
HAVING COUNT(*) > 1);