[Oracle] 빈 값을 채워 Sequence 채번하기

oracle에서 Sequence 값을 순서대로 채번하여 테이블의 key 값으로 넣어주는 경우가 있을 것이다.
보통은 단순하게 MAX + 1을 하여 값을 저장하게 된다.
그런데 채번하여 저장된 이 값의 중간 데이터가 삭제되는 경우,
key값이 중간에 빠지는 경우가 발생하게 된다.
이러한 경우를 방지하기 위하여 채번할 때 중간에 빠지는 값이 없이 저장하는 방법에 대해 알아보자.

 

1) 채번하는 코드의 시작과 끝을 아는 경우

: 이 경우는 채번값의 시작과 끝이 어떤 값으로 저장되는지, 즉 코드값의 범위를 아는 경우이다.
예를 들어, key 값이 한 자리 수이고 시작 채번값이 1이라면 범위는 1~9일 것이다.
이 경우에는 아래 예시와 같이 코드를 작성하면 된다.

WITH DATA_TABLE AS (
    SELECT '1' AS SEQ FROM DUAL
    UNION ALL
    SELECT '2' AS SEQ FROM DUAL
    UNION ALL
    SELECT '5' AS SEQ FROM DUAL
    UNION ALL
    SELECT '6' AS SEQ FROM DUAL
    UNION ALL
    SELECT '9' AS SEQ FROM DUAL
)
SELECT MIN(SEQ) RESULT_SEQ
FROM    (   SELECT LPAD(ROWNUM + TO_NUMBER('1') - 1, 1, '0') AS SEQ
            FROM DATA_TABLE       
            WHERE SEQ BETWEEN 1 AND 9 
            AND LENGTH(SEQ) = 1
            MINUS   
            SELECT SEQ
            FROM DATA_TABLE
            WHERE SEQ BETWEEN 1 AND 9 
            AND LENGTH(SEQ) = 1
        );

SELECT LPAD(ROWNUM + TO_NUMBER('1') - 1, 1, '0') FROM DUAL;

위 코드를 나누어서 해석해보자.

1-1)

SELECT LPAD(ROWNUM + TO_NUMBER('1') - 1, 1, '0') AS SEQ
FROM DATA_TABLE       
WHERE SEQ BETWEEN 1 AND 9 
AND LENGTH(SEQ) = 1

위의 쿼리는 DATA_TABLE 테이블의 1~9 사이의 SEQ값 개수만큼 ROWNUM으로 출력하는 것으로,

결과는 아래와 같다.

 

1-2)

SELECT SEQ
FROM DATA_TABLE
WHERE SEQ BETWEEN 1 AND 9 
AND LENGTH(SEQ) = 1

위의 쿼리는 DATA_TABLE 테이블의 1~9 사이의 SEQ값을 출력하는 것으로, 결과는 아래와 같다.

 

1-3) MINUS

MINUS의 의미는 말그대로 차집합 연산자로, 첫 번째 쿼리문에서 두 번째 쿼리문을 뺀 값만을 출력한다.
따라서 1-1의 결과값에서 1-2의 결과값을 뺀 값이다.

 

2) 채번하는 코드가 1부터 시작하는 경우

: 이 경우는 채번값이 1부터 시작하는 경우로, ROWNUM 값을 활용하여 작성하였다.
이 때 코드에서 NVL 함수를 활용하는데, NVL 함수는 ** NVL(값1, 값2) ** 의 형태로 사용한다.
NVL 함수는 값1이 NULL인 경우 값2를 출력하는 것이다.

WITH DATA_TABLE AS (
    SELECT '1' AS SEQ FROM DUAL
    UNION ALL
    SELECT '2' AS SEQ FROM DUAL
    UNION ALL
    SELECT '5' AS SEQ FROM DUAL
    UNION ALL
    SELECT '6' AS SEQ FROM DUAL
    UNION ALL
    SELECT '9' AS SEQ FROM DUAL
)
SELECT NVL(
    (
        SELECT NUM
        FROM (
                SELECT ROWNUM AS NUM, SEQ
                FROM (
                        SELECT SEQ
                        FROM DATA_TABLE
                        ORDER BY SEQ
                     )
            )
        WHERE NUM <> SEQ AND ROWNUM = 1
    )
    ,
    (
        SELECT MAX(NVL(SEQ, 0)) + 1 FROM DATA_TABLE
    )
) AS RESULT_SEQ
FROM DUAL;

위 코드를 해석해보면,
값1 부분에 해당하는 쿼리의 의미는 SEQ 값을 차례대로 채번하고
ROWNUM 값과 SEQ 값이 다른 값 중 가장 작은 값 하나만을 추출한다.
값2 부분에 해당하는 쿼리는 단순히 테이블의 최대 SEQ 값 + 1한 값을 채번하는 쿼리이다.
따라서 값1 부분에 해당하는 중간에 비어있는 SEQ 값이 있다면 이를 출력하고,

없으면 값2 부분에 해당하는 MAX+1값을 출력한다.

 

위 두 가지 경우의 결과값은 아래와 같이 동일하다.

 

 

[reference]

https://blog.naver.com/dkdnblack/220262986031
https://blog.naver.com/smrtalex/70135278496