動的パラメタライズド・クエリの仕様のバックアップ(No.2)

バックアップ一覧
差分を表示
現在との差分を表示
ソースを表示
動的パラメタライズド・クエリの仕様へ行く。
- 1 (2018-08-30 (木) 09:31:24)
- 2 (2018-08-30 (木) 11:17:46)

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

戻る

目次 †

目次
概要
- 用語
  - テキスト内パラメタ
  - タグ内パラメタ
XMLタグ
- ROOTタグ
- VALタグ
- IFタグ
- ELSEタグ
- SELECT-CASE-DEFAULTタグ
- LISTタグ
- JOINタグ
- SUBタグ
- WHEREタグ
- INSCOLタグ
- DELCMAタグ
- PARAMタグ
- DIVタグ
PARAMタグの型表現
- DBNullの補足
- null値の補足
- 補足
  - 補足１
  - 補足２
関連

↑

概要 †

↑

用語 †

↑

テキスト内パラメタ †

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

↑

タグ内パラメタ †

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タグの型表現 †

#	パラメタの.NET型	型を表す文字列		値を表す文字列（の制限）
#	パラメタの.NET型	通常時	配列時	値を表す文字列（の制限）
1	System.Boolean	Boolean	Boolean[]	「true」 or 「false」の文字列のみ指定可能
2	System.Byte	Byte	Byte[]	Byteに収まる文字
3	System.UInt16	UInt16	UInt16[]	UInt16に収まる数値を表す文字列
4	System.UInt32	UInt32	UInt32[]	UInt32に収まる数値を表す文字列
5	System.UInt64	UInt64	UInt64[]	UInt64に収まる数値を表す文字列
6	System.SByte	SByte	SByte[]	SByteに収まる文字
7	System.Int16	Int16	Int16[]	Int16に収まる数値を表す文字列
8	System.Int32	Int32	Int32[]	Int32に収まる数値を表す文字列
9	System.Int64	Int64	Int64[]	Int64に収まる数値を表す文字列
10	System.Decimal	Decimal	Decimal[]	Decimalに収まる数値を表す文字列
11	System.Single	Single	Single[]	Singleに収まる数値を表す文字列
12	System.Double	Double	Double[]	Doubleに収まる数値を表す文字列
13	System.Char	Char	Char[]	Charに収まる文字
14	System.String	String	String[]	任意の文字列
15	System.DateTime?	DateTime?	DateTime?[]	DateTime?（日付型）に変換可能な文字列
16	System.DBNull	DBNull	サポートしない	－
17	null値の場合	－	サポートしない	「null」という文字列のみ指定可能

↑

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[・・・]]>も使用できる。

↑

動的パラメタライズド・クエリの仕様 のバックアップ(No.2)

目次 †

概要 †

用語 †

テキスト内パラメタ †

タグ内パラメタ †

XMLタグ †

ROOTタグ †

タグ表記 †

説明 †

ネスト †

VALタグ †

タグ表記 †

説明 †

ネスト †

IFタグ †

タグ表記 †

説明 †

ネスト †

ELSEタグ †

タグ表記 †

説明 †

ネスト †

SELECT-CASE-DEFAULTタグ †

タグ表記 †

説明 †

ネスト †

LISTタグ †

タグ表記 †

説明 †

ネスト †

JOINタグ †

タグ表記 †

説明 †

ネスト †

SUBタグ †

タグ表記 †

説明 †

ネスト †

WHEREタグ †

タグ表記 †

説明 †

ネスト †

INSCOLタグ †

タグ表記 †

説明 †

ネスト †

DELCMAタグ †

タグ表記 †

説明 †

ネスト †

PARAMタグ †

タグ表記 †

説明 †

ネスト †

DIVタグ †

タグ表記 †

説明 †

ネスト †

PARAMタグの型表現 †

DBNullの補足 †

null値の補足 †

補足 †

補足１ †

補足２ †

関連 †

SetParameter †

SetUserParameter †

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

動的パラメタライズド・クエリの仕様のバックアップ(No.2)