[ORACLE] 다른 테이블에서 특정 코드 데이터가 있는지 확인하는 FUNCTION

1. SI 프로젝트 진행시 대개 PL이 ERD를 그릴때 테이블간 FK를 연결해놓는다.

 

2. 그런데, ERD를 각 개개인이 담당하는 업무별로 작성하다보니 본인의 업무내에서는 FK가 연결이 되는데 타업무와는 연결이 안 되는 경우가 있었다.

 

3. 예컨대, 회계업무에서 관리하는 거래처코드를 급여업무에서 사용하고 있었는데 거래처코드가 삭제 되어 버린다던지..

    (실제로 일어난 일)

 

4. 위 상황을 구현 단계에서 방어 할 수 있지 않을까 싶다.

 

※ 구현 스크립트 내의 TF_SPLIT_TO_ROW TABLE FUNCTION은 아래 링크에서 확인 할 수 있다.

[ORACLE] 구분자로 분리하여 행으로 반환하는 TABLE FUNCTION

 

CREATE OR REPLACE FUNCTION SF_DATA_USE_CHK
/*******************************************************************************
[T01] Function 명   : SF_DATA_USE_CHK

[T02] Function 기능 : 컬럼의 값을 다른 테이블에서 사용하고 있는지 찾는다
                      거래처코드 등의 코드를 타 업무에서 사용중인데 삭제되는 불상사를 막기 위함
[T03] 작성자        : 울면서개발하기 https://tt-dev.tistory.com/
[T04] 실행예제      : 

    -- 1. KEY가 1개인경우
    DECLARE V_DATA_USE_CHK VARCHAR(4000);
    BEGIN
        -- 1-1. 데이터 사용중인지 확인
        SELECT SF_DATA_USE_CHK('BPLC_MNG', 'BPLC_CD', #{BPLC_CD}, )
          INTO V_DATA_USE_CHK
          FROM DUAL
        ;
        -- 1-2. 유저에러 발생
        IF V_DATA_USE_CHK IS NOT NULL THEN
            RAISE_APPLICATION_ERROR(-20001, V_DATA_USE_CHK || ' 삭제 할 수 없습니다.');
        END IF;
        
        -- 1-3. 데이터 삭제
        DELETE
          FROM BPLC_MNG
         WHERE BPLC_CD = #{BPLC_CD}
        ;
    END;

    -- 2. KEY가 2개인경우
    DECLARE V_DATA_USE_CHK VARCHAR(4000);
    BEGIN
        
        -- 2-1. 데이터 사용중인지 확인
        SELECT SF_DATA_USE_CHK('DETA_MNG', 'ACC_SUBJ_CD', #{ACC_SUBJ_CD}, 'DETA_CD', #{DETA_CD})
          INTO V_DATA_USE_CHK
          FROM DUAL
        ;
        
        -- 2-2. 유저에러 발생
        IF V_DATA_USE_CHK IS NOT NULL THEN
            RAISE_APPLICATION_ERROR(-20001, V_DATA_USE_CHK);
        END IF
        ;
    
        -- 2-3. 데이터 삭제
        DELETE 
          FROM DETA_MNG
         WHERE ACC_SUBJ_CD = #{ACC_SUBJ_CD}
           AND DETA_CD = #{DETA_CD}
        ;
    END;       

    -- 3. 제외하려는 테이블이 2개 이상인경우
    DECLARE V_DATA_USE_CHK VARCHAR(4000);
    BEGIN
        
        -- 3-1. 데이터 사용중인지 확인
        SELECT SF_DATA_USE_CHK('DETA_MNG,DECI_MNG', 'ACC_SUBJ_CD', #{ACC_SUBJ_CD}, 'DETA_CD', #{DETA_CD})
          INTO V_DATA_USE_CHK
          FROM DUAL
        ;
        
        -- 3-2. 유저에러 발생
        IF V_DATA_USE_CHK IS NOT NULL THEN
            RAISE_APPLICATION_ERROR(-20001, V_DATA_USE_CHK);
        END IF
        ;
    
        -- 3-3. 데이터 삭제
        DELETE 
          FROM DETA_MNG
         WHERE ACC_SUBJ_CD = #{ACC_SUBJ_CD}
           AND DETA_CD = #{DETA_CD}
        ;
    END;       

[T09] 비고          :

*******************************************************************************/
(
      IN_NOT_IN_TABLE_NAME IN VARCHAR2              -- 확인에서 제외할 테이블. 자기 테이블은 기본으로 들어가야 된다. ','로 연결 가능.
    , IN_COLUMN_NAME_1     IN VARCHAR2              -- 확인할 컬럼명1
    , IN_COLUMN_DATA_1     IN VARCHAR2              -- 컬럼의 데이터1
    , IN_COLUMN_NAME_2     IN VARCHAR2 DEFAULT NULL -- 확인할 컬럼명2
    , IN_COLUMN_DATA_2     IN VARCHAR2 DEFAULT NULL -- 컬럼의 데이터2
    , IN_COLUMN_NAME_3     IN VARCHAR2 DEFAULT NULL -- 확인할 컬럼명3
    , IN_COLUMN_DATA_3     IN VARCHAR2 DEFAULT NULL -- 컬럼의 데이터3
    , IN_COLUMN_NAME_4     IN VARCHAR2 DEFAULT NULL -- 확인할 컬럼명4
    , IN_COLUMN_DATA_4     IN VARCHAR2 DEFAULT NULL -- 컬럼의 데이터4
    , IN_COLUMN_NAME_5     IN VARCHAR2 DEFAULT NULL -- 확인할 컬럼명5
    , IN_COLUMN_DATA_5     IN VARCHAR2 DEFAULT NULL -- 컬럼의 데이터5
)

RETURN VARCHAR2
IS
    V_SQL    VARCHAR2(4000);
    V_RETURN VARCHAR2(4000);
BEGIN

    SELECT LISTAGG(
                      CASE WHEN A.RN = 1 THEN '' ELSE CHR(13) || 'UNION ALL ' END
                        || CHR(13)
                        || 'SELECT ''' || A.TABLE_COMMENTS || ''' AS TABLE_NAME'
                        || '  FROM '   || A.TABLE_NAME
                        || ' WHERE ROWNUM = 1'
                        || '   AND '   || IN_COLUMN_NAME_1   || ' = ''' || IN_COLUMN_DATA_1 || ''''
                        || NVL2(IN_COLUMN_NAME_2
                         , '   AND '   || IN_COLUMN_NAME_2   || ' = ''' || IN_COLUMN_DATA_2 || ''''
                         , '')
                        || NVL2(IN_COLUMN_NAME_3
                         , '   AND '   || IN_COLUMN_NAME_3   || ' = ''' || IN_COLUMN_DATA_3 || ''''
                         , '')
                        || NVL2(IN_COLUMN_NAME_4
                         , '   AND '   || IN_COLUMN_NAME_4   || ' = ''' || IN_COLUMN_DATA_4 || ''''
                         , '')
                        || NVL2(IN_COLUMN_NAME_5
                         , '   AND '   || IN_COLUMN_NAME_5   || ' = ''' || IN_COLUMN_DATA_5 || ''''
                         , '')
                  )
           WITHIN GROUP(ORDER BY A.RN)
      INTO V_SQL
      FROM (
                SELECT /*+ RULE */
                       A.TABLE_NAME                             AS TABLE_NAME
                     , MAX(B.COMMENTS)                          AS TABLE_COMMENTS
                     , MAX(A.COMMENTS)                          AS COLUMN_COMMENTS
                     , ROW_NUMBER() OVER(ORDER BY A.TABLE_NAME) AS RN
                  FROM USER_COL_COMMENTS A
                    INNER JOIN USER_TAB_COMMENTS B
                      ON A.TABLE_NAME = B.TABLE_NAME
                     AND 'TABLE'      = B.TABLE_TYPE
                 WHERE A.TABLE_NAME NOT LIKE 'BIN%' -- 휴지통에 들어간 테이블 제외
                   AND REGEXP_REPLACE(A.TABLE_NAME, '[^a-zA-Z_]', '') = A.TABLE_NAME -- 숫자나 한글로 네이밍한 백업 테이블은 제외
                   AND A.TABLE_NAME NOT IN ( -- 호출시 제외하려는 테이블 제외
                                               SELECT X.TEXT
                                                 FROM TABLE(TF_SPLIT_TO_ROW(NVL(IN_NOT_IN_TABLE_NAME, 'X'))) X
                                           )
                   AND EXISTS (SELECT 1 FROM USER_COL_COMMENTS X WHERE X.COLUMN_NAME = IN_COLUMN_NAME_1                     AND X.TABLE_NAME  = A.TABLE_NAME)
                   AND EXISTS (SELECT 1 FROM USER_COL_COMMENTS X WHERE X.COLUMN_NAME = NVL(IN_COLUMN_NAME_2, X.COLUMN_NAME) AND X.TABLE_NAME  = A.TABLE_NAME)
                   AND EXISTS (SELECT 1 FROM USER_COL_COMMENTS X WHERE X.COLUMN_NAME = NVL(IN_COLUMN_NAME_3, X.COLUMN_NAME) AND X.TABLE_NAME  = A.TABLE_NAME)
                   AND EXISTS (SELECT 1 FROM USER_COL_COMMENTS X WHERE X.COLUMN_NAME = NVL(IN_COLUMN_NAME_4, X.COLUMN_NAME) AND X.TABLE_NAME  = A.TABLE_NAME)
                   AND EXISTS (SELECT 1 FROM USER_COL_COMMENTS X WHERE X.COLUMN_NAME = NVL(IN_COLUMN_NAME_5, X.COLUMN_NAME) AND X.TABLE_NAME  = A.TABLE_NAME)
                 GROUP BY A.TABLE_NAME
           ) A
    ;

    IF V_SQL IS NOT NULL THEN
        EXECUTE IMMEDIATE 'SELECT /*+ RULE */ LISTAGG(TABLE_NAME, '' '') WITHIN GROUP(ORDER BY 1) FROM (' || V_SQL || ')' INTO V_RETURN;
    END IF;

    IF V_RETURN IS NOT NULL THEN
        V_RETURN := '[' || V_RETURN || ']에서 사용중인 코드입니다.'
                        || '('
                        || IN_COLUMN_DATA_1
                        || CASE WHEN IN_COLUMN_DATA_2 IS NOT NULL THEN ' '|| IN_COLUMN_DATA_2 END
                        || CASE WHEN IN_COLUMN_DATA_3 IS NOT NULL THEN ' '|| IN_COLUMN_DATA_3 END
                        || CASE WHEN IN_COLUMN_DATA_4 IS NOT NULL THEN ' '|| IN_COLUMN_DATA_4 END
                        || CASE WHEN IN_COLUMN_DATA_5 IS NOT NULL THEN ' '|| IN_COLUMN_DATA_5 END
                        || ')';
    END IF;

    RETURN V_RETURN;

EXCEPTION
  WHEN OTHERS THEN
    RETURN '데이터 사용 확인중 예상하지 못한 오류가 발생하였습니다.' || SQLERRM;
END;