Oracle PL / SQL - NO_DATA_FOUND异常对存储过程性能有坏处理吗？

本文介绍了Oracle PL / SQL - NO_DATA_FOUND异常对存储过程性能有坏处理吗？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我正在编写一个需要很多调理的存储过程。通过C＃.NET编码的一般知识，异常可能会影响性能，我一直都在避免在PL / SQL中使用它们。我在这个存储过程中的调理主要围绕是否存在一条记录，我可以通过两种方法之一进行操作： SELECT COUNT（*）INTO var WHERE condition; IF var> 0 THEN SELECT NEEDED_FIELD INTO otherVar WHERE condition; .... -or - SELECT NEEDED_FIELD INTO var WHERE condition; EXCEPTION WHEN NO_DATA_FOUND .... 第二个情况对我来说似乎更加优雅，因为那时我可以使用NEEDED_FIELD，在第一种情况下，我不得不在第一个语句中选择条件。较少的代码但是如果存储过程使用COUNT（*）运行得更快，那么我不介意再打多一些来弥补处理速度。 任何提示？我有没有其他的可能性？ 编辑 我应该提到这一切已经嵌套在FOR LOOP中。不知道这是否与使用游标有所不同，因为我不认为我可以在FOR LOOP中将光标DECLARE作为选择。解决方案我不会使用显式光标来做到这一点。当可以使用隐式游标时，Steve F.不再建议人们使用显式游标。 具有 count（*）的方法是不安全的。如果另一个会话删除符合 count（*）的行之后的条件的行，并且在使用选择...之前的行，代码将抛出一个不能被处理的异常。 原始帖子的第二个版本没有这个问题，通常是首选。 那就是说，使用异常有一个小的开销，如果你100％确定数据不会改变，你可以使用 count（*），但我建议反对。 我在Oracle 10.2.0.1上运行了这些基准测试/ em> 32位Windows 。我只是看着经过的时间。还有其他测试工具可以提供更多的细节（例如锁存计数和使用的内存）。 SQL> create table t NEEDED_FIELD号，COND号）; declare otherVar number; begin for i in 1 .. 50000循环 begin 从t选择NEEDED_FIELD INTO otherVar，其中cond = 1; exception 当no_data_found然后 otherVar：= 0; 结束结束循环; 结束 / I'm writing a stored procedure that needs to have a lot of conditioning in it. With the general knowledge from C#.NET coding that exceptions can hurt performance, I've always avoided using them in PL/SQL as well. My conditioning in this stored proc mostly revolves around whether or not a record exists, which I could do one of two ways:SELECT COUNT(*) INTO var WHERE condition;IF var > 0 THEN SELECT NEEDED_FIELD INTO otherVar WHERE condition;....-or-SELECT NEEDED_FIELD INTO var WHERE condition;EXCEPTIONWHEN NO_DATA_FOUND....The second case seems a bit more elegant to me, because then I can use NEEDED_FIELD, which I would have had to select in the first statement after the condition in the first case. Less code. But if the stored procedure will run faster using the COUNT(*), then I don't mind typing a little more to make up processing speed.Any hints? Am I missing another possibility?EDITI should have mentioned that this is all already nested in a FOR LOOP. Not sure if this makes a difference with using a cursor, since I don't think I can DECLARE the cursor as a select in the FOR LOOP. 解决方案 I would not use an explicit cursor to do this. Steve F. no longer advises people to use explicit cursors when an implicit cursor could be used.The method with count(*) is unsafe. If another session deletes the row that met the condition after the line with the count(*), and before the line with the select ... into, the code will throw an exception that will not get handled.The second version from the original post does not have this problem, and it is generally preferred.That said, there is a minor overhead using the exception, and if you are 100% sure the data will not change, you can use the count(*), but I recommend against it.I ran these benchmarks on Oracle 10.2.0.1 on 32 bit Windows. I am only looking at elapsed time. There are other test harnesses that can give more details (such as latch counts and memory used).SQL>create table t (NEEDED_FIELD number, COND number);SQL>insert into t (NEEDED_FIELD, cond) values (1, 0);declare otherVar number; cnt number;begin for i in 1 .. 50000 loop select count(*) into cnt from t where cond = 1; if (cnt = 1) then select NEEDED_FIELD INTO otherVar from t where cond = 1; else otherVar := 0; end if; end loop;end;/declare otherVar number;begin for i in 1 .. 50000 loop begin select NEEDED_FIELD INTO otherVar from t where cond = 1; exception when no_data_found then otherVar := 0; end; end loop;end;/ 这篇关于Oracle PL / SQL - NO_DATA_FOUND异常对存储过程性能有坏处理吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！