检查表 [pl/sql 块] 中是否存在行的正确方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21140868/
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
Proper way of checking if row exists in table [pl/sql block]
提问by devBem
I was writing some tasks yesterday and it struck me that I don't really know THE PROPER and ACCEPTED wayof checking if row exists in table when I'm using pl/sql.
昨天我正在写一些任务，让我震惊的是，当我使用 pl/sql 时，我真的不知道检查表中是否存在行的正确和可接受的方法。
For examples sake let's use table
例如，让我们使用 table
PERSON(ID, Name);
Obviously I can't do(unless there's some secret method) something like:
显然我不能做（除非有一些秘密方法）这样的事情：
BEGIN
IF EXISTS SELECT id FROM person WHERE ID = 10;
-- do things when exists
ELSE
-- do things when doesn't exist
END IF;
END;
So my standard way of solving it was:
所以我解决它的标准方法是：
DECLARE
tmp NUMBER;
BEGIN
SELECT id INTO tmp FROM person WHERE id = 10;
--do things when record exists
EXCEPTION
WHEN no_data_found THEN
--do things when record doesn't exist
END;
However I don't know if it's accepted way of doing it, or if there's any better way of checking, I would really apprieciate if someone could share their wisdom with me :)
但是我不知道这样做是否被接受，或者是否有更好的检查方法，如果有人可以与我分享他们的智慧，我真的很感激:)
Cheers.
干杯。
回答by David Aldridge
I wouldn't push regular code into an exception block. Just check whether any rows exist that meet your condition, and proceed from there:
我不会将常规代码推送到异常块中。只需检查是否存在满足您的条件的任何行，然后从那里继续：
declare
any_rows_found number;
begin
select count(*)
into any_rows_found
from my_table
where rownum = 1 and
... other conditions ...
if any_rows_found = 1 then
...
else
...
end if;
回答by Bob Jarvis - Reinstate Monica
IMO code with a stand-alone SELECT used to check to see if a row exists in a table is not taking proper advantage of the database. In your example you've got a hard-coded ID value but that's not how apps work in "the real world" (at least not in myworld - yours may be different :-). In a typical app you're going to use a cursor to find data - so let's say you've got an app that's looking at invoice data, and needs to know if the customer exists. The main body of the app might be something like
带有用于检查表中是否存在行的独立 SELECT 的 IMO 代码没有充分利用数据库。在您的示例中，您有一个硬编码的 ID 值，但这不是应用程序在“现实世界”中的工作方式（至少在我的世界中不是- 您的可能不同:-）。在典型的应用程序中，您将使用游标来查找数据 - 假设您有一个正在查看发票数据的应用程序，并且需要知道客户是否存在。应用程序的主体可能类似于
FOR aRow IN (SELECT * FROM INVOICES WHERE DUE_DATE < TRUNC(SYSDATE)-60)
LOOP
-- do something here
END LOOP;
and in the -- do something hereyou want to find if the customer exists, and if not print an error message.
并在-- do something here您要查找客户是否存在，如果不存在则打印错误消息。
One way to do this would be to put in some kind of singleton SELECT, as in
一种方法是放入某种单例 SELECT，如
-- Check to see if the customer exists in PERSON
BEGIN
SELECT 'TRUE'
INTO strCustomer_exists
FROM PERSON
WHERE PERSON_ID = aRow.CUSTOMER_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
strCustomer_exists := 'FALSE';
END;
IF strCustomer_exists = 'FALSE' THEN
DBMS_OUTPUT.PUT_LINE('Customer does not exist!');
END IF;
but IMO this is relatively slow and error-prone. IMO a Better Way (tm) to do this is to incorporate it in the main cursor:
但 IMO 这相对较慢且容易出错。IMO 一个更好的方法 (tm) 是将它合并到主光标中：
FOR aRow IN (SELECT i.*, p.ID AS PERSON_ID
FROM INVOICES i
LEFT OUTER JOIN PERSON p
ON (p.ID = i.CUSTOMER_PERSON_ID)
WHERE DUE_DATA < TRUNC(SYSDATE)-60)
LOOP
-- Check to see if the customer exists in PERSON
IF aRow.PERSON_ID IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Customer does not exist!');
END IF;
END LOOP;
This code counts on PERSON.ID being declared as the PRIMARY KEY on PERSON (or at least as being NOT NULL); the logic is that if the PERSON table is outer-joined to the query, and the PERSON_ID comes up as NULL, it means no row was found in PERSON for the given CUSTOMER_ID because PERSON.ID must have a value (i.e. is at least NOT NULL).
此代码依赖于 PERSON.ID 被声明为 PERSON 上的 PRIMARY KEY（或至少为 NOT NULL）；逻辑是，如果 PERSON 表外部连接到查询，并且 PERSON_ID 出现为 NULL，则意味着没有在 PERSON 中找到给定 CUSTOMER_ID 的行，因为 PERSON.ID 必须有一个值（即至少不是空值）。
Share and enjoy.
分享和享受。
回答by gfrobenius
Many ways to skin this cat. I put a simple function in each table's package...
给这只猫剥皮的方法很多。我在每个表的包中放了一个简单的函数......
function exists( id_in in yourTable.id%type ) return boolean is
res boolean := false;
begin
for c1 in ( select 1 from yourTable where id = id_in and rownum = 1 ) loop
res := true;
exit; -- only care about one record, so exit.
end loop;
return( res );
end exists;
Makes your checks really clean...
让你的支票真正干净...
IF pkg.exists(someId) THEN
...
ELSE
...
END IF;
回答by Andreas Wismann
select nvl(max(1), 0) from mytable;
This statement yields 0 if there are no rows, 1 if you have at least one row in that table. It's way faster than doing a select count(*). The optimizer "sees" that only a single row needs to be fetched to answer the question.
如果没有行，则此语句产生 0，如果该表中至少有一行，则产生 1。它比选择计数（*）快得多。优化器“看到”只需要提取一行来回答问题。
Here's a (verbose) little example:
这是一个（详细的）小例子：
declare
YES constant signtype := 1;
NO constant signtype := 0;
v_table_has_rows signtype;
begin
select nvl(max(YES), NO)
into v_table_has_rows
from mytable -- where ...
;
if v_table_has_rows = YES then
DBMS_OUTPUT.PUT_LINE ('mytable has at least one row');
end if;
end;
回答by Dinidu Hewage
If you are using an explicit cursor, It should be as follows.
如果您使用的是显式游标，它应该如下所示。
DECLARE
CURSOR get_id IS
SELECT id
FROM person
WHERE id = 10;
id_value_ person.id%ROWTYPE;
BEGIN
OPEN get_id;
FETCH get_id INTO id_value_;
IF (get_id%FOUND) THEN
DBMS_OUTPUT.PUT_LINE('Record Found.');
ELSE
DBMS_OUTPUT.PUT_LINE('Record Not Found.');
END IF;
CLOSE get_id;
EXCEPTION
WHEN no_data_found THEN
--do things when record doesn't exist
END;
回答by Robert Tebiev
select max( 1 )
into my_if_has_data
from MY_TABLE X
where X.my_field = my_condition
and rownum = 1;
Not iterating through all records.
不遍历所有记录。
If MY_TABLEhas no data, then my_if_has_datasets to null.
如果MY_TABLE没有数据，则my_if_has_data设置为 null。
回答by Mohsen Heydari
Select 'YOU WILL SEE ME' as ANSWER from dual
where exists (select 1 from dual where 1 = 1);
Select 'YOU CAN NOT SEE ME' as ANSWER from dual
where exists (select 1 from dual where 1 = 0);
Select 'YOU WILL SEE ME, TOO' as ANSWER from dual
where not exists (select 1 from dual where 1 = 0);
