database Oracle 中数字的默认精度和小数位数是多少？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/593197/
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
What is the default Precision and Scale for a Number in Oracle?
提问by Matt
When creating a column of type NUMBER in Oracle, you have the option of not specifying a precision or scale. What do these default do if you don't specify them?
在 Oracle 中创建 NUMBER 类型的列时，您可以选择不指定精度或小数位数。如果您不指定它们，这些默认值会做什么？
采纳答案by Angelo Marcotullio
NUMBER (precision, scale)
NUMBER（精度，比例）
If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero.
如果未指定精度，则该列存储给定的值。如果未指定比例，则比例为零。
A lot more info at:
更多信息请访问：
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832
回答by maxschlepzig
The NUMBERtype can be specified in different styles:
该NUMBER类型可以指定不同的风格：
Resulting Resulting Precision Specification Precision Scale Check Comment ――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――― NUMBER NULL NULL NO 'maximum range and precision', values are stored 'as given' NUMBER(P, S) P S YES Error code: ORA-01438 NUMBER(P) P 0 YES Error code: ORA-01438 NUMBER(*, S) 38 S NO
Where the precision is the total number of digits and scale is the number of digits right or left (negative scale) of the decimal point.
其中精度是总位数，小数位数是小数点右侧或左侧（负比例）的位数。
Oracle specifies ORA-01438 as
Oracle 将 ORA-01438 指定为
value larger than specified precision allowed for this column
值大于此列允许的指定精度
As noted in the table, this integrity check is only active if the precision is explicitly specified. Otherwise Oracle silently rounds the inserted or updated value using some unspecified method.
如表中所述，此完整性检查仅在明确指定精度时才有效。否则 Oracle 会使用某种未指定的方法静默地舍入插入或更新的值。
回答by baretta
I believe the default precision is 38, default scale is zero. However the actual size of an instance of this column, is dynamic. It will take as much space as needed to store the value, or max 21 bytes.
我相信默认精度是 38，默认比例为零。但是，此列实例的实际大小是动态的。它将根据需要占用尽可能多的空间来存储值，或最多 21 个字节。
回答by Quassnoi
Oraclestores numbers in the following way: 1 bytefor power, 1 bytefor the first significand digit (that is one before the separator), the rest for the other digits.
Oracle以下列方式存储数字：1 byte对于幂，1 byte对于第一个有效数字（即分隔符之前的一位），其余数字用于其他数字。
By digitshere Oraclemeans centesimal digits(i. e. base 100)
通过digits此Oracle方法centesimal digits（即base 100）
SQL> INSERT INTO t_numtest VALUES (LPAD('9', 125, '9'))
2 /
1 row inserted
SQL> INSERT INTO t_numtest VALUES (LPAD('7', 125, '7'))
2 /
1 row inserted
SQL> INSERT INTO t_numtest VALUES (LPAD('9', 126, '9'))
2 /
INSERT INTO t_numtest VALUES (LPAD('9', 126, '9'))
ORA-01426: numeric overflow
SQL> SELECT DUMP(num) FROM t_numtest;
DUMP(NUM)
--------------------------------------------------------------------------------
Typ=2 Len=2: 255,11
Typ=2 Len=21: 255,8,78,78,78,78,78,78,78,78,78,78,78,78,78,78,78,78,78,78,79
As we can see, the maximal number here is 7.(7) * 10^124, and he have 19centesimal digits for precision, or 38decimal digits.
正如我们所看到的，这里的最大数是7.(7) * 10^124，并且他有19百分位数来表示精度，或38十进制数。
回答by spectra
Actually, you can always test it by yourself.
实际上，您始终可以自己进行测试。
CREATE TABLE CUSTOMERS
(
CUSTOMER_ID NUMBER NOT NULL,
JOIN_DATE DATE NOT NULL,
CUSTOMER_STATUS VARCHAR2(8) NOT NULL,
CUSTOMER_NAME VARCHAR2(20) NOT NULL,
CREDITRATING VARCHAR2(10)
)
;
CREATE TABLE CUSTOMERS
(
CUSTOMER_ID NUMBER NOT NULL,
JOIN_DATE DATE NOT NULL,
CUSTOMER_STATUS VARCHAR2(8) NOT NULL,
CUSTOMER_NAME VARCHAR2(20) NOT NULL,
CREDITRATING VARCHAR2(10)
)
;
select column_name, data_type, nullable, data_length, data_precision, data_scale
from user_tab_columns where table_name ='CUSTOMERS';
select column_name, data_type, nullable, data_length, data_precision, data_scale
from user_tab_columns where table_name ='CUSTOMERS';
回答by bugybunny
I expand on spectra‘s answer so people don't have to try it for themselves.
我扩展了光谱的答案，因此人们不必自己尝试。
This was done on Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production.
这是在 Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production 上完成的。
CREATE TABLE CUSTOMERS
(
CUSTOMER_ID NUMBER NOT NULL,
FOO FLOAT NOT NULL,
JOIN_DATE DATE NOT NULL,
CUSTOMER_STATUS VARCHAR2(8) NOT NULL,
CUSTOMER_NAME VARCHAR2(20) NOT NULL,
CREDITRATING VARCHAR2(10)
);
select column_name, data_type, nullable, data_length, data_precision, data_scale
from user_tab_columns where table_name ='CUSTOMERS';
Which yields
哪个产量
COLUMN_NAME DATA_TYPE NULLABLE DATA_LENGTH DATA_PRECISION DATA_SCALE
CUSTOMER_ID NUMBER N 22
FOO FLOAT N 22 126
JOIN_DATE DATE N 7
CUSTOMER_STATUS VARCHAR2 N 8
CUSTOMER_NAME VARCHAR2 N 20
CREDITRATING VARCHAR2 Y 10
