【PL/SQL】Oracle PL/SQL入門【第８回ＳＥＬＥＣＴ文】

2021年2月4日2021年2月5日

1 PL/SQL入門　SELECT文
2 いきなりですが、ダメな例です
3 STEP1. まずはカーソル定義
4 STEP2. カーソルを開いて閉じて
5 STEP3. 値を取り出してみよう
6 STEP4. 複数行の値を取り出してみよう
7 STEP5. 複数行の値を取り出してみよう PART弐
8 STEP6. エラー時のカーソル処理

PL/SQL入門　SELECT文

前回まで、通常のSQLではできない「ループ」や「処理分岐」をコーディングしてきました。
でもやっぱりデータベースを扱っているんだから、データを参照できないとね
と言う内容の第８回目です

いきなりですが、ダメな例です

PL/SQLでの「SELECT」実行として複数の方法があります。
その中でも、これはあかんと言う方法があります。
下記がその例です。

CREATE OR REPLACE PROCEDURE TEST8_1 IS
    VNAME VARCHAR2(200);
BEGIN
    SELECT NAME INTO VNAME FROM TBL_TEST WHERE (ID = 1);
END;
/
show errors

Javaや.NetからSELECTを実行するより簡単に見えます。
ソースの意味としては

・「VNAME」と言う変数を宣言します
・SELECT文で＜項目 INTO 変数＞と記述するとSELECTした値が変数に入ります

この方法の何が問題かと言うと、SELECT結果が複数行だった場合にエラーとなります。
試しにWHERE句を削除して実行してみて下さい。エラーが発生します。
テスト時は複数行返ってこないのでエラーとならなかった場合でも、本番時に複数行返ってきて大目玉みたいな事になりかねないのでこの方法は使わない様にしましょう。

STEP1. まずはカーソル定義

ダメな例から見てもらいましたが、通常の方法としてまずは「カーソル定義」を行います。
下記がその例です。

CREATE OR REPLACE PROCEDURE TEST8_2 IS
    CURSOR csrTBL_TEST IS
        SELECT NAME
          FROM TBL_TEST
         WHERE (ID = 1);
BEGIN
    NULL;
END;
/
show errors

宣言部に、「CURSOR」を使用しカーソル定義を行います。
形式としては
CURSOR カーソル名 IS SELECT文
です。
例ではSELECT文に改行を入れて複数行にしていますが、１行で記述しても問題ありません。

STEP2. カーソルを開いて閉じて

次に、宣言したカーソルをオープンしてクローズしてみます。
ソースはSTEP1の例に追記して下さい。

CREATE OR REPLACE PROCEDURE TEST8_2 IS
    CURSOR csrTBL_TEST IS
        SELECT NAME
          FROM TBL_TEST
         WHERE (ID = 1);
BEGIN
    OPEN csrTBL_TEST;
    CLOSE csrTBL_TEST;
END;
/
show errors

OPEN と CLOSE の後ろにカーソル名を記述（間には半角スペース）するとオープンしたりクローズできたりします。
使用時には必ず対になる様にしましょう。開いたら閉じる。これ必須です。
CLOSEを忘れてOPENしっぱなしになったり、OPENしていないのにCLOSEしたり（この場合はエラーとなります）気を付けましょう。

STEP3. 値を取り出してみよう

SELECT結果を取得してみます。
まずは変数宣言です。
ソースはSTEP2の例に追記して下さい。

CREATE OR REPLACE PROCEDURE TEST8_2 IS
    VNAME VARCHAR2(200);
 
    CURSOR csrTBL_TEST IS
        SELECT NAME
          FROM TBL_TEST
         WHERE (ID = 1);
BEGIN
    OPEN csrTBL_TEST;
    FETCH csrTBL_TEST INTO VNAME;
    CLOSE csrTBL_TEST;
END;
/
show errors

SELECT結果を取得するには、カーソルをOPENした後に「FETCH」を行います。
形式としては
FETCH カーソル名 INTO 変数名;
です。
これで１レコードをターゲットとした値を取得できます。
FETCHはカーソルをOPENしてから実行して下さい。
カーソルをOPENしていないとエラーとなるので気を付けて下さい。

STEP4. 複数行の値を取り出してみよう

FETCHでは１行のSELECT結果を変数に設定します。
SELECT結果全体が複数行でも、１行の結果を設定します。複数行取得するにはどうしたら良いでしょうか？
そうです。FETCHを複数回実行すれば良いのです。
ここから複数行必要となるので、WHERE句の条件を(ID >= 1)としています。
ソースはSTEP3の例に追記して下さい。

CREATE OR REPLACE PROCEDURE TEST8_2 IS
    VNAME VARCHAR2(200);
 
    CURSOR csrTBL_TEST IS
        SELECT NAME
          FROM TBL_TEST
         WHERE (ID >= 1);
BEGIN
    OPEN csrTBL_TEST;
    FETCH csrTBL_TEST INTO VNAME;
    FETCH csrTBL_TEST INTO VNAME;
    FETCH csrTBL_TEST INTO VNAME;
    FETCH csrTBL_TEST INTO VNAME;
    CLOSE csrTBL_TEST;
END;
/
show errors

この例では、
１度目のFETCHで、１レコード目の項目「NAME」の値を変数「VNAME」に設定しています。
２度目のFETCHで、２レコード目の項目「NAME」の値を変数「VNAME」に設定しています。
３度目のFETCHで、３レコード目の項目「NAME」の値を変数「VNAME」に設定しています。
４度目のFETCHで、４レコード目の項目「NAME」の値を変数「VNAME」に設定しています。
VNAMEの値はFETCHの都度、上書きされます。

この結果を見て、気が付いたかもしれないですが、
FETCH命令ではカーソルの参照レコード位置を次のレコードに進めた後に、該当レコードの値を変数に取り出します。

(。´・ω・)ん? FETCHをして該当レコードが無かった場合はどうなるのか？変数に取り出せない？？？？？
該当有り無しの判定に、カーソル属性と言うものがあります。
FETCH命令が実行される都度、カーソル属性も変更されます。ここでは２つ覚えましょう。
１．csrTBL_TEST%NOTFOUND
２．csrTBL_TEST%FOUND
※csrTBL_TESTは任意のカーソル名です

１は、FETCHした結果、該当レコードが無かった場合にTRUEが返ります
２は、FETCHした結果、該当レコードが有った場合にTRUEが返ります

例として、該当レコードが有った場合のみ、次のFETCHをする様にしてみます

CREATE OR REPLACE PROCEDURE TEST8_2 IS
    VNAME VARCHAR2(200);
 
    CURSOR csrTBL_TEST IS
        SELECT NAME
          FROM TBL_TEST
         WHERE (ID >= 1);
BEGIN
    OPEN csrTBL_TEST;
    FETCH csrTBL_TEST INTO VNAME;
    IF csrTBL_TEST%FOUND THEN
        FETCH csrTBL_TEST INTO VNAME;
 
        IF csrTBL_TEST%FOUND THEN
            FETCH csrTBL_TEST INTO VNAME;
 
            IF csrTBL_TEST%FOUND THEN
                FETCH csrTBL_TEST INTO VNAME;
            END IF;
        END IF;
    END IF;
    CLOSE csrTBL_TEST;
END;
/
show errors

該当レコードが有った場合に、次々FETCHして行きますが、１００レコードとかあったら死にますねw
PL/SQLにはカーソルをOPEN FETCH CLOSE で複数行取得する暗黙テンプレートみたいな記述があります
が次のSTEPです

STEP5. 複数行の値を取り出してみよう PART弐

いきなりですが、お決まりの記述方法を見てもらいます。

CREATE OR REPLACE PROCEDURE TEST8_2 IS
    VNAME VARCHAR2(200);
 
    CURSOR csrTBL_TEST IS
        SELECT NAME
          FROM TBL_TEST
         WHERE (ID >= 1);
BEGIN
    OPEN csrTBL_TEST;
    LOOP
        FETCH csrTBL_TEST INTO VNAME;
        EXIT WHEN csrTBL_TEST%NOTFOUND;
    END LOOP;
    CLOSE csrTBL_TEST;
END;
/
show errors

かなりすっきりしました。処理結果としてはSTEP4と同じです。
それではソースの説明です。
「LOOP ～ END LOOP;」で無限ループとなります
無限ループだと問題があるので、「EXIT WHEN 判定」を記述します。判定部分がTRUEになる時にLOOP処理から離脱します。
csrTBL_TEST%NOTFOUNDは、FETCHの結果、該当レコードが無い場合にTRUEになるのでした。
この形が基本系で、FETCHの位置やLOOPを抜ける位置は自由です。LOOPを抜ける判定も複数回行っても問題ありません。
例えば、
IF csrTBL_TEST%NOTFOUND THEN
EXIT;
END IF;
でもLOOPを抜けられます。
ここまで、値の取得を見てきました。
次が最後のSTEPとなります。カーソルを使用時のエラー処理についてです。

STEP6. エラー時のカーソル処理

ここでカーソル属性をもう１つ覚えましょう。
csrTBL_TEST%ISOPEN
です。
カーソルがOPENされている場合にTRUEとなります。
それではカーソルのエラー処理を見てみます。

CREATE OR REPLACE PROCEDURE TEST8_2 IS
    VNAME VARCHAR2(200);
 
    CURSOR csrTBL_TEST IS
        SELECT NAME
          FROM TBL_TEST
         WHERE (ID >= 1);
BEGIN
    OPEN csrTBL_TEST;
    LOOP
        FETCH csrTBL_TEST INTO VNAME;
        EXIT WHEN csrTBL_TEST%NOTFOUND;
    END LOOP;
    CLOSE csrTBL_TEST;
EXCEPTION
    WHEN OTHERS THEN
        IF csrTBL_TEST%ISOPEN THEN
            CLOSE csrTBL_TEST;
        END IF;

        RAISE;
END;
/
show errors

EXCEPTION以下がエラー処理です。
IF文でカーソルに対してISOPENで判定をしています。どうしてISOPEN判定を行うかと言うと
OPENしていないカーソルに対してCLOSEを実行するとエラーが発生する為です。
このソースではOPENより上に処理が無いですが、OPENする前にエラーが発生した場合を考慮してISOPEN判定をしています。

ここまで記述すると、カーソルを使用するテンプレートソースとして使用できます！
お疲れ様でした。
次回は、OPEN ～ FETCH ～ CLOSE を使用しないSELECT文の方法を紹介する予定です。

【PL/SQL】メニュー

サイトマップ