blocks|key|2057991|text|我相信默认精度是38，默认小数位数是0。但是，此列的实例的实际大小是动态的。它将根据需要占用尽可能多的空间来存储值，或最多21个字节。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2057992|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|2057999|text|数字(精度，小数位数)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2058000|如果未指定精度，则列按给定的方式存储值。如果未指定比例，则比例为零。|2058001|有关更多信息，请访问：|2058002|http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832|offset|length|2058003|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|2D|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@$H|U|I|V|1|W]]|A|$]]|$1|J|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|G]]]]

NUMBER (precision, scale) 

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:

<a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832" rel="noreferrer">http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832</a>

blocks|key|2058018|text|Oracle按以下方式存储数字：1+byte表示幂，1+byte表示第一个有效数字(即分隔符之前的一个)，其余数字表示其他数字。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2058019|这里的digits指的是centesimal+digits+(即base+100)|2058020|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|code-block|syntax|javascript|2058021|正如我们可以看到的，这里最大的数字是7.(7)+*+10%5E124，他有19个百分位数来表示精度，或38个小数位数。|2058022|entityMap^0|0|6|G|6|Q|6|0|3|6|C|H|W|8|0|0|I|E|Z|2|1D|2|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@$9|Y|A|Z|B|C]|$9|10|A|11|B|C]|$9|12|A|13|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|14|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|15|8|@$9|16|A|17|B|C]|$9|18|A|19|B|C]|$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|1C|8|@]|D|@]|E|$]]]|P|$]]

<code>Oracle</code> stores numbers in the following way: <code>1 byte</code> for power, <code>1 byte</code> for the first significand digit (that is one before the separator), the rest for the other digits.

By <code>digits</code> here <code>Oracle</code> means <code>centesimal digits</code> (i. e. <code>base 100</code>)

<pre><code>SQL&gt; INSERT INTO t_numtest VALUES (LPAD('9', 125, '9'))
 2 /

1 row inserted

SQL&gt; INSERT INTO t_numtest VALUES (LPAD('7', 125, '7'))
 2 /

1 row inserted

SQL&gt; INSERT INTO t_numtest VALUES (LPAD('9', 126, '9'))
 2 /

INSERT INTO t_numtest VALUES (LPAD('9', 126, '9'))

ORA-01426: numeric overflow

SQL&gt; 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
</code></pre>

As we can see, the maximal number here is <code>7.(7) * 10^124</code>, and he have <code>19</code> centesimal digits for precision, or <code>38</code> decimal digits.

blocks|key|1843869|text|实际上，你可以自己测试它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1843870|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)+)+;|offset|length|style|CODE|1843871|select+column_name,+data_type,+nullable,+data_length,+data_precision,+data_scale+from+user_tab_columns+where+table_name+='CUSTOMERS';|1843872|entityMap^0|0|0|5D|0|0|3P|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|K|$]]

Actually, you can always test it by yourself.

<code>
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'; </code>

blocks|key|2058061|text|我详述了spectra的答案，这样人们就不必自己去尝试了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2058062|这是在Oracle+Database+11g+Express+Edition+Release+11.2.0.2.0+-+Production上完成的。|2058063|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';+|code-block|syntax|javascript|2058064|哪一项会产生|2058065|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++++|2058066|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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.

<pre><code>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'; 
</code></pre>

Which yields 

<pre><code>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 
</code></pre>

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?

What is the default Precision and Scale for a Number in Oracle?

在Oracle中创建NUMBER类型的列时，可以选择不指定精度或小数位数。如果您不指定这些默认值，它们会做什么？

问Oracle中数字的默认精度和小数位数是多少？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle中数字的默认精度和小数位数是多少？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle中数字的默认精度和小数位数是多少？
EN