오라클 이야기 - 일련번호 생성하기에서 놓치기 쉬운 것

1. 시퀀스를 쓰지 않고 일련번호를 만들고 싶다.

오라클은 특정 데이터 타입에 대해서 Sequence 객체를 이용해서 자동으로 일련번호를 생성하여 입력할 수 있도록 지원한다.
물론 이 오라클의 시퀀스는 어떠한 경우에도 중복되지 않도록 설계되어 있다. 더욱이 서로 다른 여러 테이블에서도 공통으로
하나의 시퀀스 객체를 통해서 중복되지 않는 번호를 받아서 사용할 수 있다. 

그런데 오늘 살펴보려고 하는 것은 이 편리한 시퀀스를 사용하지 않고, 특정 테이블 내에서만 제한적으로 일련번호를 사용하는
방법에 대한 것이다. 좀 더 솔직하게 얘길 하자면 방법을 찾아가는 과정을 통해서 초심자가 놓치기 쉬운 부분을 짚어주고자
함이 이 글을 쓰는 궁극적인 목적이다.




2. MAX() 함수 이용하기

우선 설명을 위해서 필요한 예제 테이블을 하나 만들어 보도록 하자.

CREATE TABLE mytable (
    serial      INTEGER        NOT NULL,
    name        VARCHAR2(60)
)
/

제일 먼저 떠올릴 수 있는 것이 MAX() 함수일 것이다. 현재 테이블 내에 있는 일련번호 필드의 값 중에서 제일 큰 값에 1을
더하면 겹치지 않는 일련번호를 구할 수 있다는 논리인 것이다. 시작이 나쁘진 않다. 아마 다음과 같은 형태의 SQL문장이
만들어졌으리라.

SELECT MAX(serial) + 1 FROM mytable;

위 SQL의 결과는 다음 그림과 같다. 그러니까 아무것도 나오지 않는다.








3. 어라, 왜 아무것(NVL)도 안 나오지?

음, "아무것도 나오지 않는다"라, 그럼 NULL인가? 오호라 NVL() 함수를 이용하면 되겠군!

SELECT MAX(NVL(serial, 0)) + 1 FROM mytable;

그러나 여전히 묵묵부답이다. 그럴 수 밖에 NVL() 함수나 MAX() 함수에 대한 오해가 있었으니 이제 그 오해를 풀어보도록 하자.
우선 MAX() 함수는 말 그대로 주어진 값들 중에서 제일 큰 값을 반환해주는 함수이다. 이때 주의해서 봐야할 것이 바로 '주어진'
이라는 것이다. 값이 주어지지 않으면 비교 조차 할 수 없으니 결과가 없는 것은 당연하다.

다음으론 NVL() 함수를 살펴보자. NVL() 함수는 지정한 필드의 값이 NULL일 때 이를 대체할 값을 지정해줘서 결과값에 NULL이
발생하지 않도록 해주는 아주 유용한 함수이다. 이 때 오해하기 쉬운 것을 짚어보자면 지정한 필드라는 것은 우선 레코드가
있을 때 존재하는 것이다. SQL의 결과셋에서 한 줄의 레코드를 읽고 그 레코드를 구성하는 필드들 중에서 지정한 필드라는 것.

그러므로 결과셋 자체가 없으면 NVL도 역시 동작하지 않는다.





4. COUNT() 함수가 필요해.

결과셋이 없으면 아무것도 할 수 없나? 아니다. 결과셋이 없어도 값을 리턴해주는 재밌는 함수가 하나 있다. COUNT() 함수이다.
말 그대로 결과셋의 개수를 세어 몇 개인지 알려주는 함수이므로 결과셋이 없으면 '0'이라고 친절하게 알려준다.

SELECT COUNT(serial) FROM mytable;

이 COUNT() 함수를 잘 이용하면 원하는 결과를 얻을 수 있을 것 같다. 그런데 어떻게 해야할까? 하나의 SQL로 깔끔하게 만들
수는 없을까? 상황에 따라서 결과셋이 없을 땐 초기값에 해당하는 '1'을 반환하고, 결과셋이 있으면 최대값에 '1'을 증가한 값을
반환 하도록 말이다.



5. DECODE(), 오라클의 강력한 함수

이럴 때 사용하라고 만들어 놓은 함수가 바로 DECODE() 함수이다. 거두절미하고 바로 다음의 SQL 문장을 실행해 보도록 하자.

SELECT DECODE(COUNT(serial), 0, 1, MAX(serial) + 1) FROM mytable;

6. 실전에선 역시 시퀀스가...

DECODE() 함수가 매력적으로 보일지도 모르겠다. 하지만 어디까지나 오라클의 대표적인 함수들을 사용함에 있어 초심자가 놓칠
법한 것들을 설명하기 위해서 일련번호 이야기를 꺼낸 것이다. 실전에선 오라클의 시퀀스(Sequence) 객체를 사용하는 것이 여러
모로 유리한 것이 사실이다^[각주:1]. 그러나 예외라는 것이 있고 특수한 상황이라는 것이 또한 있으니 그러한 경우를 위해 알아둬서
해될 것은 없어 보인다.

1. 특히 Concurrency 문제에서 잇점이 있다. [본문으로]