Query:
create table test(sno number(7,2));
insert into test values (12345.67);
select * from test;
Sno
---------
12345.67
Query:
insert into test values(123456.7)
Error: value larger than specified precision allows for this column.
Query:
create table test(sno number(7,2));
insert into test values (12345.67);
select * from test;
SNO
---------
12345.67
Query:
insert into test values(123456.7)
Error: value larger than specified precision allows for this column.
Query:
insert into test values(12345.6789);
select * from test;
Sno
-------------
12345.68
Query:
insert into test values(12345.6725);
select * from test;
Sno
------------
12345.67
Number(p):
Example:
Query:
create table test1(sno number(7));
insert into test1 values(99.9);
select * from test1;
Sno
13
-------
100
Query:
insert into test1 values(99.4);
select * from test1;
Sno
-----------
99
-- Basic insert with default NUMBER (no precision/scale restriction)
INSERT INTO your_table (value_column) VALUES (CAST(123.79 AS NUMBER)); -- 123.79
-- Rounded to nearest integer due to scale 0
INSERT INTO your_table (value_column) VALUES (CAST(123.88 AS NUMBER(3))); -- 124
-- Exceeds precision: total digits = 5 > 3
-- INSERT INTO your_table (value_column) VALUES (CAST(123.89 AS NUMBER(3,2))); -- ERROR
-- Exceeds precision: total digits = 5 > 4
-- INSERT INTO your_table (value_column) VALUES (CAST(123.89 AS NUMBER(4,2))); -- ERROR
-- Fits within 5 digits with 2 decimal digits
INSERT INTO your_table (value_column) VALUES (CAST(123.89 AS NUMBER(5,2))); -- 123.89
-- Rounded to 1 decimal place
INSERT INTO your_table (value_column) VALUES (CAST(123.89 AS NUMBER(6,1))); -- 123.9
-- Rounded to hundreds (scale = -2)
INSERT INTO your_table (value_column) VALUES (CAST(123.89 AS NUMBER(6,-2))); -- 100
-- Precision = 4, Scale = 5 → allows 5 digits after decimal; leading 0s don't count
INSERT INTO your_table (value_column) VALUES (CAST(.05678 AS NUMBER(4,5))); -- .05678
-- Still within limits
INSERT INTO your_table (value_column) VALUES (CAST(.00013 AS NUMBER(4,5))); -- .00013
-- Rounded to 5 digits after decimal
INSERT INTO your_table (value_column) VALUES (CAST(.000127 AS NUMBER(4,5))); -- .00013
-- 7 digits after decimal, total digits = 2 (significant) + 7 (scale)
INSERT INTO your_table (value_column) VALUES (CAST(.0000012 AS NUMBER(2,7))); -- .0000012
-- Rounds last digit to fit scale
INSERT INTO your_table (value_column) VALUES (CAST(.00000123 AS NUMBER(2,7))); -- .0000012
-- Scientific notation, becomes 0.00012
INSERT INTO your_table (value_column) VALUES (CAST(1.2e-4 AS NUMBER(2,5))); -- 0.00012
-- Rounded to 0.00001
INSERT INTO your_table (value_column) VALUES (CAST(1.2e-5 AS NUMBER(2,5))); -- 0.00001
No comments:
Post a Comment