PAD + MOD

오늘은 Oracle의 여러가지 키워드에 대해서 알아보려고 한다.

 

 

최근 유지보수 개발 요청이 들어와서 코드를 뜯어보던 중 전반적인 아키텍쳐가 이상한 것을 깨닫고 전체 구조를 뜯어보는데 몇날며칠을 썼다. 그리고 저번주에 그 근본적인 원인을 찾았는데 그 원인은 바로 테이블 구조였다.

 

우선 PK가 이상하게 설정되어 있었다.

PK는 Primary Key의 준말로 우리말로는 기본키 라고 불린다.

기본키는 그 조합이 유일해야한다.

그런데 이 테이블은 기본키의 조합이 유일하지 않게 설계되어 있었다.

그래서 나는 유일한 값을 만들기 위해 일련번호 컬럼을 하나 추가하게 되었다.

 

나는 일련번호의 타입을 VARCHAR2(15)로 설정했는데

날짜(YYYYMMDD, 8) + 구분코드(_, 1) + 브랜치(지점)코드(___, 3) + 숫자,일련번호(___, 3)

이렇게 15자를 구성할 계획이었다.

여기서 부딪힌 벽은 마지막 일련번호였다.

오라클에서 일련번호를 어떻게 설정하지?

 

다른 테이블에서는 일련번호를 어떻게 만들어내고 있는지를 살펴보니 LPAD와 MOD라는 키워드를 쓰고 있었다.

이 키워드를 이번에 처음 보고 공부하게 되어 이 글을 보는 독자분들께도 소개시켜드리고자 가져왔다.

 

 

 

Lpad는 Left + Pad, 문자열의 왼쪽을 지정된 길이까지 특정 문자 세트로 채우는 표현식이다.

이 키워드는 lpad(Dimension, length, "characters to pad") 이렇게 쓰는데, 아래 예시를 보며 살펴보자.

 

 

Case #1

SELECT LPAD('hellomyfriend', 15, '*')
FROM DUAL

 

위와 같은 표현식이 있다고 가정해보자. 이 표현식을 실행해보겠다.

컴파일러 실행 결과

보면 hellomyfriend 왼쪽에 *이 2개 붙었다.

LPAD를 통해 hellomyfriend 13자 왼쪽에 padding characters '*'를 2개 추가하여 15자를 만든 것이다.

 

Case #2

SELECT LPAD('hellomyfriend', 10, '*')
FROM DUAL

 

이번엔 Dimension보다 length가 더 짧다. 이런 경우는 어떻게 나올까?

컴파일러 실행 결과

이번에는 오른쪽 끝에 end가 줄었다.

LPAD를 쓸 때 표현식보다 짧은 길이를 지정할 경우, 왼쪽을 기준으로 해당 길이만큼 자른다.

 

 

LPAD는 왼쪽에 패딩을 채우는 표현식이라면, RPAD는 Right + Pad, 오른쪽에 패딩을 채우는 표현식이다.

위 케이스의 반대라고 생각하면 된다!

 

 

 

 

다음은 MOD이다.

Mod는 나누기 결과 나머지를 반환하는 수학 함수이다.

이 함수를 사용하는 법과 간단한 사용 예시를 알아보자.

 

 

이 함수는 2개의 매개변수를 사용한다. Mod(arg1, arg2) 는 arg1 % arg2의 값을 리턴한다고 볼 수 있다.

예시를 보면서 이 함수를 익혀보자!

 

 

Case #1

SELECT MOD(1234,1000)
FROM DUAL;

 

위 쿼리를 실행시켜보자.

컴파일러 실행 결과

 

1234 라는 숫자를 1000으로 나눈 나머지를 리턴하는 것을 볼 수 있다.

 

 

Case #2

SELECT MOD('1234', 1000)
FROM DUAL;

 

만약 위와 같이 문자열을 arg1에 두면 어떻게 될까?

컴파일러 실행 결과

 

첫 번째 매개변수를 문자열로 설정했음에도 Case #1과 동일한 결과가 도출되는 것을 알 수 있다.

그런데 이건 '1234'가 형식상으로는 문자열처럼 보이지만 내용은 숫자라 가능한 결과 아닐까?

다음 예시를 통해 더 알아보자.

 

 

Case #3

SELECT MOD('ABCD', 1000)
FROM DUAL;

 

그럼 만약 위처럼 첫 번째 매개변수를 알파벳 문자열로 설정하면 어떻게 될까?

컴파일러 실행 결과

 

이번에는 A가 문자열이기 때문에 숫자로 바꿀 수 없다는 에러가 뜬다.

즉, MOD 함수의 인수는 숫자만 입력 가능하다.

Oracle 공식 문서

 

 

 

그래서 나는 이 일련번호를 다음과 같이 설정했다.

TO_CHAR(SYSDATE,'YYYYMMDD')
|| #DIS_CD#
|| #BR_CD#
|| LPAD(MOD(ROWNUM, 1000), 3, '0')

 

위 절을 SELECT 인자로 넣어 실행하면 아래와 같이 결과가 도출된다.

컴파일러 실행 결과

 

그런데 INSERT 문 안에 위 절을 넣고 실행하면 ROWNUM에서 에러가 생겼다.

ROWNUM은 SELECT 문에만 둬야한다는 것이다.

 

그래서 최종적으로는 아래와 같이 쿼리를 구성했다.

INSERT TABLE1
    (COL1,
     COL2,
     COL3,
     ...,
     SRNO)
VALUES (#COL1#,
        #COL2#,
        #COL3#,
        ...,
        TO_CHAR(SYSDATE, 'YYYYMMDD')
         || #DIS_CD#
         || #BR_CD#
         || LPAD(MOD(NVL(MAX(SRNO),0),1000)+1, 3, '0');

 

이렇게 쿼리를 구성했더니 정상적으로 일련번호가 채번되었다.

일련번호(PK)를 채번하는 방법은 여러가지가 있겠지만 이 방법도 고려해보면 좋을 것 같다.

(물론 SYSDATE, DIS_CD, BR_CD가 겹칠 일이 1000번 이상 발생하지 않는다는 전제하에 이 방법을 고려해보길 바란다.)

 

 

---

 

오늘 게시글에는 쓰고 싶은 내용이 많아 좀 길어졌다.

지금 맡고 있는 다른 시스템에서는 시퀀스(Sequence)를 쓰고 있어 이렇게 어렵게 일련번호를 채번할 필요가 없는데,

현재 작업 중인 이 시스템은 시퀀스를 쓰고 싶지 않아 직접 일련번호를 하나하나 + 1 해가며 갱신해줘야하는 문제가 있다.

이 복잡한 문제를 한번에 해결해줄 시퀀스는 다음에 공부해서 가져오겠다!