如果存在问题，Oracle sql 返回 true
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4098800/
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 sql return true if exists question
提问by Matt
How do I check if a particular element exists in a table - how can I return true or false?
如何检查表中是否存在特定元素 - 如何返回 true 或 false？
I have a table that has
我有一张桌子
- user_id
- user_password
- user_secretQ
- 用户身份
- 用户密码
- user_secretQ
Verbally, I want to do this: If a particular user_idexists in the user_idcolumn, then return true -- otherwise return false.
口头上，我想这样做：如果列中user_id存在特定项user_id，则返回 true -- 否则返回 false。
回答by DCookie
There is no Boolean type in Oracle SQL. You will need to return a 1 or 0, or some such and act accordingly:
Oracle SQL 中没有布尔类型。您将需要返回 1 或 0 或类似的值并采取相应措施：
SELECT CASE WHEN MAX(user_id) IS NULL THEN 'NO' ELSE 'YES' END User_exists
FROM user_id_table
WHERE user_id = 'some_user';
回答by Tony Andrews
In PL/SQL you can do this:
在 PL/SQL 中，您可以这样做：
function user_exists (p_user_id users.user_id%type) return boolean
is
l_count integer;
begin
select count(*)
into l_count
from users
where user_id = p_user_id;
return (l_count > 0);
end;
This would then be used in calling PL/SQL like this:
这将用于调用 PL/SQL，如下所示：
if user_exists('john') then
dbms_output.put_Line('John exists');
end if;
NOTE: I used count(*) in the query in the knowledge that this will only return 1 or 0 in the case of a primary key search. If there could be more than one row then I would add "and rownum = 1" to the query to prevent unnecessarily counting many records just to find out if any exists:
注意：我在查询中使用了 count(*) ，因为知道在主键搜索的情况下这只会返回 1 或 0。如果可能有不止一行，那么我会在查询中添加“and rownum = 1”，以防止不必要地计算许多记录以找出是否存在：
function user_has_messages (p_user_id users.user_id%type) return boolean
is
l_count integer;
begin
select count(*)
into l_count
from messages
where user_id = p_user_id
AND ROWNUM = 1;
return (l_count > 0);
end;
回答by Johnbabu Koppolu
Oracle RDBMS does not have boolean data type, you can only use boolean variables in PL/SQL.
Oracle RDBMS 没有布尔数据类型，只能在 PL/SQL 中使用布尔变量。
If you simply want to return strings 'TRUE' and 'FALSE' you can do this..
如果您只想返回字符串 'TRUE' 和 'FALSE'，您可以这样做..
SELECT 'TRUE' FROM DUAL WHERE EXISTS (SELECT 'x' FROM table WHERE user_id = 'id')
UNION
SELECT 'FALSE' FROM DUAL WHERE NOT EXISTS (SELECT 'x' FROM table WHERE user_id = 'id')
I like @DCookie's query though.
不过我喜欢@DCookie 的查询。
回答by grokster
select count(*) from table where userid = :userid and rownum <= 1); -- If exists then 1 else 0
select count(*) from table where userid = :userid and rownum <= 1); -- If exists then 1 else 0
回答by mk.chan
Or you could do this:
或者你可以这样做：
select decode(max(USER_ID), null, 'FALSE', 'TRUE') BOOL_VAL
from USER_TABLE where USER_ID = [some USER_ID here]
