ORACLE PL/SQL 검색어 +/- 연산자 구현

ORACLE PL/SQL +/- 검색어 연산자 구현

DECLARE

    V_SQL_SEARCH    varchar2(20000):='';

    CURSOR SEARCH_STR_LIST (V_SEARCH_STR VARCHAR2) IS
    SELECT NVL(OPT_STR,SEARCH_STR) AS SEARCH_STR, R_RN
    FROM 
    (
        SELECT STR, OPT_STR, ROW_NUMBER() OVER (ORDER BY LV1, LV2 DESC) AS RN 
        FROM (
                SELECT ' '||V_SEARCH_STR||' ' AS STR, '' AS OPT_STR, 3 AS LV1, 0 AS LV2  FROM DUAL
                UNION ALL
                SELECT STR, SUBSTRB(STR, ST,ED-ST) AS OPT_STR, 2 AS LV1, LV2
                FROM(
                    SELECT STR     
                    ,INSTRB(STR, ' +',1,LEVEL) AS ST
                    ,INSTRB(STR, ' ',INSTRB(STR, ' +',1,LEVEL)+1) AS ED
                    ,LEVEL AS LV2
                    FROM  (   
                        SELECT STR, LENGTHB(STR) AS ORI_LEN, LENGTHB(REPLACE(STR,' +')) AS NEW_LEN
                        FROM (SELECT ' '||V_SEARCH_STR||' ' AS STR FROM DUAL))
                    WHERE ORI_LEN <> NEW_LEN
                    CONNECT BY LEVEL <= (ORI_LEN - NEW_LEN)/2
                    )
                UNION ALL
                SELECT STR, SUBSTRB(STR, ST,ED-ST) AS OPT_STR, 1 AS LV1, LV2
                FROM(
                    SELECT STR     
                    ,INSTRB(STR, ' -',1,LEVEL) AS ST
                    ,INSTRB(STR, ' ',INSTRB(STR, ' -',1,LEVEL)+1) AS ED
                    ,LEVEL AS LV2
                    FROM  (   
                        SELECT STR, LENGTHB(STR) AS ORI_LEN, LENGTHB(REPLACE(STR,' -')) AS NEW_LEN
                        FROM (SELECT ' '||V_SEARCH_STR||' ' AS STR FROM DUAL))
                    WHERE ORI_LEN <> NEW_LEN
                    CONNECT BY LEVEL <= (ORI_LEN - NEW_LEN)/2
                    )))
    MODEL 
    PARTITION BY (STR)
    DIMENSION BY (RN) 
    MEASURES(OPT_STR, STR AS SEARCH_STR
            ,ROW_NUMBER()OVER(ORDER BY RN DESC) AS R_RN)
    RULES
    AUTOMATIC ORDER    
    (
        SEARCH_STR[RN] = REPLACE(NVL(SEARCH_STR[CV(RN)-1], SEARCH_STR[CV(RN)]), OPT_STR[CV(RN)], '')
    )
    ORDER BY R_RN;

BEGIN

    V_SQL_SEARCH := '';
    
    FOR SEARCH IN SEARCH_STR_LIST('검색어1 검색어2 +더하기1 +더하기2 검색어3 -빼기1 검색어4 -빼기2') LOOP
        
        IF SEARCH.R_RN = 1 THEN
            V_SQL_SEARCH := 'REGEXP_LIKE(E.NM_CNTS,''' || REPLACE(TRIM(SEARCH.SEARCH_STR),' ','|') || ''')' ;
        ELSIF SUBSTRB(SEARCH.SEARCH_STR,1,2) = ' +' THEN
            V_SQL_SEARCH := V_SQL_SEARCH || ' AND E.NM_CNTS LIKE ''%' || TRIM(REPLACE(SEARCH.SEARCH_STR,' +')) || '%'' ';
        ELSIF SUBSTRB(SEARCH.SEARCH_STR,1,2) = ' -' THEN
            V_SQL_SEARCH := V_SQL_SEARCH || ' AND E.NM_CNTS NOT LIKE ''%' || TRIM(REPLACE(SEARCH.SEARCH_STR,' -')) || '%'' ';
        END IF;
 
        dbms_output.put_line(SEARCH.R_RN || ' : ' || SEARCH.SEARCH_STR); 

    END LOOP;
        
    IF V_SQL_SEARCH IS NOT NULL THEN
        V_SQL_SEARCH := '(' ||V_SQL_SEARCH || ')';
    END IF;
        
    dbms_output.put_line(V_SQL_SEARCH);   

END;

작성 배경

네이버 검색어 연산자 + / - 의 필요성을 느껴 구현하였습니다.

좀 더 쉬운방법이 있을 듯 하나.. 

우선 떠오르는데로 작성한 것이므로 좀 더 좋은 방법을 아시는 분들은 코멘트 남겨주시면 감사하겠습니다.

실행결과