本稿はデータベースソフトウェア「SQL Anywhere」およびデータベース全般に関する英語ドキュメントを翻訳する形で提供しています。図など、部分的に英語のままになっていますが、製品のSQL Anywhere自体は完全に日本語化されていますのでご安心ください。
WITH RESULT SET構文が導入されたのは、2003年11月にリリースされたSQL Anywhere 9.0.1のEBF（ビルド1699）でした。WITH RESULT SET構文は、ストアドプロシージャが出力パラメータを持つ場合に生じる、ある特定の問題を回避するために開発されました。
ストアドプロシージャに出力パラメータがある場合、アプリケーションに結果セットも返すということができません。問題が起きるのは、出力パラメータを持つプロシージャがEXECUTE IMMEDIATE文を利用してネストしたプロシージャを呼び出す場合です。この場合、SQL Anywhereは両方のプロシージャが結果セットを返すものと想定しますが、そうすると外側のプロシージャが持つoutパラメータとの不整合が生じます。そのため、アプリケーションが外側のプロシージャを呼び出そうとすると失敗します。
この問題を解決するため、EXECUTE IMMEDIATE文とCREATE PROCEDURE文の双方に新しい構文が開発されました。今回はEXECUTE IMMEDIATE文に絞って解説したいと思います。この「新しい」構文は次のとおりです。
EXECUTE IMMEDIATE [ WITH RESULT SET ON | OFF ] string-expression
デフォルトの設定はWITH RESULT SET OFFです。
ちなみにISO SQL標準では、EXECUTE IMMEDIATEは結果セットを返す文を受け付けません。
これが重要である理由
WITH RESULT SET句はEXECUTE IMMEDIATE文の実行には何の影響も与えませんが、この句を含むプロシージャをサーバーが正しく定義できるようにする働きをします。WITH RESULT SETはWatcom-SQL方言でのみ使用可能です。対応する次のTransact-SQL構文では使用できません。
EXECUTE ( string-expression )
WITH RESULT SET ONとWITH RESULT SET OFFの指定は、プロシージャ作成時と実行時の両方に影響します。
このことを実証するために、次のようなプロシージャを作成してみましょう。
CREATE OR REPLACE PROCEDURE test_result_clause()
BEGIN
EXECUTE IMMEDIATE WITH RESULT SET OFF 'SELECT 1;';
END
この新しいプロシージャについてSQL Anywhereのカタログテーブルをクエリすると、次の結果が得られます。
ご覧のとおり、このカタログのクエリで返された結果セットは1行からなり、SYSPROCPARMから取得されるparm_nameなどのカラム値はnullになっています。これは、プロシージャ内にRESULT SET句がないため、このプロシージャで実際に結果セットが生成されるかどうかをサーバーが見極めようとした結果です。先ほどのEXECUTE IMMEDIATE文は結果セットの生成を指定しないので、サーバーは「結果セットを返さない」というデフォルトの設定に従います。そこで、サーバーは「このプロシージャには結果セットのカラムがない」と定義するため、SYSPROCPARMにはこのプロシージャに関するローが存在しなくなるわけです。
また、このプロシージャのCALLに対するDESCRIBEは結果カラムを返しません。もしもESQLアプリケーションが、カーソルをオープンするのか文を実行するのかをこの情報に基づいて判断した場合には、文の実行が選択されることになり、エラーが発生します。
それでは、先ほどのプロシージャを次のように宣言し直したとしましょう。
CREATE OR REPLACE PROCEDURE test_result_clause()
BEGIN
EXECUTE IMMEDIATE WITH RESULT SET ON 'SELECT 1;';
END
同じカタログに対するクエリが、今度は次のような結果を返します。
今回は、このプロシージャに対するローがSYSPROCPARM内に確かに存在します。このプロシージャはEXECUTE IMMEDIATE文を使っているため、サーバーはどのような結果セットになるかは分からないものの、結果セットが返されることだけは分かっているので、それを示すためにダミーの結果セットカラムを定義するわけです。
上記の例から、WITH RESULT SET句によってプロシージャの定義が変わること、またそれによってプロシージャをコールするアプリケーションが影響を受けることが分かります。APIの中には、例えばODBCのように、PREPARE-DESCRIBE-EXECUTE-OR-OPENという複合要求を使うことで、結果セットの有無に応じて文を実行するかオープンするかを決めるものもあります。文がオープンされる場合は、APIまたはアプリケーションが、システムのカタログに含まれているプレースホルダに頼るのではなく、DESCRIBE CURSOR文を発行することで実際の結果セットを決定します。現在、DBISQLとDBISQLCはどちらもこの方法をとっているので、どちらのコマンドでもプロシージャのCALLがうまくいきます。しかし、任意の文への対処方法をDESCRIBEの結果によって決めるアプリケーションではうまくいきません。
DBAは、要求レベルのログにあるストアドプロシージャを見れば、サーバーが始めにEXECUTEを試行し、そしてその後OPENを試行したことが分かるでしょう。
バッチの対処
実行時では、サーバーは、WITH RESULT SET ONが指定されていれば、結果セットを返すEXECUTE IMMEDIATE文を処理できます。''しかし''、WITH RESULT SET OFFが指定されている場合や、この句そのものが抜けている場合でも、サーバーは依然として、解析された文字列で最初にくる文のタイプに着目します。もしその文がSELECT文であれば、''結果セットが返されます''。
今度はプロシージャを次のように書き換えたとします。
CREATE OR REPLACE PROCEDURE test_result_clause()
BEGIN
EXECUTE IMMEDIATE WITH RESULT SET ON 'begin declare v int; set v=1; select v; end';
END
予想通り、CALL test_result_clause()は成功します。
ところが、先ほどのプロシージャを次のように変えると、
CREATE OR REPLACE PROCEDURE test_result_clause()
BEGIN
EXECUTE IMMEDIATE WITH RESULT SET OFF 'begin declare v int; set v=1; select v; end';
END
CALL test_result_clause()は次のエラーを返します。
最後にもう一つ。クライアント上のステートメントキャッシュがSQL Anywhereバージョン10.0.1で導入されたことにより、DESCRIBEが不正な結果を返す可能性が出てきました。「結果セットを持たない」とされていたSQL文が、後になって「結果セットを持つ」と評価される場合があるのです。上記の例でも、プロシージャ定義の変更によってこの問題が起きる可能性があります。この問題は、JDBCまたはODBC接続でクライアントのステートメントキャッシュを無効にすれば防止できます。そのためにはサーバーに接続後、直ちにmax_client_statements_cached接続オプションを0（ゼロ）に設定します。
私にWITH RESULT SETのセマンティクスを詳しく解説してくれた同僚のBruce Hay氏とIan McHardy氏に感謝します。
