DB2用户自定义数据类型UDT


DB2用户自定义数据类型UDT
 
DB2用户自定义类型(User-Defined distinct types，UDT)，这里涉及自定义单值类型，基于DB2内置数据类型。
1、创建
语法如下：
CREATE [distinct] TYPE distinct_type_name AS source_data_type WITH COMPARISONS
 
source_data_type：指DB2内置数据类型，字符型(char，varchar等)，日期时间型(date，time，timestamp等)，
                  数值型(integer，double，decimal等)，考虑到平台的兼容性，
                  有些数据类型需要使用其他类型进行替代：
                  float —> double or real：float类型最好使用double或者real数据类型替代。
                  numeric —> decimal
                  long varchar —> varchar,dlob,clob
                  long vargraphic —> vargraphic or dbclob
WITH COMPARISONS：允许系统生成一个转换函数，在自定义类型和基类型之间进行转换。
                  因为用户自定义单值数据类型不能直接与DB2内置类型进行比较，需要通过转换函数，
                  默认创建的转换函数名与类型名distinct_type_name相同。
需要注意的是：
★此类型名必须作为数据库同一schema下的对象唯一存在，不允许重名。
★即使是基于同一个DB2基本类型而创建的UDT，也不能直接进行比较，
  因为DB2 SQL PL支持强数据类型，不同UDT之间的比较需要进行显示转换。
★下面这些函数是自动创建的：
  从DB2基本数据类型转换到UDT的函数：distinct_type_name
  从UDT转换到基本类型的函数：data_type_name
  若基类型是smallint的话，UDT可以将integer类型转换为自定义类型
  若基类型是char的话，UDT可以将varchar类型转换为自定义类型
  若基类型是graphic的话，UDT可以将vargraphic类型转换为自定义类型
 
★WITH COMPARISONS将不支持LONG VARCHAR，LONG VARGRAPHIC两种数据类型。
下面创建两个自定义数据类型：公里(kilometers)，英里(miles)
db2 => create type kilometers as integer with comparisons
DB20000I  SQL 命令成功完成。
db2 => create type miles as integer with comparisons
DB20000I  SQL 命令成功完成。
 
当创建完成这两个UDT之后，系统将会自动创建如下函数：
miles(integer)：将integer数据类型转换为miles数据类型
integer(miles)：将miles数据类型转换为integer数据类型
kilometers(integer)：将integer数据类型转换为kilometers类型
integer(kilometers)：将kilometers类型转换为integer类型
 
2、使用
下面创建一个表，存储旅游信息：
create table travel(
id char(9) not null,       --编号
kdistance kilometers,      --距离，单位千米，1千米≈0.6英里
mdistance miles,           --距离，单位英里，1英里≈1.6千米
constraint pk_travel primary key(id)
)
产看表结构：
db2 => describe table travel
                                数据类型                      列
列名                             模式       数据类型名称      长     小数位      NULL
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    CHARACTER                    9     0 否
KDISTANCE                       ADMINIST> KILOMETERS                   0     0 是
MDISTANCE                       ADMINIST> MILES                        0     0 是
  3 条记录已选择。
 
添加数据：
db2 => insert into travel(id,kdistance) values('JLCC00001',12),('GZGY00001',20)
DB20000I  SQL 命令成功完成。
db2 => select * from travel
ID        KDISTANCE   MDISTANCE
--------- ----------- -----------
JLCC00001          12           0
GZGY00001          20           0
  2 条记录已选择。
 
更新数据：
db2 => update travel set mdistance=kdistance
DB21034E  该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。
在SQL 处理期间，它返回：SQL0408N  值与其赋值目标的数据类型不兼容。
目标名为 "MDISTANCE"。SQLSTATE=42821
 
正确的更新应该，将kilometers类型的数据转换为基类型integer，再进行其他处理，如下：
db2 => update travel set mdistance=integer(ceil(integer(kdistance)*0.6))
DB20000I  SQL 命令成功完成。
db2 => select * from travel
ID        KDISTANCE   MDISTANCE
--------- ----------- -----------
JLCC00001          12           8
GZGY00001          20          12
  2 条记录已选择。
 
下面进行不同数据类型之间的比较：
db2 => select * from travel
ID        KDISTANCE   MDISTANCE
--------- ----------- -----------
JLCC00001          12           8
GZGY00001          20          12
SCCD00001          29          34
LNDL00001          45          33
  4 条记录已选择。
 
 
db2 => select count(*) from travel where mdistance > kdistance
SQL0401N  运算 ">" 的操作数的数据类型不兼容或者不可比较。  SQLSTATE=42818
 
db2 => ? 42818
SQLSTATE 42818: 运算符或函数的操作数不兼容或者不可比较。
 
通过将kilometers类型先转换为其基类型integer，在转换为miles类型，就可以与miles类型的数据进行比较了。
实际上，所有基于自定义单值数据类型的比较操作，都需要将该UDT转换为其基类型，在进行比较或者转换操作。
db2 => select * from travel where mdistance > miles(integer(kdistance))
ID        KDISTANCE   MDISTANCE
--------- ----------- -----------
SCCD00001          29          34
  1 条记录已选择。
 
 
db2 => select * from travel where mdistance > 20
SQL0401N  运算 ">" 的操作数的数据类型不兼容或者不可比较。  SQLSTATE=42818
 
db2 => select * fr