テーブル名が可変の動的SQLをファンクションにしたい

質問

Oracle9iのWindowsXP環境です。

以下のようなPL/SQLを作成しましたが、同じようなselect文の繰り返しなので整理したいのと、レスポンスが悪いのでファンクションにしたいのですが、テーブル名が可変のため上手くいきません。「Table&1」は置換変数で、batファイルからパラメータが渡り、「Table200812」のように変更されます。

【***.bat】
set /p phara
sqlplus -s ***/***@*** ***.sql %phara%
【***.sql】
省略
begin
select aaa into a1 from Table&1 where code = 'AAAAA';
・・
select aaa into a2 from Table&1 where code = 'BBBBB';
・・
select aaa into a3 from Table&1 where code = 'CCCCC';
・・
省略
end;
/

以下、試しに作成しようとしたファンクションですが、やはり予想どおりですが「表がありません」とコンパイルエラーになります。

create function Func_test(code IN varchar2, Table1 IN varchar2) return number is
ret number;
begin
select aaa into ret from Table1 where code = 'code';
return ret;
end;
/
※コール側は、「a1 = Func_Test('AAAAA', Table&1);」

そもそもこれが実現できたとしてレスポンスが上がるものなのでしょうか？どちらにせよコードを整理する意味でもファンクションにはしたいのですが。。
宜しくお願い致します。

回答No.2

utakataXEX · ベストアンサー

#1です。

>要はテーブル名が可変なファンクション

結局それを実現するのが、「EXECUTE IMMEDIATE」か「DBMS_SQL」を使う、と言う事になると思います。

>同じようなSELECT文が実際のコード上では２０回以上

これを動的SQLでまとめたとしても、実際は違うSQL（テーブルが違うなら間違いなく違うSQLです）を発行しているので、その点では、ストアド化でパフォーマンスが大きく改善する事はないと思います。
動的SQLの場合、解析の時間も余計に食いますし。

解析の時間を減らす、と言う意味では、パラメータは、code, Table1のままにして、動的SQLではなくロジックでELSIFで冗長に判断する方がいいでしょうね。

それよりも、１つ１つの実行計画が良好なものであるなら、20発くらいの連投はパフォーマンスに大きな影響はないはずですよ。バッチ処理ならなおさらです。

お礼

ご回答ありがとうございます。

>>結局それを実現するのが、「EXECUTE IMMEDIATE」か「DBMS_SQL」を使う、と言う事になると思います。

EXECUTE IMMEDIATE ('SELECT *** FROM') || YYMM WHERE *** ;
ということでしょうか？
確かにコンパイルは通ると思うのですが、これで結果は正しく返ってきましたでしょうか？ちょっと今試せる環境がないのですが・・

>これを動的SQLでまとめたとしても、実際は違うSQL（テーブルが違うなら間違いなく違うSQLです）を発行しているので、その点では、ストアド化でパフォーマンスが大きく改善する事はないと思います。
動的SQLの場合、解析の時間も余計に食いますし。

なるほど。。やはりそうですよね。。

補足

返事遅くなりましたが、EXECUTE IMMEDIATE で上手くいけました。
どうもありがとうございました。

回答No.1

utakataXEX

そもそも、このファンクションは動的SQLになっていないですね。

ORACLEの動的SQLと言えば、「EXECUTE IMMEDIATE」か「DBMS_SQL」です。
（リンク参照ください）

で、レスポンスですが、そもそも実行計画（プラン）は見ていますか？
トレースを取ってみて、FULL SCAN や Disk Read が多発しているようなら、INDEXを張るとか、条件を見直すとかが必要になると思います。

参考URL：

http://www.shift-the-oracle.com/plsql/native-dynamic-sql.html

お礼

ご回答ありがとうございます！

まず、「動的SQL」という表現が良くなかったかもわかりません。要はテーブル名が可変なファンクションを作成したいだけなのです。
実行計画はまだ見ていませんが、そもそも同じようなSELECT文が実際のコード上では２０回以上も発行されていますので、それをストアド化してレスポンスが下げられないものなのかなぁと思ってみたのです。
言葉足らずであったり、表現がまぎらわしくて申し訳ありませんでした。