Oracle Database
動的SQL

.NET Framework C# Language Entity Framework Java Language MySQL PHP Regular Expressions spring SQL Microsoft SQL Server
サーチ…


前書き

動的SQLを使用すると、実行時にSQLクエリコードをアセンブルできます。この手法にはいくつかの欠点があり、非常に注意深く使用する必要があります。同時に、より複雑なロジックを実装することができます。 PL / SQLでは、コードで使用されるすべてのオブジェクトがコンパイル時に存在し、有効である必要があります。そのため、PL / SQLでDDL文を直接実行することはできませんが、動的SQLではこれを実行できます。

備考

いくつかの重要な発言：

  1. 文字列連結を使用してクエリに値を追加したり、代わりにパラメータを使用したりしないでください。これは間違っています： 

    execute immediate 'select value from my_table where id = ' || 
     id_valiable into result_variable;

    そして、これは正しいです： 

    execute immediate 'select value from my_table where id = :P '
    using id_valiable into result_variable;

    これには2つの理由があります。 1つはセキュリティです。文字列連結はSQLインジェクションを可能にします。以下のクエリでは、変数に値1 or 1 = 1が含まれる場合、 UPDATEステートメントはテーブル内のすべての行を更新します。 

    execute immediate 'update my_table set value = ''I have bad news for you'' where id = ' || id;

    2番目の理由はパフォーマンスです。 Oracleは、実行するたびにパラメータなしで問合せを解析しますが、パラメータ付き問合せはセッションで1回のみ解析されます。

  2. データベースエンジンがDDL文を実行するとき、前に暗黙のコミットを実行することに注意してください。

動的SQLで値を選択

ユーザーが異なるテーブルからデータを選択したいとします。テーブルはユーザーによって指定されます。 

 function get_value(p_table_name varchar2, p_id number) return varchar2 is
    value varchar2(100);
  begin
    execute immediate 'select column_value from ' || p_table_name || 
                      ' where id = :P' into value using p_id;
    return value;
  end;

通常どおりこの関数を呼び出します。 

declare
  table_name varchar2(30) := 'my_table';
  id number := 1;
begin
  dbms_output.put_line(get_value(table_name, id));
end;

テストするテーブル： 

create table my_table (id number, column_value varchar2(100));
insert into my_table values (1, 'Hello, world!');

動的SQLに値を挿入する

以下の例は、前の例のテーブルにvalueを挿入します。 

declare
  query_text varchar2(1000) := 'insert into my_table(id, column_value) values (:P_ID, :P_VAL)';
  id number := 2;
  value varchar2(100) := 'Bonjour!';
begin
  execute immediate query_text using id, value;
end;
/

動的SQLの値を更新する

最初の例からテーブルを更新しましょう： 

declare
  query_text varchar2(1000) := 'update my_table set column_value = :P_VAL where id = :P_ID';
  id number := 2;
  value varchar2(100) := 'Bonjour le monde!';
begin
  execute immediate query_text using value, id;
end;
/

DDL文を実行する

このコードはテーブルを作成します： 

begin
  execute immediate 'create table my_table (id number, column_value varchar2(100))';
end;
/

匿名ブロックを実行する

無名ブロックを実行できます。この例は、動的SQLから値を返す方法も示しています。 

declare
  query_text varchar2(1000) := 'begin :P_OUT := cos(:P_IN); end;';
  in_value number := 0;
  out_value number;
begin
  execute immediate query_text using out out_value, in in_value;
  dbms_output.put_line('Result of anonymous block: ' || to_char(out_value));
end;
/


Modified text is an extract of the original Stack Overflow Documentation
ライセンスを受けた CC BY-SA 3.0
所属していない Stack Overflow