data type之number

SQL> create table n
2 (
3 num number(5,2)
4 );

Table created.

SQL> insert into n values (12345.12);
insert into n values (12345.12)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL>    
SQL> insert into n values (12345.1);
insert into n values (12345.1)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into n values(12345.0);
insert into n values(12345.0)
                     *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into n values(1234.12);
insert into n values(1234.12)
                     *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into n values(1234.1);
insert into n values(1234.1)
                     *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> desc t
ERROR:
ORA-04043: object t does not exist

SQL> desc n
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
NUM                                                NUMBER(5,2)

SQL> insert into n values (123.12);

1 row created.

SQL> insert into n values (1234.1);
insert into n values (1234.1)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into n values (12.123);

1 row created.

SQL>   
SQL> select * from n;

       NUM
----------
    123.12
     12.12

SQL> commit;

Commit complete.

SQL> desc n
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
NUM                                                NUMBER(5,2)

SQL> select * from n;

       NUM
----------
    123.12
     12.12

SQL> insert into n values (1234.10000);
insert into n values (1234.10000)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into n values (12345);
insert into n values (12345)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into n values(1234);
insert into n values(1234)
                     *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> alter table n drop column num;
alter table n drop column num
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS

SQL> drop table n;

Table dropped.

SQL>  
SQL> creater table n(num number(5,-2));
SP2-0734: unknown command beginning "creater ta..." - rest of line ignored.
SQL>
SQL>
SQL> r
1* drop table n
drop table n
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> creater table n(num number(5,-2));
SP2-0734: unknown command beginning "creater ta..." - rest of line ignored.
SQL> creater table n(num number(5,-2));
SP2-0734: unknown command beginning "creater ta..." - rest of line ignored.
SQL> creater table n(num number(5,-2));
SP2-0734: unknown command beginning "creater ta..." - rest of line ignored.
SQL>
SQL>
SQL>
SQL>
SQL> create table n (   
2 num number(5,-2)
3 );

Table created.

SQL> desc n
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
NUM                                                NUMBER(5,-2)

SQL> insert into values(12345);
insert into values(12345)
            *
ERROR at line 1:
ORA-00903: invalid table name

SQL> insert into n values(12345);

1 row created.

SQL> insert into n values(123456);

1 row created.

SQL> insert into n values(1234567);

1 row created.

SQL> insert into n values(12345678);
insert into n values(12345678)
                     *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL>              
SQL>
SQL> select * from n;

       NUM
----------
     12300
    123500
   1234600

SQL> insert into n values(1);

1 row created.

SQL> insert into n values(1.11);

1 row created.

SQL> insert into n values(11.123);

1 row created.

SQL> select * from n;

       NUM
----------
     12300
    123500
   1234600
         0
         0
         0

6 rows selected.

SQL> insert into n values(111.1111);

1 row created.

SQL> select * from n;

       NUM
----------
     12300
    123500
   1234600
         0
         0
         0
       100

7 rows selected.

SQL> insert into n values(1111.1111111111111111111111);

1 row created.

SQL> select * from n;

       NUM
----------
     12300
    123500
   1234600
         0
         0
         0
       100
      1100

8 rows selected.

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/42174/viewspace-327231/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/42174/viewspace-327231/