• ベストアンサー
  • 困ってます

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

共感・応援の気持ちを伝えよう!

  • 回答数2
  • 閲覧数4125
  • ありがとう数4

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

  • ベストアンサー
  • 回答No.2

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

共感・感謝の気持ちを伝えよう!

質問者からのお礼

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

質問者からの補足

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

関連する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   ・   ・ 申し訳ありませんが宜しくお願いします。

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

    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 お恥ずかしいですがいくらコードを見てもどこが原因なのかわかりません。 申し訳ありませんがご教授お願いできませんでしょうか。 昨日から悩んでいます。 どうか助けて下さい、宜しくお願いします。

  • 引数がROWTYPE型のストアドファンクション

    引数がROWTYPE型のファンクションを呼び出したいのですが、 SELECT TEST_FUNC(MY_TABLE.*) FROM MY_TABLE; で呼び出せないのでしょうか? (TEST_FUNCの引数はMY_TABLE%ROWTYPE型です) よろしくお願いします。

その他の回答 (1)

  • 回答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

共感・感謝の気持ちを伝えよう!

質問者からのお礼

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

関連するQ&A

  • 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)ではどちらが実行速度が速いのでしょうか? 実際に実行すればわかることですが、そういった環境が ないので、確認できません。宜しくお願い致します。

  • SQL文の作成について

    はじめまして SQL Serverを使用テーブルの更新クエリを作っています。 ◆質問 副問合せの結果をIn句で見ていますがExists句の方がパフォーマンスがあるといわれているので直したいと思っています。 可能でしょうか? ◆SQL文 UPDATE [TABLE_A] SET Data = null WHERE Code IN (Select Code From [TABLE_B] B, [TABLE_C] C Where (B.Start <= GetDate() AND C.End >= GetDate()) AND (B.TimeCode = C.TimeCode)) ◆KEY [TABLE_A].Code = [TABLE_B].Code [TABLE_B].TimeCode = [TABLE_C].TimeCode ◆[TABLE_A] Code Data 001 100 002 200 003 300 004 400 <- DataをNullにする ◆[TABLE_B] Code TimeCode 001 AAA 002 AAA 003 AAA 004 BBB <- 今日2/18ヒット 001 CCC 002 CCC ◆[TABLE_C] TimeCode Start End AAA 2009/1/1 2009/2/15 BBB 2009/2/13 2009/2/20 <- 今日2/18ヒット CCC 2009/2/19 2009/10/5 データは[TABLE_A] 6万件、[TABLE_B]が20万件、[TABLE_C]が100件程度です。 夜遅くにすみませんがお願いします。

  • 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 Server 2005 にて、 以下のようなストアドファンクションを作成しようとしても、 「メッセージ 156、レベル 15、状態 1、プロシージャ TestKansu、行 30 キーワード 'end' 付近に不適切な構文があります。」 といったエラーメッセージが表示されてしまい、 うまく作成できません。。 文法など、いろいろ調べてみたところ、特に問題なさそうな 感じではあるのですが。。 どこに問題があるのか、どなたかご教授願えないでしょうか? なにとぞ、よろしくお願いいたします。 -------------------------------- use tempdb go -- 呼び出し形式 create function TestKansu ( @Date as datetime, @Type as varchar(1) ) returns @ReturnTable table ( StartDate varchar(8), EndDate varchar(8) ) as begin if @Type is null begin raiserror('正しいtypeを指定してください',-1,-1) end if @Date is null begin SET @Date = getdate() end if @Type = '0' begin insert into @ReturnTable select convert(varchar(8), dateadd(dd ,-2 ,@Date), 112) as StartDate, convert(varchar(8), dateadd(dd ,-2 ,@Date), 112) as EndDate, end if @Type = '1' begin insert into @ReturnTable select convert(varchar(8), @Date, 112) as StartDate, convert(varchar(8), @Date, 112) as EndDate end return end --------------------------------

  • 複数の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で他のテーブルに無いIDの抽出

    MySQLを使っています。 下記は、 1.テーブルaaaには、存在するidのものが、 2.テーブルbbbには、存在しない、 3.テーブルaaaのレコードを抽出したいものです。 "SELECT DISTINCT aaa.*" + " FROM aaa" + " WHERE (aaa.flag = false)" + " AND NOT EXISTS (SELECT * FROM bbb" + " WHERE (aaa.id = bbb.id) AND (bbb.flag = false))" ですが、実行すると、下記エラーがでます。 SQLException:Base table or view not found, message from server: \"Unknown table 'bbb' in where clause" どうすればよいのでしょうか?

    • ベストアンサー
    • MySQL
  • 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をセットしたいのですが。 初歩的なことだとは思いますが、よろしくお願いします。

  • ストアド内で実行した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です。 再度、申し訳ありませんがアドバイス、宜しくお願いします。 では、失礼します。

  • 副問合せ 続(Oracle SQL)

    問題:NATION表とINVENTION表より人口密度(人口/面積)が100以下の国で、発明事項がある国の国コードを表示する。 方法1:主問合せでNATION表を利用する 方がうまくいきません。 私の考えた下記SQL文では実行結果が80件返ってしまいます。 答えは方法2と同じ5件ですがどこが悪いのでしょうか? 回答のほどよろしくお願い致します。 方法1:主問合せでNATION表を利用する SELECT CODE FROM NATION WHERE (POPULATION / AREA) <=ANY(SELECT 100 FROM INVENTION) SELECT CODE FROM NATION WHERE POPULATION / AREA < 100;(上と同じ意味) INVENTION表の副問合せがうまくできていないので5件出力できない? 方法2:主問合せでINVENTION表を利用する SELECT DISTINCT NATION_CODE FROM INVENTION WHERE NATION_CODE = ANY(SELECT CODE FROM NATION WHERE 100 >= POPULATION / AREA) NATION_CODE ----------- 107 136 142 154 157 各表の構造はこのようになります。 SQL> DESCRIBE INVENTION 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- INVENTION NOT NULL VARCHAR2(30) INVENTOR VARCHAR2(30) YEAR NUMBER(4) NATION_CODE NUMBER(4) SQL> DESCRIBE NATION 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- CODE NOT NULL NUMBER(5) NATION NOT NULL VARCHAR2(28) CAPITAL VARCHAR2(20) AREA NUMBER(22) POPULATION NUMBER(22)

  • 動的SQLよりファンクションをコールできますか?

    PL/SQLで教えていただきたいのです。 (1)動的SQLより、ファンクションをコールできますか? (2)もしできるなら、どういった書式になるのでしょうか? 以下のようなコードを書いていますが、2行目でコンパイルエラーになってしまいます。 vs2Sql := 'SELECT a.CLM1,' || FunctionXXX(a.CLM1) || ' AS CLM2 ' || 'FROM TargetTBL a WHERE ...'; よろしくお願いいたします。