Oracle의 DBMS_RANDOM 패키지 활용 방법

다양한 이유로 프로그램을 작성할 때, RANDOM 데이터가 필요한 상황이 발생합니다. 이를 효과적으로 처리할 수 있도록, 일반적인 프로그래밍 언어에서는 RANDOM 데이터를 효과적으로 생성하는 방법을 제공하고 있습니다. 데이터베이스를 관리하는 DBMS에서도 이런 RANDOM 데이터를 생성하는 도구를 제공하고 있으며, Oracle RDBMS는 버전 8 이상부터 DBMS_RANDOM Package를 활용해 RANDOM 데이터를 생성할 수 있습니다.

이 글에서는 Oracle DBMS_RANDOM Package에 대해 간단히 알아보고, Oracle RDBMS에서 RANDOM 데이터를 생성하는 다양한 방법을 소개하겠습니다. 마지막으로 간단한 퀴즈를 통해, 실제 활용하는 예제를 살펴보겠습니다.

About Oracle DBMS_RANDOM Package

Oracle RDBMS의 DBMS_RANDOM Package는 임의의 숫자나 문자, 또는 숫자와 문자의 조합 등을 생성할 수 있습니다. 이 기능은 다양한 곳에 응용하여 활용할 수 있습니다. 가장 대표적인 예로 임의의 데이터를 생성하여 비밀번호로 활용^[각주:1]하거나 특정 범위 안에 속하는 데이터를 생성하여 테스트 데이터 등으로 활용할 수 있습니다.

Summary of DBMS_RANDOM Subprograms

Subprogram	Description
INITIALIZE Procedure	패키지를 초기화합니다.
NORMAL Function	정규 분포의 임의의 수를 반환합니다.
RANDOM Procedure	임의의 수를 생성합니다.
SEED Procedure	Seed 값을 재설정합니다.
STRING Function	임의의 문자를 생성합니다.
TERMINATE Procedure	종료 패키지입니다.
VALUE Functions	임의의 수를 생성합니다. 0~1 사이의 숫자를 생성하며, 소수점 38자리 정밀도를 가지는 임의의 수를 생성합니다.

INITIALIZE Procedure

이 프로시저는 생성자를 초기화합니다. 이 프로시저는 현재 정상적으로 동작하지만, 공식적으로 Oracle RDBMS 11gR1부터 지원이 종료되었습니다. 그러므로 앞으로는 사용하지 못할 수도 있습니다.

이 프로시저는 간단히 SEED Procedure를 호출하는 것으로 대체할 수 있습니다.

NORMAL Function

이 함수는 표준 정규 분포^[각주:2] 범위의 임의의 숫자를 반환합니다.

SELECT DBMS_RAMDOM.NORMAL FROM DUAL;

RANDOM Procedure

이 프로시저는 임의의 수를 생성합니다. 이 프로시저의 반환 값은 -2³¹ ~ 2³¹입니다. 이 프로시저는 현재 정상적으로 동작하지만, 공식적으로 Oracle RDBMS 11gR1부터 지원이 종료되었습니다. 그러므로 앞으로는 사용하지 못할 수도 있습니다.

SELECT DBMS_RANDOM.RANDOM FROM DUAL;

SEED Procedure

이 프로시저는 시드(SEED) 값을 리셋합니다. 이 예제는 다음 링크의 글을 참조했습니다.

SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.put_line('Run 1 : seed=0');
  DBMS_RANDOM.seed (val => 0);
  FOR i IN 1 ..5 LOOP
    DBMS_OUTPUT.put_line('i=' || i || 
                         ' : value=' || DBMS_RANDOM.value(low => 1, high => 10));
  END LOOP;

  DBMS_OUTPUT.put_line('Run 2 : seed=0');
  DBMS_RANDOM.seed (val => 0);
  FOR i IN 1 ..5 LOOP
    DBMS_OUTPUT.put_line('i=' || i || 
                         ' : value=' || DBMS_RANDOM.value(low => 1, high => 10));
  END LOOP;

END;
/

STRING Function

이 함수는 임의의 문자를 생성합니다. 이 함수는 두 개의 매개변수를 가지는데, 첫 번째 매개변수는 Option 값을 의미하고, 두 번째 매개변수는 반환될 문자열의 길이를 의미합니다.

이 함수에서 사용할 수 있는 옵션 값은 다음과 같으며, 대소문자를 구분하지 않습니다. 이 옵션 값 이외의 문자를 입력하면, 대문자로 구성된 알파벳 문자열을 반환합니다. 이 옵션에서 아쉬운 점은 대/소문자로 혼합된 알파벳과 숫자 등의 조합으로 구성된 문자열을 생성하지 못하는 것입니다. 개인적으로는 빈번히 활용하는 옵션으로 생각하는데, 제공하지 않아 아쉽습니다.

'U' - 대문자로 구성된 알파벳 문자열을 반환
'L' - 소문자로 구성된 알파벳 문자열을 반환
'A' - 대/소문자 혼합된 알파벳 문자열을 반환
'X' - 알파벳 대문자와 숫자가 혼합된 문자열을 반환
'P' - 인쇄 가능한 문자로 구성된 문자열을 반환

대문자 20자리 임의의 문자열을 생성하는 질의 예제입니다.

SELECT DBMS_RANDOM.STRING('U', 20) FROM DUAL;

소문자 20자리 임의의 문자열을 생성하는 질의 예제입니다.

SELECT DBMS_RANDOM.STRING('L', 20) FROM DUAL;

대소문자가 혼합된 20자리 임의의 문자열을 생성하는 질의 예제입니다.

SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL;

대문자와 숫자가 혼합된 20자리 임의의 문자열을 생성하는 질의 예제입니다.

SELECT DBMS_RANDOM.STRING('X', 20) FROM DUAL;

인쇄 가능한 문자가 혼합된 20자리 임의의 문자열을 생성하는 질의 예제입니다.

SELECT DBMS_RANDOM.STRING('P', 20) FROM DUAL;

TERMINATE Procedure

DBMS_RANDOM Package를 종료할 때, 이 프로시저를 호출합니다. 이 프로시저는 현재 정상적으로 동작하지만, 공식적으로 Oracle RDBMS 11gR1부터 지원이 종료되었습니다. 그러므로 앞으로는 사용하지 못할 수도 있습니다.

VALUE Functions

이 기본 함수는 0~1 사이의 임의의 숫자를 생성합니다. 이때, 생성한 숫자는 소수점 38자리 정밀도를 가집니다. 이 함수는 두 가지 구문 형식을 가지며, 예제를 통해 살펴보겠습니다.

일반적으로 활용하는 질의 예제입니다. 0~1 사이의 임의의 숫자를 생성합니다.

SELECT DBMS_RANDOM.VALUE FROM DUAL;

다음은 생성하는 숫자의 범위를 지정하는 예제입니다. 1~100 사이의 임의의 숫자를 생성합니다.

SELECT DBMS_RANDOM.VALUE(1, 100) FROM DUAL;

지금까지 Oracle RDBMS에서 제공하는 DBMS_RANDOM Package에 대해 간단하게 살펴봤습니다. Oracle DBMS_RANDOM Package에 대해 더 자세한 사항은 Oracle Document를 참조하시기 바랍니다.

Quiz Ⅰ

[문제] 대소문자와 숫자가 결합된 20자리 임의의 문자열을 생성하시오.

이 문제의 모범 답안은 다음과 같습니다.

SELECT LISTAGG(V,'') WITHIN GROUP (ORDER BY RN)
  FROM (SELECT ROWNUM RN
             , DBMS_RANDOM.STRING(DECODE(TRUNC(DBMS_RANDOM.VALUE(1,3)),1,'A','X'),1) V
          FROM DUAL CONNECT BY LEVEL <= 20);

비록 간단한 질의이긴 하지만, 불편한 점이 있는 것도 사실입니다. 개인적으로는 DBMS_RANDOM.STRING() 함수에서 옵션을 제공했으면 하는 바람입니다.

Quiz Ⅱ

[문제] 다음 요구사항을 만족하는 데이터를 생성하시오.

요구사항은 다음과 같습니다. 먼저 A1~A10 지역(REG)이 있습니다. 각 지역별로 최대 5개의 센서가 있으며, 센서 목록은 CO₂(S01), 온도(S02), 습도(S03), 소음(S04), 조도(S05)입니다. 단, 조도 센서는 A01~A05 지역에만 있습니다.

센서별 생성 값의 범위는 다음과 같습니다. S01 센서는 450~2,000PPM이며, S02 센서는 20~34도, S03 센서는 27~61%, S04 센서는 300~1500Lx, 마지막으로 S05 센서는 38~55dB입니다. 생성할 데이터는 2014년 09월 01일부터 2014년 09월 30일까지의 데이터이며, 시간 당 수집되는 센서 데이터는 각 센서별로 약 30회 수집합니다.