处理此 Oracle ORA-01403: no data found Exception 的正确方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9104153/
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
What is the correct way to deal with this Oracle ORA-01403: no data found Exception?
提问by Chris Holmes
I have a DB table that I am more or less treating like a queue. I'm trying to fetch a single item from it. This works, except when the SELECT..INTO fails (which can happen if there is only one item in the queue and two users on separate machines try and fetch it; only one is going to win).
我有一个数据库表，我或多或少地将其视为队列。我正在尝试从中获取单个项目。这是有效的，除非 SELECT..INTO 失败（如果队列中只有一个项目并且两个用户在不同的机器上尝试获取它，就会发生这种情况；只有一个会获胜）。
This leads to the familiar ORA-01403: no data foundexception. I tried to change the SP so that it would return NULL record in this case - the same sort of result you'd get it a query couldn't find any records - but to no avail. I am doing something wrong here.
这导致了熟悉的ORA-01403: no data found异常。我试图更改 SP，以便在这种情况下它会返回 NULL 记录 - 与查询找不到任何记录时得到的结果相同 - 但无济于事。我在这里做错了。
PROCEDURE sp_GetNextEmailFromQueue (pAgentId IN NUMBER, pRecs OUT recordSet)
IS
EMAIL_ID INTEGER;
BEGIN
SELECT id INTO EMAIL_ID FROM
(SELECT id, is_replied_to, is_being_worked, date_received
FROM SSQ_EMAILS
WHERE is_replied_to = 0 AND is_being_worked =0
ORDER BY date_received ASC)
WHERE rownum = 1;
UPDATE SSQ_EMAILS x
SET x.is_being_worked = 1,
x.agent_id = pAgentId,
x.work_started_date = SYSDATE
WHERE x.id = EMAIL_ID;
OPEN pRecs FOR
SELECT x.id,
x.message_id,
x.to_email,
x.from_email,
x.subject,
x.message,
x.date_received,
x.href_link,
x.is_being_worked,
x.work_started_date,
x.is_replied_to
FROM SSQ_EMAILS x
WHERE x.id = EMAIL_ID;
EXCEPTION
WHEN no_data_found
THEN
OPEN pRecs FOR
SELECT NULL
FROM SSQ_EMAILS;
END;
回答by Justin Cave
I'd put the exception handler around the piece of code that is actually causing the error to be thrown. If email_idis NULL, the UPDATEwill not update any rows and the SELECTwill not return any rows.
我会将异常处理程序放在实际导致抛出错误的代码段周围。如果email_id为 NULL，UPDATE则不会更新任何行并且SELECT不会返回任何行。
PROCEDURE sp_GetNextEmailFromQueue (pAgentId IN NUMBER, pRecs OUT recordSet)
IS
EMAIL_ID INTEGER;
BEGIN
BEGIN
SELECT id
INTO EMAIL_ID
FROM (SELECT id, is_replied_to, is_being_worked, date_received
FROM SSQ_EMAILS
WHERE is_replied_to = 0 AND is_being_worked =0
ORDER BY date_received ASC)
WHERE rownum = 1;
EXCEPTION
WHEN no_data_found
THEN
email_id := null;
END;
UPDATE SSQ_EMAILS x
SET x.is_being_worked = 1,
x.agent_id = pAgentId,
x.work_started_date = SYSDATE
WHERE x.id = EMAIL_ID;
OPEN pRecs FOR
SELECT x.id,
x.message_id,
x.to_email,
x.from_email,
x.subject,
x.message,
x.date_received,
x.href_link,
x.is_being_worked,
x.work_started_date,
x.is_replied_to
FROM SSQ_EMAILS x
WHERE x.id = EMAIL_ID;
END;
Be aware, though, that this code does not prevent two different callers from working on the same row. If two session call this procedure at the same time, it's entirely possible that both will select the same row. If you want to prevent that, the SELECTwould need to lock the row it selected with the FOR UPDATEclause.
但是请注意，此代码不会阻止两个不同的调用者在同一行上工作。如果两个会话同时调用此过程，则完全有可能两者都选择同一行。如果您想防止这种情况发生，则SELECT需要使用FOR UPDATE子句锁定它选择的行。
回答by Sérgio Michels
You can do nothing in this case:
在这种情况下，您无能为力：
exception
when no_data_found then
null;
end;
This will return null in pRecsright?
这将返回 nullpRecs对吗？
EDIT
编辑
Second approach:
第二种方法：
cursor c_mail is
SELECT id
FROM
(SELECT id, is_replied_to, is_being_worked, date_received
FROM SSQ_EMAILS
WHERE is_replied_to = 0 AND is_being_worked =0
ORDER BY date_received ASC)
WHERE rownum = 1;
....
open c_mail;
fetch c_mail into email_id; -- no_data_found not happens
close c_mail;
回答by miazo
How about this, avoiding exception handling:
这个怎么样，避免异常处理：
/* returns X */
SELECT DUMMY FROM DUAL WHERE 1 = 1;
/* no data found */
SELECT DUMMY FROM DUAL WHERE 1 = 0;
/* returns NULL */
SELECT MIN(DUMMY) FROM DUAL WHERE 1 = 0;
回答by Alex Poole
select null from ssq_emailswill still get a 1403 if there are no records in the table. I'm not sure you want to do anything in the exception handler; not sure how your caller will deal with pRecsbeing empty though.
select null from ssq_emails如果表中没有记录，仍然会得到 1403。我不确定你想在异常处理程序中做任何事情；不知道你的来电者将如何处理pRecs空的问题。
回答by Chris Holmes
Solved it by doing this:
通过这样做解决了它：
EXCEPTION
WHEN no_data_found THEN
OPEN pRecs FOR
SELECT NULL
FROM SSQ_EMAILS s
WHERE s.id IS NULL;
It works because the RefCursor has to be opened. I need an empty result, and this seems like a safe way to guarantee that, because the ID is the PK and cannot be null.
它起作用是因为必须打开 RefCursor。我需要一个空的结果，这似乎是一种安全的保证方法，因为 ID 是 PK 并且不能为空。
