关键字
dump函数 oracle 、人大金仓、KingbaseES
问题描述
语法:DUMP(expression [,return_fmt [,start_position ] [.length ]])
功能:显示某个数据的数据类型,所占字节长度及其在数据库内部实际的存储形式。从start_position位置开始。
返回值是varchar2类型。
有4种调用形式:
- dump(expression)
- dump(expression,return_fmt)
- dump(expression,return_fmt,start_position)
- dump(expression,return_fmt,start_position,length)
参数处理逻辑如下:
- expression:要分析的字符串或表达式;
- return_fmt默认为10,可用值为8、16、17、1008、1010、1016和1017.其中,8为八进制,10为十进制,10为十进制,16为十六进制,17为单字符,而后四个顺序含义和前面四个相同及以上4种加上1000,表示在返回值中加上当前字符集;
- 如果return_fmt输入的值小于1000且不在8,10,16,17之内,则将其视为17;若大于1000且不在1008,1010,1016,1017之内,则将其视为1017;
- 支持start_position为负数情况,视为倒数返回的字符位置;
- Length:要分析的字符数,默认整个字符串或表达式长度;
- 支持length为负数情况,将其转化为正数执行;
- 如果length为0,默认整个字符串或表达式长度。
问题分析
同样的函数在不同数据库下功能完全一致吗?
测试点:
函数 | 用例 | 结果 |
Dump(expr) | select dump('asd') from dual; | Typ=96 Len=3: 97,115,100 |
select dump(12) from dual; | Typ=2 Len=2: 193,13 | |
select dump(-12) from dual; | Typ=2 Len=3: 62,89,102 | |
select dump(12.2) from dual; | Typ=2 Len=3: 193,13,21 | |
select dump('') from dual; | Null | |
select dump(' ') from dual; | Typ=96 Len=1: 32 | |
select dump(null) from dual; | Null | |
select dump(to_date('2023-08-25','YYYY-MM-DD')) from dual; | Typ=13 Len=8: 231,7,8,25,0,0,0,0 | |
select dump(last_ddl_time)from user_objects where rownum=1; | Typ=12 Len=7: 120,123,3,30,17,55,57 | |
select dump(to_timestamp('2023-08-25 10:30:00','YYYY-MM-DD HH24:MI:SS')) from dual; | Typ=187 Len=20: 231,7,8,25,10,30,0,0,0,0,0,0,0,0,3,0,0,0,0,0 | |
select dump(rawtonhex('126')) from dual; | Typ=1 Len=12: 0,51,0,49,0,51,0,50,0,51,0,54 | |
select dump(hextoraw('abc')) from dual; | Typ=23 Len=2: 10,188 | |
select dump(3.40282E+38F) from dual; | Typ=100 Len=4: 255,127,255,238 | |
select dump(2.22507485850720E-308) from dual; | Typ=2 Len=1: 128 | |
select dump(to_char(to_date('2023','YYYY'))) from dual; | Typ=1 Len=9: 48,49,45,65,85,71,45,50,51 | |
select dump(to_nchar('126')) from dual; | Typ=1 Len=6: 0,49,0,50,0,54 | |
select dump(-1D) from dual; | Typ=101 Len=8: 64,15,255,255,255,255,255,255 | |
select dump(interval '123' year(3)) from dual; | Typ=189 Len=24: 123,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,7,0,0,0 | |
select dump(interval '400' day(3)) from dual; | Typ=190 Len=24: 144,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,10,0,0,0 | |
Dump(expr,return_fmt) | select dump('asd',8) from dual; | Typ=96 Len=3: 141,163,144 |
select dump('asd',10) from dual; | Typ=96 Len=3: 97,115,100 | |
select dump('asd',16) from dual; | Typ=96 Len=3: 61,73,64 | |
select dump('asd',17) from dual; | Typ=96 Len=3: a,s,d | |
select dump('asd',18) from dual; | Typ=96 Len=3: a,s,d | |
select dump('asd',1010) from dual; | Typ=96 Len=3 CharacterSet=AL32UTF8: 97,115,100 | |
select dump('asd',1017) from dual; | Typ=96 Len=3 CharacterSet=AL32UTF8: a,s,d | |
select dump('asd',1018) from dual; | Typ=96 Len=3 CharacterSet=AL32UTF8: a,s,d | |
select dump('asd',1011) from dual; | Typ=96 Len=3 CharacterSet=AL32UTF8: 97,115,100 | |
select dump('asd',1009) from dual; | Typ=96 Len=3 CharacterSet=AL32UTF8: 97,115,100 | |
select dump('asd',1008.2) from dual; | Typ=96 Len=3 CharacterSet=AL32UTF8: 141,163,144 | |
select dump('asd',1008.5) from dual; | Typ=96 Len=3 CharacterSet=AL32UTF8: 141,163,144 | |
select dump('asd',1008.8) from dual; | Typ=96 Len=3 CharacterSet=AL32UTF8: 141,163,144 | |
select dump('asd',-16.1) from dual; | Typ=96 Len=3: a,s,d | |
select dump('asd','16') from dual; | Typ=96 Len=3: 61,73,64 | |
select dump('asd','17') from dual; | Typ=96 Len=3: a,s,d | |
select dump('asd','17.1') from dual; | Typ=96 Len=3: a,s,d | |
select dump('asd','17.8') from dual; | Typ=96 Len=3: a,s,d | |
select dump('asd',0) from dual; | Typ=96 Len=3: 97,115,100 | |
select dump('asd',null) from dual; | Typ=96 Len=3: 97,115,100 | |
select dump(to_date('2023-08-25','YYYY-MM-DD'),10) from dual; | Typ=13 Len=8: 231,7,8,25,0,0,0,0 | |
select dump(to_date('2023-08-25','YYYY-MM-DD'),8) from dual; | Typ=13 Len=8: 347,7,10,31,0,0,0,0 | |
select dump(hextoraw('abc'),16) from dual; | Typ=23 Len=2: a,bc | |
select dump(hextoraw('abc'),17) from dual; | Typ=23 Len=2: ^J,bc | |
select dump(hextoraw('abc'),1010) from dual; | Typ=23 Len=2: 10,188 | |
Dump(expr,return_fmt,start_position) | select dump('asd',10,1) from dual; | Typ=96 Len=3: 97,115,100 |
select dump('asd',10,3) from dual; | Typ=96 Len=3: 100 | |
select dump('asd',10,-3) from dual; | Typ=96 Len=3: 97,115,100 | |
select dump('asd',10,-2) from dual; | Typ=96 Len=3: 115,100 | |
select dump('asd',10,-1) from dual; | Typ=96 Len=3: 100 | |
select dump('asd',10,-6) from dual; | 空 | |
select dump('asd',10,6) from dual; | 空 | |
select dump('asd',10,1.5) from dual; | Typ=96 Len=3: 97,115,100 | |
select dump('asd',10,1.2) from dual; | Typ=96 Len=3: 97,115,100 | |
select dump('asd',10,1.8) from dual; | Typ=96 Len=3: 97,115,100 | |
select dump('asd',10,2.1) from dual; | Typ=96 Len=3: 115,100 | |
select dump('asd',10,-2.2) from dual; | Typ=96 Len=3: 115,100 | |
select dump('asd',10,-2.6) from dual; | Typ=96 Len=3: 115,100 | |
select dump('asd',10,'1') from dual; | Typ=96 Len=3: 97,115,100 | |
select dump('asd',10,'1.8') from dual; | Typ=96 Len=3: 97,115,100 | |
Dump(expr,return_fmt,start_position,length) | select dump('abcdef',10,1,1) from dual; | Typ=96 Len=6: 97 |
select dump('abcdef',10,1,0) from dual; | Typ=96 Len=6: 97,98,99,100,101,102 | |
select dump('abcdef',10,1,null) from dual; | Typ=96 Len=6: 97,98,99,100,101,102 | |
select dump('abcdef',10,1,'') from dual; | Typ=96 Len=6: 97,98,99,100,101,102 | |
select dump('abcdef',10,1,' ') from dual; | ERROR | |
select dump('abcdef',10,1,7) from dual; | Typ=96 Len=6: 97,98,99,100,101,102 | |
select dump('abcdef',10,1,-1) from dual; | Typ=96 Len=6: 97 | |
select dump('abcdef',10,1,-6) from dual; | Typ=96 Len=6: 97,98,99,100,101,102 | |
select dump('abcdef',10,1,-7) from dual; | Typ=96 Len=6: 97,98,99,100,101,102 | |
select dump('abcdef',10,1,3.1) from dual; | Typ=96 Len=6: 97,98,99 | |
select dump('abcdef',10,1,3.5) from dual; | Typ=96 Len=6: 97,98,99 | |
select dump('abcdef',10,1,3.8) from dual; | Typ=96 Len=6: 97,98,99 | |
select dump('abcdef',10,1,'3') from dual; | Typ=96 Len=6: 97,98,99 | |
select dump('abcdef',10,1,'3.8') from dual; | Typ=96 Len=6: 97,98,99 | |
select dump('abcdef',10,1,'-3.8') from dual; | Typ=96 Len=6: 97,98,99 | |
结论
函数定义:varchar2 dump(any params,int hex,int pos,int len)
函数功能:显示某个数据的数据类型,字节长度,当前字符集和该数据的按字节表示的内部存储值。
参数含义:
- any: 要分析的表达式可以是任意类型;
- hex:返回数据的格式;8为八进制,10为十进制,16为十六进制,17为单字符,以上4种加上1000,表示在返回值中加上当前字符集,默认为10;
- pos:返回值的起始字节位置,默认为1;
- len:返回值的字节长度,默认为NULL表示完整结果值。
函数处理逻辑:
- 任意参数输入NULL,输出NULL;
- 若hex小于17且不在8,16数值之内,均当默认值10处理;若大于17小于1000则认为参数17处理;若大于1000小于1017且不是1008或1016,则认为参数为1010;若大于1017,则认为参数为1017;
- 若hex为小数,则将其截断为整数;若hex为负数,均将其视为17;
- 若pos大于字节长度,返回NULL;
- 若pos为小数,则将其截断为整数;若pos为负数,则从结果值的结尾处向左侧开始计数;
- 若len为负数,则取绝对值处理;若len为小数,将其截断为整数;
函数主要属性:
- proname:truncate
- pronamespace:8000
- prokind:f(普通函数)
- proisstrict:t
- provolatile:i
- proparallel:s
和oracle不一致的说明:
- oracle数据库与kes数据库的数据类型的唯一标识值不同,oracle对应输出code值,kes输出oid;
因为两个数据库内部实现不同,导致字节长度和存储形式也存在不同;