【PL/SQL】静的SQLと動的SQLの概要

2020年7月4日2020年9月3日

C#やJava等でSQLを記述している時は意識する事はないと思いますが、PL/SQLをコーディングする上で理解しておかないといけない事に「静的SQL」と「動的SQL」があります。
「静的SQL」と「動的SQL」のSELECT文を例に説明して行きたいと思います。

静的SQL

下記が静的SQLの例となります。カーソルを定義しOPEN～FETCH～CLOSEでデータを取得しています。

DECLARE
    CURSOR csrTEST IS
        SELECT *
          FROM TBL
         WHERE (AAA = 1) AND (BBB = 'B');
    DB_TEST csrTEST%ROWTYPE;
BEGIN
    OPEN csrTEST;
    LOOP
        FETCH csrTEST INTO DB_TEST;
        EXIT WHEN csrTEST%NOTFOUND;
    END LOOP;
    CLOSE csrTEST;
EXCEPTION
    WHEN OHTERS THEN
        IF csrTEST%ISOPEN THEN
            CLOSE csrTEST;
        END IF;
END

PL/SQLをコーディングして、しばらく経つと、１つの疑問がでてきます。
処理の分岐によって、SELECTの条件が変わる時にどうすれば良いのか？
例えば、WHERE句に「CCC = 2」が必要になる場合、２本のSELECT文を記述する？
３つ、４つ・・・・と条件が増えて行ったら・・・？
下記は力押しでコーディングした例です。「TEST1」と「TEST2」のカーソルを分岐して処理しています。

DECLARE
    CURSOR csrTEST1 IS
        SELECT *
          FROM TBL
         WHERE (AAA = 1) AND (BBB = 'B');
 
    CURSOR csrTEST2 IS
        SELECT *
          FROM TBL
         WHERE (AAA = 1) AND (BBB = 'B') AND (CCC = 2);        
    DB_TEST csrTEST%ROWTYPE;
 
    SFLG BOOLEAN := FALSE;
BEGIN
    IF SFLG THEN
        OPEN csrTEST1;
        LOOP
            FETCH csrTEST1 INTO DB_TEST;
            EXIT WHEN csrTEST1%NOTFOUND;
        END LOOP;
        CLOSE csrTEST1;
    ELSE
        OPEN csrTEST2;
        LOOP
            FETCH csrTEST2 INTO DB_TEST;
            EXIT WHEN csrTEST2%NOTFOUND;
        END LOOP;
        CLOSE csrTEST2;
    END IF;
EXCEPTION
    WHEN OHTERS THEN
        IF csrTEST1%ISOPEN THEN
            CLOSE csrTEST1;
        END IF;
 
        IF csrTEST2%ISOPEN THEN
            CLOSE csrTEST2;
        END IF;
END

結論として、この方法だと条件が増えて行くと死にますw
そこで、「動的SQL」の出番となります。

動的SQL

下記が動的SQLの例となります。SELECT文を文字列として記述しています。
C#やJava等でコーディングした場合はこの形ではないでしょうか。

DECLARE
    sql_stmt VARCHAR2(32767);
    TYPE typ_TEST IS REF CURSOR;
    csrTEST typ_TEST;
    DB_TEST TEST%ROWTYPE;
    SFLG BOOLEAN := FALSE;
BEGIN
    --共通部分
    sql_stmt := 'SELECT * ' ||
                  'FROM TBL ' ||
                 'WHERE (AAA = 1) AND (BBB = ''B'')';
 
    --WHERE句付加
    IF NOT SFLG THEN
        sql_stmt := sql_stmt || ' AND (CCC = 2)';
    END IF;
 
    OPEN csrTEST FOR sql_stmt;
    LOOP
        FETCH csrTEST INTO DB_TEST;
        EXIT WHEN csrTEST%NOTFOUND;
    END LOOP;
    CLOSE csrTEST;
EXCEPTION
    WHEN OHTERS THEN
        IF csrTEST%ISOPEN THEN
            CLOSE csrTEST;
        END IF;
END

条件分岐に合わせて、SQL（文字列）を作成しています。
上記はカーソル変数を使用したSELECTの動的SQLですが、他にも動的SQLの発行方法には種類があります。
下記の「動的SQLのコーディング方法」を参照して下さい。

記事を読む >> 動的SQLのコーディング方法