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

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

第47回 「動的SQLでのバインド変数の使用」

2013.01.28

こんにちは。インストラクターの蓑島です。
今回は動的SQLでのバインド変数の使用について解説します。
パフォーマンスの向上に役立つ機能です。

では最初に、バインド変数を使用しない、以下のようなプロシージャを作ってみます。

<<<ケース1 動的SQLの値を文字列連結している場合>>>
SQL> CREATE OR REPLACE PROCEDURE GET_ENAME ( P_ID IN NUMBER,
  2                                            P_NAME OUT VARCHAR2)
  3  IS
  4     V_NAME EMP.ENAME%TYPE;
  5  BEGIN
  6  /****************************************************************/
  7  -- 動的SQLで、指定された社員番号の社員名を取得する
  8  /****************************************************************/
  9     EXECUTE IMMEDIATE
 10       'SELECT ENAME FROM EMP WHERE EMPNO = ' || P_ID
 11       INTO V_NAME;
 12  /****************************************************************/
 13  -- その社員名を呼び出し元に返す
 14  /****************************************************************/
 15       P_NAME := V_NAME;
 16  END;
 17  /

プロシージャが作成されました。

簡単に言えば、社員番号をもとに、その社員の名前を返す処理です。
そのための問い合わせを、動的SQLで行っています。

ここで、このプロシージャが様々な社員番号で何度も繰り返し実行されるとするとこのプロシージャ処理のパフォーマンスはよくないものとなります。
なぜなら毎回違うSQL文が実行されることになるからです。
10行目の動的SQL(文字列のSQL)の書き方を見てください。値を文字列連結(||)してます。

10行目のように、SQLの処理する値を文字列連結( || ) で組み立てて動的SQLを生成すると、同じ値でない限りは、異なったSQL文になり、SQL文は共有されません。

例えば、上記のGET_NAMEプロシージャを異なる社員番号「100」と「200」でそれぞれコールしたとします。
そうすると、実行される動的SQLは以下のSQLです。それぞれ(1),(2)としておきます。

SELECT ENAME FROM EMP WHERE EMPNO = 100 ←(1)
SELECT ENAME FROM EMP WHERE EMPNO = 200 ←(2)

ここで、(1)と(2)は同一の文、というわけではありませんね。
つまり100と200の違いがあるので、異なるSQLです。

しかし、ここで、動的SQLの値部分を文字列連結ではなく、バインド変数を使った書き方にすると、値が違っていても同一の文とすることができます。
上記のプロシージャをそのような形に修正したものが、以下のケースです。

<<<ケース2 動的SQLの値をバインド変数にしている場合>>>
SQL> CREATE OR REPLACE PROCEDURE GET_ENAME ( P_ID IN NUMBER,
  2                                            P_NAME OUT VARCHAR2)
  3  IS
  4     V_NAME EMP.ENAME%TYPE;
  5  BEGIN
  6  /****************************************************************/
  7  -- 動的SQLの値をバインド変数としたケース
  8  /****************************************************************/
  9     EXECUTE IMMEDIATE
 10       'SELECT ENAME FROM EMP WHERE EMPNO = :EMPID'
 11                     INTO V_NAME USING P_ID;
 12  /****************************************************************/
 13  -- その社員名を呼び出し元に返す
 14  /****************************************************************/
 15       P_NAME := V_NAME;
 16  END;
 17  /

プロシージャが作成されました。

ここで、このプロシージャを社員番号「100」と「200」でそれぞれコールしたと場合、値が違うにもかかわらず、SQL文は同じになります。すなわち、以下の文です。

SELECT ENAME FROM EMP WHERE EMPNO = :EMPID ← (3)

上記(3)の文は、ソースコードの9行目の動的SQL文と同一です。
この中の「:EMPID」という部分がバインド変数というものです。
バインド変数の名前は特に決まったものはありませんが、先頭に必ずコロン(:)を付けます。
そして、バインド変数の値はソースコード11行目のUSING句でバインド変数の値を指定します。

バインド変数は、「変数」だから事前に宣言しないの?という疑問を持たれる方もいると思いますが、バインド変数は、PL/SQLプログラム(PL/SQLブロック)の外側に領域を持っている変数なので、PL/SQLブロック内で宣言することはありません。

大事なことは、「:EMPID」の値が「100」でも「200」でも、データベースの中では、「SELECT ENAME FROM EMP EMPNO = :EMPID」という「一つの文」が、処理する値を変えながら使い回しされているということです。
これにより、解析処理(←まだ説明していませんが。。)1回で済むので、パフォーマンスが向上します。
文字列連結(上記ケース1)の場合は、比較する値が違っているため異なるSQLとなり、そのたびに解析処理が行われます。

このように、値だけが違うSQL文が何度も実行される環境であればあるほど(大半のシステムはそうですね)、バインド変数を使うメリットは大きいと言えます。

なお、前回(第46回)ご紹介したカーソル変数による動的SQLでもバインド変数が使えます。
以下のような構文です。(バイインド変数名 を :B1としてます)

OPEN カーソル変数名 FOR 'SELECT ・・・・ WHERE 列名 = :B1 ' USING BIの値;

それでは、今回はここまでにします。また次回をご期待ください。

先頭へ戻る