EMAP開發(fā)-動態(tài)SQL語法培訓

1、2016年1月EMAP開發(fā)動態(tài)SQL語法培訓n 01-SQL中的參數(shù)匹配及語法標簽n 02-語法標簽的詳細介紹n 03-使用時的注意事項標準的SQLupdate T set valuePi = 3.14, valueStr = test where id = 1可設置參數(shù)的SQLupdate T set valuePi = ?, valueStr = ? where id = ?位置1 3.14位置2 test位置3 1為參數(shù)綁定名稱及類型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、態(tài)參數(shù)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普通的動態(tài)參數(shù)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問題&作業(yè)？1.自動生成動態(tài)參數(shù)和普通的動態(tài)參數(shù)分別適合哪些場景2.子句可以在哪些場景下使用n 01-SQL中的參數(shù)匹配及語法標簽n 02-語法標簽的詳細介紹n 03-使用時的

5、注意事項標簽1：#auto作用：根據(jù)“顯示列”或“固定參數(shù)”生成指定格式的語句根據(jù)顯示列表生成語句顯示列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根據(jù)固定參數(shù)生成語句#autoinsertNvaluePi, valueStr, id#autoinsertV?, ?, ?#autoupdatevaluePi = ?, valueStr = ?, id = ?#autoandvaluePi = ? and valueStr = ? and id = ?#autoorvaluePi = ? or valueStr = ? or id = ?固定參數(shù)name=“pi” type=“double” colName=“valuePi”name=“str” type=“String” colName=“varlueStr”name

7、=“id” type=“int” colName=“id”設置生成的范圍固定參數(shù)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 = ?生成動態(tài)參數(shù)固定參數(shù)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的語法總結(jié)#automodel,d;begin,endmod

10、el 生成語句的模式d 是否生成動態(tài)參數(shù)，可設置的值為”dynamic”或簡寫”d”begin 生成范圍的起始值end 生成范圍的結(jié)束值問題&作業(yè)？1.參數(shù)列表已確定，列數(shù)大于5。編寫一個更新語句，最后3列為條件，其中第一個為固定條件，后兩個為動態(tài)條件，其余的為更新列均為動態(tài)標簽2：#param作用：標識出語句中一段包含一個參數(shù)的部分，此部分會根據(jù)參數(shù)是否設置來確定是否需要生成到最終的執(zhí)行語句中有些動態(tài)參數(shù)無法自動生成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 = ?問題&作業(yè)？1.下面的的語句中，如果存在姓名（xm）條件才添加關聯(lián)表，應如何編寫FROM T_CLASS JOIN T_STU ON T_CLASS.stuId = T_STU.stuIdWHERE T_CLASS.valid = 1 AND T_STU.xm = ?標簽3：#sub作用：在運行時設置動態(tài)子句，處理與參數(shù)無關的動態(tài)語句。如：表名 判斷操作符等子句的引用select * from #sub

12、where #suborder by #sub子句根據(jù)在語句中出現(xiàn)的位置進行引用 子句位置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的語法總結(jié)#sub$ 中的為子句模板$ 為設置子句的位置當設置了子句時，會將設置的子句與模板拼在一起添加到語句中當設置子句為空時，那整個子句將不會添加到語句中問題&作業(yè)？1.如何通過子句（#sub）生成動態(tài)的排序2.如果有固定的排序，分別按 固定排序優(yōu)先，固定排序滯后 兩種情況設置子句標簽4：#check作用：檢查此標簽之后的動態(tài)標簽是否有生成語句，如果有則刪除第一個單詞，如果沒有則什么都不生成。動態(tài)標簽：#automodel,dynamic#param#sub去除多余的起始符號固定參數(shù)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 = ?替換起始符號固定參數(shù)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的語法總結(jié)#checkappend=;end=1append 去除第一個單詞之后將添加此部分end 是否與前一

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

17、der by $自動生成的順序固定參數(shù)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需要按順序排列參數(shù)固定參數(shù)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