「[[Open棟梁 wiki>https://opentouryo.osscons.jp]]」は、「[[Open棟梁Project>https://github.com/OpenTouryoProject/]]」,「[[OSSコンソーシアム .NET開発基盤部会>https://www.osscons.jp/dotNetDevelopmentInfrastructure/]]」によって運営されています。 -[[戻る>動的パラメタライズド・クエリ]] *目次 [#h023904f] #contents *用語 [#ef6a7b63] **テキスト内パラメタ [#k6ea5d2c] -パラメタライズド・クエリのパラメタ。 -タグ内の文字列の先頭にあるパラメタライズド・クエリのパラメタをテキスト内パラメタとして認識する。 -パラメタ値には、パラメタライズド・クエリのパラメタとして有効なパラメタ値を設定する。 **タグ内パラメタ [#l94d276d] -XMLタグのname属性に指定するパラメタ。 -パラメタ値にtrue、false、nullを許可しており、 --trueが設定されるとタグが有効になる。 --falseやnullが設定されるとタグが無効になる。 --true、false、null以外の値が設定された場合エラーとなる。 *XMLタグ [#p8bce455] **ROOTタグ [#ce77c9e6] ルートのタグ ***タグ表記 [#zcdaf798] <ROOT> ・・・クエリ定義・・・ </ROOT> ***説明 [#da7616c6] クエリ定義は全て、このタグの中に収める。 ***ネスト [#l7442c7d] ROOTタグ以外の全てのタグをネストできる。 **VALタグ [#q110546f] パラメタとして設定した文字列に置換される。 ***タグ表記 [#d3414806] <VAL name="xxx"/> ***説明 [#ib5376bd] -任意の文字列挿入が可能のため、どのようなパターンのSQL整形にも対応可能。~ -[[タグ内パラメタ>#l94d276d]]に文字列を設定するとVALタグが、設定した文字列で置換される。~ パラメタ値にnullが設定された場合、パラメタが設定されない場合は、無効になりVALタグが削除される。 -&color(red){SQLインジェクションが可能であるため、ユーザ入力をVALタグに直接指定しないようにする};~ (直接指定する場合は、シングル クォーテーションのエスケープなどを施すこと)。 ***ネスト [#rd8b8a12] タグのネストは不可能。 **IFタグ [#xf2551f1] 最もよく使うタグで、基本的にWHERE句の条件を動的化する目的で使用するが、それ以外の場所・目的でも利用可能。 ***タグ表記 [#y3046325] -[[テキスト内パラメタ>#k6ea5d2c]] or [[タグ内パラメタ>#l94d276d]]~ ※ 両方のパラメタが指定された場合、[[テキスト内パラメタ>#k6ea5d2c]]が優先される。 --例1: [[テキスト内パラメタ>#k6ea5d2c]] <IF> ・・・[演算子] 条件式・・・ </IF> --例2: [[タグ内パラメタ>#l94d276d]] <IF name="xxx"> ・・・[演算子] 条件式・・・ </IF> -[[ELSEタグ>#n33fbccc]]の利用例 --例1: <IF>AND XXX=@P1<ELSE>AND XXX IS NULL</ELSE></IF> --例2: <IF name="xxx">AND XXX IS NOT NULL</IF> ***説明 [#ab5ffbff] 通常WHERE句内の条件式を囲うことで、条件式の有効・無効を制御する。 -[[テキスト内パラメタ>#k6ea5d2c]]に、 --パラメタが設定された場合、タグとパラメタが有効になり、 --パラメタが設定されない場合は、タグとパラメタが無効になる。 -[[タグ内パラメタ>#l94d276d]]に、 --trueが設定されるとIFタグが有効になる。 --falseが設定されると、IFタグが無効になり、 ---[[ELSEタグ>#n33fbccc]]が無い場合はエラーになる。 ---[[ELSEタグ>#n33fbccc]]がある場合は、[[ELSEタグ>#n33fbccc]]が有効になる。 -[[テキスト内パラメタ>#k6ea5d2c]]・[[タグ内パラメタ>#l94d276d]]のどちらでも、nullが設定された場合、 ---[[ELSEタグ>#n33fbccc]]が無い場合はエラーになる。 ---[[ELSEタグ>#n33fbccc]]がある場合は、[[ELSEタグ>#n33fbccc]]が有効になる。 ***ネスト [#t47d4591] [[ELSEタグ>#n33fbccc]]、[[VALタグ>#q110546f]]のみネスト可能。 **ELSEタグ [#n33fbccc] [[IFタグ>#xf2551f1]]に対応した、XXX IS NULL,XXX IS NOT NULLなどの条件式を記述する。 ***タグ表記 [#m807d998] <ELSE> ・・・[演算子] 条件式・・・ </ELSE> -例1: <IF>AND XXX=@P1<ELSE>AND XXX IS NULL</ELSE></IF> -例2: <IF name="xxx">AND XXX IS NOT NULL<ELSE>AND XXX IS NULL</ELSE></IF> ***説明 [#n4077ce4] -[[IFタグ>#xf2551f1]]のパラメタ設定でELSEタグが有効になる場合の条件式を記述できる。 -[[テキスト内パラメタ>#k6ea5d2c]]・[[タグ内パラメタ>#l94d276d]]のどちらも記述できない。 ***ネスト [#j8af05a5] [[VALタグ>#q110546f]]のみネスト可能。 **SELECT-CASE-DEFAULTタグ [#g724b843] [[タグ内パラメタ>#l94d276d]]を使用して、SELECT-CASE-DEFAULTする。 ***タグ表記 [#n9a6b0d4] <SELECT name="xxx"> <CASE value="A">・・・</CASE> <CASE value="B">・・・</CASE> <DEFAULT>・・・</DEFAULT> </SELECT> ※ DEFAULTタグは省略可能 ***説明 [#m206280b] SELECTタグに[[タグ内パラメタ>#l94d276d]]を指定する。 -指定されたパラメタ値によって、任意のCASEタグ、若しくはDEFAULTタグ中のステートメントを有効にする。 --パラメタ値が、CASEタグのvalue属性中の文字列に一致する場合、当該CASEタグの中のステートメントを有効にする。 --上記に合致するCASEタグが無い場合は、DEFAULTタグ中のステートメントを有効にする。 --nullが設定された場合、パラメタが設定されない場合は、無効になりSELECTタグが削除される。 ***ネスト [#l2f2c8cc] [[VALタグ>#q110546f]]のみネスト可能。 **LISTタグ [#v8a24535] IN句の条件式を囲うことで、パラメタリストの指定と、IN句の有効・無効を制御する。 ***タグ表記 [#nec0b6c6] <LIST> ・・・[演算子] IN句の条件式・・・ </LIST> ***説明 [#k9f2b0bd] -[[テキスト内パラメタ>#k6ea5d2c]]を指定する。 --[[テキスト内パラメタ>#k6ea5d2c]]が無い場合は、エラーとなる。 --[[テキスト内パラメタ>#k6ea5d2c]]に、 ---パラメタが設定された場合、タグとパラメタが有効になり、 ---パラメタが設定されない場合は、タグとパラメタが無効になる。 [[テキスト内パラメタ>#k6ea5d2c]]を指定する。 -[[テキスト内パラメタ>#k6ea5d2c]]が無い場合は、エラーとなる。 -[[テキスト内パラメタ>#k6ea5d2c]]に、 --パラメタが設定された場合、タグとパラメタが有効になり、 --パラメタが設定されない場合は、タグとパラメタが無効になる。 -パラメタ値は基本的にArrayList型かList<T>型で設定する(パラメタが1つの場合は、値型でも設定可)。 --実行時のパラメタは、@パラメタ名_1, @パラメタ名_2, @パラメタ名_3 ・・・と展開される。 --要素数が0の場合、nullが設定された場合、パラメタが設定されない場合は、無効になりタグが削除される。 ***ネスト [#x7d8277a] [[VALタグ>#q110546f]]のみネスト可能。 **JOINタグ [#nd1d11be] JOIN句をJOINタグで囲うことで、JOIN句の有効・無効を制御する。 ***タグ表記 [#h4c904de] <JOIN name="xxx"> ・・・JOIN句・・・ </JOIN> ***説明 [#r3594f38] [[タグ内パラメタ>#l94d276d]]を指定する。 -JOIN句を有効にする場合は、パラメタ値にtrueを設定する。 -JOIN句を無効にする場合は、パラメタ値にfalse、nullが設定する。 ***ネスト [#p161045f] [[ROOT>#ce77c9e6]]、[[PARAM>#j563a460]]、[[DIV>#c51cdbfa]]タグ以外の全てのタグをネストできる。 **SUBタグ [#l0101526] WHERE句内で使用するサブクエリの条件式をSUBタグで囲うことで、サブクエリの条件式の有効・無効を制御する。 ***タグ表記 [#oe1407be] <SUB name="xxx"> ・・・[演算子] サブクエリの条件式・・・ </SUB> ***説明 [#f0840231] [[タグ内パラメタ>#l94d276d]]を指定する。 -サブクエリの条件式を有効にする場合は、パラメタ値にtrueを設定する。 -サブクエリの条件式を無効にする場合は、パラメタ値にfalse、nullが設定する。 ***ネスト [#v5eca64a] [[ROOT>#ce77c9e6]]、[[PARAM>#j563a460]]、[[DIV>#c51cdbfa]]タグ以外の全てのタグをネストできる。 **WHEREタグ [#u8979f73] WHERE句をWHEREタグで囲うことで、 -[[IF>#xf2551f1]],[[LIST>#v8a24535]],[[SUB>#l0101526]]タグの処理でWHERE句内に条件式が無くなる場合や、 -「WHERE AND 条件式」などの不正な状態になり得る場合、 不要なWHERE句や演算子(AND、OR)が削除されるようになる。 ***タグ表記 [#r9e70939] <WHERE> ・・・WHERE句・・・ </WHERE> ***説明 [#bc5c7366] -【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タグ>#xf2551f1]]は、・・・[演算子] 条件式・・・としている。) ***ネスト [#wf50e1e3] [[ROOT>#ce77c9e6]]、[[PARAM>#j563a460]]、[[DIV>#c51cdbfa]]タグ以外の全てのタグをネストできる。 **INSCOLタグ [#wf684a15] -INSERT句のカラム リスト専用に設計されている。 -通常は、自動生成されたSQLでのみ利用する。ユーザは、基本的に使用しない。 ***タグ表記 [#ydf6924d] <INSCOL name="xxx"> InsertColumn , </INSCOL> ***説明 [#n3f02c36] -INSERT句のカラム リスト内のカラム情報を囲い、~ [[IFタグ>#xf2551f1]]でINSERT句のVALUESパラメタ リストを囲えば、動的なINSERT文を作成できる。 -[[タグ内パラメタ>#l94d276d]]を指定する。 --[[タグ内パラメタ>#l94d276d]]と同じ名前のパラメタが設定されている場合は、カラムを有効にする。 --[[タグ内パラメタ>#l94d276d]]と同じ名前のパラメタが設定されていない場合は、カラムを無効にする。 ***ネスト [#nf89b1e7] [[VALタグ>#q110546f]]のみネスト可能。 **DELCMAタグ [#e17e8fb8] -UPDATE句のカラム リスト専用に設計されている。 -通常は、自動生成されたSQLでのみ利用する。ユーザは、基本的に使用しない。 ***タグ表記 [#c63ccc3f] <DELCMA> ・・・カンマ区切りのリスト・・・ </ DELCMA > ***説明 [#f9f2cf3f] カンマ区切りのリストをプログラムで生成した場合、~ 生成した文字列の前・後に余分に付与されているカンマを削除する 【カンマの削除】~ , aaa, bbb, ccc, ddd, eee, fff, ggg, → aaa, bbb, ccc, ddd, eee, fff, ggg ***ネスト [#k49e7b3d] [[ROOT>#ce77c9e6]]、[[PARAM>#j563a460]]、[[DIV>#c51cdbfa]]タグ以外の全てのタグをネストできる。 **PARAMタグ [#j563a460] [[動的パラメタライズド・クエリ分析ツール]]を使用し、~ PARAMタグ間に指定したパラメタ値で動的パラメタライズド・クエリをテスト実行する。 ***タグ表記 [#n6e09196] <PARAM> ・・・パラメタ情報・・・ </PARAM> ***説明 [#bdff6027] パラメタの記述ルールを以下に示す。 -【ユーザ定義パラメタ(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/> --例:値のリストが2つ以上の場合、ArrayList型のパラメタとなる。 P1, String, xxx, yyy, zzz<DIV/> -【DBNull型のパラメタ】 --ルール:パラメタ名, DBNull[, 任意]<DIV/>~ ※ 「値を表す文字列」は有っても無くてもよい。 -【nullのパラメタ】 --ルール:パラメタ名, (任意), null<DIV/>~ ※ 「型を表す文字列」は有っても無視される。 上記の「型を表す文字列」・「値を表す文字列」については、[[下記>#e99ff356]]を参照のこと。 ***ネスト [#o8303b61] [[DIVタグ>#c51cdbfa]]のみネストできる。 **DIVタグ [#c51cdbfa] ***タグ表記 [#j368835d] <DIV/> ***説明 [#a5fa81ca] [[PARAMタグ>#j563a460]]内で、パラメタを区切るために使用する。 ***ネスト [#c4a682c3] ネストは不可能。 *PARAMタグの型表現 [#e99ff356] |#|パラメタの.NET型|>|型を表す文字列|値を表す文字列(の制限)|h |~|~|通常時|配列時|~| |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の補足 [#i1ae4cc8] -DBのフィールドにnull値 を設定する場合Insert、Updateのクエリのパラメタに「DBNull」設定する。 -Where句の条件のパラメタには、「DBNull」を使用できないので注意する(通常、SQL上でIS NULLと指定する)。 -指定例 --P1 , DBNull , --P1 , DBNull , xxx --P1 , DBNull , yyy ※ xxx, yyyの値はあっても無視される。 ***null値の補足 [#h4234489] 動的パラメタライズド・クエリのパラメタに「null」を使用した場合は、~ タグを無効にするような動作になる([[IFタグ>#xf2551f1]]-[[ELSEタグ>#n33fbccc]]タグでは、[[ELSEタグ>#n33fbccc]]が有効になる)。 -指定例 --P1 , , null --P1 , xxx , null --P1 , yyy , null ※ xxx, yyyの値はあっても無視される。 **補足 [#z92a8f6a] ***補足1 [#tdfdffde] -タグ処理に利用された後の扱い --[[テキスト内パラメタ>#k6ea5d2c]]は、タグの処理後に(パラメタのコレクションに)そのまま残される(静的なSQLの実行に使用され、複数個所に作用するため)。 --[[タグ内パラメタ>#l94d276d]]は、タグの処理後に(パラメタのコレクションから)消去される(生成された静的なSQLの実行には不要なパラメタのため)。 -このため、 --[[テキスト内パラメタ>#k6ea5d2c]]は全てのタグに作用する。 --[[タグ内パラメタ>#l94d276d]]は最初の1つのタグにしか作用しない。~ [[制限事項>FAQ - D層フレームワーク#o212ae41]]として認識していますが、以下を考慮すると仕様が複雑になるため、今後も対応は行わない予定です。 ---パラメタを複数の種類のタグに跨って使用できるか? ---[[テキスト内パラメタ>#k6ea5d2c]]・[[タグ内パラメタ>#l94d276d]]で混在できるか? ***補足2 [#f2645dd6] -「動的パラメタライズド・クエリ」のXML中に「<、>」の演算子があると、~ これがXMLのタグと認識され、XMLの構文チェックでエラーとなる。 -このため、XML中に「<、>」の演算子を記述する場合は、 --&lt;、&gt;(小文字で)を利用する。~ --最新バージョンでは、CDATAセクション:<![CDATA[・・・]]>も使用できる。 *関連 [#z9d8c5c1] **[[SetParameter]] [#l560ed19] **[[SetUserParameter]] [#rbf1ed3c] **[[動的パラメタライズド・クエリのサンプル>https://gist.github.com/daisukenishino2/f50689531157d54806fb245e8a4fe661]] [#ob539a2b] IP:155.190.48.34 TIME:"2022-12-22 (木) 13:20:13" REFERER:"https://opentouryo.osscons.jp/index.php?cmd=edit&page=%E5%8B%95%E7%9A%84%E3%83%91%E3%83%A9%E3%83%A1%E3%82%BF%E3%83%A9%E3%82%A4%E3%82%BA%E3%83%89%E3%83%BB%E3%82%AF%E3%82%A8%E3%83%AA%E3%81%AE%E4%BB%95%E6%A7%98" USER_AGENT:"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36"