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
の中身が変わるたびに別のクエリとして認識してしまうため、キャッシュを圧迫してしまい消さなくて良いクエリが消されてしまう。