DB2 V95常用函数详解(带实例).docx

DB2常用函数实例版使用说明:函数定义形式 result : schema-name.funcation_name(para-list) 其中参数列表形式,表示必选参数,表示可选参数,|表示任选其一 下面的所有实例均在DB2 V95版本测试通过，如果是新版本会加注。any-builtin-type:Any data type that is not a distinct typestructured-type:Any user-defined structured type defined to the databasecharacter-type:Any of the character string types: CHAR, VARCHAR, LONG VARCHAR, CLOB.datetime-type:Any of the datetime types: DATE, TIME, TIMESTAMPgraphic-type:Any of the double byte character string types: GRAPHIC, VARGRAPHIC, LONGVARGRAPHIC, DBCLOB.string-type:Any type from character type, graphic-type or BLOBnumeric-type:Any of the numeric types: SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE.函数定义的Schema有sysfun和sysibm1 字符串函数ascii 返回字符串首字符的ASCII码值 integer : sysfun.ascii(|) values sysfun.ascii(A),sysfun.ascii(ABC),sysfun.ascii(中国人); 结果：65 65 206hex 返回字符的十六进制值 varchar : hex() values hex(中),hex(A),x41,xE590B4; 结果:E590B4 41 A 中chr 返回ASCII值对应的字符 char : sysfun.chr() values sysfun.chr(89); 结果:Yconcat 字符串连接 string-type : sysibm.concat(,) values sysibm.concat(hello,world);结果:helloworldlower,lcase 转小写，upper,ucase 转大写 varchar(4000)|clob(1M) : sysfun.lcase(|) char|varchar : sysibm.lower(|) values sysibm.lower(Your Name),sysibm.upper(Your Name); 结果:your name YOUR NAME values sysibm.lcase(Your Name),sysfun.ucase(Your Name); 结果:your name YOUR NAMEltrim 去左空格，rtrim 去右空格，trim 去左右空格 char|varchar|graphic sysibm.ltrim(|) varchar(4000)|clob(1M) sysfun.ltrim(|) rtrim定义同ltrim，只不过trim函数定义不清楚 values |sysibm.ltrim( h w )|,|sysfun.rtrim( h w )|,|trim( h w )|; 结果:h w h w h wtrim 去除表达式前后指定的字符，默认是空格 character-type : sysibm.trim(B|BOTH|L|LEADING|T|TRAILING FROM ) 将表达式前或后(默认是BOTH)指定的字符(strip-express)移除 values trim(0 from 00012300) 结果：123strip 去除表达式前后指定的字符，默认是空格 character-type : sysibm.strip(,B|BOTH|L|LEADING|T|TRAILING,) values strip(*88000*,b,*) 结果：88000posstr 查找字符串位置(从1开始计数),没有找到返回0 string-type : sysibm.posstr(,) 前者为source-string，后为search-string values sysibm.posstr(the one is not that one,one); 结果:5position 查找字符串位置，与posstr功能类似，由于考虑到不同国家的字符集，增强编码模式 string-type : sysibm.position(,CODEUNITS16|CODEUNITS32|OCTETS) values position(a,abca,OCTETS) 结果：1locate 搜索子字符串出现的位置，找不到返回0 integer : sysfun.locate(,integer) 其中varchar(4000)还可以换成clob(1M)或blob(1M) values sysfun.locate(y,yesterday),sysfun.locate(y,yesterday,2); 结果: 1 9repeat 重复字符串 varchar(4000)|clob(1M)|blob(1M) : sysfun.repeat(|,) values sysfun.repeat(ab,5); 结果:abababababspace 产生空格 varchar(4000) : sysfun.space() values |sysfun.space(5)| ; 结果: replace 替换字符串 varchar(4000)|clob(1M)|blob(1M) : sysfun.replace(,) 在exp1中查找所有exp2使用exp3来替换 values sysfun.replace(abac,a,d); 结果:dbdcleft 返回左边字符串 right 返回右边字符串 varchar(4000)|clob(1M)|blob(1M) : sysfun.left(|,) right函数同样定义 values left(adf.adf,5),right(adf.adf,5) ; 结果:adf.a f.adf values right(digits(3),5),left(rtrim(char(3)|00000,5); 结果: 00003 30000 这也是左补零右补零的方法substr 截取字符中 string-type : sysibm.substr(,integer:length) 要求start从1string的长度，如果指定length的话，要求必须能取到length位字符，不然会报错。 values sysibm.substr(abcdasd,1,2),sysibm.substr(abcdasd,3); 结果: ab cdasdsubstring 字符串截取函数，与substr用法类似，不同的是substr是按字节截取，substring可以指定string unit string-type : sysibm.substring(,CODEUNITS16|CODEUNITS32|OCTETS) 其中CODEUNITS16按16-bit UTF-16的code unit，CODEUNITS32按32-bit UTF-32 code units，OCTETS则按字节 values substring(中国人是我,1,4,CODEUNITS16),substring(中国人是我,1,6,OCTETS) 结果：中国人是 中国octet_length 返回字符串的字节数 integer : sysibm.octet_length() values octet_length(abc中); 结果：6length 返回字符串的长度(字节) integer : sysibm.length(,CODEUNITS16|CODEUNITS32|OCTETS)character_length 功能同length函数，使用时必须输入字符码 values length(abc中),character_length(abc中,CODEUNITS32); 结果：6 42 数学函数digits 返回固定长度(不够前导补零)仅数字的字符串，不包括符号和点，结果长度small integer5位，large integer10位，big integer19位，decimal类型p精度位 string : sysibm.digits() values digits(smallint(2.3),digits(2.3),digits(bigint(2.3),digits(dec(2.3,8,2); 结果是：00002,23,0000000000000000002,00000230abs、absval 取绝对值 values abs(-23); 结果：23ceiling、floor 向上取整和向下取整 values ceiling(13.56),floor(13.56); 结果：14 13mod 取模，取余数 values mod(10,3); 结果：1rand 返回随机数 values rand;round 国舍五入 values round(23.853,0),round(23.853,1),decimal(round(23.853,1),3,1) ,round(23.853,-1); 结果：24.000 23.900 23.9 20.000sign 如果参数大于0返回1，如果参数小于0返回-1，如果参数为0返回0power 返回参数1的参数2次幂sqrt 返回该参数的平方根degrees 求角度radians 将度转换为弧度3 日期函数0)了解一下unix timestamp与DB2中的timestamp区别Unix时间戳(Unix Timestamp):从格人威治时间1970年01月01日00时00分00秒(1970年1月1日 00:00:00 GMT)到现在的总秒数即从1970年1月1日(UTC/GMT的午夜)开始所经过的秒数，不考虑闰秒。一个小时为3600秒，一个基准日(纪元日,epoch day)为86400秒。以32位整型进行保存，可能会在2038年1月19日产生一些问题。GMT:Greenwich Mean Time 格人威治时间UTC:Universal Time Coordinated UTC时间，现在已经渐渐变成GMT的同义词转换当前时间为GMT时间：current timestamp - current timezone数据库中的timestamp与unix timestamp是不同的，即数据库中的timestamp并不是时间戳，它是一个日期时间类型记录的是日期和时间信息。1)DB2中关于日期与时间的表示有timestamp日期和时间(其中时间部分精确到ff6,即1000000ms)，date日期，time时间(精确到秒)oracle中的timestamp类型精确到ff9毫秒2)寄存器中的日期与时间current timestamp 当前时间戳current date 当前日期current time 当前时间3)日期时间对象直接相减，返回timestamp duration(数据类型为dec(20,6)，即yyyymmddhhmiss.ff6)、date duration(数据类型为dec(8,0)，即yyyymmdd)和time duration(数据类型为dec(6,0)，即hhmiss)timestamp-timestamp 返回两个时间戳对象相差的年月日时分秒毫秒date-date 返回两个日期相差年月日time-time 返回两个时间相差的时分秒values date(2011-02-13)-date(2010-01-12); 返回1 01 01，也就是1年1个月1日values date(2998-02-13)-date(1220-08-26); 返回1777 05 18，也就是1777年5个月18日4)DB2中日期与时间对象能直接与带单位的数值(忽略小数部分)相加减年years、月months、日days、小时hours、分钟minutes、秒seconds、毫秒microsecondsvalues current time + 3 hours - 30 minutes + 58 seconds ; 结果：02:20:30values current date + 2 years - 2 months + 20 days; 结果：2013-12-03values current timestamp + 2 years - 2 months + 20 days + 3 hours - 30 minutes + 58 seconds; 结果：2013-12-04 02:20:305)日期时间字符串转日期时间对象timestamp、date、time、year、month、day、hour、minute、second、microsecond这些函数的参数为timestmap、date、time、timestamp duration、date duration、time duration以及有效的时间日期字符串，结果依次返回参数的timestamp、日期、时间、年、月、日、小时、分钟、秒和毫秒部分SELECT timestamp(2012-11-23 18:35:30.580000) col1, date(2012-11-23) col2, time(18:35:30) col3, current timestamp col17, current date col18, current time col19, year(current timestamp) col4, year(current date) col5, month(current timestamp) col6, month(current date) col7, day(current timestamp) col8, day(current date) col9, hour(current timestamp) col10, hour(current time) col11, minute(current timestamp) col12, minute(current time) col13, second(current timestamp) col14, second(current time) col15, microsecond(current timestamp) col16 FROM sysibm.sysdummy1;6)其它日期时间函数days 返回自0001年1月1日以来的天数，可以用来计算两个日期相隔的天数 bigint : sysibm.days(timestamp|date|date-string) values days(current date)-days(date(2012-11-02); 结果：21timestamp_format 根据格式化字符串和日期字符串返回timestamp timestamp : sysibm.timestamp_format(,) ，其中exp1表示日期字符串，exp2表示格式化字符串 其中支持的日期时间格式化字符串有：yyyy 4位年、mm 2位月 、dd 2位月、hh12 12小时制小时、hh24 24小时制小时、mi 2位分钟、ss 2位秒、ff 16位毫秒 格式化分隔符有dash (-)、period (.)、slash (/)、comma (,)、apostrophe ()、semi-colon (;)、colon (:)、blank ( ) SELECT timestamp_format(2012/11/23 235935.540540,yyyy/mm/dd hh24miss.ff6) col1, timestamp_format(20121123,yyyymmdd) col2, timestamp_format(23,59,hh24,mi) col3 FROM sysibm.sysdummy1;to_timestamp和to_date 字符串转timestamp和date类型，是timestamp_format的同义词，用法类似timestampdiff 计算返回表达式中指定部分的值 integer:sysfun.timestampdiff(,) 参数exp1是指定值1(毫秒)|2(秒)|4(分钟)|8(小时)|16(天)|32(周)|64(月)|128(季)|256(年)，参数exp2表示timestamp duration的char(22)字符串 values timestampdiff(16,char(current timestamp - (current timestamp - 23 days); 注意：1、此函数在计算时按1月=30天计算，因此计算天不准确，建议使用days()计算相差的天数，例如下面计算2011-03-13与2010-01-26相差天数，结果为413和411，明显示411是正确的，而413是将2010年2月的28天当30天多算了2天 values timestampdiff(16,char(timestamp(2011-03-13 03:16:34.562003)-timestamp(2010-01-26 00:00:00); values days(timestamp(2011-03-13 03:16:34.562003) - days(timestamp(2010-01-26 00:00:00); 2、因为返回值为integer类型，所以函数在计算时有精度误差，它是只舍不入的，例如下面结果4，实际上应该是4分钟59秒 values timestampdiff(4,char(current timestamp - (current timestamp - 5 minutes + 1 seconds);quarter 返回日期所在季度，返回14 values quarter(current date) ;midnight_seconds 返回自午夜过去的秒数，即从00:00:00到指定时间间隔秒数 SELECT current timestamp col1, midnight_seconds(current timestamp) col2, midnight_seconds(current time) col3 FROM sysibm.sysdummy1;monthname 返回月分名称 values monthname(current date);dayofyear 返回日期在当年中的第几天，返回1366dayofweek 返回日期在周中的位置 integer : sysfun.dayofweek(timestamp|date|date-string) 返回17，分别表示星期天、星期一.星期六 values dayofweek(current date);dayname 返回日期是星期几 string : sysfun.dayname(timestamp|date|date-string) 返回本地星期的表示，如星期一week 返回日期是当年的第几周，返回154last_day 返回日期当月的最后一天日期，在v9.7中使用s4 类型转换函数在DB2中内置数据类型都有自己相应的数据类型转换函数，如smallint、integer、bigint、decimal、real、double、float、char、varchar、clob、graphic、blob等因为其使用简单，不作详述decimal char 假设字段定义为decimal(20,5)，现在存储8.9，直接转结果是字符串为000000000000008.90000 ，注意后面有一个空格 values char(cast(8.9 as decimal(20,5); 最佳写法：values strip(trim(char(cast(8.9 as decimal(20,5),both,0); 结果是字符串8.9 字符串8.9转成decimal(10,5)写法 values decimal(8.94,10,2);或者values cast(8.934 as decimal(10,5);cast 数据类型转换 data-type : cast( as ) values cast(123 as integer);decimal或者dec decimal : sysibm.dec(,integer,integer) 返回数值的小数显示形式 参数说明：numeric-type 数值类型,integer1 精度(precision),integer2 该度(scale)，就是显示的小数位数 如果precision设置过小，会报SQLSTATE=22003错误，数值转换时越界，即要求整数部分位数 precision - scale， 例如decimal(223.345,6,2)中6定义一共6位数字，2定义两位小数，如果需要转换的字符串非小数部分位数超6-2就会报错 。 decimal : sysibm.dec(,integer,integer,varchar) 将字符串转成小数形式的数值，需要指定decimal-character（最后一个参数) values sysibm.dec(233.2323,4,1); 结果:233.2 values sysibm.dec(233,4,1); 结果:233.00 values sysibm.dec(23.2323,5,1,.); 结果:23.20 values sysibm.decimal(223.345,6,2); 结果:223.34char 转字符串 char : sysibm.char(,varchar) 返回数值的字符串格式，如果指定varchar字符串的话，那么decimal中的点按指定字符串显示 char : sysibm.char(,integer) 返回字符串，如果指定integer的话就按长度返回 char : char(,keywords) 返回日期的字符串，按keywords格式化 DB2日期的缺省格式由数据库的地区代码决定，该代码在数据库创建的时候被指定。可以在创建数据库时使用 territory=USA 来定义地区代码，可选值有: DEF 使用与地区代码相匹配的日期和时间格式 EUR 使用欧洲日期和时间的 IBM 标准格式，格式:dd.mm.yyyy ISO 使用国际标准组织（ISO）制订的日期和时间格式，格式:yyyy-mm-dd JIS 使用日本工业标准的日期和时间格式,格式为:yyyy-mm-dd LOC 使用与数据库地区代码相匹配的本地日期和时间格式，本地日期格式，在char(date-type,keywords)中使用LOCATE USA 使用美国日期和时间的 IBM 标准格式，格式:mm/dd/yyyy values sysibm.char(223.345,) ; 结果:223,345 values sysibm.char(223.345,4) ; 结果:223. values char(current date,ISO); 结果:2012-05-09 values sysibm.char(current date); 结果:2012-05-09 values sysibm.char(current timestamp);结果:2012-05-09-23.22.03.906000 values sysibm.char(34); 结果:34to_char 将timestamp类型参数转成指定格式字符串 定义: varchar : sysibm.to_char(,) 实例: values sysibm.to_char(current timestamp,yyyy-mm-dd hh:mi:ss); 结果:2012-05-10 01:24:435 其它函数coalesce 取第一个非空值，可以用来将NULL转成指定值 any-type : sysibm.coalesce(par1,par2,.) 要求参数类型一致，可以接受任何build-in data type values coalesce(cast(null as integer),2,3);value 取第一个非空值，用法类似于coalescenullif 参数相等时返回NULL，否则返回第一个参数 any-type : sysibm.nullif(,) nullif(e1,e2) 相当于 case when e1=e2 then null else e1 end values sysibm.nullif(a,a),sysibm.nullif(cast(null as char(1),a);数值截断函数 trunc或truncate decimal-type : sysfun.trunc(,) 其中exp2为正数时截断小数位(保留小数位)，为负数时向左截断整数位(变0) values sysfun.truncate(345.2344,2),truncate(345.2344,56),truncate(345.2344,0),truncate(345.2344,-1); 结果:345.23 345.2344 345 340translate 字符串转换 varchar : sysibm.translate(,varchar:pad-string) 按自左到右顺序，按位找from-string中的字符在source-string出现时使用同位置(与from-string中字符相同位置)的字符替换， 如果在from-string中的位置在to-string中没有，则使用pad-string字符串(默认是空格)替换 values sysibm.translate(Hello World,db2,World,%); 结果:He%b db2% 解释:from-string为World，to-string为db2，替换字符串为%，就是在Hello World中替换W-d,o-b,r-2,l-%,d-% vlaues |sysibm.translate(Hello World,db2,World)| ; 结果:He b db2 listagg 聚集函数DB2 v9.7.4 中新增的函数，用于拼SELECT id,LISTAGG(name,) WITHIN GROUP(ORDER BY name) names FROM (VALUES(1,张三),(1,李明),(1,杨青),(2,柯言),(2,何枫) AS t(id,name)GROUP BY id;结果：id name- -1 张三,李明,杨青2 何枫,柯言SELECT stno,LISTAGG(subname| |score,) WITHIN GROUP(ORDER BY 1) info FROM (VALUES (NO01,语文,90) , (NO01,数学,99) , (NO01,英语,98) , (NO02,语文,96),(NO02,数学,95) AS t(stno,subname,score)GROUP BY stno;结果：STNO INFO- -NO01 语文 90,数学 99,英语 98NO02 语文 96,数学 95xmlagg 函数SELECT stno,REPLACE(REPLACE(XML2CLOB(XMLAGG(XMLELEMENT(NAME stno,subname| |score| , ),),) info FROM (VALUES (NO01,语文,90) , (NO01,数学,99) , (NO01,英语,98) , (NO02,语文,96),(NO02,数学,95) AS t(stno,subname,score)GROUP BY stno;结果：STNO INFO- -NO01 语文 90 , 数学 99 , 英语 98 ,NO02 语文 96 , 数学 95 ,type_id、type_name和type_schema 返回数据类型的id、name和schema，要求参数是structured data typestrip 功能增加的trim标量函数，它可以用来去除表达式的前缀和后缀特殊应用：-获取当前月份values month(current timestamp) ;-获取当前日期是当前月的第几天values day(current timestamp) ;-获取当前月表天数-获取上月最后一天values current timestamp - day(current timestamp) days ;-获取上月第一天values current timestamp -1 months - day(current timestamp) days + 1 days ;-获取本月第一天values current timestamp - day(current timestamp) days + 1 days ;values current timestamp - day(current timestamp) days - hour(current timestamp) hours - minute(current timestamp) minutes - second(current timestamp) seconds - microsecond(current timestamp) microsecond + 1 days ;-获取本月最后一天values current timestamp + 1 months - day(current timestamp) days ;values current timestamp - day(current timestamp) days + 1 days + 1 months - 1 days ;values current timestamp + 1 months - day(current timestamp) days - hour(current timestamp) hours - minute(current timestamp) minutes - second(current timestamp) seconds - microsecond(current timestamp) microsecond ;values current date