動的パラメタライズド・クエリの仕様

「Open棟梁 wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。

• 戻る

目次 †

概要 †

用語 †

テキスト内パラメタ †

• パラメタライズド・クエリのパラメタ。
• タグ内の文字列の先頭にあるパラメタライズド・クエリのパラメタをテキスト内パラメタとして認識する。
• パラメタ値には、パラメタライズド・クエリのパラメタとして有効なパラメタ値を設定する。

タグ内パラメタ †

• XMLタグのname属性に指定するパラメタ。

• パラメタ値にtrue、false、nullを許可しており、
  • trueが設定されるとタグが有効になる。
  • falseやnullが設定されるとタグが無効になる。
  • true、false、null以外の値が設定された場合エラーとなる。

XMLタグ †

ROOTタグ †

ルートのタグ

タグ表記 †

<ROOT>
    ・・・クエリ定義・・・
</ROOT>

説明 †

クエリ定義は全て、このタグの中に収める。

ネスト †

ROOTタグ以外の全てのタグをネストできる。

VALタグ †

パラメタとして設定した文字列に置換される。

タグ表記 †

<VAL name="xxx"/> 

説明 †

• 任意の文字列挿入が可能のため、どのようなパターンのSQL整形にも対応可能。
  

• タグ内パラメタに文字列を設定するとVALタグが、設定した文字列で置換される。
  パラメタ値にnullが設定された場合、パラメタが設定されない場合は、無効になりVALタグが削除される。

• SQLインジェクションが可能であるため、ユーザ入力をVALタグに直接指定しないようにする
  （直接指定する場合は、シングル クォーテーションのエスケープなどを施すこと）。

ネスト †

タグのネストは不可能。

IFタグ †

最もよく使うタグで、基本的にWHERE句の条件を動的化する目的で使用するが、それ以外の場所・目的でも利用可能。

タグ表記 †

• テキスト内パラメタ or タグ内パラメタ
  ※ 両方のパラメタが指定された場合、テキスト内パラメタが優先される。

• 例１: テキスト内パラメタ

  <IF>
      ・・・[演算子] 条件式・・・
  </IF>

• 例２: タグ内パラメタ

  <IF name="xxx">
      ・・・[演算子] 条件式・・・
  </IF>

• ELSEタグの利用例

• 例１：

  <IF>AND XXX=@P1<ELSE>AND XXX IS NULL</ELSE></IF>

• 例２：

  <IF name="xxx">AND XXX IS NOT NULL</IF>

説明 †

通常WHERE句内の条件式を囲うことで、条件式の有効・無効を制御する。

• テキスト内パラメタに、
  • パラメタが設定された場合、IFタグとパラメタが有効になり、
  • パラメタが設定されない場合は、IFタグとパラメタが無効になる。

• タグ内パラメタに、
  • trueが設定されるとIFタグが有効になる。
  • falseが設定されると、IFタグが無効になり、
    • ELSEタグが無い場合はエラーになる。
    • ELSEタグがある場合は、ELSEタグが有効になる。

• テキスト内パラメタ・タグ内パラメタのどちらでも、nullが設定された場合、
  • ELSEタグが無い場合はエラーになる。
  • ELSEタグがある場合は、ELSEタグが有効になる。

ネスト †

ELSEタグ、VALタグのみネスト可能。

ELSEタグ †

IFタグに対応した、XXX IS NULL，XXX IS NOT NULLなどの条件式を記述する。

タグ表記 †

<ELSE>
    ・・・[演算子] 条件式・・・
</ELSE>

• 例１：

  <IF>AND XXX=@P1<ELSE>AND XXX IS NULL</ELSE></IF>

• 例２：

  <IF name="xxx">AND XXX IS NOT NULL<ELSE>AND XXX IS NULL</ELSE></IF>

説明 †

• IFタグのパラメタ設定でELSEタグが有効になる場合の条件式を記述できる。
• テキスト内パラメタ・タグ内パラメタのどちらも記述できない。

ネスト †

VALタグのみネスト可能。

SELECT-CASE-DEFAULTタグ †

タグ内パラメタを使用して、SELECT-CASE-DEFAULTする。

タグ表記 †

<SELECT name="xxx">
    <CASE value="A">・・・</CASE>
    <CASE value="B">・・・</CASE>
    <DEFAULT>・・・</DEFAULT>
</SELECT>

※ DEFAULTタグは省略可能

説明 †

SELECTタグにタグ内パラメタを指定する。

• 指定されたパラメタ値によって、任意のCASEタグ、若しくはDEFAULTタグ中のステートメントを有効にする。
  • パラメタ値が、CASEタグのvalue属性中の文字列に一致する場合、当該CASEタグの中のステートメントを有効にする。
  • 上記に合致するCASEタグが無い場合は、DEFAULTタグ中のステートメントを有効にする。
  • nullが設定された場合、パラメタが設定されない場合は、無効になりSELECTタグが削除される。

ネスト †

VALタグのみネスト可能。

LISTタグ †

IN句の条件式を囲うことで、パラメタリストの指定と、IN句の有効・無効を制御する。

タグ表記 †

<LIST>
    ・・・[演算子] IN句の条件式・・・
</LIST>

説明 †

• テキスト内パラメタを指定する。テキスト内パラメタが無い場合は、エラーとなる。

• パラメタ値は基本的にArrayList?型かList<T>型で設定する（パラメタが１つの場合は、値型でも設定可）。
  • 実行時のパラメタは、@パラメタ名_1, @パラメタ名_2, @パラメタ名_3 ・・・と展開される。
  • 要素数が０の場合、nullが設定された場合、パラメタが設定されない場合は、無効になりタグが削除される。

ネスト †

VALタグのみネスト可能。

JOINタグ †

JOIN句をJOINタグで囲うことで、JOIN句の有効・無効を制御する。

タグ表記 †

<JOIN name="xxx">
    ・・・JOIN句・・・
</JOIN>

説明 †

タグ内パラメタを指定する。

• JOIN句を有効にする場合は、パラメタ値にtrueを設定する。
• JOIN句を無効にする場合は、パラメタ値にfalse、nullが設定する。

ネスト †

ROOT、PARAM、DIVタグ以外の全てのタグをネストできる。

SUBタグ †

WHERE句内で使用するサブクエリの条件式をSUBタグで囲うことで、サブクエリの条件式の有効・無効を制御する。

タグ表記 †

<SUB name="xxx">
    ・・・[演算子] サブクエリの条件式・・・
</SUB>

説明 †

タグ内パラメタを指定する。

• サブクエリの条件式を有効にする場合は、パラメタ値にtrueを設定する。
• サブクエリの条件式を無効にする場合は、パラメタ値にfalse、nullが設定する。

ネスト †

ROOT、PARAM、DIVタグ以外の全てのタグをネストできる。

WHEREタグ †

WHERE句をWHEREタグで囲うことで、

• IF，LIST，SUBタグの処理でWHERE句内に条件式が無くなる場合や、
• 「WHERE AND 条件式」などの不正な状態になり得る場合、

不要なWHERE句や演算子（AND、OR）が削除されるようになる。

タグ表記 †

<WHERE>
    ・・・WHERE句・・・
</WHERE>

説明 †

• 【WHERE 句の削除】
  SELECT<WHERE>WHERE</WHERE> → SELECT

• 【演算子（AND）の削除】
  SELECT<WHERE>WHERE AND 条件式</WHERE> → SELECT　WHERE 条件式

• 【演算子（OR）の削除】
  SELECT<WHERE>WHERE OR 条件式</WHERE> → SELECT　WHERE 条件式

• 【制限事項】
  以下のケースでは、SELECTとはならない。
  • SELECT<WHERE>WHERE OR</WHERE>
  • SELECT<WHERE>WHERE AND</WHERE>

（・・・このため、IFタグは、・・・[演算子] 条件式・・・としている。）

ネスト †

ROOT、PARAM、DIVタグ以外の全てのタグをネストできる。

INSCOLタグ †

• INSERT句のカラム リスト専用に設計されている。
• 通常は、自動生成されたSQLでのみ利用する。ユーザは、基本的に使用しない。

タグ表記 †

<INSCOL name="xxx">
    InsertColumn , 
</INSCOL>

説明 †

• INSERT句のカラム リスト内のカラム情報を囲い、
  IFタグでINSERT句のVALUESパラメタ リストを囲えば、動的なINSERT文を作成できる。

• タグ内パラメタを指定する。
  • タグ内パラメタと同じ名前のパラメタが設定されている場合は、カラムを有効にする。
  • タグ内パラメタと同じ名前のパラメタが設定されていない場合は、カラムを無効にする。

ネスト †

VALタグのみネスト可能。

DELCMAタグ †

• UPDATE句のカラム リスト専用に設計されている。
• 通常は、自動生成されたSQLでのみ利用する。ユーザは、基本的に使用しない。

タグ表記 †

<DELCMA>
    ・・・カンマ区切りのリスト・・・
</ DELCMA >

説明 †

カンマ区切りのリストをプログラムで生成した場合、
生成した文字列の前・後に余分に付与されているカンマを削除する

【カンマの削除】

, aaa, bbb, ccc, ddd, eee, fff, ggg,　→ aaa, bbb, ccc, ddd, eee, fff, ggg

ネスト †

ROOT、PARAM、DIVタグ以外の全てのタグをネストできる。

PARAMタグ †

動的パラメタライズド・クエリ分析ツールを使用し、
PARAMタグ間に指定したパラメタ値で動的パラメタライズド・クエリをテスト実行する。

タグ表記 †

<PARAM>
    ・・・パラメタ情報・・・
</PARAM>

説明 †

パラメタの記述ルールを以下に示す。

• 【ユーザ定義パラメタ（VALタグ）】
  • ルール：パラメタ名, 置換する文字列<DIV/>
  • 例：

    P1, xxx<DIV/>

• 【通常のパラメタ】
  • ルール：パラメタ名, 型を表す文字列, 値を表す文字列<DIV/>
  • 例：

    P1, String, xxx<DIV/>
    P2, Int32, 123<DIV/>

• 【配列のパラメタ】
  • ルール：パラメタ名, .NET型を表す文字列[], カンマ区切りの値のリスト<DIV/>
  • 例：

    P1, String[], xxx, yyy, zzz<DIV/>
    P2, Int32[], 123, 456, 789<DIV/>

• 【ArrayList?型のパラメタ】
  • ルール：パラメタ名, 型を表す文字列, カンマ区切りの値のリスト<DIV/>
  • 例：値のリストが２つ以上の場合、ArrayList?型のパラメタとなる。

    P1, String, xxx, yyy, zzz<DIV/>

• 【DBNull型のパラメタ】
  • ルール：パラメタ名, DBNull[, 任意]<DIV/>
    ※ 「値を表す文字列」は有っても無くてもよい。

• 【nullのパラメタ】
  • ルール：パラメタ名, （任意）, null<DIV/>
    ※ 「型を表す文字列」は有っても無視される。

上記の「型を表す文字列」・「値を表す文字列」については、下記を参照のこと。

ネスト †

DIVタグのみネストできる。

DIVタグ †

タグ表記 †

<DIV/>

説明 †

PARAMタグ内で、パラメタを区切るために使用する。

ネスト †

ネストは不可能。

PARAMタグの型表現 †

DBNullの補足 †

• DBのフィールドにnull値 を設定する場合Insert、Updateのクエリのパラメタに「DBNull」設定する。
• Where句の条件のパラメタには、「DBNull」を使用できないので注意する（通常、SQL上でIS NULLと指定する）。

• 指定例
  • P1 , DBNull ,
  • P1 , DBNull , xxx
  • P1 , DBNull , yyy

※ xxx, yyyの値はあっても無視される。

null値の補足 †

動的パラメタライズド・クエリのパラメタに「null」を使用した場合は、
タグを無効にするような動作になる（IFタグ-ELSEタグタグでは、ELSEタグが有効になる）。

• 指定例
  • P1 , , null
  • P1 , xxx , null
  • P1 , yyy , null

※ xxx, yyyの値はあっても無視される。

補足 †

補足１ †

• タグ処理に利用された後の扱い
  • テキスト内パラメタは、タグの処理後に（パラメタのコレクションに）そのまま残される（静的なSQLの実行に使用され、複数個所に作用するため）。
  • タグ内パラメタは、タグの処理後に（パラメタのコレクションから）消去される（生成された静的なSQLの実行には不要なパラメタのため）。

• このため、
  • テキスト内パラメタは全てのタグに作用する。
  • タグ内パラメタは最初の１つのタグにしか作用しない。
    制限事項として認識していますが、以下を考慮すると仕様が複雑になるため、今後も対応は行わない予定です。
    • パラメタを複数の種類のタグに跨って使用できるか？
    • テキスト内パラメタ・タグ内パラメタで混在できるか？

補足２ †

• 「動的パラメタライズド・クエリ」のXML中に「<、>」の演算子があると、
  これがXMLのタグと認識され、XMLの構文チェックでエラーとなる。

• このため、XML中に「<、>」の演算子を記述する場合は、
  • ＆ｌｔ；、＆ｇｔ；（小文字で）を利用する。
    
  • 最新バージョンでは、CDATAセクション：<![CDATA[・・・]]>も使用できる。

関連 †

SetParameter †

SetUserParameter †

動的パラメタライズド・クエリのサンプル †