[DB/Oracle] 무작위 20자리 문자열 키값 만들기 (년월일시분초 + 랜덤 6자리)

업무에서 테이블의 키값으로 자주 쓰는 컬럼을 만들때, yyyyMMddHH24MISS + 랜덤 6자리 로 20 짜리 키값을 만든다.

 

다음은 오라클 쿼리이다.

SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') || ( 
	SELECT LISTAGG( 
    				CHR( 
                                        CASE 
                                            WHEN rn BETWEEN 1 AND 26 THEN 65 + rn - 1 -- 'A'~'Z' 
                                            ELSE 48 + (rn - 27) -- '0'~'9' 
                                        END 
                       		 ), '' ) WITHIN GROUP (ORDER BY pos) -- 생성 순서대로 
     FROM ( 
     		SELECT TRUNC(DBMS_RANDOM.VALUE(0,36)) + 1 AS rn, 
            	   LEVEL AS pos 
              FROM DUAL 
           CONNECT BY LEVEL <= 6 
          ) 
	) AS KEY20 
 FROM DUAL;

 

LISTAGG는 여러 행에 흩어져 있는 문자열 값을 “한 줄 문자열”로 모아 붙이는(집계) 함수이다.
형식은 LISTAGG(표현식 [, 구분자]) WITHIN GROUP (ORDER BY 정렬기준) 이다.

이 쿼리에서 LISTAGG가 하는 일은 아래와 같다.

1. 서브쿼리

SELECT TRUNC(DBMS_RANDOM.VALUE(0,36)) + 1 AS rn
	 , LEVEL AS pos 
  FROM DUAL 
CONNECT BY LEVEL <= 6

• 6개의 행을 만든다. 각 행은 rn(1~36 사이 난수), pos(1~6)가 있음.

2. 문자로 변환

CHR( 
CASE WHEN rn BETWEEN 1 AND 26 THEN 65 + rn - 1 -- 'A'~'Z' 
ELSE 48 + (rn - 27) -- '0'~'9' 
END )

• 각 행의 rn을 영대문자/숫자 1글자로 바꾼다.

3. LISTAGG(..., '') WITHIN GROUP (ORDER BY pos)

• 6개 행의 한 글자씩을 구분자 없이(''), pos 순서대로 이어 붙여 길이 6의 문자열을 만든다.
• 예: 행별 문자 ['K','Q','1','B','9','A'] → KQ1B9A

 

4. 최종적으로 바깥쪽 TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')(14자리)와 이어 붙여 20자리 문자열을 만든다.

 

 

※ 정리

• LISTAGG는 결과가 너무 길면(SQL 컨텍스트에서 VARCHAR2 4000바이트 초과) ORA-01489가 날 수 있다.
  이 쿼리는 6자라 문제 없다.
• 정렬을 바꾸면 붙는 순서가 바뀐다. 여기서는 pos로 생성 순서를 명시했다.
• 구분자를 넣고 싶다면 두 번째 인자에 ',' 같은 값을 줄 수 있다.