【PL/SQL】性能改善のためのバインド変数（ホスト変数）の使い方

Oracle PL/SQLを使っている現場で、動的SQLを組むときは必ずバインド変数（ホスト変数）を使用しなければならない。
※正式呼称は「バインド変数」が正しいため、以下バインド変数で統一する。

理由としては、以下に例を挙げて説明する。

/* 悪い例 */
query := query || 'SELECT A.COMPANY'
query := query || ' FROM INVOICE A'
query := query || ' WHERE A.INVOICENO = ' || '''' || lv_invoiceno || ''''
query := query || ' AND A.DATE = ' || '''' || lv_date || '''';

OPEN lcur_invoice FOR query;

この動的SQLを見ても何が悪いかわからない。
しかし、Oracleのオプティマイザは上記SQLのlv_invoicenoやlv_dateの中身が変わるたびに別のクエリとして認識してしまうため、キャッシュを圧迫してしまい消さなくて良いクエリが消されてしまう。

バインド変数を使う

/* 良い例 */
query := query || 'SELECT A.COMPANY '
query := query || ' FROM INVOICE A'
query := query || ' WHERE A.INVOICENO = :P1'
query := query || ' AND A.DATE = :P2';

OPEN lcur_invoice FOR query USING lv_invoiceno, lv_date;

上記のようにSQLを組み立てることで例えlv_invoicenoやlv_dateの値が変わってもOracleは1つのクエリとして見てくれる。
そのため、次回からはキャッシュに蓄えられているこのクエリを呼び出すだけで良いため性能向上が見込める。

ちなみに:P1や:P2はプレースホルダ名と呼ばれてどんな名前でも良い。
ただし、プレースホルダに1対1になるようにUSINGの後ろにバインド変数を並べる必要がある。

エラー：バインドされていない変数があります。

動的SQLの場合、コンパイルは通っても実行時にエラーになる場合がある。
その典型が「バインドされていない変数があります。」

query := query || 'SELECT A.COMPANY '
query := query || 'FROM INVOICE A'

IF lb_closed = FALSE THEN
    query := query || 'WHERE A.INVOICENO = :P1'
    query := query || 'AND A.DATE = :P2';
ELSE
    query := query || 'AND A.DATE > :P3';
END IF;

OPEN lcur_invoice FOR query USING lv_invoiceno, lv_date, lv_date;

上記のように書くと実行時エラーが発生する。
「プレースホルダとバインド変数が1対1になるように」と言ったがあれは半分嘘。

実際は以下のように書かなければならない。

query := query || 'SELECT * '
query := query || 'FROM INVOICE A'
IF lb_closed = FALSE THEN
    query := query || 'WHERE A.INVOICENO = :P1'
    query := query || 'AND A.DATE = :P2';
ELSE
    query := query || 'AND A.DATE > :P3';
END IF;

IF lb_closed = FALSE THEN
    OPEN lcur_invoice FOR query USING lv_invoiceno, lv_date;
ELSE
    OPEN lcur_invoice FOR query USING lv_date;
END IF

何が違うかというとカーソルオープン時に条件分岐を追加して渡すバインド変数を変えている点だ。

上記のSQLは条件によって必要なバインド変数が1つ or 2つに変化する。
その場合は、USINGの後ろに並べるバインド変数もその個数に合わせなければならない。

それが「（実行時も考慮して）プレースホルダとバインド変数が1対1になるように」という言葉の真意。



以上