表number列的資料插入insert小測試

SQL> desc test_trim;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(3,1) --列型別為number(3,1)，表示整數位和小數位共3位，小數位為1位，所以整數位只能為2位
 B                                                  VARCHAR2(20)

SQL> truncate table test_trim;

Table truncated.

SQL> insert into test_trim values(20.39,'c');

1 row created.

SQL> insert into test_trim values(20.34,'b');

1 row created.

SQL> insert into test_trim values(20.359,'a');--這裡小數位採用四取五入

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_trim;

         A B
---------- --------------------
      20.4 c
      20.3 b
      20.4 a  --四捨五入的插入列值到表中

SQL> insert into test_trim values(20.959,'d');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_trim;

         A B
---------- --------------------
      20.4 c
      20.3 b
      20.4 a
        21 d

SQL> insert into test_trim values(100,'t');--不允許插入，因為整數位只能是2位
insert into test_trim values(100,'t')
                             *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into test_trim values(99.99,'tb');--這個也插不進來，雖然整位數是2位，但小數位四捨五入後，整數變成100，所以不能插入
insert into test_trim values(99.99,'tb')
                             *
ERROR at line 1: