Number Data Type In SQL Oracle Number(P,S);

 Number Data Type In SQL Oracle   Number(P,S);

p> precision (total number of digits) 

S>scale ( it is used to store fixed, floating point numbers).

Syntax: 

                column name number(p,s) 

Example:

                SQL> create table test(sno number(7,2));

CODE

----------

"CREATE TABLE TEST(SNO NUMBER(7,2))

INSERT INTO TEST VALUES(12345.67);

SELECT * FROM TEST;"

Let's try some other values 

INSERT INTO TEST VALUES(123456.7);

Error: value larger than specified precision allows for this column.

  Note:

 whenever we are using number(p,s) then total number of digits before decimal places upto “p-s” number of digits.

[eg:- p-s => 7-2=5] 

After Decimal:

 INSERT INTO TEST VALUES(12345.6789);

SELECT * FROM TEST;

OP=12345.68

Note: 

whenever we are using number(p,s) and also if we are passing more number of digits after decimal place then oracle server only automatically rounded that number based on specified “scale”. Number(p): It is used to store fixed numbers only. Maximum length of the precision is up to “38”.

Example:

 SQL> create table test1(sno number(7)); 

 SQL> insert into test1 values(99.9); 

 SQL> select * from test1; 

OP=100

SQL> insert into test1 values(99.4); 

SQL> select * from test1; 

OP=99