OracleのNUMBERデータ型についてまとめてます。
NUMBERは高精度な数値を扱うためのデータ型
NUMBERは、整数と小数を扱える数値型。Oracleで扱うことができます。
公式ドキュメントによると、事実上どんな大きさの数値でも格納可能となっています。最大精度は38桁。
- 1 x 10-130～9.99…9 x 10125の範囲の正の数。最大有効桁数は38です。
- -1 x 10-130～9.99…99 x 10125の範囲の負の数。最大有効桁数は38です。
- 0（ゼロ）。
- 正と負の無限大（Oracle Databaseバージョン5からのインポート時にのみ生成されます）。
NUMBER(3,0）という宣言は、整数部が3桁、小数部が0桁の指定になります。結果、小数点第一位で丸め処理がおこなわれ、3桁の整数となります。
create table tbl (s_no NUMBER(3,0) ); insert into tbl values ( 100.01 ); select * from tbl; S_NO ----- 100
NUMBER(p,s)という宣言で、pは小数点以下を含んだ数値全体の桁数（最大38桁）、sは小数点以下の桁数を表します。
create table tbl2 (s_no NUMBER(10,3) ); insert into tbl2 values ( 123456.123456789012345670 ); select * from tbl2; S_NO ----- 123456.123 insert into tbl2 values ( 1234567890.123456789012345670 ); ORA-01438: value larger than specified precision allowed for this column ORA-06512: at "SYS.DBMS_SQL", line 1721
公式ドキュメントでは、NUMBER (precision, scale) と表記されています。precisionは精度、scaleはスケール（小数点以下の桁数）です。
なお、宣言した桁に入りきらない大きな数をinsertしようとすると上記のエラーが出力されます。value larger than specified precision allowed for this column（この列に許容される指定精度より大きな値です）というエラーは、桁があふれてしまったことを示しています。
また、NUMBER(p,s)の指定でsが負の数だった場合、整数部のs桁で丸め処理をおこなう指定になります。たとえば、NUMBER(10,-3）と指定すると、全体の桁は10桁で、整数部3桁で丸めを行います。
123456.123450をinsertすると123000に丸められていることがわかります。
数値がどのように丸められるのか見てみましょう。
以下のSQLはテーブルtbl10をcreateし全体10桁、小数点以下2桁のカラムnumを指定します。小数点以下3桁で丸めが発生するため、insertした値はそれぞれ以下の値でテーブルに格納されます。
- 100.99 → 100.99
- 90.551 → 90.55
- 88.556 → 88.56
create table tbl10 (num NUMBER(10,2) ); insert into tbl10 values ( 100.99 ); insert into tbl10 values ( 90.551 ); insert into tbl10 values ( 88.556 ); select * from tbl10; NUM ----- 90.55 88.56 100.99
NUMBER型のフォーマット
NUMBER型の数値は、TO_CHARにフォーマット指定をすることでカンマ区切りや0パディングなどの体裁を指定することが可能です。
select COUNTRY, TO_CHAR("1960", '999,999,999'),
TO_CHAR("1970", '999,999,999'),
TO_CHAR("1980", '999,999,999'),
TO_CHAR("1990", '999,999,999'),
TO_CHAR("2000", '999,999,999'),
TO_CHAR("2010", '999,999,999'),
TO_CHAR("2019", '999,999,999')
from WORLD.WORLD_POPULATION;
COUNTRY TO_CHAR("1960",'999,999,999') TO_CHAR("1970",'999,999,999') TO_CHAR("1980",'999,999,999') TO_CHAR("1990",'999,999,999') TO_CHAR("2000",'999,999,999') TO_CHAR("2010",'999,999,999') TO_CHAR("2019",'999,999,999')
Bermuda 44,400 55,000 54,670 59,326 61,833 65,124 -
Bolivia 3,353,125 4,217,248 5,368,901 6,794,046 8,495,271 10,156,601 -
Brazil 72,775,883 96,060,361 121,740,438 149,648,341 174,504,898 195,210,154 -
Barbados 230,858 238,616 248,784 259,336 267,190 280,396 -
Brunei Darussalam 81,817 129,719 193,049 256,929 331,801 400,569 -
:
TO_CHAR(NUMBER型の数値,フォーマットをあらわす文字列）のように指定すると、数値をカンマ区切りしたり0詰めすることができます。
REM カンマ区切り
select TO_CHAR( 1234, '9,999') from dual;
TO_CHAR(1234,'9,999')
----------
1,234
REM 整数・小数点の桁数指定
select TO_CHAR( 12.1234, '999.99') from dual;
TO_CHAR(12.1234,'999.99')
----------
12.12
REM 1以下の小数
select TO_CHAR( 0.1234, '999.99') from dual;
TO_CHAR(0.1234,'999.99')
.12
REM 1以下の小数で整数部を0にする
select TO_CHAR( 0.1234, '990.99') from dual;
TO_CHAR(0.1234,'990.99')
----------
0.12
REM プラスマイナス符号をつける（プラスの場合）
select TO_CHAR( 12.1234, 'S999.99') from dual;
TO_CHAR(12.1234,'S999.99')
----------
+12.12
REM プラスマイナス符号をつける（マイナスの場合）
select TO_CHAR( -12.1234, 'S999.99') from dual;
TO_CHAR(-12.1234,'S999.99')
----------
-12.12
まとめ
- NUMBERは38桁の精度で整数＋小数を扱うことができるデータ型
- NUMBER(p,s）という記述で、最大桁数と丸め処理する桁を指定する。pが負の場合は整数部で丸め処理をおこなう。
- TO_CHAR関数にフォーマット指定を記述することで、NUMBER型のフォーマット整形が可能
