1、2016年1月EMAP開發動態SQL語法培訓n 01-SQL中的參數匹配及語法標簽n 02-語法標簽的詳細介紹n 03-使用時的注意事項標準的SQLupdate T set valuePi = 3.14, valueStr = test where id = 1可設置參數的SQLupdate T set valuePi = ?, valueStr = ? where id = ?位置1 3.14位置2 test位置3 1為參數綁定名稱及類型update T set valuePi = ?, valueStr = ? where id = ?位置1 name=“pi”位置2 name=“str”

2、位置3 name=“id”type=“double”type=“String”type=“int”“pi” “3.14”“str” “test”“id” “1”自動生成update T set #autoupdate;1,-2 where id = ?name=“pi” type=“double” colName=“valuePi”name=“str” type=“String” colName=“varlueStr”name=“id” type=“int” colName=“id”update T set valuePi = ?, valueStr = ? where id = ?自動生成動

3、態參數update T set #autoupdate,dynamic;1,-2 where id = ?name=“pi” type=“double” colName=“valuePi”name=“str” type=“String” colName=“varlueStr”name=“id” type=“int” colName=“id”“pi” “3.14”“str” “test”“id” “1”update T set valuePi = 3.14, valueStr = test where id = 1“pi” “3.14”“str” X“id” “1”update T set va

4、luePi = 3.14 where id = 1普通的動態參數delete from T #param(group)where id in ( select mainId from SUB where subId = ?)name=“subId” type=“ind” colName=“subId”子句delete from #subselect #sub.nextval as serial from dual問題&作業？1.自動生成動態參數和普通的動態參數分別適合哪些場景2.子句可以在哪些場景下使用n 01-SQL中的參數匹配及語法標簽n 02-語法標簽的詳細介紹n 03-使用時的

5、注意事項標簽1：#auto作用：根據“顯示列”或“固定參數”生成指定格式的語句根據顯示列表生成語句顯示列name=“pi” type=“double” colName=“valuePi”name=“str” type=“String” colName=“varlueStr”name=“id” type=“int” colName=“id”#autoselectvaluePi as “pi”, valueStr as “str”, id as “id”select #autoselectfrom Tselect valuePi as “pi”, valueStr as “str”, id as

6、“id”from T根據固定參數生成語句#autoinsertNvaluePi, valueStr, id#autoinsertV?, ?, ?#autoupdatevaluePi = ?, valueStr = ?, id = ?#autoandvaluePi = ? and valueStr = ? and id = ?#autoorvaluePi = ? or valueStr = ? or id = ?固定參數name=“pi” type=“double” colName=“valuePi”name=“str” type=“String” colName=“varlueStr”name

7、=“id” type=“int” colName=“id”設置生成的范圍固定參數name=“id1” type=“double” colName=“a”name=“id2” type=“String” colName=“b”name=“id3” type=“int” colName=“c”name=“id4” type=“Date” colName=“d”name=“id5” type=“Datetime” colName=“e”#autoupdate;1,-1a = ?, b = ?, c = ?, d= ?, e = ?#autoupdate;1,-2a = ?, b = ?, c = ?

8、, d= ?#autoupdate;2,4b = ?, c = ?, d = ?#autoupdate;2b = ?, c = ?, d= ?, e = ?生成動態參數固定參數name=“pi” type=“double” colName=“valuePi”name=“str” type=“String” colName=“varlueStr”name=“id” type=“int” colName=“id”#autoinsertN,d#param(1), valuePi#param(2), valueStr#param(3), id#autoinsertV,d#param(1), ?#par

9、am(2), ?#param(3), ?#autoupdate,d#param(1), valuePi = ?#param(2), valueStr = ?#param(3), id = ?#autoand,dynamic#param(1) and valuePi = ?#param(2) and valueStr = ?#param(3) and id = ?#autoor,dynamic#param(1) or valuePi = ?#param(2) or valueStr = ?#param(3) or id = ?#auto的語法總結#automodel,d;begin,endmod

10、el 生成語句的模式d 是否生成動態參數，可設置的值為”dynamic”或簡寫”d”begin 生成范圍的起始值end 生成范圍的結束值問題&作業？1.參數列表已確定，列數大于5。編寫一個更新語句，最后3列為條件，其中第一個為固定條件，后兩個為動態條件，其余的為更新列均為動態標簽2：#param作用：標識出語句中一段包含一個參數的部分，此部分會根據參數是否設置來確定是否需要生成到最終的執行語句中有些動態參數無法自動生成delete from T where status = 1 #param(b)and birth ? and type = ?select sum(amount) fr

11、om ( select amount, rownum as num#param(t), type from T) tmpwhere num ? #param(t)and type = ?問題&作業？1.下面的的語句中，如果存在姓名（xm）條件才添加關聯表，應如何編寫FROM T_CLASS JOIN T_STU ON T_CLASS.stuId = T_STU.stuIdWHERE T_CLASS.valid = 1 AND T_STU.xm = ?標簽3：#sub作用：在運行時設置動態子句，處理與參數無關的動態語句。如：表名 判斷操作符等子句的引用select * from #sub

12、where #suborder by #sub子句根據在語句中出現的位置進行引用 子句位置1 子句位置2 子句位置3子句模板select * from T#subwhere $#suborder by $子句位置1 a = 1子句位置2 b, cselect * from Twhere a = 1order by b, c#子句模板的編寫樣例select * from Twhere status = 1 and name like %select * from Torder by a, b, cselect * from Twhere status = 1 #suband $select *

13、from Torder by a#sub, $#sub的語法總結#sub$ 中的為子句模板$ 為設置子句的位置當設置了子句時，會將設置的子句與模板拼在一起添加到語句中當設置子句為空時，那整個子句將不會添加到語句中問題&作業？1.如何通過子句（#sub）生成動態的排序2.如果有固定的排序，分別按 固定排序優先，固定排序滯后 兩種情況設置子句標簽4：#check作用：檢查此標簽之后的動態標簽是否有生成語句，如果有則刪除第一個單詞，如果沒有則什么都不生成。動態標簽：#automodel,dynamic#param#sub去除多余的起始符號固定參數name=“pi” type=“double”

14、 colName=“valuePi”name=“str” type=“String” colName=“varlueStr”name=“id” type=“int” colName=“id”update T set #autoupdate,dynamicupdate T set , valuePi = ?, valueStr = ?, id = ?update T set #check #autoupdate,dynamicupdate T set valuePi = ?, valueStr = ?, id = ?替換起始符號固定參數name=“pi” type=“double” colNam

15、e=“valuePi”name=“str” type=“String” colName=“varlueStr”name=“id” type=“int” colName=“id”where #autoand,dynamicwhere and valuePi = ? and valueStr = ? and id = ?#checkappend=where #autoand,dynamicwhere valuePi = ? and valueStr = ? and id = ?#check的語法總結#checkappend=;end=1append 去除第一個單詞之后將添加此部分end 是否與前一

16、個檢測配對，作為結束標記問題&作業？1.對于如下條件，如果每個參數都是動態的，改如何寫WHERE a = ? AND b = ? AND (c = ? OR d = ? OR e = ?)n 01-SQL中的參數匹配及語法標簽n 02-語法標簽的詳細介紹n 03-使用時的注意事項動態監測的截斷#checkappend=where #autoand,dynamic #suband $#suborder by $whereby a, b沒有條件，僅設置了排序：a, b#checkappend=where #autoand,dynamic #suband $#checkend=1#subor

17、der by $自動生成的順序固定參數name=“id1” type=“double” colName=“a”name=“id2” type=“String” colName=“b”name=“value1” type=“int” colName=“c”name=“value2” type=“Date” colName=“d”name=“value3” type=“Datetime” colName=“e”update T set #autoupdate;3,5 where #autoand;1,2update T set c = ?, d = ?, e = ? where a = ? and b = ? id1 id2 value1 value2 value3需要按順序排列參數固定參數name=“value1” type=“int” colName=“c”name=“value2” type=“Date” colName=“d”name=“value3” type=“Datetime” colName=“e”name=“id1” type=“double” colName=“a”name=“id2” type=“String” colName=“b”update T set #autoupdate;1,3 wher