SAK 図書館

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

Oracle PL/SQL 編9 - 動的 SQL、EXECUTE IMMEDIATE、using、into、returning

以前は、DBMS_SQL パッケージで動的 SQL を使用していたが、
最近は、EXECUTE IMMEDIATE を使用すると簡単である。
尚、解析のオーバーヘッドはかなりある。

■動的SQL - EXECUTE IMMEDIATE

  ・単純な SQL 発行は、次のようする。

declare
begin

  execute immediate 'create table test (data1 number, data2 number)';

end;
/

declare
begin

  execute immediate 'drop table test';

end;
/

  ・オブジェクト名の可変指定にバインド変数は許されないようである。
    次のように文字列の結合で使用する。
    (create table :tbl1 ... では動作しない。)

declare
  tbl1 varchar2(40);
  sql1 varchar2(2000);
begin

  tbl1 := 'test';
  sql1 := 'create table ' || tbl1 || ' (data1 number, data2 number)';
  execute immediate sql1;

end;
/

  ・バインド変数を使用した SQL 発行は、次のようする。

declare
  sql1 varchar2(2000);
begin

  sql1 := 'insert into test values (:data1, :data2)';
  execute immediate sql1 using 100, 200;

end;
/

  ・バインド変数をパラメタで受け取れば、汎用性が高いコードにできる。
    この例は、サンプルなので直接代入している。

declare
  data1 number;
  data2 number;
  sql1 varchar2(2000);
begin

  data1 := 100;
  data2 := 200;
  sql1 := 'insert into test values (:data1, :data2)';
  execute immediate sql1 using data1, data2;

end;
/

  ・select の結果をレコード変数に取得もできる。
    (単一行を戻さないとエラーになる。)

set serveroutput on;

declare
  test_rec test%rowtype;
  sql1 varchar2(2000);
begin

  sql1 := 'select * from test where data1 = :data1';
  execute immediate sql1 into test_rec using 100;
  dbms_output.put_line ('-- 取得データ --');
  dbms_output.put_line (test_rec.data1);
  dbms_output.put_line (test_rec.data2);

end;
/

  ・select の結果を複数行取得もできる。
    この場合は、open for と fetch を使用する。
    ただ、「fetch cv into item1;」、「fetch cv into rec;」の型チェックが
    厳しく、項目やレコードを動的に指定できない。
    何か方法を考えないと、限定された使い方しかできない。

set serveroutput on;

declare
  type cutype is ref cursor;
  cv cutype;
  item1 テストm.キー%type;
begin

  open cv for
    'select キー from テストm where キー like :data1'
    using 'a%';

  loop
    fetch cv into item1;
    exit when cv%notfound;
    dbms_output.put_line (item1);
  end loop;

  close cv;

end;
/

set serveroutput on;

declare
  type cutype is ref cursor;
  cv cutype;
  rec テストm%rowtype;
begin

  open cv for
    'select * from テストm where キー like :data1'
    using 'a%';

  loop
    fetch cv into rec;
    exit when cv%notfound;
    dbms_output.put_line (rec.キー);
  end loop;

  close cv;

end;
/

  ・update の更新結果を取得することもできる。

set serveroutput on;

declare
  sql1 varchar2(2000);
  ret1 number;
begin

  sql1 := 'update test set data2 = :data2 where data1 = :data1
    returning data2 into :ret1';
  execute immediate sql1 using 11, 100 returning into ret1;
  dbms_output.put_line ('-- 取得データ --');
  dbms_output.put_line (ret1);

end;
/

■Oracle PL/SQL 編資料
■SQL 基礎実地編資料
■SQL 基礎編資料
■SQL チューニング編資料
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索



Copyright (C) 1999-2017 Y.SAK