Oracle 编号和 varchar 连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2330437/
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 number and varchar join
提问by broschb
I have a query that joins two tables. One table has a column that is of type varchar, and the other table has type of number. I have executed my query on 3 oracle databases, and am seeing some strange results I hope can be explained. On two of the databases something like the following works.
我有一个连接两个表的查询。一个表有一个 varchar 类型的列，另一个表有一个 number 类型。我已经在 3 个 oracle 数据库上执行了我的查询，并且看到了一些我希望可以解释的奇怪结果。在两个数据库上，类似以下的工作。
select a.col1, b.somecol
from tableA a inner join tableB b on b.col2=a.col1;
In this query tableA.col1 is of type number and tableB.col2 is of type varchar. This works fine in two of the databases but not in the third. In the third I get (ORA-01722) error. In the third I need to do something like...
在这个查询中 tableA.col1 是 number 类型，tableB.col2 是 varchar 类型。这在两个数据库中运行良好，但在第三个数据库中不起作用。在第三个中，我收到 (ORA-01722) 错误。在第三个中，我需要做一些类似的事情......
select a.col1, b.somecol
from tableA a inner join tableB b on b.col2=to_char(a.col1);
This works in all the databases. The question I have is why? The above is a simplified query, and the real query is a little more complex and retrieves a lot of data, hence the first version is much faster. If I could get that to work in all environments it would be great.
这适用于所有数据库。我的问题是为什么？上面是一个简化的查询，真正的查询稍微复杂一点，检索的数据很多，因此第一个版本要快得多。如果我能让它在所有环境中工作，那就太好了。
Does anyone know why this may work in some oracle databases and not others without the cast on the datatype? Is there a global setting that enables such behavior?
有谁知道为什么这可能在某些 oracle 数据库中起作用，而在没有数据类型强制转换的情况下则不能在其他数据库中起作用？是否有启用此类行为的全局设置？
回答by APC
One reason why implicit conversions fail is when the joining varchar column contains data which is not numeric. Oracle handles number to varchar2 joins by converting the strings (check out Gary's citation in his comment), so it actually executes this :
隐式转换失败的原因之一是当加入的 varchar 列包含非数字数据时。Oracle 通过转换字符串来处理数字到 varchar2 连接（在他的评论中查看 Gary 的引用），所以它实际上执行了这个：
select a.col1, b.somecol
from tableA a inner join tableB b on to_number(b.col2)=a.col1;
If tableB.col2 contains values which are not numeric - seems quite likely, it is a string after all - then it will hurl ORA-01722: invalid number. By explicitly casting the number column to a string you short-circuit Oracle's default behaviour.
如果 tableB.col2 包含不是数字的值 - 看起来很有可能，毕竟它是一个字符串 - 那么它将 hurl ORA-01722: invalid number。通过将数字列显式转换为字符串，您可以短路 Oracle 的默认行为。
The fact that you don't get this problem in your first two environments is a matter of luck not configuration. It could strike at any time, because it only requires one non-numeric string to break the query. So really you ought to run with the explicit conversion in all environments.
在前两个环境中没有遇到此问题的事实是运气问题而不是配置问题。它可以随时触发，因为它只需要一个非数字字符串来中断查询。所以你真的应该在所有环境中使用显式转换运行。
As for performance, you could build a function-based index ...
至于性能，你可以建立一个基于函数的索引......
create index whatever_idx on tableA ( to_char(col1) )
/
