クエリ内部でDML操作を実行できない"の解決策？

私はデータ分析ツールを使っていますが、ユーザーから値を受け取り、それをパラメータとして渡し、テーブルに保存することが要件でした。かなり単純なので、私はこれを書くために座っていました。

create or replace
procedure complex(datainput in VARCHAR2)
is
begin
insert into dumtab values (datainput);
end complex;

これをSQL Developerで、次のようなステートメントを使って実行しました。

begin
complex('SomeValue');  
end;

問題なく動作し、テーブルに値が挿入されました。しかし、上記のステートメントはデータ分析ツールではサポートされていないため、代わりに関数を使用することに頼りました。以下はその関数のコードですが、コンパイルはできています。

create or replace
function supercomplex(datainput in VARCHAR2)
return varchar2
is
begin
insert into dumtab values (datainput);
return 'done';
end supercomplex;

もう一度、SQL Developerで実行してみましたが、以下のコードを実行すると、cannot perform a DML operation inside a queryと表示されました。

select supercomplex('somevalue') from dual;

私の質問は

  • SQL Developer**で上記の関数を実行できるステートメントが必要です。
  • 私が探しているものを実行できる関数で、select文で実行可能なもの。
  • PL/SQLの初心者なので、ルールや構文がよくわかりません。

P.S. これがC++かJavaだったらどんなによかったか :(

EDIT

DMine（ツール）で実行する前に、SQL Developer上で関数を実行して、それが有効かどうかをテストする必要があるからです。SQLで無効なものはDMineでも無効ですが、その逆はありません。

ありがとうございました。状況を理解し、なぜそれが違法/推奨されないのかがわかりました。

Joshua1729
編集された質問 5日 1月 2012 в 7:50
プログラミング
sql oracle function stored-procedures plsql
4日 1月 2012 в 3:20
1ビュー
ソリューション

pragma autonomous_transaction` というディレクティブを使用することができます。これは、ORA-14551 を発生させずに DML を実行することができる独立したトランザクションの中で関数を実行します。

自律トランザクション]1は独立しているので、DMLの結果は親トランザクションの範囲外でコミットされることに注意してください。ほとんどの場合、これは許容できる回避策ではありません。

SQL> CREATE OR REPLACE FUNCTION supercomplex(datainput IN VARCHAR2)
  2     RETURN VARCHAR2 IS
  3     PRAGMA AUTONOMOUS_TRANSACTION;
  4  BEGIN
  5     INSERT INTO dumtab VALUES (datainput);
  6     COMMIT;
  7     RETURN 'done';
  8  END supercomplex;
  9  /

Function created

SQL> SELECT supercomplex('somevalue') FROM dual;

SUPERCOMPLEX('SOMEVALUE')
--------------------------------------------------------------------------------
done

SQL> select * from dumtab;

A
--------------------------------------------------------------------------------
somevalue

そもそもなぜこのエラーが発生するのかについては、Tom Kyte has a nice explanation があります。行の処理順序に依存する可能性があるため、安全とは言えません。さらに、Oracle はこの関数が行ごとに少なくとも1回、多くても1回実行されることを保証していません。

Vincent Malgrat
編集した答え 4日 1月 2012 в 4:29
解説 (0)

例えば戻り値を受け取るための変数を宣言するだけです。

declare
    retvar varchar2(4);
begin
    retvar := supercomplex('somevalue');
end;

selectが機能しないのは、関数が挿入を実行しているからです。

John Doyle
編集した答え 4日 1月 2012 в 5:45
解説 (1)

戻り値を無視するために、ダミーの if ... end if; 文の中で関数を実行するだけです。

exec if supercomplex('somevalue') then null; end if;

または、戻り値を出力する put_line プロシージャのパラメータとして実行します。


exec dbms_ouput ('result of supercomplex='||supercomplex('somevalue')) を実行します。

スーパーコンプレックスの結果=done
解説 (0)