小数(s，p)还是数字(s，p)?
最近在做db2 -> oracle迁移项目时，遇到了这种情况.开发人员无意中使用小数(s，p)列创建了新的表结构.我不记得 Oracle 支持这个，但后来一些挖掘表明它是一个 ANSI 数据类型，因此被 oracle 支持.
recently, while working on a db2 -> oracle migration project, we came across this situation. the developers were inadvertently creating new table structures using decimal(s,p) columns. I didn't remember Oracle supporting this, but then some digging showed that its a ANSI data type therefore supported by oracle.
然而，我的问题仍然存在 -
However, question for me remained -
- 内部如何处理这些数据?
- 使用 ANSI 类型而不是 Oracle 的内置类型是否有成本?
- 如果目标类型是Oracle内置类型，数据迁移过程中会不会有影响?
推荐答案
在 Oracle 中，它们是相同的:
In Oracle, they are the same:
创建表和簇的SQL语句也可以使用ANSI数据IBM 产品 SQL/DS 和 DB2 中的类型和数据类型.甲骨文识别与 Oracle 不同的 ANSI 或 IBM 数据类型名称数据库数据类型名称.它将数据类型转换为等效的Oracle 数据类型，将 Oracle 数据类型记录为列数据类型，以Oracle数据类型存储列数据基于下表中显示的转换.
SQL statements that create tables and clusters can also use ANSI data types and data types from the IBM products SQL/DS and DB2. Oracle recognizes the ANSI or IBM data type name that differs from the Oracle Database data type name. It converts the data type to the equivalent Oracle data type, records the Oracle data type as the name of the column data type, and stores the column data in the Oracle data type based on the conversions shown in the tables that follow.
此引用下方的表格显示 DECIMAL(p,s) 在内部被视为 NUMBER(p,s):
The table below this quote shows that DECIMAL(p,s) is treated internally as a NUMBER(p,s):
SQL> create table t (a decimal(*,5), b number (*, 5));
Table created
SQL> desc t;
Name Type Nullable Default Comments
---- ----------- -------- ------- --------
A NUMBER(*,5) Y
B NUMBER(*,5) Y
但是，DECIMAL 的比例默认为 0，这意味着 DECIMAL(*) 被视为 NUMBER(*, 0), 即 INTEGER:
However, the scale defaults to 0 for DECIMAL, which means that DECIMAL(*) is treated as NUMBER(*, 0), i.e. INTEGER:
SQL> create table t (a decimal, b number, c decimal (5), d decimal (5));
Table created
SQL> desc t;
Name Type Nullable Default Comments
---- --------- -------- ------- --------
A INTEGER Y
B NUMBER Y
C NUMBER(5) Y
D NUMBER(5) Y
相关文章