初心者がORACLE,PL/SQLを書く際に役立つTIPS集（SELECT編）

2015.05.12

おはようございます。ユニトラストの櫻井です。

今回は「SQLの基本的なことは学んできたけれど、OracleとPL/SQLは初めて！」、という初心者が悩みがちなポイントとその解決策を説明していきます。

目次

• その１．「SELECT INTO」で例外「NO_DATA_FOUND」の発生を回避したい！
• その２．ORDER BYとROWNUMを組み合わせても目的のレコードが取得できない！
• その３．SELECT結果をまとめて変数に格納したい！

その１．「SELECT INTO」で例外「NO_DATA_FOUND」の発生を回避したい！

検索結果を変数に格納したい場合、下記のような形でSELECT INTOを用います。

SELECT
    POST_ID
INTO
    POST_ID
FROM
    STAFF
WHERE
    STAFF.ID = '00001';

上記SQL文は、STAFFテーブルから社員番号’00001’のPOST_ID（役職ID）を取得し、変数のPOST_IDに格納するものです。この場合、該当するレコードが存在しないと、結果はどうなるでしょうか？

【初心者にありがちな考え】

検索結果で該当するレコードが存在しない場合、POST_IDにはNULLが格納されるんじゃないの？

【実際の処理】

検索結果で該当するレコードが存在しない場合、例外の「NO_DATA_FOUND」が発生します。

上記のSQL文を例にすると、社員番号’00001’のレコードが存在しない場合、例外「NO_DATA_FOUND」が発生します。そのため、「変数POST_IDにNULLが格納され、処理が続行する」ということはありません。

【対応策その１】

CASEとCOUNTを組み合わせることで、SELECT INTOの実行を制御することができます。

SELECT
    COUNT(*)
INTO
    REC_COUNT
FROM
    STAFF
WHERE
    STAFF.ID = '00001';

CASE REC_COUNT
WHEN '0' THEN
    SELECT
        ''
    INTO
        POST_ID
    FROM
        DUAL
ELSE
    SELECT
        POST_ID
    INTO
        POST_ID
    FROM
        STAFF
    WHERE
        STAFF.ID = '00001'
END CASE;

上記のSQL文では、あらかじめ同じ検索条件で一致するレコードが存在するかをCOUNTで確かめます。その後、CASEを利用し、レコードが存在しない場合は空（NULL）を、存在する場合は検索結果を変数に格納します。

【対応策その２】

BEGIN
    SELECT
        POST_ID
    INTO
       POST_ID
    FROM
        STAFF
    WHERE
        STAFF.ID = '00001';
    EXCEPTION
        WHEN OTHERS THEN
        POST_ID ;= '';
END;

上記のSQL文では、新たなPL/SQLブロックを作り、例外「NO_DATA_FOUND」発生に備えます。例外発生後、POST_IDに”が格納され、PL/SQLブロックを抜けて処理が続きます。

その２．ORDER BYとROWNUMを組み合わせても目的のレコードが取得できない！

ORDER BYしソートした結果を変数に格納したい場合、どのようなSQL文を発行すればよいでしょうか？

【初心者にありがちな考え】

SELECT
    PRICE
INTO
    LOWEST_PRICE
FROM
    PRODUCT
WHERE
    ROWNUM = 1
ORDER BY
    PRICE;

上記のSQL文は、PRODUCTテーブルのレコードをPRICEの昇順にソートし、1番目のレコードのPRICEを変数のLOWEST_PRICEに格納することを目的としたものです。しかし…

【実際の処理】

ROWNUMはORDER BYが実行される前に各レコードに設定されます。そのため、上記のSQL文では目的の結果を得ることはできません。

【対応策その１】

副問い合わせとして検索結果をORDER BYし、これにROWNUMをあてはめる、という方法があります。この手法は専門的にはTOP-N分析と言い、OracleのようにLIMIT句が使用できないDBMSではデファクトスタンダードとなっています。

SELECT
    *
INTO
    LOWEST_PRICE
FROM
    (SELECT
        PRICE
    FROM
        PRODUCT
    ORDER BY
        PRICE)
WHERE
    ROWNUM = 1;

ROWNUMは、例えば「ROWNUM < 5」とすることで行番号5未満のレコードを取得することができます（このままだと例外のTOO_MANY_ROWSが発生しますが…）。ただし、行番号5を取得する「ROWNUM = 5」や、行番号5以上を取得する「ROWNUM >= 5」といった条件指定までは行うことができません。

【対応策その２】

ROWNUMの代わりに、Window関数のひとつであるROW_NUMBER関数を用いる方法です。こちらの方法では、「行番号 = 5」、「行番号 >= 5」といった、ROWNUMで行えなかった行番号の指定が可能です。

SELECT
    *
INTO
    ROW_NO
    LOWEST_PRICE
FROM
    (SELECT
        ROW_NUMBER() OVER(ORDER BY PRICE,PRODUCT_ID) RN
        PRICE
    FROM
        PRODUCT)
WHERE
    RN = 1;

ROW_NUMBER関数では、上記のように一意に定まるキー（ここではPRODUCT_KEYを主キーと仮定）でソートする必要があります。余談ですが、PARTITION BY句を用いることで、サブクエリの結果をパーティション分割（グループ化）することもできます。こちらを利用すれば、各グループの行番号を指定するなど、更に検索条件の幅が広がります。

その３．SELECT結果をまとめて変数に格納したい！

1つの検索結果を1つの変数に格納するためには、「その１」で示したように、「SELECT INTO」を用いることになります。では、複数の検索結果をVARRAY型やTABLE型（コレクション型）の配列に格納する場合には何を使用すれば良いでしょうか？

【初心者にありがちな考え】

SELECT INTOを用いれば良いのでは？

【実際の処理】

SELECT INTOで複数の検索結果を取得し、値に格納しようとすると、例外「TOO_MANY_ROWS」が発生します。そのため、検索結果をまとめて変数に格納したい場合には、「BULK COLLECT INTO」を使用します。

【対応策】

SELECT
    POST_ID
BULK COLLECT INTO
    POST_ID_LIST
FROM
    STAFF;

上記SQL文は、STAFFテーブルの各レコードのPOST_IDをPOST_ID_LIST（ここでは仮にTABLE OF VARCHAR2(2)とする）に格納する、というものです。

【おまけ】

BULK COLLECT INTO文は、動的SQL（EXECUTE IMMEDIATE）と組み合わせて使用することも可能です。

・検索結果が単一の場合

V_SQL := 'SELECT POST_ID FROM STAFF WHERE STAFF.ID = ' || q'<'00001'>';
EXECUTE IMMEDIATE V_SQL INTO POST_ID_LIST;

・検索結果が複数の場合

V_SQL := 'SELECT POST_ID FROM STAFF';
EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO POST_ID_LIST;

また、ついつい見逃しがちなケアレスミスですが、動的SQLで実行するSQL文字列の末尾に;（セミコロン）は不要です。セミコロンが入っていた場合、動的SQLの発行に失敗します。

以上、初心者がORACLE,PL/SQLを書く際に役立つTIPS集（SELECT編）でした。