• ベストアンサー
  • すぐに回答を!

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

参考の通りに、ストアドプロシージャ作成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 ;

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

  • 回答数1
  • 閲覧数892
  • ありがとう数12

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

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

stored function からは procedure 経由でも prepared 文は発行できないようです。function自体がprepared に近い機構で動作してて多重prepareは出来ないんだとかいう理由だったような?細かい理由は英文マニュアル参照。 とりあえず、日本語マニュアルには、一文ですまされてますが。 http://dev.mysql.com/doc/refman/5.1-olh/ja/sql-syntax-prepared-statements.html 後半に以下の一文 「準備済みステートメントの SQL 構文はストアドプロシージャー内で利用できますが、ストアドファンクションやトリガー内では利用できません。」 procedure 側へ、データ取りだしSQL文も渡して、procedure内で完結させるしかないのでは? procedure の連鎖は可能なんだし。 それとも、以下の記事のように、 Gearman MySQL UDF をインストールしたうえでさらに、別アプリケーションから実行とか? http://d.hatena.ne.jp/heavenshell/20100610/1276161261 上記は、自前MySQLサーバーでないと無理かと思うけど、どういう状況における何のログなのかによっても、取り得る方法は変わってくるでしょう。

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

質問者からのお礼

ご回答ありがとうございます。 既存環境の障害で異常終了をすることが判明し、 原因を探るため、ストアドファンクション内でのログ出力を考えていました。 異常終了の問題となった箇所は、phpからsql文を発行し、 ストアドファンクションを呼び出している処理でした。 ご教示の通り、多重prepared はできないため、この方法は諦めました。 問題については、 ある条件の場合のみ落ちることが解っていたため、 コードの解析により解決しました。 原因は未初期化変数の参照によるものでした。 >http://d.hatena.ne.jp/heavenshell/20100610/1276161261 自前の環境でないので、 どうしようもない状況の最後の切り札として提案しようと思います。

関連するQ&A

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

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

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

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

  • ストアドについて質問

    質問をご覧いただきまして有難うございます。 初めてストアドプロシージャを作成しており、サンプルでいろいろと作ってみようと思い、作成コードを実行しようとしたんですが、以下のようなメッセージが表示され、正常に実行できません。 ORA-00001: 一意制約(OES4.PK_SITT_IKO_LOG)に反しています ORA-06512: "OES2.PRA002",行16 ORA-06512: 行4 以下はストアドです。 CREATE OR REPLACE PROCEDURE PRACTICE001 IS BEGIN DECLARE --ループ CNT NUMBER; BEGIN CNT := 0; LOOP EXIT WHEN CNT >= 10; CNT := CNT + 1; insert into LOG_TBL values(systimestamp,'test','comments are...'); END LOOP; END; END; テーブル(LOG_TBL)定義は次のようになっています。 LOG_TIME timestamp NOT NULL COMMENT1 varchar2(20) COMMENT2 varchar2(200) タイムスタンプであれば重複もないと思うんですが。。。 原因がよくわからないです。。。 宜しくお願い致します!

  • 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
  • ストアドファンクションのエラーについて

    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★ CREATE OR REPLACE TYPE TBL_NUM10 AS VARRAY(10) OF NUMBER(10); CREATE OR REPLACE PACKAGE TEST_PKG IS TYPE TBL_NUM10 IS VARRAY(10) OF NUMBER(10); PROCEDURE HAIRETU(O_CNT OUT TBL_NUM10); END TEST_PKG; / CREATE OR REPLACE PACKAGE BODY TEST_PKG IS PROCEDURE HAIRETU(O_CNT OUT TBL_NUM10) IS TMP_CNT TBL_NUM10; BEGIN TMP_CNT := TBL_NUM10(0,0,0,0,0,0,0,0,0,0); TMP_CNT(1) := 1; TMP_CNT(2) := 2; TMP_CNT(3) := 3; O_CNT := TMP_CNT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END HAIRETU; END TEST_PKG; / DECLARE O_CNT TBL_NUM10; BEGIN TEST_PKG.HAIRETU(O_CNT); DBMS_OUTPUT.PUT_LINE(O_CNT(1)); DBMS_OUTPUT.PUT_LINE(O_CNT(2)); DBMS_OUTPUT.PUT_LINE(O_CNT(3)); END; / ★実行結果★ SQL実行中に以下のエラーが発生しました。 ORA-06550: 行4、列4: PLS-00306: 'HAIRETU'の呼出しで、引数の数または型が正しくありません。 ORA-06550: 行4、列4: PL/SQL: Statement ignored よろしくお願いします。

  • ストアド内で実行した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のストアドで以下のことを実行したいです。 1.aユーザのaテーブルにあるユーザ情報(=スキーマ名になっています)を取得 2.取得したユーザ情報(=スキーマ名)を元にスキーマ.テーブル名で  テーブルの情報を参照する 3.スキーマ.テーブル名で参照したテーブルの情報を元に  他のテーブルのデータを参照する 1については取得できたのですが、2以降を別ストアドで作成して スキーマ名を引き渡そうかと思ったのですが、実行すると 「表またはビューが存在しません」と表示されます。 実現できる方法をご教授ください。 CREATE OR REPLACE PROCEDURE del_datatbl(con_user IN VARCHAR2, del_date IN DATE) IS TYPE cur_typ IS REF CURSOR; v_inv_cursor cur_typ; -- Declare a cursor variable v_inv_query VARCHAR2(200); v_inv_num NUMBER; v_inv_cust VARCHAR2(20); v_inv_amt NUMBER; w_date DATE; BEGIN IF(del_date IS NULL) THEN w_date := ADD_MONTHS(SYSDATE,-18); ELSE w_date := del_date; END IF; v_inv_query := 'SELECT clm_mjres_MSEQ FROM ' || con_user || '.T_MNGJRES' || ' WHERE TO_CHAR(CLM_MJRES_DATE,'|| 'YYYY/MM/DD' || ') <= TO_CHAR(w_date,' || 'YYYY/MM/DD' || ')'; DBMS_OUTPUT.PUT(v_inv_query); OPEN v_inv_cursor FOR v_inv_query USING v_inv_num; LOOP FETCH v_inv_cursor INTO v_inv_num; DBMS_OUTPUT.PUT(v_inv_num); EXIT WHEN v_inv_cursor%NOTFOUND; END LOOP; CLOSE v_inv_cursor; END; . run show errors

  • php×postgresでのストアド利用について

    postgresで作成済みの手続き(ストアド)をphpから呼び出し結果を取得したいです。 ■スキーマ CREATE SCHEMA test AUTHORIZATION postgres; ■テーブル CREATE TABLE test.tbl1 ( item1 char(1), item2 int4 ) WITHOUT OIDS; ■ストアド CREATE OR REPLACE FUNCTION test.proc1(IN in_para1 int2, OUT ot_para1 date, OUT ot_para2 date, OUT ot_para3 date) AS $BODY$declare begin select current_date + in_para1 into ot_para1; select tablename from pg_tables into ot_para2; select * from test.tbl1 into ot_para3; end;$BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION test.proc1(IN in_para1 int2, OUT ot_para1 date, OUT ot_para2 date, OUT ot_para3 date) OWNER TO postgres; サンプル的に下準備をしました。 この後、php側でこのストアドを呼び出すコードを教えて下さい。

    • ベストアンサー
    • PHP
  • ストアドファンクションを差し替えたい

    VIEW は、CREATE OR REPLACE VIEW のようにして差し替えることができますが、 ストアドファンクションは、このような差し替えには対応していないのでしょうか? 本番稼動しているデータベースのストアドファンクションを差し替えたい場合は、 BEGIN DROP FUNCTION CREATE FUNCTION COMMIT とすればよろしいでしょうか?