【PL/SQL】動的SQLのコーディング方法

2021年11月3日

※必要があれば「静的SQLと動的SQLの概要」ページを参照下さい。
※必要があれば【動的SQLからバルクバインドを使用する】記事を読む > 【PL/SQL】バルク・バインドもどうぞ。


「動的SQL」は条件によってSQLが変化する場合に柔軟に対応する為や、DDL（CREATE TABLE 等）の発行に使用します。
特にDDLは静的SQLでは記述できません。
PL/SQL上の静的SQLでは、コンパイル時にSQLで指定したオブジェクトが存在している必要がある為です。
※ 例えば、PL/SQL上の静的SQLで「CREATE TABLE」を記述するとコンパイルエラーとなります。指定したテーブル(オブジェクト)がその時点で存在しない為です。

動的SQLには、ORACLE8以前からある「DBMS_SQLパッケージ」を使う方法、ORACLE8i以降からある「システム固有の動的SQL」の記述方法があります。
それぞれメリット・デメリットがあるので、順に説明して行きます。

システム固有の動的SQL
（INSERT、UPDATE、DELETE、DDL）

ORACLE8i以降からある方法です。
「：」を付けた項目名は「プレースホルダ」（バインド変数）となります。
「パフォーマンス」や「SQLインジェクション対策」の為に積極的に使用していきたいです。
「プレースホルダ」は、EXECUTE IMMEDIATE の USING句に並べた値の順番にバインド（紐付け）されます。
ただ、USING句の並びでバインドされる為、プレースホルダの個数が未確定（※）の場合は「USING」では記述できません。後記の「DBMS_SQLパッケージ」を使用します。
※未確定とは、例えば下記の場合だと、条件によって「DDD」の項目が有ったり無かったりする事です

DECLARE
    sql_stmt VARCHAR2(32767);
BEGIN
    sql_stmt := 'INSERT INTO TBL(AAA,' ||
                                'BBB,' ||
                                'CCC' ||
                               ') VALUES(' ||
                                   ':AAA,' ||
                                   ':BBB,' ||
                                   ':CCC' ||
                               ')';
 
    FOR I IN 1..8
    LOOP
        EXECUTE IMMEDIATE sql_stmt USING I, 'A', 'B'; -- I変数の値が1～8で繰返し実行される 
    END LOOP;
 
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END

上記を実行した「TBL」テーブルの結果は下記となります。

AAABBBCCC
1AB
2AB
3AB
4AB
5AB
6AB
7AB
8AB

システム固有の動的SQL（SELECT INTO）

ORACLE8i以降からある方法です。
「SELECT INTO」を動的SQLで記述すると下記となります。SELECT結果が２行以上になるとエラーとなるので注意が必要です。（よっぽどの理由がない限りはカーソルを定義しましょう）
プレースホルダの個数が未確定の場合は「USING」では記述できません。

DECLARE
    sql_stmt VARCHAR2(32767);
    DB_TBL TBL%ROWTYPE;
BEGIN
    sql_stmt := 'SELECT * ' ||
                  'FROM TBL ' ||
                 'WHERE (AAA = :AAA) AND (BBB = :BBB)';
  
    EXECUTE IMMEDIATE sql_stmt INTO DB_TBL USING 1, 'A';
END

カーソル変数を使用した動的SQL（SELECT）

ORACLE8i以降からある方法です。
「IS REF CURSOR」でカーソル変数を定義し、OPEN ～ FOR で文字列として記述したSQLを発行します。
プレースホルダの個数が未確定の場合は「USING」では記述できません。

DECLARE
    sql_stmt VARCHAR2(32767);
    TYPE typ_TEST IS REF CURSOR;
    csrTEST typ_TEST;
    DB_TEST TEST%ROWTYPE;
BEGIN
    sql_stmt := 'SELECT * ' ||
                  'FROM TBL ' ||
                 'WHERE (AAA = :AAA) AND (BBB = :BBB)';
 
    OPEN csrTEST FOR sql_stmt USING 1, 'B';
    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

DBMS_SQLパッケージ
（INSERT、UPDATE、DELETE、DDL）

ORACLE８以前からある方法です。システム固有の動的SQL（EXECUTE IMMEDIATE）では実施する事ができない、プレースホルダの個数が未確定なSQLを記述できます。逆にプレースホルダの個数が決まっている場合は、パフォーマンスの問題やコーディング量が少なくなるのでシステム固有の動的SQLを使用します。
下記は、「SFLG」の条件によって「DDD」項目が追加される例です。

DECLARE
    sql_stmt VARCHAR2(32767);
    cid  INTEGER;
    result  INTEGER;
    SFLG BOOLEAN := FALSE;
BEGIN
    sql_stmt := 'INSERT INTO TBL(AAA,' ||
                                'BBB,' ||
                                'CCC';
 
    IF NOT SFLG THEN
        sql_stmt := sql_stmt || ', DDD';
    END IF;
 
    sql_stmt := sql_stmt || ') VALUES(' ||
                                 ':AAA,' ||
                                 ':BBB,' ||
                                 ':CCC';
 
    IF NOT SFLG THEN
        sql_stmt := sql_stmt || ', :DDD';
    END IF;
 
    sql_stmt := sql_stmt || ')';
 
    cid := DBMS_SQL.OPEN_CURSOR;
 
    DBMS_SQL.PARSE(cid, sql_stmt, dbms_sql.native);
 
    FOR I IN 1..8
    LOOP
        DBMS_SQL.BIND_VARIABLE(cid, ':AAA', I);
        DBMS_SQL.BIND_VARIABLE(cid, ':BBB', 'B');
        DBMS_SQL.BIND_VARIABLE(cid, ':CCC', 'C');
 
        IF NOT SFLG THEN
            DBMS_SQL.BIND_VARIABLE(cid, ':DDD', 'D');
        END IF;
  
        result := DBMS_SQL.EXECUTE(cid);
    END LOOP; 

   DBMS_SQL.CLOSE_CURSOR(cid);
 
   COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(cid) THEN
            DBMS_SQL.CLOSE_CURSOR(cid);
        END IF;
 
        ROLLBACK;
END

「DBMS_SQL」で「INSERT」等を発行する手順は下記の通りです
１．文字列でSQLを記述する
２．「DBMS_SQL.OPEN_CURSOR」でカーソルのハンドル番号を取得する
３．「DBMS_SQL.PARSE」でSQLの解析を行う
４．「DBMS_SQL.BIND_VARIABLE」でプレースホルダに値を渡す。項目名で指定するので並びは関係ないです。
５．「DBMS_SQL.EXECUTE」でSQLを発行する。戻り値はSQLで処理された件数
６．「DBMS_SQL.CLOSE_CURSOR」でカーソルのハンドルを閉じる
※値を数回渡す場合は「4. BIND_VARIABLE」と「5. EXECUTE」を繰り返し実行します

DBMS_SQLパッケージ（SELECT）

ORACLE８以前からある方法です。システム固有の動的SQL（EXECUTE IMMEDIATE）では実施する事ができない、プレースホルダの個数が未確定なSQLを記述できます。
ただ、SELECTで取得する項目を個別に番号で指定する必要があるので、項目数が多いと死にますw

DECLARE
    sql_stmt VARCHAR2(32767);
    cid  INTEGER;
    ret  INTEGER;
    fetch INTEGER;
 
    L_AAA DBMS_SQL.Number_Table;
    L_BBB DBMS_SQL.Varchar2_Table;
        
    SFLG BOOLEAN := FALSE;
BEGIN
    sql_stmt := 'SELECT AAA, BBB ' ||
                  'FROM TBL ' ||
                 'WHERE (AAA = :AAA) AND ' ||
                       '(BBB = :BBB) AND ' ||
                       '(CCC = :CCC)';
 
    IF NOT SFLG THEN
        sql_stmt := sql_stmt || ' AND (DDD = :DDD)';
    END IF;
  
    cid := DBMS_SQL.OPEN_CURSOR;
 
    DBMS_SQL.PARSE(cid, sql_stmt, dbms_sql.native);
 
    DBMS_SQL.BIND_VARIABLE(cid, ':AAA', 1);
    DBMS_SQL.BIND_VARIABLE(cid, ':BBB', 'B');
    DBMS_SQL.BIND_VARIABLE(cid, ':CCC', 'C');
 
    IF NOT SFLG THEN
        DBMS_SQL.BIND_VARIABLE(cid, ':DDD', 'D');
    END IF;
  
    ret := DBMS_SQL.EXECUTE(cid);
    LOOP
        fetch := DBMS_SQL.FETCH_ROWS(cid);
        EXIT WHEN fetch <= 0;
 
        DBMS_SQL.COLUMN_VALUE(cid, 1, L_AAA);
        DBMS_SQL.COLUMN_VALUE(cid, 2, L_BBB);
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(cid);
 EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(cid) THEN
            DBMS_SQL.CLOSE_CURSOR(cid);
        END IF;
END

「DBMS_SQL」で「SELECT」を発行する手順は下記の通りです
１．文字列でSQLを記述する
２．「DBMS_SQL.OPEN_CURSOR」でカーソルのハンドル番号を取得する
３．「DBMS_SQL.PARSE」でSQLの解析を行う
４．「DBMS_SQL.BIND_VARIABLE」でプレースホルダに値を渡す。項目名で指定するので並びは関係ないです。
５．「DBMS_SQL.EXECUTE」でSQLを発行する
６．「DBMS_SQL.FETCH_ROWS」で行を読み込む
７．「DBMS_SQL.COLUMN_VALUE」で項目の値を取得する。項目名ではなく、何番目の項目かで指定する
※値を代入する変数は「DBMS_SQL.Number_Table」、「DBMS_SQL.Varchar2_Table」で宣言していますが通常通り「NUMBER」、「VARCHAR2」で宣言しても問題ありません
８．「DBMS_SQL.CLOSE_CURSOR」でカーソルのハンドルを閉じる

DBMS_SQLパッケージ
（【SELECT】DBMS_SQL.TO_REFCURSOR）

ORACLE11g以降からある方法です。DBMS_SQLパッケージでSELECTを発行すると、値の取得に各項目を個別に指定しなければいけませんが、「DBMS_SQL.TO_REFCURSOR」でカーソルハンドルを変換する事で通常のカーソルFETCHにする事ができます。
DBMS_SQLパッケージのプレースホルダ指定の柔軟性と、カーソル変数でのデータ取得と良いとこ取りができます。

DECLARE
    sql_stmt VARCHAR2(32767);
    cid  INTEGER;
    ret  INTEGER;
    fetch INTEGER;
 
    TYPE typ_TBL IS REF CURSOR;
    csrTBL typ_TBL;
 
    DB_TBL TBL%ROWTYPE;
BEGIN
    sql_stmt := 'SELECT AAA, BBB ' ||
                  'FROM TBL ' ||
                 'WHERE (AAA = :AAA) AND ' ||
                       '(BBB = :BBB) AND ' ||
                       '(CCC = :CCC)';
   
    cid := DBMS_SQL.OPEN_CURSOR;
 
    DBMS_SQL.PARSE(cid, sql_stmt, dbms_sql.native);
 
    DBMS_SQL.BIND_VARIABLE(cid, ':AAA', 1);
    DBMS_SQL.BIND_VARIABLE(cid, ':BBB', 'B');
    DBMS_SQL.BIND_VARIABLE(cid, ':CCC', 'C');
   
    ret := DBMS_SQL.EXECUTE(cid);
    csrTBL := DBMS_SQL.TO_REFCURSOR(cid);
    LOOP
        FETCH csrTBL INTO DB_TBL;
        EXIT WHEN csrTBL%NOTFOUND;
    END LOOP;
    CLOSE csrTBL;
 EXCEPTION
    WHEN OTHERS THEN
        IF csrTBL%ISOPEN THEN
            CLOSE csrTBL;
        END IF;
END

「DBMS_SQL.TO_REFCURSOR」を使用する手順は下記の通りです
１．文字列でSQLを記述する
２．「DBMS_SQL.OPEN_CURSOR」でカーソルのハンドル番号を取得する
３．「DBMS_SQL.PARSE」でSQLの解析を行う
４．「DBMS_SQL.BIND_VARIABLE」でプレースホルダに値を渡す。項目名で指定するので並びは関係ないです。
５．「DBMS_SQL.EXECUTE」でSQLを発行する
６．「DBMS_SQL.TO_REFCURSOR」でカーソルハンドルをカーソル変数に変換する
７．通常のFETCHで値を取得する事ができる
８．通常のCLOSEでカーソルを閉じる

【動的SQLからバルクバインドを使用する】記事を読む >> 【PL/SQL】バルク・バインド

記事を読む >>【PL/SQL】パッケージ・其の壱（概要）

【PL/SQL】メニュー

サイトマップ

2021年11月3日Oracle,PL/SQL,データベース,プログラム動的SQL

Posted by こっぷ