我可以在 Oracle 中为 varchar2 传递一个数字吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10420376/
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
Can I pass a number for varchar2 in Oracle?
提问by Victor
I have an Oracle table and a column (col1) has type varchar2(12 byte). It has one row and value of col1is 1234
我有一个 Oracle 表和一个列 ( col1) 有类型varchar2(12 byte)。它有一个行的值col1就是1234
When I say
当我说
select * from table where col1 = 1234
Oracle says invalid number. Why is that? Why I cannot pass a number when it is varchar2?
Oracle 说无效号码。这是为什么？为什么我不能传递一个数字varchar2？
EDIT: All the responses are great. Thank you. But I am not able to understand why it does not take 1234when 1234is a valid varchar2 datatype.
编辑：所有的反应都很棒。谢谢你。但我无法理解为什么它并不需要1234的时候1234是有效的VARCHAR2数据类型。
采纳答案by JAQFrost
The problem is that you expect that Oracle will implicitly cast 1234 to a character type. To the contrary, Oracle is implicitly casting the column to a number. There is a non-numeric value in the column, so Oracle throws an error. The Oracle documentationwarns against implicit casts just before it explains how they will be resolved. The rule which explains the behaviour you're seeing is:
问题是您希望 Oracle 将 1234 隐式转换为字符类型。相反，Oracle 将列隐式转换为数字。列中有一个非数字值，所以 Oracle 会抛出错误。在Oracle文档警告说，不要隐式转换之前它解释了他们将如何得到解决。解释您所看到的行为的规则是：
When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.
将字符值与数值进行比较时，Oracle 会将字符数据转换为数值。
回答by Sathyajith Bhat
Oracle says invalid number. Why is that? Why I cannot pass a number when it is varchar2?
Oracle 说无效号码。这是为什么？为什么当它是 varchar2 时我不能传递一个数字？
Oracle does an implicit conversion from character type of col1to number, since you're comparing it as a number.
Oracle 进行了从字符类型col1到数字的隐式转换，因为您将其作为数字进行比较。
Also, you assume that 1234is the onlyrow that's being fetched. In reality, Oracle has to fetch all rows from the table, and then filter out as per the whereclause. Now there's a character value in col1that's being fetched before it encounters your 1234row & that's causing the error, since the character cannot be converted to a number.
此外，您假设这1234是唯一被提取的行。实际上，Oracle 必须从表中获取所有行，然后根据where子句过滤掉。现在有一个字符值在col1它遇到您的1234行之前被提取，这会导致错误，因为该字符无法转换为数字。
This fiddleshows the behaviour. Since abccanot be converted to a number, you get that error message
这个小提琴显示了行为。由于abc无法转换为数字，您会收到该错误消息
Now if the onlyrecord in the table is that of col1 containing a numeric character, you'll see that the statement will work fine
现在，如果表中唯一的记录是 col1 包含数字字符的记录，您将看到该语句将正常工作
回答by Gordon Linoff
Oh, it is much better to convert to char rather than to numbers:
哦，转换为字符而不是数字要好得多：
select *
from table
where col1 = to_char(1234)
When the col1 does not look like a number, to_number returns an error, stopping the query.
当 col1 看起来不像数字时， to_number 返回错误，停止查询。
