如何将 Oracle NUMBER 主键转换为 SQL Server 以供 Ado.Net 使用
这可能是一个复合问题，因为我可能不完全理解这个问题。
我从 Oracle Apex 查询界面抓取数据并将结果存储在本地 SQL Server 实例中，以便本地应用程序可以利用这些数据。
我从中提取的 Oracle 数据库使用定义为 NUMBER 数据类型的主键（未定义精度或小数位数）。我应该在 SQL Server 端使用什么数据类型来存储 Oracle NUMBER 字段？
我发现的大多数文档都规定在 SQL Server 中将 NUMBER 数据字段存储为浮点数，但这并没有什么帮助，因为其中许多 NUMBER 字段代表主键（使用浮点值作为主键是一个坏主意） 。
我曾尝试在 Sql Server 中使用 DECIMAL(38,38) 来存储 Oracle 值，但是当您尝试使用 DECIMAL(38,38) 类型设置 ADO.NET SqlParameter（例如在 SqlCommand 上）时，值时，您将收到算术溢出错误，因为它始终会完全扩展 SCALE。例如，数字 23425 将扩展为 23425.00000000000000000000000000000000000000，并会给出溢出错误，因为它使用的存储空间多于 SqlParameter 数据类型精度中可用的存储空间。
This could be a composite question, as I may not fully understand the issue.
I am scraping data from an Oracle Apex Query interface and storing the results in a local SQL Server instance so that local applications can take advantage of the data.
The Oracle database I am pulling from uses primary keys that are defined as NUMBER data types (without precision or scale defined). What data type should I use on the SQL Server end to store an Oracle NUMBER field?
Most of the documentation I have found states to store a NUMBER data field in SQL Server as a Float, but this is not helpfull since many of these NUMBER fields represent primary keys (using a floating point value as a primary key is a bad idea).
I had toyed with using DECIMAL(38,38) in Sql Server to store the Oracle value, but when you attempt to set an ADO.NET SqlParameter (Such as on an SqlCommand) with a type of DECIMAL(38,38) with a value you will get an Arithmetic Overflow error, as it will always fully expand the SCALE. E.G. the number 23425 would expand to 23425.00000000000000000000000000000000000000 and would give an overflow error because it has used up more storage than is available in the precision of the data type of the SqlParameter.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在 Oracle 中，当未指定精度或小数位数时，NUMBER 为 十进制浮点类型。不要与二进制浮点类型混淆。
Oracle 还通过指定精度和可选的 NUMBER 比例来支持十进制定点，即
NUMBER(11,2)，这将有利于将美元价值保持在 999,999,999.99 美元。SQL Server 支持使用 DECIMAL 类型和浮点二进制 float 和 real SQL Server 定点十进制或浮点二进制数据类型都无法保存 Oracle 十进制浮点类型 (NUMBER) 的所有可能值，而不会出现错误或信息丢失。
如果您正在处理 Oracle 数据库，所有数字数据都以 NUMBER 类型保存，没有精度或小数位数，并且某些数据是整数值，例如代理主键和某些数据是定点值，例如美元和其他数据，其规模确实变化很大，并且只需要保留近似值，那么您将需要逐列映射数据类型。
In Oracle, when no precision or scale are specified NUMBER is decimal floating point type. Not to be confused with a binary floating point type.
Oracle also supports decimal fixed point by specifying precision and optionally scale to NUMBER, i.e.
NUMBER(11,2)which will would be good for holding US dollar values to $999,999,999.99.SQL Server supports fixed point decimal with the DECIMAL type and floating point binary with float and real Neither of SQL Servers fixed point decimal or floating point binary data types will hold all possible values of Oracles decimal floating point type (NUMBER) with out either error or loss of information.
If you are dealing with an Oracle database were all numeric data is held in NUMBER type without precision or scale and some data is of integer values, like surrogate primary keys and some data is fixed point values, such as US Dollars and other data is truly widely varying in scale and only needs approximate values held, then you will need to map data types on a column by column basis.
就像评论一样，似乎很难相信这些值不是整数，但话虽如此……
听起来真正的问题是，这是整数还是浮点数？因为最简单的解决方案是将其转换为 int。
您可以通过对源表进行查询来查看十进制值是否始终为零，如果是，则它是一个 int。
我不使用 Oracle，但我相信 SQL 的正确风格是这样的：
如果没有得到行，则有整数。如果你只得到一行，那么他们正在使用浮动。
如果由于某种奇怪的原因它们是浮点数，您仍然可以将它们转换为整数。执行查询找出最高精度，然后乘以 10^x，其中 x 是最大精度。这再次为您提供了整数。
Just as a comment, it seems hard to believe the values would be anything but ints, but with that being said....
It sounds like the real question is, is this an int or a float? Because the easiest solution would be to convert it to an int.
You can find out by doing a query on the source table to see if the decimal value is always zero, if so it is an int.
I don't use Oracle but I believe the correct flavor of SQL would be this:
If you get no rows, you have integers. If you get even one row, they are using floats.
If for some strange reason they are floats, you can still convert them to ints. Do a query to find out the highest precision, and then multiple by 10^x, where x is the max precision. This gives you ints again.