对oracle sql的一些总结
2018年04月08日
1、sql的分頁和查詢效率
在實現一個問題的時候，同樣的結果我寫了兩個sql。
sql1
1 SELECT C1.*, C2.CF_NAMES 2 FROM FAMS_CLASSIFY C1 3 LEFT JOIN (SELECT A.CL_ID, LISTAGG(B.CF_NAME, ',') WITHIN GROUP(ORDER BY B.CF_NAME) AS CF_NAMES 4 FROM FAMS_CLASSIFY A 5 LEFT JOIN FAMS_CUSTOMFIELD B 6 ON A.CL_ID = B.CL_ID 7 GROUP BY A.CL_ID) C2 8 ON C1.CL_ID = C2.CL_ID 9 WHERE C1.CL_TYPE = '2' 10 AND C1.CL_STATUS = '0' 11 AND 1=1 12 ORDER BY C1.CL_PID ASC, C1.CL_ID ASCsql2
1 SELECT A.CL_ID, 2 A.CL_NAME, 3 A.CL_PID, 4 A.CL_PNAME, 5 LISTAGG(B.CF_NAME, ',') WITHIN GROUP(ORDER BY B.CF_NAME) AS CF_NAMES 6 FROM FAMS_CLASSIFY A 7 LEFT JOIN FAMS_CUSTOMFIELD B 8 ON A.CL_ID = B.CL_ID 9 WHERE A.CL_TYPE = '2' 10 AND A.CL_STATUS = '0' 11 AND 1 = 1 12 GROUP BY A.CL_ID, A.CL_NAME, A.CL_PID, A.CL_PNAME 13 ORDER BY A.CL_PID ASC, A.CL_ID ASC ;在效率問題上詢問了前輩，得到的建議是使用第二個，因為分組跟子查詢都會影響效率，相比之下分組好一點。
?
另外產生的一個問題是，如果使用.net并使用c#語言實現sql的話，第一個sql是無法（在我的能力范圍內）使用拉姆達查詢對象的，但是又需要做結果分頁。
以前用的是拉姆達查詢對象的一個toPageList()方法，d進去發現里面的sql使用的是skip和take來分頁，但是百度上都說oracle是使用rownum來分頁的，沒有其他分頁方法。
最后詢問知道skip和take是?Linq?的語法，轉換成sql可能也是rownum。
?
2018年04月10日
2、sql字符串的拆分和組合
今天嘗試sql的時候需要用到拆分字符串，搜索了一下基本上是兩種方法，正則表達式和存儲函數。
因為要寫到程序里面，而且版本符合10g+以上的要求，我比較傾向于偷懶一點的正則表達式。
1 SELECT REGEXP_SUBSTR ('abc1,cbd2,db3,db5', '[^,]+', 1,rownum) 2 3 FROM DUAL 4 5 CONNECT BY ROWNUM <= LENGTH ('abc1,cbd2,db3,db5') - LENGTH (REPLACE ('abc1,cbd2,db3,db5', ',', ''))+1;說到了拆分，就順便寫一下組合字符串。
1 select id,listagg(name,',') within GROUP (order by name) from test group by id;?
?
?
2018年04月11日
3、sql的無效字符錯誤
犯了一個幼稚的錯誤，因為不是第一次犯了，還是寫下來比較記得住。
1 List<FAMS_FIXEDASSETS> list = db.SqlQuery<FAMS_FIXEDASSETS>(string.Format(@" 2 SELECT FA_ID, FA_DEPARTMENTID FROM fams_fixedassets WHERE fa_departmentid in 3 (SELECT id FROM WXDEPARTMENT E START WITH ID IN ({0}) CONNECT BY PRIOR E.ID = E.PARENTID);" 4 , range)).ToList();執行進入到內部的時候，找到的sql語句放到pl/sql上是可以執行的，但是在程序中catch到錯誤? ?ORA-00911: 無效字符
百度了一下，是因為我多了一個分號。
放在程序里的sql語句不能有分號。
放在程序里的sql語句不能有分號。
放在程序里的sql語句不能有分號。
?
?
4、拉姆達查詢對象的ToList和ToPageList
同樣是拉姆達查詢對象的應用，同樣的輸出內容和sql語句，code1可以執行，code2發生錯誤，傳入方法內部的sql為空字符串。
code1
1 return queryData.Select<FAMS_EquipmentViewModel>(@" 2 IE.*,……, 3 E.NAME AS FA_DEPARTMENTNAME") 4 .ToPageList(pageModel.page, pageModel.rows);?
code2
1 1 return queryData.Select<FAMS_EquipmentViewModel>(@" 2 2 IE.*,……, 3 3 E.NAME AS FA_DEPARTMENTNAME") 4 4 .ToList();?
錯誤的問題在于code2使用ToList()方法不應該在select的時候使用@字符。
?
?
5、JSSDK的調用
調用企業微信的掃一掃接口，需要引用jssdk的配置，我看了一下自己以前寫的代碼，copy了過來。
html頁面代碼
1 <script type="text/javascript" src="http://res.wx.qq.com/open/js/jweixin-1.2.0.js"></script> 2 <script class="wxconfig"> 3 wx.config({ 4 beta: true,// 必須這么寫，否則在微信插件有些jsapi會有問題 5 debug: @jsSdkConfig.Debug, // 開啟調試模式,調用的所有api的返回值會在客戶端alert出來，若要查看傳入的參數，可以在pc端打開，參數信息會通過log打出，僅在pc端時才會打印。 6 appId: '@jsSdkConfig.AppId', // 必填，企業微信的cropID 7 timestamp: @jsSdkConfig.Timestamp, // 必填，生成簽名的時間戳 8 nonceStr: '@jsSdkConfig.NonceStr', // 必填，生成簽名的隨機串 9 signature: '@jsSdkConfig.Signature',// 必填，簽名，見附錄1 10 jsApiList: [ 11 'checkJsApi', 12 'onMenuShareAppMessage', 13 'onMenuShareWechat', 14 'startRecord', 15 'stopRecord', 16 'onVoiceRecordEnd', 17 'playVoice', 18 'pauseVoice', 19 'stopVoice', 20 'uploadVoice', 21 'downloadVoice', 22 'chooseImage', 23 'previewImage', 24 'uploadImage', 25 'downloadImage', 26 'getNetworkType', 27 'openLocation', 28 'getLocation', 29 'hideOptionMenu', 30 'showOptionMenu', 31 'hideMenuItems', 32 'showMenuItems', 33 'hideAllNonBaseMenuItem', 34 'showAllNonBaseMenuItem', 35 'closeWindow', 36 'scanQRCode', 37 'previewFile', 38 'openEnterpriseChat', 39 'selectEnterpriseContact' 40 ] 41 }); 42 </script><!--./注入權限驗證配置-->這樣寫是不夠的，jsSdkConfig要在一開頭就定義好。
1 var jsSdkConfig = (BIS.Weixin.Models.JsSdkConfig)ViewBag.JsSdkConfig;?
然后測試頁面各種500，翻了錯誤日志說：“未將對象引用設置到對象的實例”。
再看看以前的代碼，controller那里沒有定義jsSdkConfig。
控制器代碼
1 WeixinWorkAPI workApi = new WeixinWorkAPI(); 2 ViewBag.JsSdkConfig = workApi.GetJsSdkConfig(this.RequestUrl);?
?
2018年04月18日
6、json數組傳遞后臺（C#）要求
在做一個還算挺復雜的功能，修改的時候，要記錄修改的每個字段和字段更改前后的值。
因此我除了傳遞表單的所有字段和值之外，還要傳遞一個數組，用來存放被更改的字段和更改后的值。
我在前臺，通過onchange事件，把被更改的字段和值放到了一個數組里。
這里就有一個問題，如果我的onchange時間是這樣寫：
1 function ChangeFixedAssetsInfo(field,index){ 2 faArray1.push(field); 3 faArray2.push($("#"+field).val()); 4 }?
那么如果用戶對同一個字段進行多次修改的時候，數組就會有同一個字段的多個記錄，那么后臺取值的時候，就很麻煩。
所以我采用了如下方法：
1 function ChangeFixedAssetsInfo(field,index){ 2 faArray[index]=new Object(); 3 faArray[index].UR_FIELDNAME=field; 4 faArray[index].UR_NEW=$("#"+field).val(); 5 }給每個字段不用的index數字，如果同一個字段多次修改，則覆蓋之前的內容。
結果我傳入后臺的時候悲劇了。后臺接收的faArray數組長度相同，但是內容全部為null。
最后發現是因為數組的index不連續的問題。
傳入后臺的數組索引必須是連續的且以0開始的正整數。
那么為了不浪費之前做的努力，我只好在傳入后臺之前，對整個faArray數組for循環，通過push方法添加到另外一個數組中去，將新的數組傳遞到后臺，就可以成功接收了。
?
7、jqGrid凍結列多選框架的刷新錯位問題
jqGrid框架的，初始化的時候，選擇凍結列+多選。一頁顯示30條數據，但是你的整個grid的高度是屏幕高度（一般都是這樣設置的），只能顯示10條左右。
如果你選擇了要拉動滾動條才能選擇的某一行數據集，比如第20條數據，那么當你執行以下方法時，
1 //重新加載grid,從第一頁開始 2 function ReloadJqGuid(param) { 3 if (!param) { 4 param = getFormJson("#searchForm"); 5 } 6 jQuery(grid_selector).setGridParam({ postData: param, page: 1 }).trigger("reloadGrid"); 7 8 }你會發現出現了錯位情況。凍結列和非凍結列對不到一起。
只有當滾動條滾動往上滾動的時候才能恢復。
原因大概是jqGrid的加載方式造成的，太具體的我也沒有去研究。
這個倒是挺好解決的。
?增加一行代碼，將每次的滾動條定位到最上方即可。
1 //重新加載grid,從第一頁開始 2 function ReloadJqGuid(param) { 3 if (!param) { 4 param = getFormJson("#searchForm"); 5 } 6 jQuery(grid_selector).setGridParam({ postData: param, page: 1 }).trigger("reloadGrid"); 7 8 $('.ui-jqgrid-bdiv').scrollTop(0); 9 }?
?
2018年05月11日
8、SQL查詢優化
知識就是力量啊……一個我好不容易實現了功能的sql語句，有100行，執行速度是700ms，前輩把他壓縮到了30行，執行速度是100ms。
簡潔高效的代碼簡直是一生的追求。
先放一下我自己的原生態的sql，大概是草稿紙級別的，主要實現的功能是聯合wff_fieldshow表查詢，如果field_id字段在表里面就顯示，如果不在就不顯示，同時還區分了子表和主表，所以有四個select我粗暴的用union連接了起來。
code1?
1 SELECT * 2 FROM (SELECT A.FORM_CODE AS FORM_CODE, 3 D.FIELD_DESNAME AS FIELD_DESNAME, 4 D.FIELD_ID AS FIELD_ID, 5 D.FIELD_NAME AS FIELD_NAME, 6 WFF.FIELD_ID AS IDD, 7 0 AS TABLE_SUB, 8 0 AS FIELD_SHOW, 9 WFF.ORDER_ID AS ORDER_ID 10 FROM EZ_FORM@OA A, 11 EZ_FORM_TABLE@OA B, 12 EZ_FORM_FIELD@OA C, 13 TFIELD@OA D 14 LEFT JOIN WFF_FIELDSHOW WFF 15 ON D.FIELD_ID = WFF.FIELD_ID, TTABLE@OA E 16 WHERE A.FORM_ID = B.FORM_ID 17 AND B.ID = C.FORM_TABLE_ID 18 AND B.TABLE_ID = E.TABLE_ID 19 AND C.FIELD_ID = D.FIELD_ID 20 AND D.FIELD_TABLE = E.TABLE_ID 21 AND A.FORM_CODE = 'BGYPQGD' 22 AND FORM_TYPE = '0' 23 AND E.TABLE_DESNAME NOT LIKE '%子表%' 24 AND WFF.FIELD_ID IS NULL 25 UNION 26 SELECT A.FORM_CODE AS FORM_CODE, 27 D.FIELD_DESNAME AS FIELD_DESNAME, 28 D.FIELD_ID AS FIELD_ID, 29 D.FIELD_NAME AS FIELD_NAME, 30 WFF.FIELD_ID AS IDD, 31 0 AS TABLE_SUB, 32 1 AS FIELD_SHOW, 33 WFF.ORDER_ID AS ORDER_ID 34 FROM EZ_FORM@OA A, 35 EZ_FORM_TABLE@OA B, 36 EZ_FORM_FIELD@OA C, 37 TFIELD@OA D 38 JOIN WFF_FIELDSHOW WFF 39 ON D.FIELD_ID = WFF.FIELD_ID, TTABLE@OA E 40 WHERE A.FORM_ID = B.FORM_ID 41 AND B.ID = C.FORM_TABLE_ID 42 AND B.TABLE_ID = E.TABLE_ID 43 AND C.FIELD_ID = D.FIELD_ID 44 AND D.FIELD_TABLE = E.TABLE_ID 45 AND A.FORM_CODE = 'BGYPQGD' 46 AND FORM_TYPE = '0' 47 AND E.TABLE_DESNAME NOT LIKE '%子表%' 48 AND A.FORM_CODE = WFF.FORM_CODE 49 UNION 50 SELECT A.FORM_CODE AS FORM_CODE, 51 D.FIELD_DESNAME AS FIELD_DESNAME, 52 D.FIELD_ID AS FIELD_ID, 53 D.FIELD_NAME AS FIELD_NAME, 54 WFF.FIELD_ID AS IDD, 55 1 AS TABLE_SUB, 56 0 AS FIELD_SHOW, 57 WFF.ORDER_ID AS ORDER_ID 58 FROM EZ_FORM@OA A, 59 EZ_FORM_TABLE@OA B, 60 EZ_FORM_FIELD@OA C, 61 TFIELD@OA D 62 LEFT JOIN WFF_FIELDSHOW WFF 63 ON D.FIELD_ID = WFF.FIELD_ID, TTABLE@OA E 64 WHERE A.FORM_ID = B.FORM_ID 65 AND B.ID = C.FORM_TABLE_ID 66 AND B.TABLE_ID = E.TABLE_ID 67 AND C.FIELD_ID = D.FIELD_ID 68 AND D.FIELD_TABLE = E.TABLE_ID 69 AND A.FORM_CODE = 'BGYPQGD' 70 AND FORM_TYPE = '0' 71 AND E.TABLE_DESNAME LIKE '%子表%' 72 AND WFF.FIELD_ID IS NULL 73 UNION 74 SELECT A.FORM_CODE AS FORM_CODE, 75 D.FIELD_DESNAME AS FIELD_DESNAME, 76 D.FIELD_ID AS FIELD_ID, 77 D.FIELD_NAME AS FIELD_NAME, 78 WFF.FIELD_ID AS IDD, 79 1 AS TABLE_SUB, 80 1 AS FIELD_SHOW, 81 WFF.ORDER_ID AS ORDER_ID 82 FROM EZ_FORM@OA A, 83 EZ_FORM_TABLE@OA B, 84 EZ_FORM_FIELD@OA C, 85 TFIELD@OA D 86 JOIN WFF_FIELDSHOW WFF 87 ON D.FIELD_ID = WFF.FIELD_ID, TTABLE@OA E 88 WHERE A.FORM_ID = B.FORM_ID 89 AND B.ID = C.FORM_TABLE_ID 90 AND B.TABLE_ID = E.TABLE_ID 91 AND C.FIELD_ID = D.FIELD_ID 92 AND D.FIELD_TABLE = E.TABLE_ID 93 AND A.FORM_CODE = 'BGYPQGD' 94 AND FORM_TYPE = '0' 95 AND E.TABLE_DESNAME LIKE '%子表%' 96 AND A.FORM_CODE = WFF.FORM_CODE) 97 ORDER BY ORDER_ID ASC;前輩很神奇的用case when這個我以前從來不知道的東西把sql壓縮優化成了下面這樣。
code2
1 SELECT * 2 FROM (SELECT A.FORM_CODE AS FORM_CODE, 3 D.FIELD_DESNAME AS FIELD_DESNAME, 4 D.FIELD_ID AS FIELD_ID, 5 D.FIELD_NAME AS FIELD_NAME, 6 WFF.FIELD_ID AS IDD, 7 E.TABLE_DESNAME, 8 case 9 when E.TABLE_DESNAME LIKE '%子表%' then 10 1 11 else 12 0 13 end AS TABLE_SUB, 14 case 15 when WFF.FIELD_ID IS NULL then 16 0 17 else 18 1 19 end AS FIELD_SHOW, 20 WFF.ORDER_ID AS ORDER_ID 21 FROM EZ_FORM@OA A, 22 EZ_FORM_TABLE@OA B, 23 EZ_FORM_FIELD@OA C, 24 TFIELD@OA D 25 LEFT JOIN WFF_FIELDSHOW WFF 26 ON D.FIELD_ID = WFF.FIELD_ID, TTABLE@OA E 27 WHERE A.FORM_ID = B.FORM_ID 28 AND B.ID = C.FORM_TABLE_ID 29 AND B.TABLE_ID = E.TABLE_ID 30 AND C.FIELD_ID = D.FIELD_ID 31 AND D.FIELD_TABLE = E.TABLE_ID 32 AND A.FORM_CODE = 'BGYPQGD' 33 AND FORM_TYPE = '0') 34 ORDER BY ORDER_ID ASC;?
?
2018年05月14日
9、.net調用存儲過程的out參數問題
寫了一個存儲過程在.NET程序中調用發現catch到一個錯誤
ORA-06502:?PL/SQL:?數字或值錯誤?:?character?string?buffer?too?small?ORA-06512:?在?"BIS.PRO_FIELDSHOW_INSERT",?line?61?ORA-06502:?PL/SQL:?數字或值錯誤?:?character?string?buffer?too?small?ORA-06512:?在?line?1
在plsql中調用存儲過程，正確。
那么就是.net調用的問題了，我的代碼如下：
public string SaveFieldShow(string form_code,string masterids,string subids,string userid){IDataParameter[] para = new IDataParameter[5];para[0] = new OracleParameter("@FORM_CODE", form_code);para[1] = new OracleParameter("@MASTERIDS", masterids);para[2] = new OracleParameter("@SUBIDS", subids);para[3] = new OracleParameter("@USERID", userid);para[4] = new OracleParameter("@ERROR_MESSAGE", OracleDbType.Varchar2,ParameterDirection.Output);DBHelperOra.RunProcedureNoReturn("PRO_FIELDSHOW_INSERT",para);return para[4].Value.ToString();}?
錯誤所說的line61就是給error_message賦值的語句。那么就是這個out參數傳遞過去的時候出錯了。
ORA-06502的錯誤信息為字符緩沖區太小，我查了一下oracle的字符最大長度為4000，放入我要返回的字符串是綽綽有余的，那么就是,net傳遞過去的out參數的字符長度太小，查看了一下他有個重載方法可以設置參數的長度。
以下是正確的代碼
para[4] = new OracleParameter("@ERROR_MESSAGE", OracleDbType.Varchar2, 4000, "", ParameterDirection.Output);改成這樣就OK了。
?
10、名稱排序
寫SQL進行排序的時候我們可能按照username進行排序，我們可能按照名字的拼音、比劃及偏旁部首進行排序，Oracle剛好提供了這樣的一個函數nlssort（），
NLSSORT()，用來進行語言排序
拼音
SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_PINYIN_M')
筆劃
SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_STROKE_M')
部首
SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_RADICAL_M')
?
11、無序排序分頁出現的問題
不知道是不是只有oracle會出現這樣的問題。
如果你使用rownum來進行分頁，即
select * from ( select row_limit.*, rownum rownum_ from (select * from tablename ) row_limit where rownum <= 125 ) where rownum_ >100?如果你的查詢語句沒有做排序，或者排序是無甚意義的排序，即排序字段幾乎沒有分別，那么就容易出現數據無序排列或者隨機排列的問題。那么分頁就失去了意義，不管是分頁是100~125或者75~100，其結果都是一樣的。
因此需要在查詢語句中加上order by columnName 或者 order by columnName ,rownum 或者 order by columnName,ID(主鍵)
?
12、WebApi項目引用的問題
報錯如下：
找到多個與名為“主頁”的控制器匹配的類型。
原因是啟動webapi項目的時候，因為引用了另外一個Web項目，而web項目有一個與首頁同名的controller，vs報錯。
排查后發現當初引用了web項目后再取消，在bin文件夾下還是產生了一個引用web的文件，需要刪除。
?
轉載于:https://www.cnblogs.com/AnnsBlog/p/8743349.html
總結
以上是生活随笔為你收集整理的对oracle sql的一些总结的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Python中string、json、b
- 下一篇： POJ:3579-Median（二分＋尺