对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 ASC
sql2
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的文件，需要删除。
13、Orcale中使用MD5加密
1、首先创建一个函数并执行
CREATE OR REPLACE FUNCTION MD5(
passwd IN VARCHAR2)
RETURN VARCHAR2
IS
retval varchar2(32);
BEGIN
retval := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => passwd)) ;
RETURN retval;
END;
2、然后进行select查询或者其他sql语句。
select md5('123456') from dual;
UPDATE sysuser SET pwd=md5(userid)
