Oracleデータベースで重複のないランダムなテストデータを生成する方法

Oracleデータベースで重複のないランダムなテストデータを生成する方法 Database

Oracleデータベースのテーブルに重複のないランダムな数値や文字列や日付データを作成したい場合があります。例えば重複しないユーザー名を大量に生成したい場合等です。重複データを生成してしまわないようにするためにはどうすれば良いのかを解説したいと思います。

この記事では、ランダムな数値、ランダムな文字列、ランダムな日付のそれぞれの生成方法を解説すると共に、それぞれの値が重複しないようにする方法も解説しています。テストデータを生成する場合、同じデータが生成されてしまっては困る状況で大変重宝します。

テスト用のテーブルを作成

まず最初に重複のないテストデータを大量に生成するためのテーブルを作成します。以下のようにsample_tableというテーブルを作成します。

CREATE TABLE sample_table (
    user_id NUMBER,
    user_name VARCHAR2(50),
    created_date DATE
);

以降の説明では、このテーブルに対して重複のないデータを登録していく方法を記載していきます。

重複のないランダムな数値データの生成方法

ランダムな数値データを生成するには「DBMS_RANDOM.VALUE」を使います。ここでは引数に「(1, 100)」と指定していますので、1から100までの数値をランダムに生成するという意味になります。

SQL> SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.ENABLE;
  FOR cur_rec IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('Random value : ' || DBMS_RANDOM.VALUE(1, 100));
  END LOOP;
END;
/

Random value : 3.63845685950893770667962630034833550162
Random value : 28.78131094227491195418755312450760325651
Random value : 97.90953761529813090428234455986391955383
Random value : 99.68004534108299196181048089269723327962
Random value : 37.92453091522948575422231213092821843016
Random value : 38.65340969536991294687688533870424446194
Random value : 5.53996197485758663372723984520785722406
Random value : 19.64638924323251342445133204714439950513
Random value : 9.80798224148568751892029162197320213839
Random value : 53.17023725185013537538117990955681592317

PL/SQL procedure successfully completed.

しかしこのままだと小数点以下の値が含まれていますので、整数のみ必要な場合には次のようにTRUNC関数を組み合わせると1から100までのランダムな整数のみを生成することが出来ます。

SQL> SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.ENABLE;
  FOR cur_rec IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('Random value : ' || TRUNC(DBMS_RANDOM.VALUE(1, 100)));
  END LOOP;
END;
/

Random value : 82
Random value : 25
Random value : 83
Random value : 74
Random value : 22
Random value : 14
Random value : 52
Random value : 47
Random value : 39
Random value : 41

PL/SQL procedure successfully completed.

しかし何度か実行していると同じ値が出力されてしまい、以下のように重複した値が生成されてしまいます。ここでは79という数字が重複してしまっています。

SQL> SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.ENABLE;
  FOR cur_rec IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('Random value : ' || TRUNC(DBMS_RANDOM.VALUE(1, 100)));
  END LOOP;
END;
/

Random value : 75
Random value : 79
Random value : 79
Random value : 29
Random value : 12
Random value : 71
Random value : 92
Random value : 65
Random value : 87
Random value : 27

PL/SQL procedure successfully completed.

重複のない数値データを生成してテーブルにデータをINSERTするためには、次のように数値列に一意制約(UNIQUE)を設定しておきます。先に作成していたsample_tableの数値列であるuser_id列に一意制約を設定します。こうすることで重複したデータが登録できないようになります。

ALTER TABLE sample_table ADD CONSTRAINT sample_table_user_id_uk UNIQUE (user_id);

そして以下のPL/SQLブロックを実行してみます。ここでは1から20,000までのランダムな数値を生成し、10,000件のデータを登録するようにしてあります。必要な件数に応じて値を変えてみて下さい。

DECLARE
    num_of_inserted NUMBER := 0;
BEGIN
    LOOP
        BEGIN
            -- ランダムな数値データをINSERTしてみる
            INSERT INTO sample_table
                (user_id)
            VALUES
                (TRUNC(DBMS_RANDOM.VALUE(1, 20000)));

            -- 重複のないデータをINSERT出来たら件数をカウントアップする
            num_of_inserted := num_of_inserted + 1;

            -- 一意制約エラーになったら再度やり直す
            EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL;
        END;
        EXIT WHEN num_of_inserted = 10000;
    END LOOP;
    COMMIT;
END;
/

上記PL/SQLブロックでは、まずランダムな数値を生成したデータでINSERTを試みます。もし重複したデータをINSERTしようとすると、DUP_VAL_ON_INDEXという一意索引制約違反エラーが発生します。このエラーが発生した場合にはデータ件数を加算せず、再度ランダムな数値を生成して重複が無くなるまでINSERTを試みます。この仕組みによって重複データを生成しないようにしています。

すると重複のない数値データが10,000件生成されてテーブルにINSERTされていて、重複した数値が存在しないことが分かります。

-- 登録された件数
SQL> SELECT COUNT(*) FROM sample_table;

  COUNT(*)
----------
     10000


-- 重複データチェック
SQL> SELECT user_id, COUNT(*) FROM sample_table GROUP BY user_id HAVING COUNT(*) > 1;

no rows selected

重複のないランダムな文字列データの生成方法

ランダムな文字列データを生成するには「DBMS_RANDOM.STRING」を使います。ここでは引数に「(‘A’, 10)」と指定していますので、アルファベットの大文字・小文字の組み合わせで10文字の文字列をランダムに生成するという意味になります。

SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.ENABLE;
  FOR cur_rec IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('Random value : ' || DBMS_RANDOM.STRING('A', 10));
  END LOOP;
END;
/

Random value : rOIyDBDlvw
Random value : UeCfLfrnZf
Random value : sGutdjszrc
Random value : kXDUACnXUF
Random value : RbjxEsNxFE
Random value : QGWCKUyAyu
Random value : doIyQUgpCg
Random value : kJPJcwyHri
Random value : nJFOeusBVz
Random value : wQLdDiwQmG

PL/SQL procedure successfully completed.

ランダムな文字列の種類を指定したい場合には、「DBMS_RANDOM.STRING」関数の第１引数の値を次のように変更すると文字列の種類を変更することが出来ます。

「U」または「u」アルファベットの大文字
「L」または「l」アルファベットの子文字
「A」または「a」アルファベットの大文字・小文字
「X」または「x」アルファベットの大文字と数字
「P」または「p」印字可能な文字列(記号も含む)

しかしこれらランダムな文字列も重複してしまう可能性があります。そのため、先の数値と同様に文字列の列にも一意制約(UNIQUE)を設定して重複データが登録されないようにしておきます。

ALTER TABLE sample_table ADD CONSTRAINT sample_table_user_name_uk UNIQUE (user_name);

そして以下のPL/SQLブロックを実行してみます。ここではアルファベット大文字だけの組み合わせでランダムな10文字の文字列を生成し、10,000件のデータを登録するようにしてあります。必要な件数に応じて値を変えてみて下さい。

DECLARE
    num_of_inserted NUMBER := 0;
BEGIN
    LOOP
        BEGIN
            -- ランダムな文字列データをINSERTしてみる
            INSERT INTO sample_table
                (user_name)
            VALUES
                (DBMS_RANDOM.STRING('P', 10));

            -- 重複のないデータをINSERT出来たら件数をカウントアップする
            num_of_inserted := num_of_inserted + 1;

            -- 一意制約エラーになったら再度やり直す
            EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL;
        END;
        EXIT WHEN num_of_inserted = 10000;
    END LOOP;
    COMMIT;
END;
/

すると重複のない文字列データが10,000件生成されてテーブルにINSERTされていて、重複した文字列が存在しないことが分かります。

-- 登録された件数
SQL> SELECT COUNT(*) FROM sample_table;

  COUNT(*)
----------
     10000

-- 重複データチェック
SQL> SELECT user_name, COUNT(*) FROM sample_table GROUP BY user_name HAVING COUNT(*) > 1;

no rows selected

重複のないランダムな日付データの生成方法

ランダムな日付データを生成するには現在日付を示す「SYSDATE」と「DBMS_RANDOM.VALUE」を組み合わせて使います。ここではDBMS_RANDOM.VALUEの引数に「(0, 36500)」と指定していますので、本日の日付から36500日先(約100年)までの日付をランダムに生成するという意味になります。さらに時刻データは不要ですので、この例ではTRUNC関数を使って時刻部分を削っていますが、時刻が必要な場合にはTRUNC関数を削除してください。

SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.ENABLE;
  FOR cur_rec IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('Random value : ' || TRUNC(SYSDATE + DBMS_RANDOM.VALUE(0, 365)));
  END LOOP;
END;
/

Random value : 05/05/2025 00:00:00
Random value : 07/18/2025 00:00:00
Random value : 10/18/2025 00:00:00
Random value : 01/29/2025 00:00:00
Random value : 08/28/2025 00:00:00
Random value : 08/16/2025 00:00:00
Random value : 12/13/2025 00:00:00
Random value : 01/27/2025 00:00:00
Random value : 03/29/2025 00:00:00
Random value : 01/15/2025 00:00:00

PL/SQL procedure successfully completed.

しかしこれらランダムな日付も重複してしまう可能性があります。そのため、先の数値と同様に日付の列にも一意制約(UNIQUE)を設定して重複データが登録されないようにしておきます。

CREATE UNIQUE INDEX sample_table_created_date_uk ON sample_table (TRUNC(created_date));

そして以下のPL/SQLブロックを実行してみます。ここでは本日から36500日先までの日付データを生成し、10,000件のデータを登録するようにしてあります。必要な件数に応じて値を変えてみて下さい。

DECLARE
    num_of_inserted NUMBER := 0;
BEGIN
    LOOP
        BEGIN
            -- ランダムな日付データをINSERTしてみる
            INSERT INTO sample_table
                (created_date)
            VALUES
                (TRUNC(SYSDATE + DBMS_RANDOM.VALUE(0, 36500)));

            -- 重複のないデータをINSERT出来たら件数をカウントアップする
            num_of_inserted := num_of_inserted + 1;

            -- 一意制約エラーになったら再度やり直す
            EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL;
        END;
        EXIT WHEN num_of_inserted = 10000;
    END LOOP;
    COMMIT;
END;
/

すると重複のない日付データが10,000件生成されてテーブルにINSERTされていて、重複した日付が存在しないことが分かります。

-- 登録された件数
SQL> SELECT COUNT(*) FROM sample_table;

  COUNT(*)
----------
     10000
 
 
 -- 重複データチェック
SQL> SELECT created_date, COUNT(*) FROM sample_table GROUP BY created_date HAVING COUNT(*) > 1;

no rows selected

タイトルとURLをコピーしました