網頁

▼

2017/8/28

Oracle 數字資料型態 NUMBER data type

本篇簡單說明Oracle資料庫的數字資料型態

NUMBER資料型態用來儲存正負固定且可帶小數的數字，也就是可儲存整數(Integer)或浮點數(Float)。精確度為38位的有效數字(Significant figures)。

下面SQL建立一個範例資料表MY_TEST，有一個欄位COL_01的型態為NUMBER

CREATE TABLE MY_TEST 
(
  COL_01 NUMBER(10,2)
);

建立NUMBER欄位的語法為

column_name NUMBER

建立NUMBER欄位時，可以指定該欄位的precision和scale如下

column_name NUMBER(precision, scale)

precision為總位數，最多為38，scale為小數點後的位數

例如宣告一個欄位名稱為salary，資料型態為NUMBER，有效位數為10位數，小數後2兩位，則語法為...

salary NUMBER(10, 2)

如果沒給定precision，則會以儲存的值為準，如果沒給定scale，則預設為0

下面範例用來測試NUMBER(10,2)的各種結果

INSERT INTO MY_TEST (COL_01) VALUES (9999999999); -- 10位數 錯誤
INSERT INTO MY_TEST (COL_01) VALUES (999999999); -- 9位數 錯誤
INSERT INTO MY_TEST (COL_01) VALUES (99999999); -- 8位數 成功
INSERT INTO MY_TEST (COL_01) VALUES (99999999.99); -- 8位數，小數點後2位 成功
INSERT INTO MY_TEST (COL_01) VALUES (99999999.999); -- 8位數，小數點後3位 錯誤
INSERT INTO MY_TEST (COL_01) VALUES (9999999.999); -- 7位數，小數點後3位 成功 存入10000000
INSERT INTO MY_TEST (COL_01) VALUES (999999.9999); -- 6位數，小數點後4位 成功 存入1000000
INSERT INTO MY_TEST (COL_01) VALUES (999999.994); -- 6位數，小數點後3位 成功 存入999999.99

若超過設定範圍，會發生
ORA-01438: 值大於此資料欄所允許的指定精確度
ORA-01438: value larger than specified precision allowed for this column的錯誤

當小數位超過scale範圍會四捨五入進位。

因此NUMBER(10,2)可儲存的最大值為99999999.99

所以NUMBER(10,2)若在UI表單中<input>欄位的maxlength要設定成11，因為多一個小數點的空間，但為了避免使用者輸入超過範圍的數字造成錯誤，所以在前端要加上其他驗證。

如果scale為負數，代表從小數點前第幾位開始四捨五入，例如NUMBER(10,-2)的各種測試結果如下

INSERT INTO MY_TEST (COL_01) VALUES (9999999999); -- 10位數 成功 10000000000
INSERT INTO MY_TEST (COL_01) VALUES (99999999999); -- 11位數 成功 存入100000000000
INSERT INTO MY_TEST (COL_01) VALUES (999999999999); -- 12位數 錯誤
INSERT INTO MY_TEST (COL_01) VALUES (123456); -- 6位數 成功 存入123500
INSERT INTO MY_TEST (COL_01) VALUES (123445); -- 6位數 成功 存入123400
INSERT INTO MY_TEST (COL_01) VALUES (999999999900); -- 12位數 成功 存入999999999900
INSERT INTO MY_TEST (COL_01) VALUES (999999999940); -- 12位數 成功 存入999999999900
INSERT INTO MY_TEST (COL_01) VALUES (999999999950); -- 12位數 錯誤
INSERT INTO MY_TEST (COL_01) VALUES (999999999909); -- 12位數 成功 存入999999999900

由上可知NUMBER(10,-2)可儲存的最大值為999999999900，從小數前二位也就是十位數的位置開始四捨五入，個位數被忽略直接計為0。

Oracle資料庫在儲存數字時實際上是使用科學記號的方式來儲存。1byte用來儲存指數，最多20bytes用來儲存尾數，結果會限制在38位的精確位數。

例如數字412會以4.12 x 102的形式儲存，1byte儲存指數(2)，2bytes儲存3位數的尾數(4,1,2)。負數則用另外1byte儲存。

可以使用VSIZE()函式來查看數值所占用的byte大小

SELECT VSIZE(0) FROM DUAL; -- 1
SELECT VSIZE(2) FROM DUAL; -- 2
SELECT VSIZE(-2) FROM DUAL; -- 3
SELECT VSIZE(100) FROM DUAL; -- 2
SELECT VSIZE(999) FROM DUAL; -- 3

如果覺得文章有幫助的話還幫忙點個Google廣告，感恩。


參考：

1 則留言: