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

このQ&Aのポイント
  • Oracle9iのWindowsXP環境で、テーブル名が可変の動的SQLをファンクションにしたいです。
  • 現在、同じようなselect文の繰り返しでレスポンスが悪いので、整理したいです。
  • ファンクションにすることでコードを整理できるだけでなく、レスポンスも向上する可能性があります。
回答を見る
  • ベストアンサー

テーブル名が可変の動的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);」 そもそもこれが実現できたとしてレスポンスが上がるものなのでしょうか?どちらにせよコードを整理する意味でもファンクションにはしたいのですが。。 宜しくお願い致します。

  • Oracle
  • 回答数2
  • ありがとう数4

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

  • ベストアンサー
  • utakataXEX
  • ベストアンサー率69% (711/1018)
回答No.2

#1です。 >要はテーブル名が可変なファンクション 結局それを実現するのが、「EXECUTE IMMEDIATE」か「DBMS_SQL」を使う、と言う事になると思います。 >同じようなSELECT文が実際のコード上では20回以上 これを動的SQLでまとめたとしても、実際は違うSQL(テーブルが違うなら間違いなく違うSQLです)を発行しているので、その点では、ストアド化でパフォーマンスが大きく改善する事はないと思います。 動的SQLの場合、解析の時間も余計に食いますし。 解析の時間を減らす、と言う意味では、パラメータは、code, Table1のままにして、動的SQLではなくロジックでELSIFで冗長に判断する方がいいでしょうね。 それよりも、1つ1つの実行計画が良好なものであるなら、20発くらいの連投はパフォーマンスに大きな影響はないはずですよ。バッチ処理ならなおさらです。

ryozyryozy
質問者

お礼

ご回答ありがとうございます。 >>結局それを実現するのが、「EXECUTE IMMEDIATE」か「DBMS_SQL」を使う、と言う事になると思います。 EXECUTE IMMEDIATE ('SELECT *** FROM') || YYMM WHERE *** ; ということでしょうか? 確かにコンパイルは通ると思うのですが、これで結果は正しく返ってきましたでしょうか?ちょっと今試せる環境がないのですが・・ >これを動的SQLでまとめたとしても、実際は違うSQL(テーブルが違うなら間違いなく違うSQLです)を発行しているので、その点では、ストアド化でパフォーマンスが大きく改善する事はないと思います。 動的SQLの場合、解析の時間も余計に食いますし。 なるほど。。やはりそうですよね。。

ryozyryozy
質問者

補足

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

その他の回答 (1)

  • utakataXEX
  • ベストアンサー率69% (711/1018)
回答No.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
ryozyryozy
質問者

お礼

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

関連するQ&A

  • ストアドファンクションの戻り値をテーブルに・・

    SQL/PLUSにて作成したファンクションの戻り値で返ってきた文字列をテーブル名として使用するにはどうすればよいのでしょうか?? まず以下のようにSQL/PLUSにてFUNCTIONを作成しました。 create or replace function FuncXXXXX (inA varchar2) return varchar2 is OutA varchar2; begin select MAX(table_name) into outA from all_tables where table_name like inA || '%'; return outA end; / それを以下省略ですが、テーブルでコールしようとするとエラー(ORA-00905:キーワードがありません)が発生します。このような使い方はできないのでしょうか? select   ・   ・ from AAAA inner join FuncXXXXX(inA) ※execute FuncXXXXX(inA)でも同様 on   ・   ・ 申し訳ありませんが宜しくお願いします。

  • PL/SQLの基礎的な質問ですが・・

    こんばんわ! PL/SQLの超初心者なのですが、以下ように「aaa」という変数に一つ目のselect文の結果を代入して、二つ目のselect文のテーブルにその名前を当てはめたいだけなのです。 DECLAER aaa VARCHAR2(30) BEGIN select max(TABLE_NAME) into aaa from ALL_TABLES Like '***'; select * from aaa END; ところが、結果は「表またはビューが存在しません」となります。 このような使い方はできないのでしょうか? どなたかご教示いただけませんでしょうか?宜しくお願いします。

  • SQL文の実行速度について

    こんにちは。SQLを勉強しています。 質問をさせてください。 Oracleテーブル(test_table:レコード数はかなりあると考えて構いません) にnumber(int),value(varchar), code(varchar)の3つのフィールドが存在 するとします。 今、codeを指定してvalueを取得したいと考えています。 (1)---------------------------- select value from test_table where code='001'; select value from test_table where code='002'; select value from test_table where code='003'; (2)---------------------------- select code, value from test_table where code='001' or code='002' or code='003' (1)と(2)ではどちらが実行速度が速いのでしょうか? 実際に実行すればわかることですが、そういった環境が ないので、確認できません。宜しくお願い致します。

  • ストアドファンクションのエラーについて

    PostgreSQLでストアドファンクションを作成しました。 ファンクションの内容は下記の通りです。 CREATE FUNCTION getRenban (VARCHAR) RETURNS VARCHAR(7) AS ' DECLARE key ALIAS FOR $1; code VARCHAR(7); code2 VARCHAR(2); code5 VARCHAR(5); renban INTEGER; new_code VARCHAR(7); BEGIN SELECT MAX(code) INTO code FROM M_ITEM WHERE flg = TRIM($1); code2 := TRIM($1); IF code IS NULL THEN renban := 1; ELSE SELECT SUBSTR(code, 2) INTO code5; SELECT TO_NUMBER(code5, ''99999'') INTO renban; renban := renban + 1; END IF; code5 := ''''; SELECT TO_CHAR(renban, ''00000'') INTO code5; SELECT code2 || code5 INTO new_code; RETURN new_code; END; ' language 'plpgsql' ; コンソール上で下記のコマンドを実行したらエラーが出力されました。 SELECT getrenban('01'); 出力されたエラーの内容は下記の通りです。 ERROR: value too long for type character varying(5) CONTEXT: PL/pgSQL function "getrenban" line 19 at SQL statement お恥ずかしいですがいくらコードを見てもどこが原因なのかわかりません。 申し訳ありませんがご教授お願いできませんでしょうか。 昨日から悩んでいます。 どうか助けて下さい、宜しくお願いします。

  • PL/SQL)Functionの引数(文字列)をWHERE文に記述する方法

    PL/SQLで、Functionの中で実行するselect文のWhereに、Functionの 引数で受けとった値をセットしようとしています。 この引数が文字列(VARCHAR2型)の場合、どのようにWHEREで、記述したらよいのでしょうか? ご教授のほどをよろしくお願いします。 CREATE OR REPLACE FUNCTION Fn_TBL_AAA (psCODE IN VARCHAR2) IS CURSOR TBL_AAA_CUR IS SELECT * FROM TBL_AAA WHERE BBB = psCODE ; TBL_AAA_REC TTBL_AAA_CUR%ROWTYPE;       :       : ※上記のように記述すると、実行ではエラーが発生しないのですが、  取得したい結果が返ってきません。TABLE(TBL_AAA)のBBBは、  VARCHAR2なので、通常のSQL*PLUSのSELECT文ではBBB = psCODE  はBBB = 'psCODE'のように [']で括るのですが、PL/SQLの  FUNCTION内ではどのように記述すればよいのかが  わかりません。 以上、よろしくお願いいたします。

  • 複数のPKを持つテーブル同士の結合について

    以下のような2つのPKを持つテーブル同士で、お互いに存在しないPKのデータを抽出するときのSQLを教えて下さい。 以下のデータの場合、テーブルAではCODE_1がAAAAAのデータ、テーブルBではCODE_1がCCCCCのデータのことを指します。 [テーブルA] CODE_1 CODE_2 ----- --- AAAAA 123 BBBBB 123 [テーブルB] CODE_1 CODE_2 ----- --- BBBBB 123 CCCCC 123 PKがCODE_1だけの場合は、 select distinct テーブルA.CODE_1 from テーブルA,テーブルB where テーブルA.CODE_1 not in ( select テーブルA.CODE_1 from テーブルA,テーブルB where テーブルA.CODE_1 = テーブルB.CODE_1) で、AAAAAが抽出できるとこまでは分かったんですが、複数のPKになったらいきなり分からなくなってしまいました。 どうかよろしくお願いします。

  • ストアド内で実行したSQLの出力結果について

    現在、引数の値を元に下記のようなSQL文を生成しています。 SELECT code, name FROM T_CGY WHERE code = '1111' UNION SELECT code, name FROM T_CGY WHERE code = '1110' UNION SELECT code, name FROM T_CGY WHERE code = '1100' UNION SELECT code, name FROM T_CGY WHERE code = '1000' ORDER BY code; ※出力されたSQL文に誤りがないかを確認する為にコピーしてターミナル上で実行したら該当するレコードを取得する事ができました。 それをストアドプロシージャ内で生成したSQL文を「QUERY EXECUTE」を実行して該当するレコードが取得できるように改造してみました。 下記が出力された結果です。 getCgyData -------------------- (1000,洋服) (1100,子供服) (1110,ズボン) (1111,長ズボン) 出力されたデータは私が望んだ内容なんですが・・・ 私的には下記のように出力したいと考えています。 col1    | col2 ------------------------- 1000  | 洋服 1100  | 子供服 1110  | ズボン 1111  | 長ズボン 何とか上記のように出力できるようにするにはどうしたらいいでしょうしょうか。 そもそも上記のように出力する事は可能なのでしょうか。 CREATE FUNCTION getCgyData (VARCHAR) RETURNS TABLE(col1 VARCHAR, col2 VARCHAR) AS ' DECLARE key ALIAS FOR $1; sql TEXT; BEGIN                 ・                 ・ ※ここでSQL文を生成して、変数(sql)に格納しています。                 ・                 ・ RETURN QUERY EXECUTE sql; END; ' language 'plpgsql' ; データベースのバージョンはpostgreSQL8.4.9です。 再度、申し訳ありませんがアドバイス、宜しくお願いします。 では、失礼します。

  • PL/SQLでテーブル名に変数を使いたい

    下記のようにV_TABLE_NAMEに不定のテーブル名で使用したい ご存知の方は教えて頂けないでしょうか?V_TABLE_NAMEに取得したテーブルをセットし、データの件数を取得するPL/SQL CURSOR C_CUR IS SELECT COUNT (*) FROM V_TABLE_NAME; BEGIN for ・・・・・ loop ・・・  ・・・ V_TABLE_NAME := GET_TABLE;  OPEN C_CUR;  FETCH C_CUR into w_count; CLOSE C_CUR; end loop;

  • PL/SQLでのSQL文法

    こんにちは。教えてください。 PL/SQLを使うのが初めで戸惑っています。 まず、PROCEDUREで =========================== BEGIN SELECT NAME FROM TABLE1 WHERE NAME='tanaka'; END; / =========================== このように書いて実行させようとすると、「コンパイルエラー」 「INTO句はこのSELECT文に入ります」というエラーが出ます。 SQLの文法的には間違ってないと思うのですが、INTO句とはこれいかに?? ネットでPL/SQLで検索かけて調べたのですがあまりにも初歩すぎて わかりませんでした。 PL/SQLやORACLEのメーリングリストでお勧めがありましたら教えてください。 よろしくお願いいたします。

  • SQLでのCASEの使い方

    ASPからSQLを実行し、DBを操作するプログラムを組んでいます。 SQLをクエリアナライザで実行したときに→のところでエラーが出るのですが、どのようにしたらいいでしょうか? ----------------------------------------------- if object_id('xxxx') is not null  drop procedure xxxx go CREATE PROCEDURE xxxx (  @client_id char(10),  @user_id  char(10),   :(省略)  @code_1  char(6) output,  @code_2  char(6) output,   :(省略)  @code_9  char(6) output ) AS BEGIN  SET NOCOUNT ON  BEGIN TRANSACTION    DECLARE @no     int  SELECT @no = '0'  WHILE (@no <= 9)  BEGIN   SELECT @AAA = ISNULL(MAX job_code), '000000')    FROM TABLE    WHERE client_id = @client_id     And user_id = @user_id    SET @AAA = dbo.fbx_NextAlphanumeric(@AAA)   :(省略)   :   CASE @no →正しくない構文    WHEN 0 THEN SELECT @code_1 = @AAA    WHEN 1 THEN SELECT @code_2 = @AAA →正しくない構文     :(省略)    WHEN 9 THEN SELECT @code_9 = @AAA   END   SELECT @no = @no + 1  END  COMMIT TRANSACTION  RETURN 1 END go ----------------------------------------------- やりたいのは、SQL実行後にOUTPUTする変数「@code_1~9」にWHILEでループしながら求めた変数@AAAをセットしたいのですが。 初歩的なことだとは思いますが、よろしくお願いします。