本篇簡單說明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 則留言:
張貼留言