みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

テーブル名が可変の動的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);」

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

投稿日時 - 2008-12-30 18:02:54

QNo.4593582

困ってます

質問者が選んだベストアンサー

#1です。

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

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

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

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

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

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

投稿日時 - 2008-12-31 09:27:36

補足

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

投稿日時 - 2009-01-07 23:44:58

お礼

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

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

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

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

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

投稿日時 - 2008-12-31 14:25:00

このQ&Aは役に立ちましたか?

2人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.1

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

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

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

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

投稿日時 - 2008-12-30 22:59:25

お礼

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

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

投稿日時 - 2008-12-30 23:29:38

あなたにオススメの質問