orcale查询踩的坑(Orcale query stepped on the pit)

首先说明一下,这个是生产上的问题,使用的是orcale,package下的储存过程。

事情经过是这样的:最近业务验证数据,在页面操作,调用存储过程计算数据,计算完成后导出Excel老是空表,后来查询生产发现保存的异常日志,发现子查询返回多条,大概知道原因了。一顿操作猛如虎,发到生产再提了计算,结果还是空,查询异常记录还是那个错,顿时方了。检查代码,都做了子查询唯一处理,应该都没问题呀。那出问题的地方就在最后的汇总语句里咯,你猜怎么着,还真在这。这个问题有点隐蔽,把这个sql单独拿出来参数换成对应实参,一查询没问题。是不是又方了,现在到了关键哈。通常查询默认是会分页的(mysql是查整个),在查询前100条时,正常,当查询1000条时,报上面的错了。这个是亲自经历得记下来,问题找出来了,就好解决,难的是怎么定位问题~

————————

First of all, this is a production problem. It uses the storage process under orcale package.

The process is as follows: Recently, the business verified the data, operated on the page, called the stored procedure to calculate the data, exported the excel table after the calculation is completed, and then queried the production and found the saved exception log. It was found that multiple sub queries returned. I probably know the reason. The operation was as fierce as a tiger. It was sent to the production and then the calculation was carried out. The result was still empty. The query exception record was still the wrong one. It was solved immediately. Check the code and do the unique processing of sub query. It should be all right. The problem lies in the final summary statement. Guess what, it’s really here. This problem is a little hidden. Take out the SQL alone and replace the parameters with the corresponding arguments. There is no problem with a query. Is it square again? Now it’s the key. Normally, queries are paged by default (MySQL checks the whole). When querying the first 100 items, it is normal. When querying 1000 items, the above error is reported. This is a personal experience. Write it down. Once the problem is found, it will be easy to solve. The difficult thing is how to locate the problem ~