動的SQLの実行

概要

標準的なSQLの仕様として、CRUD構文のテーブル部に変数を指定することはできません。

これはDV（Data Virtualization）でも同様です。以下の例は受注をいくつかのスレッドに分類して、異なるデータソースに格納したいケースですが、このようにINSERT INTOの引数に変数を指定するとエラーとなります。

PROCEDURE InvalidDynamicallyExecute(IN threadid NUMERIC, IN orderid NUMERIC)
BEGIN
  DECLARE dsrc VARCHAR;
  SET dsrc = '/shared/examples/ds_orders/tutorial/orders_' || CAST(threadid AS VARCHAR); 
  INSERT INTO dsrc VALUES(orders);
END

DVではこれを解決するための方法を提供していますので、今回ご紹介いたします。

動作確認環境

１．INSERT / UPDATE / DELETEの場合

DVではINSERT、UPDATE、DELETEの各構文を動的に扱うためのステートメント、EXECUTE IMMEDIATEが用意されています。

EXECUTE IMMEDIATE <valueExpr>

<valueExpr>には文字列型で最終的なステートメントの全文を指定します。

したがって上記の例はこのようにすることで動作します。

PROCEDURE ValidDynamicallyExecute(IN threadid NUMERIC, IN orderid NUMERIC)
BEGIN
 EXECUTE IMMEDIATE 'INSERT INTO /shared/examples/ds_orders/tutorial/orders_' || CAST(threadid AS VARCHAR) || '(orderid) VALUES(' || CAST(orderid AS VARCHAR) || ')';
END

ただし一般的には次のように<valueExpr>を変数とし、可読性や保守性を高めます。

PROCEDURE ValidDynamicallyExecute(IN threadid NUMERIC, IN orderid NUMERIC)
BEGIN
  DECLARE stmt VARCHAR;
  SET stmt = 'INSERT INTO /shared/examples/ds_orders/tutorial/orders_' || CAST(threadid AS VARCHAR) || '(orderid) VALUES(' || CAST(orderid AS VARCHAR) || ')';
  EXECUTE IMMEDIATE stmt;
END

２．SELECTの場合

OPENステートメントを使用します。

OPEN <cursorVariableName> FOR <valueExpression>

取得したカーソル変数を利用してプロシージャの外部へ返したり、スクリプトの内部で活用することができます。

例：

PROCEDURE ValidDynamicallyExecuteSelect(
 IN threadid NUMERIC,
 OUT cur CURSOR(orderid NUMERIC)
)
BEGIN
 DECLARE stmt VARCHAR;
 SET stmt = 'SELECT orderid FROM /shared/examples/ds_orders/tutorial/orders_' || CAST(threadid AS VARCHAR);
 OPEN cur FOR stmt;
END

関連情報

例 6 (個別の Inset による動的 SQL 抽出)

例 7 (変数名による動的 SQL 挿入)