Oracle 9i：据说缺少右括号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/448821/
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 9i: Supposedly missing right parenthesis
提问by Mike Hofer
I have a simple function written in Oracle 9i (version 9.2.0.4.0) to simulate an in-line IF. For those interested, here's the code:
我有一个用 Oracle 9i（版本 9.2.0.4.0）编写的简单函数来模拟内嵌 IF。有兴趣的可以看一下代码：
create or replace
FUNCTION IIF
(testExpression NUMBER,
trueResult NUMBER,
falseResult NUMBER)
RETURN NUMBER
AS
BEGIN
/*
A simple in-line IF function for use with SQL queries. If the test
expression evaluates to any non-zero value, it is considered to be
true, and the trueResult is returned. Otherwise, falseResult is
returned.
*/
IF (testExpression is null) or (testExpression = 0) THEN
return falseResult;
ELSE
return trueResult;
END IF;
END IIF;
This isn't rocket science. Now, here's the big mystery: if I execute the following SQL statements, everything is just fine, and works exactly as I expect:
这不是火箭科学。现在，这是一个大谜团：如果我执行以下 SQL 语句，一切都很好，并且完全符合我的预期：
SELECT IIF(1, 'true', 'false') FROM DUAL;
SELECT IIF(0, 'false', 'true') FROM DUAL;
However, the following generates a really bizarre error from Oracle:
但是，以下内容会从 Oracle 中生成一个非常奇怪的错误：
SELECT IIF((0 = 1), 'false', 'true') FROM DUAL;
That error is as follows:
该错误如下：
ORA-00907: missing right parenthesis.
Clearly, that isn't the case. Would anyone happen to have an explanation for this little bit of bizarreness?
显然，事实并非如此。有人会碰巧对这种奇怪的现象做出解释吗？
It's taking a whole lot of self-control at the moment to restrain myself from hurling the Oracle server out the window. Oracle seems rifewith these kinds of inanities.
目前需要大量的自我控制才能克制自己将 Oracle 服务器扔出窗外。甲骨文似乎充斥着这种愚蠢的行为。
EDIT: Is there some kind of magic syntax I have to use to use an equality operator in a select statement?
编辑：我必须使用某种魔法语法来在 select 语句中使用相等运算符吗？
回答by Jon Ericson
Maybe you can explain what you are trying to do. I think you are looking for the CASEor DECODEfunctions, but I can't be sure. It seems like you are working against the grain of the SQL language for some reason.
也许你可以解释你想要做什么。我认为您正在寻找CASEorDECODE功能，但我不能确定。出于某种原因，您似乎在与 SQL 语言的粒度背道而驰。
The error occurs because Oracle doesn't expect a relational operator in the selectclause of the query:
发生错误是因为 Oracle 不希望select在查询子句中使用关系运算符：
SQL> SELECT IIF(1>7, 0, 1) FROM DUAL;
SELECT IIF(1>7, 0, 1) FROM DUAL
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> SELECT 1=0 FROM DUAL;
SELECT 1=0 FROM DUAL
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
See this Ask Tom article.
请参阅此 Ask Tom 文章。
回答by Gary Myers
You want
你要
SELECT CASE WHEN *expr* then 'true' else 'false' end col_alias FROM DUAL;
or for simple equality test
或用于简单的相等测试
SELECT DECODE(*val1*,*val2*,'true','false') col_alias FROM dual;
This will perform much better than dropping out to a PL/SQL function. There's a number of reasons why your approach doesn't work.
这将比退出 PL/SQL 函数执行得更好。您的方法不起作用的原因有很多。
Booleans aren't an Oracle data type.
They are a PL/SQL datatype, but even then there's no implicit conversion to numbers (zero false, non-zero true)
There's no 'expression' datatype, so the SQL engine can't pass the expression to PL/SQL.
Even if you passes the expression as a string to PL/SQL, the PL/SQL engine cannot dynamically evaluate an expression in a string. It would have to build the string into a dynamic SQL statement and execute that dynamically, returning a result (in a valid SQL datatype, like a string or number).
布尔值不是 Oracle 数据类型。
它们是 PL/SQL 数据类型，但即使如此也没有隐式转换为数字（零假，非零真）
没有“表达式”数据类型，因此 SQL 引擎无法将表达式传递给 PL/SQL。
即使您将表达式作为字符串传递给 PL/SQL，PL/SQL 引擎也无法动态计算字符串中的表达式。它必须将字符串构建到动态 SQL 语句中并动态执行，返回结果（以有效的 SQL 数据类型，如字符串或数字）。
回答by DCookie
- There is no BOOLEAN type in the Oracle database, so you can't use an expression like "(1=0)".
- Even if there were a BOOLEAN type, you have declared the first argument to be of type NUMBER. You have to pass it a number, or something that can be implicitly converted to one.
- Finally, I don't understand how any of your examples actually produced anything besides "invalid number", since your second and third arguments can't be converted to numbers, either.
- Oracle 数据库中没有 BOOLEAN 类型，因此不能使用“(1=0)”之类的表达式。
- 即使存在 BOOLEAN 类型，您也已将第一个参数声明为 NUMBER 类型。你必须给它传递一个数字，或者一些可以隐式转换为 1 的东西。
- 最后，我不明白您的任何示例实际上如何产生“无效数字”之外的任何内容，因为您的第二个和第三个参数也无法转换为数字。
回答by Quintin Robinson
Having never worked with oracle I can't directly offer any sound advice, but looking at the statement it could be oracle having a problem inferring number from boolean?..
从未与 oracle 合作过，我无法直接提供任何合理的建议，但查看声明可能是 oracle 在从布尔值推断数字时出现问题？...
The IIF signature is expecting a NUMBER as the first argument and in the call you are passing a BOOL evaluation.
IIF 签名期望 NUMBER 作为第一个参数，并且在调用中您将传递 BOOL 评估。
This is just a thought.
这只是一个想法。
回答by tuinstoel
Besides decode and case, there is also nvl.
除了解码和大小写，还有 nvl。
