SQL 检查 Oracle 中是否存在记录的最有效方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3434437/
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's the most efficient way to check if a record exists in Oracle?
提问by Please click here
A)
一种）
select decode(count(*), 0, 'N', 'Y') rec_exists
from (select 'X'
from dual
where exists (select 'X'
from sales
where sales_type = 'Accessories'));
B)
乙)
select decode(count(*), 0, 'N', 'Y') rec_exists
from (select 'X'
from sales
where sales_type = 'Accessories');
C) Something else (specify)
C) 别的东西（具体说明）
EDIT: It was hard to pick the "correct" answer, as the best approach depends on what you want to do after checking if the value exists, as pointed out by APC. I ended up picking the answer by RedFilter, since I had originally envisioned this check as a function by itself.
编辑：很难选择“正确”的答案，因为最佳方法取决于您在检查值是否存在后想要做什么，正如 APC 所指出的那样。我最终选择了 RedFilter 的答案，因为我最初设想这个检查本身就是一个函数。
回答by RedFilter
select case
when exists (select 1
from sales
where sales_type = 'Accessories')
then 'Y'
else 'N'
end as rec_exists
from dual;
回答by APC
What is the underlying logic you want to implement? If, for instance, you want to test for the existence of a record to determine to insert or update then a better choice would be to use MERGEinstead.
您要实现的底层逻辑是什么？例如，如果您想测试记录是否存在以确定插入或更新，那么更好的选择是改用MERGE。
If you expect the record to exist most of the time, this is probably the most efficient way of doing things (although the CASE WHEN EXISTSsolution is likely to be just as efficient):
如果您希望记录大部分时间都存在，这可能是最有效的处理方式（尽管CASE WHEN EXISTS解决方案可能同样有效）：
begin
select null into dummy
from sales
where sales_type = 'Accessories'
and rownum = 1;
-- do things here when record exists
....
exception
when no_data_found then
-- do things here when record doesn't exists
.....
end;
You only need the ROWNUM line if SALES_TYPE is not unique. There's no point in doing a count when all you want to know is whether at least one record exists.
如果 SALES_TYPE 不是唯一的，则您只需要 ROWNUM 行。当您只想知道是否至少存在一条记录时，进行计数是没有意义的。
回答by tyger
select count(1) into existence
from sales where sales_type = 'Accessories' and rownum=1;
Oracle plan says that it costs 1 if seles_type column is indexed.
Oracle 计划说如果 seles_type 列被索引，它的成本是 1。
回答by Aashish
here you can check only y , n if we need to select a name as well that whether this name exists or not.
在这里你可以只检查 y , n 如果我们需要选择一个名字以及这个名字是否存在。
select name , decode(count(name),0, 'N', 'Y')
from table
group by name;
Here when it is Y only then it will return output otherwise it will give null always. Whts ths way to get the records not existing with N like in output we will get Name , N. When name is not existing in table
这里只有当它是 Y 时，它才会返回输出，否则它总是给 null。用这种方法来获取不存在 N 的记录，就像在输出中一样，我们将得到 Name ，N。当表中不存在 name 时
回答by Randy
select decode(count(*), 0, 'N', 'Y') rec_exists
from sales
where sales_type = 'Accessories';
回答by Dominic Isaia
Simply get a count of the record(s) you're looking for. If count > 0 then record(s) exist.
只需计算您要查找的记录数即可。如果计数> 0，则记录存在。
DECLARE
rec_count NUMBER := 0;
BEGIN
select count(*)
into rec_count
from EMPLOYEETABLE
WHERE employee_id = inEMPLOYEE_ID
AND department_nbr = inDEPARTMENT_NBR;
if rec_count > 0 then
{UPDATE EMPLOYEETABLE}
else
{INSERT INTO EMPLOYEETABLE}
end if;
END;
回答by nofil qureshi
select CASE
when exists (SELECT U.USERID,U.USERNAME,U.PASSWORDHASH
FROM TBLUSERS U WHERE U.USERID =U.USERID
AND U.PASSWORDHASH=U.PASSWORDHASH)
then 'OLD PASSWORD EXISTS'
else 'OLD PASSWORD NOT EXISTS'
end as OUTPUT
from DUAL;
回答by Awknewbie
select NVL ((select 'Y' from dual where exists
(select 1 from sales where sales_type = 'Accessories')),'N') as rec_exists
from dual
1.Dual table will return 'Y' if record exists in sales_type table 2.Dual table will return null if no record exists in sales_type table and NVL will convert that to 'N'
1.如果sales_type表中存在记录，双表将返回'Y' 2.如果sales_type表中不存在记录，双表将返回空值，NVL将其转换为'N'
回答by Jeremy
The most efficient and safest way to determine if a row exists is by using a FOR-LOOP... You won't even have a difficult time if you are looking to insert a row or do something based on the row NOT being there but, this will certainly help you if you need to determine if a row exists. See example code below for the ins and outs...
确定一行是否存在的最有效和最安全的方法是使用 FOR-LOOP ......如果您要插入一行或根据不存在的行执行某些操作，您甚至不会遇到困难，如果您需要确定一行是否存在，这肯定会对您有所帮助。有关详细信息，请参阅下面的示例代码...
If you are only interested in knowing that 1 record exists in your potential multiple return set, than you can exit your loop after it hits it for the first time.
如果您只想知道潜在的多重返回集中存在 1 条记录，那么您可以在它第一次命中后退出循环。
The loop will not be entered into at all if no record exists. You will not get any complaints from Oracle or such if the row does not exist but you are bound to find out if it does regardless. Its what I use 90% of the time (of course dependent on my needs)...
如果不存在记录，则根本不会进入循环。如果该行不存在，您将不会收到来自 Oracle 的任何投诉，但您一定会发现它是否存在。它是我 90% 的时间使用的（当然取决于我的需要）......
EXAMPLE:
例子：
DECLARE
v_exist varchar2(20);
BEGIN
FOR rec IN
(SELECT LOT, COMPONENT
FROM TABLE
WHERE REF_DES = (SELECT REF_DES FROM TABLE2 WHERE ORDER = '1234')
AND ORDER = '1234')
LOOP
v_exist := "IT_EXISTS"
INSERT INTO EAT_SOME_SOUP_TABLE (LOT, COMPONENT)
VALUES (rec.LOT, rec.COMPONENT);**
--Since I don't want to do this for more than one iteration (just in case there may have been more than one record returned, I will EXIT;
EXIT;
END LOOP;
IF v_exist IS NULL
THEN
--do this
END IF;
END;
--This is outside the loop right here The IF-CHECK just above will run regardless, but then you will know if your variable is null or not right!?. If there was NO records returned, it will skip the loop and just go here to the code you would have next... If (in our case above), 4 records were returned, I would exit after the first iteration due to my EXIT;... If that wasn't there, the 4 records would iterate through and do an insert on all of them. Or at least try too.
-- 这在此处的循环之外 上面的 IF-CHECK 无论如何都会运行，但是您将知道您的变量是否为空或不正确！？。如果没有返回记录，它将跳过循环并直接转到下一个代码...如果（在我们上面的例子中），返回了 4 条记录，由于我的 EXIT，我将在第一次迭代后退出;... 如果那不存在，这 4 条记录将遍历并对所有记录进行插入。或者至少也尝试一下。
By the way, I'm not saying this is the only way you should consider doing this... You can
顺便说一句，我并不是说这是你应该考虑这样做的唯一方法......你可以
SELECT COUNT(*) INTO v_counter WHERE ******* etc...
Then check it like
然后检查它像
if v_counter > 0
THEN
--code goes here
END IF;
There are more ways... Just determine it when your need arises. Keep performance in mind, and safety.
还有更多的方法......当你需要的时候确定它。牢记性能和安全。
回答by EvilTeach
SELECT 'Y' REC_EXISTS
FROM SALES
WHERE SALES_TYPE = 'Accessories'
The result will either be 'Y' or NULL. Simply test against 'Y'
结果将是“Y”或 NULL。简单地测试“Y”
