e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第44回 「動的SQLの実行の仕方(EXECUTE IMMEDIATE)」

2012.12.10

こんにちは。インストラクターの蓑島です。
前回から「動的SQL」をテーマに解説しております。今回から動的SQLの具体的な方法をご紹介します。

前回解説したように、動的SQLとは、文字列のSQLのことです。
文字列ですから、実行時に文字列連結などでSQL文を作成できます。その後実行するわけです。
ではさっそく、そういった簡単な例を紹介します。

以下の例をご覧ください。

SQL> SET SERVEROUTPUT ON   ←SQL*PlusやSQL*Developerの画面表示を有効に設定
SQL> DECLARE
  2     V1  VARCHAR2(10) := 'DEPT';
  3     V2  VARCHAR2(10) := '99';
  4     V3  VARCHAR2(10) := 'TEST';
  5     V_STMT        VARCHAR2(100);
  6  BEGIN
  7     V_STMT := 'INSERT INTO ' || V1 ||
  8               ' VALUES (' || V2 ||',''' || V3 ||''',NULL)';
  9     DBMS_OUTPUT.PUT_LINE('組み立てたSQL⇒ ' || V_STMT);
 10     EXECUTE IMMEDIATE V_STMT;
 11  END;
 12  /
組み立てたSQL⇒ INSERT INTO DEPT VALUES (99,'TEST',NULL)

PL/SQLプロシージャが正常に完了しました。

ここでは、無名ブロックで、文字列連結によりSQL文を組み立てて、そのSQL文を実行している例です。確認のため組み立てたSQL文を画面表示してます。

まず、宣言部(2~5行目)で、文字列変数V1,V2,V3,V_STMTを宣言しています。V1, V2, V3にはそれぞれ'DEPT','99','TEST'という文字が格納されています。

実行部の7-8行目で、それらの文字列変数を組み立てて『INSERT INTO DEPT VALUES (99,'TEST',NULL)』という文字列を生成し、V_STMT変数に格納しています。
シングルコーテーション(')が連続しているのは、2つのシングルコーテーションで一つのシングルコーテーションを文字そのものとして表しているものです。
そのV_STMT変数の中身を9行目で画面表示しています。そしてこのV_STMT変数の中身である文字列のINSERT文を実行している箇所が10行目です。
すなわち、 EXECUTE IMMEDIATE というキーワードを使えば、文字列のSQL文を実行できるわけです。

実際にDEPT表を問い合わせて99番の行がINSERTされたかどうか確認しましょう。

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME                 LOC
---------------------------------------------------
        10 ACCOUNTING      NEW YORK
        20 RESEARCH           DALLAS
        30 SALES                  CHICAGO
        40 OPERATIONS        BOSTON
        99 TEST                                        ←この行です。

確かにINSERTされていますね。

上の例は無名ブロックで説明しましたが、例えばこの例がプロシージャだとすれば、実行時に受け渡されるパラメータによって処理するSQL文が決まるといったようなことになります。実際のアプリケーション開発ではプロシージャやパッケージをつかうことになると思いますが、まさに実行時に動的にSQL文が決まるわけです。

いずれにしろ、この例のようにEXECUTE IMMEDIATEというキーワードを使えば、文字列のSQL文を実行できます。

文字列のSQL文は変数に格納されている必要はありません。変数ではなく、直接、文字列のSQL文を指定できます(当然ですね)。
例えば以下のようにです。

SQL> BEGIN
  2    EXECUTE IMMEDIATE
  3     'UPDATE DEPT SET DEPTNO = 80 WHERE DEPTNO = 99';
  4  END;
  5  /

PL/SQLプロシージャが正常に完了しました。

大変簡単ですね。ということで結論です。動的SQL文の実行構文は以下の通りです。

<<<動的SQLの実行構文>>>
  EXECUTE  IMMEDIATE  文字列のSQL文(またはそれを格納した文字列変数);

上述のように大変簡単なのですが、実はもう少し説明することがあります。
例えば、SELECT文の場合、どのように結果をとりだすのかとか、パフォーマンス向上などのため、値に対してバインド変数を使うとか、などです。

それではそういった説明は次回以降にいたします。今回はここまでです。
また次回、ご期待ください。

先頭へ戻る