functions.out

来自「derby database source code.good for you.」· OUT 代码 · 共 531 行 · 第 1/2 页

ij> -- Test various functions
create table alltypes(
  id int not null primary key,
  smallIntCol smallint,
  intCol int,
  bigIntCol bigint,
  floatCol float,
  float1Col float(1),
  float26Col float(26),
  realCol real,
  doubleCol double,
  decimalCol decimal,
  decimal10Col decimal(10),
  decimal11Col decimal(11),
  numeric10d2Col numeric(10,2),
  charCol char,
  char32Col char(32),
  charForBitCol char(16) for bit data,
  varcharCol varchar(64),
  varcharForBitCol varchar(64) for bit data,
  longVarcharCol long varchar,
  blobCol blob(10k),
  clobCol clob(10k),
  dateCol date,
  timeCol time,
  timestampCol timestamp);
0 rows inserted/updated/deleted
ij> insert into allTypes(id) values(1),(2);
2 rows inserted/updated/deleted
ij> update allTypes set smallIntCol = 2 where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set intCol = 2 where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set bigIntCol = 3 where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set floatCol = 4.1 where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set float1Col = 5 where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set float26Col = 6.1234567890123456 where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set realCol = 7.2 where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set doubleCol = 8.2 where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set decimalCol = 9 where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set decimal10Col = 1234 where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set decimal11Col = 1234 where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set numeric10d2Col = 11.12 where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set charCol = 'a' where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set char32Col = 'abc' where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set charForBitCol = X'ABCD' where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set varcharCol = 'abcde' where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set varcharForBitCol = X'ABCDEF' where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set longVarcharCol = 'abcdefg' where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set blobCol = cast( X'0031' as blob(10k)) where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set clobCol = 'clob data' where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set dateCol = date( '2004-3-13') where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set timeCol = time( '16:07:21') where id = 1;
1 row inserted/updated/deleted
ij> update allTypes set timestampCol = timestamp( '2004-3-14 17:08:22.123456') where id = 1;
1 row inserted/updated/deleted
ij> select id, length(smallIntCol) from allTypes order by id;
ID         |2          
-----------------------
1          |2          
2          |NULL       
ij> select id, length(intCol) from allTypes order by id;
ID         |2          
-----------------------
1          |4          
2          |NULL       
ij> select id, length(bigIntCol) from allTypes order by id;
ID         |2          
-----------------------
1          |8          
2          |NULL       
ij> select id, length(floatCol) from allTypes order by id;
ID         |2          
-----------------------
1          |8          
2          |NULL       
ij> select id, length(float1Col) from allTypes order by id;
ID         |2          
-----------------------
1          |4          
2          |NULL       
ij> select id, length(float26Col) from allTypes order by id;
ID         |2          
-----------------------
1          |8          
2          |NULL       
ij> select id, length(realCol) from allTypes order by id;
ID         |2          
-----------------------
1          |4          
2          |NULL       
ij> select id, length(doubleCol) from allTypes order by id;
ID         |2          
-----------------------
1          |8          
2          |NULL       
ij> select id, length(decimalCol) from allTypes order by id;
ID         |2          
-----------------------
1          |3          
2          |NULL       
ij> select id, length(decimal10Col) from allTypes order by id;
ID         |2          
-----------------------
1          |6          
2          |NULL       
ij> select id, length(decimal11Col) from allTypes order by id;
ID         |2          
-----------------------
1          |6          
2          |NULL       
ij> select id, length(numeric10d2Col) from allTypes order by id;
ID         |2          
-----------------------
1          |6          
2          |NULL       
ij> select id, length(charCol) from allTypes order by id;
ID         |2          
-----------------------
1          |1          
2          |NULL       
ij> select id, length(char32Col) from allTypes order by id;
ID         |2          
-----------------------
1          |32         
2          |NULL       
ij> select id, length(charForBitCol) from allTypes order by id;
ID         |2          
-----------------------
1          |16         
2          |NULL       
ij> select id, length(varcharCol) from allTypes order by id;
ID         |2          
-----------------------
1          |5          
2          |NULL       
ij> select id, length(varcharForBitCol) from allTypes order by id;
ID         |2          
-----------------------
1          |3          
2          |NULL       
ij> select id, length(longVarcharCol) from allTypes order by id;
ID         |2          
-----------------------
1          |7          
2          |NULL       
ij> select id, length(blobCol) from allTypes order by id;
ID         |2          
-----------------------
1          |2          
2          |NULL       
ij> select id, length(clobCol) from allTypes order by id;
ID         |2          
-----------------------
1          |9          
2          |NULL       
ij> select id, length(dateCol) from allTypes order by id;
ID         |2          
-----------------------
1          |4          
2          |NULL       
ij> select id, length(timeCol) from allTypes order by id;
ID         |2          
-----------------------
1          |3          
2          |NULL       
ij> select id, length(timestampCol) from allTypes order by id;
ID         |2          
-----------------------
1          |10         
2          |NULL       
ij> -- try length of constants
values( length( 1), length( 720176), length( 12345678901));
1          |2          |3          
-----------------------------------
4          |4          |8          
ij> values( length( 2.2E-1));
1          
-----------
8          
ij> values( length( 1.), length( 12.3), length( 123.4), length( 123.45));
1          |2          |3          |4          
-----------------------------------------------
1          |2          |3          |3          
ij> values( length( '1'), length( '12'));
1          |2          
-----------------------
1          |2          
ij> values( length( X'00'), length( X'FF'), length( X'FFFF'));
1          |2          |3          
-----------------------------------
1          |1          |2          
ij> values( length( date('0001-1-1')), length( time('0:00:00')), length( timestamp( '0001-1-1 0:00:00')));
1          |2          |3          
-----------------------------------
4          |3          |10         
ij> -- try a length in the where clause
select id from allTypes where length(smallIntCol) > 5 order by id;
ID         
-----------
ij> select id from allTypes where length(intCol) > 5 order by id;
ID         
-----------
ij> select id from allTypes where length(bigIntCol) > 5 order by id;
ID         
-----------
1          
ij> select id from allTypes where length(floatCol) > 5 order by id;
ID         
-----------
1          
ij> select id from allTypes where length(float1Col) > 5 order by id;
ID         
-----------
ij> select id from allTypes where length(float26Col) > 5 order by id;
ID         
-----------
1          
ij> select id from allTypes where length(realCol) > 5 order by id;
ID         
-----------
ij> select id from allTypes where length(doubleCol) > 5 order by id;
ID         
-----------
1          
ij> select id from allTypes where length(decimalCol) > 5 order by id;
ID         
-----------
ij> select id from allTypes where length(decimal10Col) > 5 order by id;
ID         
-----------
1          
ij> select id from allTypes where length(decimal11Col) > 5 order by id;
ID         
-----------
1          
ij> select id from allTypes where length(numeric10d2Col) > 5 order by id;
ID         
-----------
1          
ij> select id from allTypes where length(charCol) > 5 order by id;
ID         
-----------
ij> select id from allTypes where length(char32Col) > 5 order by id;
ID         
-----------
1