📄 floattypes.out
字号:
ij> -- ** insert decimal.sql---- Test DECIMAL and NUMERIC. Note that we-- know that DECIMAL and NUMERIC are pretty much the-- same thing, so we don't do much testing with-- the two types other than to make sure the -- syntax is the same.-- test some of the meta datadrop table tmp;ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it does not exist.ij> create table tmp (tmpcoldecimal dec(8,4), tmpcolnumeric numeric);0 rows inserted/updated/deletedij> select columndatatype from sys.syscolumns where columnname like 'TMPCOL%';COLUMNDATATYPE ---------------DECIMAL(8,4) NUMERIC(5,0) ij> drop table tmp;0 rows inserted/updated/deletedij> -- Negative tests, bad precision/scalecreate table bad (d decimal(11,12));ERROR 42Y43: Scale '12' is not a valid scale with precision of '11'.ij> create table bad (d decimal(0,0));ERROR 42X48: Value '0' is not a valid precision for DECIMAL.ij> create table bad (d decimal(0));ERROR 42X48: Value '0' is not a valid precision for DECIMAL.ij> create table bade(d decimal(32));ERROR 42X48: Value '32' is not a valid precision for DECIMAL.ij> create table bade(d decimal(31,32));ERROR 42Y42: Scale '32' is not a valid scale for a DECIMAL.ij> create table bade(d decimal(32,32));ERROR 42X48: Value '32' is not a valid precision for DECIMAL.ij> -- Simple acceptance testvalues cast (1 as dec);1 ------1 ij> values cast (1 as decimal);1 ------1 ij> values cast (1 as decimal(5));1 ------1 ij> values cast (1 as dec(5));1 ------1 ij> values cast (1.1 as dec(5,3));1 --------1.100 ij> values cast (1.1 as numeric(5,3));1 --------1.100 ij> -- cast to all valid typesvalues cast (1.1 as int);1 -----------1 ij> values cast (1.1 as bigint);1 --------------------1 ij> values cast (1.1 as smallint);1 ------1 ij> values cast (1.1 as real);1 -------------1.1 ij> values cast (1.1 as float);1 ----------------------1.1 ij> values cast (1.1 as char(10));1 ----------1.1 ij> -- cast all valid types to decvalues cast ((cast (1 as int)) as dec);1 ------1 ij> values cast ((cast (1 as bigint)) as dec);1 ------1 ij> values cast ((cast (1 as smallint)) as dec);1 ------1 ij> values cast ((cast (1 as real)) as dec);1 ------1 ij> values cast ((cast (1 as float)) as dec);1 ------1 ij> values cast ((cast (1 as char(10))) as dec);1 ------1 ij> -- cast overflow,-- make a number bigger than everything but -- decimal, and then try to cast itdrop table tmp;ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it does not exist.ij> create table tmp(d decimal(31 ,0));0 rows inserted/updated/deletedij> insert into tmp values (cast ('100000000000000000000000000000' as dec(31,0)));1 row inserted/updated/deletedij> update tmp set d = d * d;ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(31,0).ij> select cast(d as int) from tmp;1 -----------ERROR 22003: The resulting value is outside the range for the data type INTEGER.ij> select cast(d as smallint) from tmp;1 ------ERROR 22003: The resulting value is outside the range for the data type SMALLINT.ij> select cast(d as bigint) from tmp;1 --------------------ERROR 22003: The resulting value is outside the range for the data type BIGINT.ij> select cast(d as float) from tmp;1 ----------------------1.0E29 ij> select cast(d as real) from tmp;1 -------------1.0E29 ij> select cast(d as double precision) from tmp;1 ----------------------1.0E29 ij> -- test alternative syntaxselect cast(d as double) from tmp;1 ----------------------1.0E29 ij> insert into tmp values (+1.79769E+308);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(31,0).ij> select * from tmp;D --------------------------------100000000000000000000000000000 ij> drop table tmp;0 rows inserted/updated/deletedij> -- try inserting various types into decimal.-- we expect silent truncation of the fractiondrop table tmp;ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it does not exist.ij> create table tmp (d decimal(5,2));0 rows inserted/updated/deletedij> insert into tmp values (100);1 row inserted/updated/deletedij> insert into tmp values (cast (100 as smallint));1 row inserted/updated/deletedij> insert into tmp values (cast (100 as bigint));1 row inserted/updated/deletedij> insert into tmp values (cast (100 as real));1 row inserted/updated/deletedij> insert into tmp values (cast (100 as double precision));1 row inserted/updated/deletedij> insert into tmp values (cast (100.999 as real));1 row inserted/updated/deletedij> insert into tmp values (100.999e0);1 row inserted/updated/deletedij> insert into tmp values (100.999);1 row inserted/updated/deletedij> --too biginsert into tmp values (1000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (cast (1000 as smallint));ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (cast (1000 as bigint));ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (cast (1000 as real));ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (cast (1000 as double precision));ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (cast (1000.999 as real));ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (1000.999e0);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (1000.999);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> --try a few values that hit borders in how java.lang.Double work--(this is really tied to some details in the internals of-- SQLDecimal)insert into tmp values (1000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (10000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (100000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (1000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (10000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (100000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (1000000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (10000000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (100000000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (1000000000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (10000000000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (100000000000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (-1000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (-10000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (-100000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (-1000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (-10000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (-100000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (-1000000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (-10000000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (-100000000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (-1000000000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (-10000000000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> insert into tmp values (-100000000000000);ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).ij> drop table tmp;0 rows inserted/updated/deletedij> create table tmp(d dec(1,1));0 rows inserted/updated/deletedij> insert into tmp values (0.0);1 row inserted/updated/deletedij> insert into tmp values (-0.0);1 row inserted/updated/deletedij> insert into tmp values (0.1);1 row inserted/updated/deletedij> insert into tmp values (-0.1);1 row inserted/updated/deletedij> insert into tmp values (0.1e0);1 row inserted/updated/deletedij> insert into tmp values (-0.1e0);1 row inserted/updated/deletedij> select * from tmp;D ----0.0 0.0 0.1 -0.10.1 -0.1ij> delete from tmp;6 rows inserted/updated/deletedij> insert into tmp values (0);1 row inserted/updated/deletedij> insert into tmp values (0.0e0);1 row inserted/updated/deletedij> insert into tmp values (0.0e10);1 row inserted/updated/deletedij> insert into tmp values (-0);1 row inserted/updated/deletedij> insert into tmp values (-0.0e0);1 row inserted/updated/deletedij> insert into tmp values (-0.0e10);1 row inserted/updated/deletedij> insert into tmp values (cast (0 as smallint));1 row inserted/updated/deletedij> insert into tmp values (cast (0 as bigint));1 row inserted/updated/deletedij> insert into tmp values (cast (0 as real));1 row inserted/updated/deletedij> select * from tmp;D ----0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 ij> drop table tmp;0 rows inserted/updated/deletedij> create table tmp(d dec(1,0));0 rows inserted/updated/deletedij> insert into tmp values (1.0);1 row inserted/updated/deletedij> insert into tmp values (1);1 row inserted/updated/deletedij> insert into tmp values (1.0e0);1 row inserted/updated/deletedij> insert into tmp values (-1.0);1 row inserted/updated/deletedij> insert into tmp values (-1);1 row inserted/updated/deletedij> insert into tmp values (-1.0e0);1 row inserted/updated/deletedij> insert into tmp values (cast (1 as smallint));1 row inserted/updated/deletedij> insert into tmp values (cast (1 as bigint));1 row inserted/updated/deletedij> insert into tmp values (cast (1 as real));1 row inserted/updated/deletedij> select * from tmp;D ----1 1 1 -1 -1 -1 1 1 1 ij> drop table tmp;0 rows inserted/updated/deletedij> -- Using the DOUBLE built-in function-- test that double maps to the double data type-- all of the following should work if DOUBLE appears in the COLUMNDATATYPE columncreate table tmp (x double);0 rows inserted/updated/deletedij> insert into tmp values (1);1 row inserted/updated/deletedij> select columnname, columndatatype from sys.syscolumns c, sys.systables t where c.referenceid = t .tableid and t.tablename='TMP';COLUMNNAME |COLUMNDATATYPE ------------------------------------------------------------------------------------------------------------------------------------------------X |DOUBLE ij> drop table tmp;0 rows inserted/updated/deletedij> -- cast dec as as a numeric type in a select listcreate table tmp (d decimal);0 rows inserted/updated/deletedij> insert into tmp values (1.1);1 row inserted/updated/deletedij> --should all passinsert into tmp values (1);1 row inserted/updated/deletedij> select cast(d as int) from tmp;1 -----------1 1 ij> select cast(d as smallint) from tmp;1 ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -