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

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

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

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

  • 回答数4
  • 閲覧数1929
  • ありがとう数5

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

  • ベストアンサー
  • 回答No.3
  • 3rd_001
  • ベストアンサー率66% (115/174)

このくらいならSQL*Plusなら、ストアドを使わなくても可能です。 test.sql ----------- set line 150 set pages 150 column max_tab_name NEW_VALUE MAXTAB select MAX(table_name) AS max_tab_name from all_tables where table_name like upper('&&1%'); select * from &&MAXTAB; exit ----------- $ sqlplus scott/tiger @test emp SQL*Plus: Release 11.1.0.6.0 - Production on 火 12月 9 18:35:51 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options に接続されました。 旧 3: where table_name like upper('&&1%') 新 3: where table_name like upper('emp%') MAX_TAB_NAME ------------------------------------------------------------ EMP_HIST 旧 1: select * from &&MAXTAB 新 1: select * from EMP_HIST EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEL_DATE ---------- -------------------- ------------------ ---------- -------- ---------- ---------- ---------- -------- 7369 SMITH CLERK 7902 80-12-17 800 20 08-02-01

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

関連するQ&A

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

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

  • ストアドファンクションがうまく作成できない

    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 --------------------------------

  • ストアドファンクション⇒プロシージャ呼出し失敗する

    参考の通りに、ストアドプロシージャ作成SQL文を発行し、 コマンドライン上からは、以下の通りの結果1となり、 レコードがinsertされます。 <結果1> mysql> call logger('ほげ', 'ホゲ'); Query OK, 1 row affected (0.01 sec) mysql> select * from PROCEDURE_LOG; +--------+--------+---------------------+ | NAME | QUERY | EXECUTE_DATE | +--------+--------+---------------------+ | ほげ | ホゲ | 2014-03-26 10:41:26 | +--------+--------+---------------------+ 1 row in set (0.00 sec) ですが、testというストアドファンクションから呼び出してみましたが、 以下の結果2の通り、失敗します。 <結果2> mysql> select test(); ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger <test> BEGIN CALL logger('test','1'); RETURN CONCAT('END'); END 現在動作している環境では、どこでエラーが発生したかがわからず、 ロガーの処理をなんとしても実装したいのですが、 いろいろ試してみても、解決できませんでした。 ご教示のほど、よろしくお願いします。 <参考> http://treeapps.hatenablog.com/entry/20120106/p1 <ストアドプロシージャ作成SQL文(logger)> SET NAMES UTF8; delimiter // -- ログ出力 DROP PROCEDURE IF EXISTS logger// CREATE PROCEDURE logger( IN PROCEDURE_NAME TEXT, IN SQL_TEXT TEXT ) BEGIN DECLARE CNT INT; CREATE TABLE IF NOT EXISTS PROCEDURE_LOG ( NAME VARCHAR(64), QUERY TEXT, EXECUTE_DATE TIMESTAMP, KEY IDX1 (NAME, QUERY(64), EXECUTE_DATE) ) ENGINE=MYISAM DEFAULT CHARSET=UTF8; -- 古いログを削除 SELECT COUNT(*) INTO CNT FROM PROCEDURE_LOG; IF CNT >= 1000 THEN DELETE FROM PROCEDURE_LOG LIMIT 1; END IF; -- テーブルにログを記録 SET @sql = CONCAT('INSERT INTO PROCEDURE_LOG VALUES (', QUOTE(PROCEDURE_NAME), ',', QUOTE(SQL_TEXT), ', null)'); PREPARE stmt FROM @sql; EXECUTE stmt; END // delimiter ;

    • ベストアンサー
    • MySQL

その他の回答 (3)

  • 回答No.4

もしかしたら、SQLPlus限定の話でしたか? (そんな気がしてきました) それでよければ#3さんのように置換変数を使ってやっていただければいいかと思います。 その意味では蛇足ですが、一旦テーブルに受ける方の例を書いておきます。 CREATE OR REPLACE PROCEDURE POP_WORK(inA VARCHAR2) IS outA VARCHAR2(100); cntA VARCHAR2(100); BEGIN SELECT MAX(TABLE_NAME) INTO outA FROM ALL_TABLES WHERE TABLE_NAME LIKE UPPER(inA) || '%'; SELECT COUNT(*) INTO cntA FROM ALL_TABLES WHERE TEMPORARY='Y' AND TABLE_NAME='GTT_' || outA; IF (cntA > 0) THEN DBMS_OUTPUT.PUT_LINE('GTT_' || outA || ' ALREADY EXIST'); ELSIF (outA IS NOT NULL) THEN EXECUTE IMMEDIATE ('CREATE GLOBAL TEMPORARY TABLE GTT_' || outA || ' ON COMMIT PRESERVE ROWS' || ' AS (SELECT * FROM ' || outA || ')'); DBMS_OUTPUT.PUT_LINE('GTT_' || outA); ELSE DBMS_OUTPUT.PUT_LINE('NO TABLE'); END IF; END; / プロシージャが作成されました。 SQL>SET SERVEROUT ON SQL>EXEC POP_WORK('TBL'); GTT_TBLC PL/SQLプロシージャが正常に完了しました。 SQL>SELECT * FROM GTT_TBLC; SQL>TRUNCATE TABLE GTT_TBLC; SQL>DROP TABLE GTT_TBLC;

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

  • 回答No.2

テーブル名の最大値を収録すること自体はファンクションで可能ですが、その戻り値をSQL文でテーブルとして使用することはできません。 カラムもできないはずです。 動的SQLであれば両方とも可能ですが、PL/SQLブロック内の処理になるため、一旦テーブルに受けるか、カーソルを回して書きだすかしないと値を受け取ることができないと思います。

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

質問者からのお礼

ご回答ありがとうございます。 テーブルにかくとは一旦ビュー等作成するということでしょうか? また「カーソルを回して書き出す」とは具体的にどういうことでしょうか?初歩的質問で申し訳ありませんが宜しくお願いします。

  • 回答No.1

カラム名、テーブル名を動的に設定することはできません。 従って、そのアプローチはできないです。 そこから先は質問者さんがどういう環境で何をしようとしているかによりますね。 (アプリ側でコントロールするのか、テーブルや一時テーブルを経由するのか、動的SQLでカーソルフェッチするのか、SQL/Plus限定で置換変数を使うのか、など)

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

質問者からのお礼

ご回答ありがとうございます。 テーブル名にtablename_yyyymmが複数あり、それの最大値を取得したいだけなのですが、ストアドファンクションでは無理なのでしょうか? 尚バージョンは9iです。 ちなみにテーブル名は今回初めて試みましたがカラムも動的には無理でしょうか?以前サンプルで見たような気がしましたが… 申し訳ないですが宜しくお願いします。

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

  • ストアドファンクションの実行

    CREATE OR REPLACE FUNCTION hoge(str IN VARCHAR2) ~ を作成した後、 PL*SQLにてSQL> exec hoge('fuga'); を実行すると、 行1でエラーが発生しました。: ORA-06550: 行1、列7: PLS-00221: 'hoge'がプロシージャではないか、または未定義です・ ORA-06550: 行1、列7: PL/SQL: Statement ignore と言われます。 どうすればエラーを解決できますでしょうか? 助言を宜しくお願い致します。

  • postgres ストアドファンクションないのレコードの取得について

    質問させていただきます pg/plsqlのストアドファンクション内で 受け取った引数を sql := select * from hoge; FOR rec IN EXECUTE sql LOOP return rec.col;     <<<この部分で END LOOP; return; rec.引数名のようにして 引数から取得した 列を指定したいのですが、そのまま入れると そのような列はありませんと言うような返事を 返されてしまいます。 うまくやる方法はないものでしょうか? よろしくお願いします。

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

  • Mysql ストアドファンクション 意図しない結果

    以下の通りストアドファンクションを作成しました。 関数名:fgetLineNew インパラメータ:shop Varchar 8 ※1つだけ。。。 戻り値: Int 11 <ソース> BEGIN DECLARE pline INTEGER(11) DEFAULT NULL; SELECT COUNT(1) INTO pline FROM `hoge` WHERE `SHOP` = shop; IF pline IS NULL THEN SET pline = 10000; END IF; RETURN pline; END 上記のように、作成したストアドファンクションですが、 どんな引数を入れても、戻り値が全ての行(817行)になってしまいます。 以下を参照。 Where区がまったく働いていないのですが、何が原因なのでしょうか。 教えてください。 <ストアドファンクション実行> mysql> select fgetLineNew(1); +----------------+ | fgetLineNew(1) | +----------------+ | 817 | +----------------+ 1 row in set (0.01 sec) mysql> select fgetLineNew(525); +------------------+ | fgetLineNew(525) | +------------------+ | 817 | +------------------+ 1 row in set (0.01 sec) mysql> select fgetLineNew(101); +------------------+ | fgetLineNew(101) | +------------------+ | 817 | +------------------+ 1 row in set (0.00 sec) mysql> select fgetLineNew(525); +------------------+ | fgetLineNew(525) | +------------------+ | 817 | +------------------+ 1 row in set (0.00 sec) mysql> select fgetLineNew('525'); +--------------------+ | fgetLineNew('525') | +--------------------+ | 817 | +--------------------+ 1 row in set (0.01 sec)

    • ベストアンサー
    • MySQL
  • テーブルが存在していたらDROPしてからCREATEするには?

    作成しようとするテーブルが既に存在している場合 一度DROP TABLEを行いたくて、以下のSQLを作成したのですが 削除が実行されません。 そもそものやり方自体が間違っているのでしょうか? 実行後のメッセージは テーブルが存在しなかった場合→コマンドは正常に完了しました。 テーブルが存在した場合→データベースにオブジェクト名 'SLIP_ITEMXX' が既に存在します。 と、表示されます。 --テーブルが存在した場合削除する ※ストアド使用 CREATE PROCEDURE SP_SLIP_ITEMXX AS DECLARE @TableName varchar DECLARE @Sql varchar set @TableName = 'SLIP_ITEMXX' SET @Sql = 'if exists (select * from dbo.sysobjects where id = object_id(N' + @TableName + ') ' + 'DROP TABLE ' + @TableName EXEC(@Sql) GO --テーブル生成 CREATE TABLE SLIP_ITEMXX( [slip_no] [int] NOT NULL ) GO --ストアド削除 DROP PROCEDURE SP_SLIP_ITEMXX GO

  • T-SQLで一時テーブルの名前を毎回変更できるストアドを作りたい

    お世話になります。 初歩的な質問で恐縮ですがご教示いただければ幸いです。 T-SQLでグローバル一時テーブルの名前を毎回変更できるストアドを作り、Access2003アプリから実行させる予定です。 ストアドに、CREATE TABLE [dbo].[@tablename]のように書き、 ストアド実行時に@tablenameの値を ##ABC のように入力したのですが、テーブルはtempdbではなく、しかもテーブル名は@tablenameでテーブルが作成されてしまいます。 CREATE TABLE [dbo].@tablename のように[]をとると ストアド自体を作成できずエラーになってしまいます。 宜しくお願いします。 create PROCEDURE [dbo].[ストアドプロシージャー名] (@tablename nvarchar(255)) AS BEGIN from SET NOCOUNT ON; CREATE TABLE [dbo].[@tablename] <以下省略>

  • 無名ブロック内でのDDL実行について

    環境は Linux + oracle 10g R2です。 簡単な無名プロシージャを書いていてはまって しまいました。 分かる方でしたら、あっというまに指摘していただけそうなので こちらに質問しました。 無名ブロック内で以下の事を行いたいのですが、うまく動作を確認できません。 ※以下は意味の無い処理となっていますが、今回説明用に短くしてみました。 ◇実現したい事 1.CREATE TABLE文の実行 (ここではEX01テーブルを作成します) 2.CREATE したテーブルにたいしてINSERT 3.CREATE したテーブルのDROP ◇私の実行結果 $ sqlplus scott/tiger@orcl SQL> SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME ='EX01'; レコードが選択されませんでした。 -- 私の思いでは以下のプロシージャは正常に動作するのでは? -- と思うのですが、以下の通りエラーとなってしまいます。 -- SQL> BEGIN 2 DBMS_UTILITY.EXEC_DDL_STATEMENT('CREATE TABLE EX01 ( C1 NUMBER,C2 VARCHAR2(10))'); 3 INSERT INTO EX01(C1,C2) VALUES(1,'AAA'); 4 DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TABLE EX01'); 5 END; 6 / INSERT INTO EX01(C1,C2) VALUES(1,'AAA'); * 行3でエラーが発生しました。: ORA-06550: 行3、列13: PL/SQL: ORA-00942: 表またはビューが存在しません。 ORA-06550: 行3、列1: PL/SQL: SQL Statement ignored -- -- なので、処理を分割して行ってみました。 -- まずはCREATE TABLEのみ -- SQL> BEGIN 2 DBMS_UTILITY.EXEC_DDL_STATEMENT('CREATE TABLE EX01 ( C1 NUMBER,C2 VARCHAR2(10))'); 3 END; 4 / PL/SQLプロシージャが正常に完了しました。 -- -- CREATE TABLEは正常に動作したようです。 -- SQL> INSERT INTO EX01(C1,C2) VALUES(1,'AAA'); 1行が作成されました。 -- -- INSERT文も正常に動作したようです。 -- SQL> BEGIN 2 DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TABLE EX01'); 3 END; 4 / PL/SQLプロシージャが正常に完了しました。 -- -- なんとDROP TABLEも正常に動作したようです。 -- SQL> SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME ='EX01'; レコードが選択されませんでした。 SQL> そうなんです。3行まとめて記載するとエラーとなるのですが、 上記の通りそれぞれ分けて実行すると正常に動作するのです。 これはどこが悪いのでしょうか? 情けない事に、今日の午後はこれでほとんどつぶれてしまいました。 どなたか助けてください。 以上よろしくお願いします。

  • ORACLEで一番最初の結果だけを取る方法

    使っているオラクルのバージョンは8iです。 SQL文を投げて一番上に来る行だけを取得したいのですが、どのようなSQL文を作ればよろしいのしょうか? 例えば下のSQLを投げたらテーブル名全て返ってきますが、結果の一番上の行だけを取得したいのです。 select TABLE_NAME from all_tables よろしくお願いします。

  • 動的SQLのfetch

    動的SQLのfetch やりたいこととしては、下記の(1)のような動的SQLをfetchなりforなりで まわしたいのですがどうしたらいいのでしょうか? よろしくお願いします。 for c_rec in (select table_name from user_tables) loop   v_sql := 'select * from '||rowtbl.table_name; ・・・(1)   ~ 省略 ~ end loop;