SAK 図書館
SAK 図書館
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 編資料
Copyright (C) 1999-2018 Y.SAK