| 숫자 타입 | 설명 | ||
|---|---|---|---|
| {code:none | borderStyle=solid}NUMBER{code} | {code:none | borderStyle=none}38자리 정밀도 숫자 저장, 가변 길이(0 ~ 22 바이트){code} |
| {code:none | borderStyle=solid}BINARY_FLOAT{code} | {code:none | borderStyle=none}6자리 단정도 부동소수점 숫자 저장, 고정 길이(5 바이트){code} |
| {code:none | borderStyle=solid}BINARY_DOUBLE{code} | {code:none | borderStyle=none}13자리 배정도 부동소수점 숫자 저장, 고정 길이(9 바이트){code} |
| Demo#1 (숫자 타입) | |
|---|---|
| {code:sql | borderStyle=solid} SQL> CREATE TABLE T ( NUM_COL NUMBER, FLOAT_COL BINARY_FLOAT, DBL_COL BINARY_DOUBLE ); 2 3 4 5 |
Table created.
SQL> INSERT INTO T ( NUM_COL, FLOAT_COL, DBL_COL )
VALUES ( 1234567890.0987654321,
1234567890.0987654321,
1234567890.0987654321 ); 2 3 4
1 row created.
SQL> SET NUMFORMAT 9999999999.9999999999
SQL> SELECT * FROM T;
NUM_COL FLOAT_COL DBL_COL
SQL> DELETE FROM T;
1 row deleted.
SQL> INSERT INTO T ( NUM_COL, FLOAT_COL, DBL_COL )
VALUES ( 9999999999.9999999999,
9999999999.9999999999,
9999999999.9999999999 ); 2 3 4
1 row created.
SQL> SET NUMFORMAT 99999999999.99999999999
SQL> SELECT * FROM T;
NUM_COL FLOAT_COL DBL_COL
SQL> DELETE FROM T;
1 row deleted.
SQL> INSERT INTO T ( NUM_COL) VALUES ( 123 * 1E20 + 123*1E-20 );
1 row created.
– NUM_COL 에 들어온 값이 정밀도를 벗어나서 정확한 값을 표현하지 못함
SQL> SET NUMFORMAT 99999999999999999999999.9999999999999999999999999
SQL> SELECT NUM_COL, 123*1E20, 123*1E-20 FROM T;
NUM_COL
SQL> SELECT NUM_COL FROM T WHERE NUM_COL = 123*1E20;
NUM_COL
|
||Demo#2 (NUMBER 타입)||
|{code:sql|borderStyle=solid}
-- NUMBER(PRECISION, SCALE)
-- PRECISION : 전체 자릿수[1 ~ 38] (반올림 후 허용 자릿수, 무결성)
-- SCALE : 소수점 자릿수[-84 ~ 127] (반올림 자리, 값의 표현)
SQL> CREATE TABLE T (NUM_COL NUMBER(5,0));
Table created.
SQL> INSERT INTO T (NUM_COL) VALUES (12345);
1 row created.
SQL> INSERT INTO T (NUM_COL) VALUES (123456);
INSERT INTO T (NUM_COL) VALUES (123456)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
-- 123456 이 NUMBER(5,0) 타입에 위배됨
SQL> CREATE TABLE T (MSG VARCHAR2(10), NUM_COL NUMBER(5,2));
Table created.
SQL> INSERT INTO T (MSG, NUM_COL) VALUES ('123.45', 123.45);
1 row created.
SQL> INSERT INTO T (MSG, NUM_COL) VALUES ('123.456', 123.456);
1 row created.
SQL> SELECT * FROM T;
MSG NUM_COL
---------- ----------
123.45 123.45
123.456 123.46
SQL> INSERT INTO T (MSG, NUM_COL) VALUES ('1234', 1234);
INSERT INTO T (MSG, NUM_COL) VALUES ('1234', 1234)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
-- 1234(1234.00) 가 NUMBER(5,2) 타입에 위배됨 (-999.99 ~ 999.99)
-- NUMBER(5,2) 는 소수점 셋째 자리에서 반올림, NUMBER(5,-2) 는 10 자리에서 반올림
SQL> CREATE TABLE T (MSG VARCHAR2(10), NUM_COL NUMBER(5,-2));
Table created.
SQL> INSERT INTO T (MSG, NUM_COL) VALUES ('123.45', 123.45);
1 row created.
SQL> INSERT INTO T (MSG, NUM_COL) VALUES ('123.456', 123.456);
1 row created.
SQL> SELECT * FROM T;
MSG NUM_COL
---------- ----------
123.45 100
123.456 100
SQL> INSERT INTO T (MSG, NUM_COL) VALUES ('1234567', 1234567);
1 row created.
SQL> SELECT * FROM T;
MSG NUM_COL
---------- ----------
123.45 100
123.456 100
1234567 1234600
SQL> INSERT INTO T (MSG, NUM_COL) VALUES ('12345678', 12345678); -- NUMBER(5,-2)는 일곱자리 까지 허용(십의 자리까지는 반올림으로 인해 0)
INSERT INTO T (MSG, NUM_COL) VALUES ('12345678', 12345678)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
|
| Demo#3 (가변길이 NUMBER 타입) | |
|---|---|
| {code:sql | borderStyle=solid} SQL> CREATE TABLE T ( X NUMBER, Y NUMBER ); |
Table created.
SQL> INSERT INTO T ( X )
SELECT TO_NUMBER(RPAD('9', ROWNUM*2, '9'))
FROM ALL_OBJECTS
WHERE ROWNUM <= 11; 2 3 4
11 rows created.
SQL> UPDATE T SET Y = X + 1;
11 rows updated.
SQL> SET NUMFORMAT 9999999999999999999999999
SQL> COLUMN V1 FORMAT 99
SQL> COLUMN V2 FORMAT 99
SQL> SELECT X, Y, VSIZE(X) V1, VSIZE(Y) V2 FROM T ORDER BY X;
X Y V1 V2
11 rows selected.
-- 두개의 유효숫자가 1바이트를 차지한다, Y 컬럼의 유효숫자는 1 한개이며 유효숫자가 아닌 0의 갯수만 저장하면 됨
|
||Demo#4 (부동소수점 숫자 타입)||
|{code:sql|borderStyle=solid}
-- IEEE 표준 부동소수점 숫자 타입, 근삿값으로 사용됨
SQL> SELECT TO_CHAR( 0.3F + 0.1F, '0.99999999999999' ) FROM DUAL;
TO_CHAR(0.3F+0.1F
-----------------
0.40000000600000
^--> 버그가 아니고, IEEE 부동소수점 숫자의 방식에서 나타나는 현상
|
| Non-native 숫자 타입 | 설명 | ||
|---|---|---|---|
| {code:none | borderStyle=solid}NUMERIC(P,S){code} | {code:none | borderStyle=none}NUMBER(P,S){code} |
| {code:none | borderStyle=solid}DECIMAL(P,S){code} | {code:none | borderStyle=none}NUMBER(P,S){code} |
| {code:none | borderStyle=solid}INTEGER/INT{code} | {code:none | borderStyle=none}NUMBER(38){code} |
| {code:none | borderStyle=solid}SMALLINT{code} | {code:none | borderStyle=none}NUMBER(38){code} |
| {code:none | borderStyle=solid}FLOAT(P){code} | {code:none | borderStyle=none}NUMBER{code} |
| {code:none | borderStyle=solid}DOUBLE PRECISION{code} | {code:none | borderStyle=none}NUMBER{code} |
| {code:none | borderStyle=solid}REAL{code} | {code:none | borderStyle=none}NUMBER{code} |
| Demo#5 (연산성능) | |
|---|---|
| {code:sql | borderStyle=solid} SQL> CREATE TABLE T ( NUM_TYPE NUMBER, FLOAT_TYPE BINARY_FLOAT, DOUBLE_TYPE BINARY_DOUBLE ); 2 3 4 5 |
Table created.
SQL> INSERT /*+ APPEND */ INTO T
SELECT ROWNUM, ROWNUM, ROWNUM
FROM ALL_OBJECTS; 2 3
71166 rows created.
SQL> COMMIT;
Commit complete.
SQL> EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE;
PL/SQL procedure successfully completed.
SQL> SELECT SUM(LN(NUM_TYPE)) FROM T;
SUM(LN(NUM_TYPE))
call count cpu elapsed disk query current rows
SQL> SELECT SUM(LN(FLOAT_TYPE)) FROM T;
SUM(LN(FLOAT_TYPE))
call count cpu elapsed disk query current rows
SQL> SELECT SUM(LN(DOUBLE_TYPE)) FROM T;
SUM(LN(DOUBLE_TYPE))
call count cpu elapsed disk query current rows
SQL> SET NUMFORMAT 999999.99999999999999
SQL> SELECT SUM(LN(NUM_TYPE)) FROM T;
SUM(LN(NUM_TYPE))
SQL> SELECT SUM(LN(DOUBLE_TYPE)) FROM T;
SUM(LN(DOUBLE_TYPE))
SQL> SELECT SUM(LN(CAST( NUM_TYPE AS BINARY_DOUBLE) )) FROM T; -- NUMBER 형을 BINARY_DOUBLE 로 CAST 만 해도 효과 있음
SUM(LN(CAST(NUM_TYPEASBINARY_DOUBLE)))
call count cpu elapsed disk query current rows
|