Dynamic SQL Command

Dynamic SQLでは、仮想プロシージャ内で任意のSQL コマンドを実行できます。DYNAMIC SQLは、実行前に正確なコマンド・フォームがわからない場合に便利です。

Usage

EXECUTE IMMEDIATE <expression>
 AS <variable> <type> [, <variable> <type>]*
    [INTO <variable>]
    [USING <variable>=<expression> [, <variable>=<expression>]*]
    [UPDATE <literal>]

Syntax

<expression>のデータ型はSTRING またはCLOBです。文字列の長さは、 setMaxStringLength(INTEGER)を介して設定された文字列の長さ制限には影響されず、利用可能な空きメモリの量にのみ影響されます；
オプションのAS Clauseは、実行されたSQL文字列によって返される投影シンボル名と型を定義するために使用されます。AS Clauseのシンボルは、実行されたSQL文字列によって返されたシンボルと位置的にマッチします。変換不可能な型や、実行された SQL 文字列によって返される列の数が少なすぎる場合はエラーになります。AS句が指定されない場合、エラーは発生しませんが、結果も返されません；
オプションのINTO句は、動的SQLを指定された一時テーブルに投影します。INTO句を指定すると、動的コマンドはINSERTとQUERY EXPRESSIONのように動作するステートメントを実行します。Dynamic SQL CommandがINTO句で一時テーブルを作成する場合、テーブルのメタデータを定義するためにAS句が必要です；
USING句により、動的SQL文字列に、実行時に指定された値にバインドされる変数参照を含めることができます。これにより、SQL文字列を周囲のプロシージャ変数名や入力名からある程度独立させることができます。動的コマンドUSING句では、各変数は短い名前のみで指定されます。ただし、動的 SQL では、USING変数をDVAR.に完全修飾する必要があります。USING句は、動的SQLで正規表現として使用される値のみです。USING句を使用してテーブル名やキーワードなどを置き換えることはできません。これにより、シンボルの使用は Prepared Statement における通常のバインド式と同等になります。USING句は、必要な文字列操作の量を減らすのに役立ちます。USING句の値にバインドされていない SQL 文字列のUSINGシンボルを参照すると、例外が発生します；
UPDATE句は、updating model countを指定するために使用されます。許容される値は (0, 1, *) です。INTO Clauseが指定されていない場合、0がデフォルト値です。

Examples

Dynamic SQL

以下は、動的SQL文字列のCriteriaを構築する、より複雑なアプローチを示す例です。つまり、仮想プロシージャAccountAccess.GetAccountsはインプットID , LastName , bdayを持ちます。IDに値が指定されている場合、その値のみが動的 SQL 条件で使用されます。それ以外の場合、LastNameに値が指定されると、プロシージャは値が検索文字列かどうかを検出します。LastNameに加えてbdayが指定された場合、LastNameとの複合条件に使用されます。

通常、複雑なCriteriaは手順へのINPUTに基づいて形成されます。この単純な例では、Criteriaはusing Clauseを参照し、SQL文字列がプロシージャの値を直接参照しないようにしています：

BEGIN
...
 
DECLARE string criteria = 'Customer.Accounts.Last = DVARS.LastName';
 
DECLARE string sql_string = 'SELECT ID, First || '' '' || Last AS Name, Birthdate FROM Customer.Accounts WHERE ' || criteria;
 
EXECUTE IMMEDIATE sql_string AS ID integer, Name string, Birthdate date INTO #temp USING LastName='some name';
 
LOOP ON (SELECT ID FROM #temp) AS myCursor
...
 
END

SQL 文字列の実行により、ID , Name , Birthdateのカラムを持つ#tempテーブルが作成されます。また、LastNameに値をバインドするために、USING句があり、これはCriteriaで参照されていることに注意してください。この後、temp TablesはDynamic SQLの値で使用できます。

Dynamic SQL with USING Clause and Dynamically Built Criteria String

BEGIN
...
 
DECLARE string crit = null;
 
IF (AccountAccess.GetAccounts.ID IS NOT NULL)
    crit = '(Customer.Accounts.ID = DVARS.ID)';
 ELSE IF (AccountAccess.GetAccounts.LastName IS NOT NULL)
 BEGIN
            IF (AccountAccess.GetAccounts.LastName == '%')
                ERROR "Last name cannot be %";
 ELSE IF (LOCATE('%', AccountAccess.GetAccounts.LastName) < 0)
                crit = '(Customer.Accounts.Last = DVARS.LastName)';
 ELSE
                crit = '(Customer.Accounts.Last LIKE DVARS.LastName)';
                    IF (AccountAccess.GetAccounts.bday IS NOT NULL)
                        crit = '(' || crit || ' and (Customer.Accounts.Birthdate = DVARS.BirthDay))';
 END
ELSE
    ERROR "ID or LastName must be specified.";
 
EXECUTE IMMEDIATE 'SELECT ID, First || '' '' || Last AS Name, Birthdate FROM Customer.Accounts WHERE ' || crit
    USING ID=AccountAccess.GetAccounts.ID, LastName=AccountAccess.GetAccounts.LastName, BirthDay=AccountAccess.GetAccounts.Bday;
 
...
END

Known Limitations and Workarounds

Dynamic SQL Commandの使用にはいくつかのLIMITがあり、このセクションではその説明と解決策を提供します。

1. Dynamic SQL Command を使用すると、Temp Tables を使用する Assignment Statement が必要になります：

BEGIN
 EXECUTE IMMEDIATE <expression> AS x string INTO #temp;
 DECLARE string VARIABLES.RESULT = (SELECT x FROM #temp);
END

2. Criteriaの一部が存在しないと、適切なCriteriaの構築が面倒になります。例えば、"Criteria "がすでにNULLであった場合、以下の例では、"Criteria "はNULLのままとなります。

BEGIN
    ...
    criteria = '(' || criteria || ' and (Customer.Accounts.Birthdate = DVARS.BirthDay))';
    ...
END

優先的なアプローチとして使用する前に、基準がNULLでないことを確認することをお勧めします。これが不可能な場合は、次の例に示すようにデフォルトを指定するのが良い方法です：

BEGIN
    ...
    criteria = '(' || nvl(criteria, '(1 = 1)') || ' and (Customer.Accounts.Birthdate = DVARS.BirthDay))';
    ...
END

3. intoResultSet動的SQLがUPDATE , DELETE , INSERTコマンドで、AS句を指定する必要がある場合（影響を受ける行数を取得する必要がある場合）、return列の名前と型を指定する必要があります。"AS "句から生成されたDynamic SQL列は、長さ、精度などのデフォルトのプロパティを持ちます。

EXECUTE IMMEDIATE <expression> AS x integer INTO #temp;

この名前は、期待されるUPDATEコマンドシンボル "count "と一致する必要はないことに注意してください。