Oracle PL/SQL - 使用“SELECT INTO”时出现 ORA-01403“未找到数据”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22026100/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle PL/SQL - ORA-01403 "No data found" when using "SELECT INTO"
提问by mathielo
I faced this problem while developing a Trigger in Oracle: ORA-01403: no data found. I did some research and understood the root of the problem. Nevertheless handling the error exceptionprevents the above error, but does not solve my problem.
我在 Oracle 中开发触发器时遇到了这个问题：ORA-01403: no data found。我做了一些研究并了解了问题的根源。尽管如此，处理错误异常可以防止上述错误，但不能解决我的问题。
What I am currently looking for is an optimalworkaround to perform the lesser query amount/achieve the best performance as possible. I'll try to describe the scenario creating simple examples to the real structure.
我目前正在寻找的是执行较少查询量/尽可能实现最佳性能的最佳解决方法。我将尝试描述为真实结构创建简单示例的场景。
Scenario
设想
I have a "date reference" table to establish periods of time, say:
我有一个“日期参考”表来确定时间段，例如：
CREATE TABLE DATE_REFERENCE (
DATE_START DATE NOT NULL,
DATE_END DATE NOT NULL,
-- Several other columns here, this is just a silly example
CONSTRAINT PK_DATE_REFERENCE PRIMARY KEY(DATE_START, DATE_END)
);
When the trigger is triggered, I'll have one DATEfield - say DATE_GIVEN(for example sake). What I need is:
当触发器被触发时，我将有一个DATE字段 - 比如说DATE_GIVEN（例如缘故）。我需要的是：
- To find the
DATE_REFERENCErow in whichDATE_GIVEN BETWEEN DATE_START AND DATE_END(easy); OR - If the previous option returns no data, I need to find the next closest
DATE_STARTtoDATE_GIVEN.
- 找到其中的
DATE_REFERENCE行DATE_GIVEN BETWEEN DATE_START AND DATE_END（简单）；或者 - 如果前面的选项返回没有数据，我需要找到下一个最近的
DATE_START到DATE_GIVEN。
In both cases, I need to retrieve the row with all columnsfrom table DATE_REFERENCE, no matter if it matches Opt 1 or 2. That's exactly where I faced the problem described.
在这两种情况下，我都需要从 table 中检索包含所有列的行DATE_REFERENCE，无论它是否与选项 1 或选项 2 匹配。这正是我遇到所描述问题的地方。
I wrote this test block to testand try to find a solution. The example below is not working, I know; but it is exactlywhat I want to accomplish(in concept). I have added comments like -- Lots of codeto make clear that will be part of a more elaborate trigger:
我写了这个测试块来测试并尝试找到解决方案。下面的例子不起作用，我知道；但这正是我想要完成的（在概念上）。我添加了一些评论-- Lots of code，以明确表示这将成为更复杂的触发器的一部分：
DECLARE
DATE_GIVEN DATE;
RESULTROW DATE_REFERENCE%ROWTYPE;
BEGIN
-- Lots of code
-- Lots of code
-- Lots of code
DATE_GIVEN := TO_DATE('2014-02-26 12:30:00', 'YYYY-MM-DD HH24:MI:SS');
-- This one throws the ORA-01403 exception if no data was found
SELECT
* INTO RESULTROW
FROM
DATE_REFERENCE
WHERE
DATE_GIVEN BETWEEN DATE_START AND DATE_END;
-- If the above didn't throw exceptions, I would continue like so:
IF RESULTROW IS NULL THEN
SELECT
* INTO RESULTROW
FROM
DATE_REFERENCE
WHERE
DATE_START > DATE_GIVEN
AND ROWNUM = 1
ORDER BY DATE_START ASC;
END IF;
-- Now RESULTROW is populated, and the rest of the trigger code gets executed ~beautifully~
-- Lots of code
-- Lots of code
-- Lots of code
END;
Question
题
Knowing that the above PL/SQL block is more of a conceptthan working code, what is the best way to get RESULTROWpopulated, minding performance and the lesser queries as possible?
知道上面的 PL/SQL 块更多的是一个概念而不是工作代码，那么获得RESULTROW填充、注意性能和尽可能少的查询的最佳方法是什么？
Sorry for the long question, but I figured scenario explanation was necessary. Thanks in advance for any help/thoughts!
抱歉问了这么长的问题，但我认为场景解释是必要的。提前感谢您的任何帮助/想法！
回答by Gordon Linoff
Just populate the field directly, using ordering and rownum:
只需使用排序和直接填充字段rownum：
SELECT * INTO RESULTROW
FROM (SELECT *
FROM DATE_REFERENCE
ORDER BY (CASE WHEN DATE_GIVEN BETWEEN DATE_START AND DATE_END
THEN 1 ELSE 0
END) DESC,
(DATE_START - DATE_GIVEN)
) t
WHERE rownum = 1;
This will populate the information with one query.
这将使用一个查询填充信息。
EDIT:
编辑：
If you want to put a condition in the subquery, it needs to be:
如果要在子查询中放置条件，则需要：
SELECT * INTO RESULTROW
FROM (SELECT *
FROM DATE_REFERENCE
WHERE DATE_GIVEN <= DATE_END
ORDER BY (CASE WHEN DATE_GIVEN BETWEEN DATE_START AND DATE_END
THEN 1 ELSE 0
END) DESC,
(DATE_START - DATE_GIVEN)
) t
WHERE rownum = 1;
I believe the right condition is DATE_GIVEN <= DATE_END. This covers both the betweencondition and should imply DATE_GIVEN < DATE_START. This assumes that DATE_ENDis never NULL.
我相信正确的条件是DATE_GIVEN <= DATE_END。这包括between条件和应该暗示DATE_GIVEN < DATE_START。这假设DATE_END是 never NULL。
回答by Piotr Czapliński
I also had similar problem, resolved it like this:
我也有类似的问题，解决方法如下：
if the row does not exists in table LADDER.INCR_PROCESSI get IsPassedas Null:
如果该行在表中不存在，LADDER.INCR_PROCESS我会得到IsPassedNull：
Declare
IsPassed Integer ;
Begin
Select I.LVL Into IsPassed
From LADDER.INCR_PROCESS I
Right
Join Dual on I.LVL >= 90010 and I.Passed = 0
Where RowNum = 1 ;
....
End;
