oracle PL SQL：NVL 第一个参数类型转换问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5155937/
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
PL SQL : NVL first parameter type conversion issue
提问by nandin
I have a table Application which has a column
我有一个表应用程序，其中有一列
BORROWINGTERM NUMBER(10,0) Nullable
why this script throw an error (ORA-01722 invalid number)
为什么这个脚本会抛出错误（ORA-01722 无效数字）
select nvl(borrowingterm, 'no term') from Application
while this one works
虽然这个有效
select nvl(to_char(borrowingterm), 'no term') from Application
and this one also works
这个也有效
select nvl(1234,5678) from dual;
base on this article
基于这篇文章
the first parameter of NVL function should be string type
NVL函数的第一个参数应该是字符串类型
回答by Lost in Alabama
You're getting the error because you are mixing two different types in the nvl clause. One number, one string. The types must be the same.
您收到错误是因为您在 nvl 子句中混合了两种不同的类型。一个数字，一个字符串。类型必须相同。
回答by Pavan Ebbadi
Keeping it simple ,
保持简单，
- String null can be replaced with string Substitute value.
- Number null can be replaced by number value and so on..
- 字符串 null 可以替换为字符串替换值。
- 数字 null 可以替换为数字值等..
Example:
例子：
select nvl(to_char(borrowingterm), 'no term') from Application;
//conert number to string and then provide string substitute value to column
//conert number to string and then provide string substitute value to column
select nvl(borrowingterm, 0') from Application;
// replacing null with 0
// replacing null with 0
回答by pablo
In short, if the first argument is numeric then Oracle attempts to convert the second argument to a number.
简而言之，如果第一个参数是数字，那么 Oracle 会尝试将第二个参数转换为数字。
See NVL documentation
请参阅 NVL 文档
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions105.htm#i91798
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions105.htm#i91798
回答by Joe Stefanelli
In your first example, because borrowingtermis a numeric type, Oracle is trying to implicitly convert the string 'no term' to a numeric, hence the error.
在您的第一个示例中，由于borrowingterm是数字类型，因此 Oracle 试图将字符串 'no term' 隐式转换为数字，因此出现错误。
See the NVL documentation.
请参阅NVL 文档。
回答by Erkan Haspulat
"the first parameter of NVL function should be string type"
“NVL函数的第一个参数应该是字符串类型”
I think you gave the answer yourself. If you are asking why, you should consider the possibility of reusing the output type of NVL in another function.
我想你自己给出了答案。如果你问为什么，你应该考虑在另一个函数中重用 NVL 的输出类型的可能性。
select to_date(nvl(number_field, 'some_text')) from dual
In the situation above, the output of nvl is unknown, thus an ORA-01722 is raised.
在上述情况下，nvl 的输出未知，因此引发 ORA-01722。
