ワークスINDEXへ
SELECT文の替わりにBEGIN～ENDブロックを使用する
2000/07/05
以下は、当サイトで公開しているフリーウェア
exp2csv
の readme.txt に書いた話なんですが、
かなり応用範囲が広いので独立して紹介したいと思います。
対象となるRDBMSは
・Sybase Adaptive Server Enterprise (旧Sybase SQL Server)
・Sybase Adaptive Server Anywhere (SQL Anywhere)
・Microsoft SQL Server
・他？？
対象となる言語は、C++, VB, Java, ASP, Access …要するに、接続方法として、
ADO, RDO, DAO, JDBC, ODBC API 等が選択できる言語すべてです。
■SELECT文⇒BEGIN～ENDブロック
ORACLEでは、BEGIN～ENDブロック（無名PL/SQLブロック）を、SQL*Plus
などのネイティブなOCIツール以外から実行する場合、
Oracle Objects for OLE や Oracle JDBC Thin Driver
などのORACLEネイティブなミドルウェアが必要です
（Thin Driverについてはちょと記憶曖昧）。
一方、SQL Server系の言語（Tracsact-SQL, Watcom-SQL）の場合は、
汎用的なミドルウェアからBEGIN～ENDブロックを実行する事ができます。
SSA + Access のパススルークエリーを例にとります。
パススルーの編集画面で SELECT文の替わりに、
begin
select 1
end
とすると、実行結果として 1 が表示されます。BEGIN～END の間は、
当然、以下の様な複数行の手続き型言語が書けるので、
BEGIN
--Transact-SQL例
DECLARE @cnt int
BEGIN
select @cnt = 0
select @cnt
END
END
begin
--Watcom-SQL例
declare cnt integer;
set cnt = 0;
select cnt;
end
これを応用して、ストアドプロシージャのOUTパラメータを
DAOで受け取る方法を考えた人がいました。ブロック内で、
OUTパラメータを変数に代入し、end の前に『SELECT 変数』
とするのです。接続方法が限定されていた頃の話ですから、
これは大変有効なトリックでした。
上記は、Access での話ですが、RDBMSに接続し、SELECT文を投げて
表示を行う様なアプリケーションは、
接続
実行(SQL文の文字列)
表示などの処理
切断
と云った手順で処理系が構築されるはずです。
これまで述べた手法は、RDBMS が SQL Server系で、かつ
SQL文の文字列が動的に変更可能であるならば、
どのようなアプリケーションでも、どのようなミドルウェアでも、
使える技（常識？）なのです。
この手法には留意点が2つあります。
■留意点(1) SELECT文の位置によるトランザクションの中断
BEGIN～ENDブロック内に複数のSELECT文があった場合、表示されるSELECT文は、
ブロック内の最初のSELECT文が適用されます。
以下の例では、最後の select cnt は無視され、結果は 0 と表示されます。
BEGIN
--Transact-SQL例
DECLARE @cnt int
BEGIN
select @cnt = 0
select @cnt
select @cnt = 1
select @cnt
END
END
begin
--Watcom-SQL例
declare cnt integer;
set cnt = 0;
select cnt;
set cnt = 1;
select cnt;
end
これは重要な問題を含んでいます。
それは、途中で無視されるという事は、トランザクションの結果にも影響がある、
と言う点です。以下は SSA の ISQL と 拙作exp2csv で、
同じSQLスクリプトを実行した場合の差異について書いています。
●ISQLで実行した場合と振舞いが同じ
以下の場合、SELECT文は最後尾にのみ書かれているので、exp2csv でもトランザクションはロールバックされます。
begin
delete from tbl where col1 = '0001';
--select count(*) from tbl;
rollback;
select count(*) from tbl;
end
●ISQLで実行した場合と振舞いが異なる！
以下の場合、最初のSELECT文以降は無視されるので、exp2csv では、
プログラム終了時にコミットされ、ロールバックは行われません。
begin
delete from tbl where col1 = '0001';
select count(*) from tbl;
rollback; --この2文は無視される
select count(*) from tbl; --この2文は無視される
end
■留意点(2) 動的な長文の解析
動的にBEGIN～ENDブロック内のステートメントを変更して実行するという事は、
常に長大なSQL文の解析が行われる可能性を意味します。パフォーマンス・チューニング
の観点から見れば好ましくありません。節度ある活用、これが基本です。
ワークスINDEXへ