GBase 8s内置dbms_lob函数说明

获取clob/blob的长度

同名函数，参数输入不同

-- dbms_lob_getlength
create function 'gbasedbt'.dbms_lob_getlength (blob)
returns integer
external name '$GBASEDBTDIR/extend/excompat.1.0/excompat.bld(dbms_lob_getlength)' language c;

create function 'gbasedbt'.dbms_lob_getlength (clob)
returns integer
external name '$GBASEDBTDIR/extend/excompat.1.0/excompat.bld(dbms_lob_getlength)' language c;

智能大对象对比函数

同名函数，参数输入不同。需要注意的是对比长度不超过2GB。

-- dbms_lob_compare
create function 'gbasedbt'.dbms_lob_compare (blob,blob,integer default 2147483647,integer default 1,integer default 1)
returns integer
external name '$GBASEDBTDIR/extend/excompat.1.0/excompat.bld(dbms_lob_compare)' language c;

create function 'gbasedbt'.dbms_lob_compare (clob,clob,integer default 2147483647,integer default 1,integer default 1)
returns integer
external name '$GBASEDBTDIR/extend/excompat.1.0/excompat.bld(dbms_lob_compare)' language c;

clob截取函数

截取clob中指定位置开始，但长度不超过32767的部分字符串

-- dbms_lob_substr
create function 'gbasedbt'.dbms_lob_substr (clob,integer default 32767,integer default 1)
returns lvarchar
external name '$GBASEDBTDIR/extend/excompat.1.0/excompat.bld(dbms_lob_substr)' language c;

如果clob长度小于32767，需对clob的输出长度进行限制。
输出整个clob，大于32767仅显示32767

-- return min(len,32767)
-- drop function if exists dbms_lob_substr(clob);
create function 'gbasedbt'.dbms_lob_substr(pclob clob)
returns lvarchar
  return dbms_lob_substr(pclob, LEAST(dbms_lob_getlength(pclob),32767),1);
end function;

输出指定长度的clob，大于32767仅显示32767

-- return min(len,size,32767)
-- drop function if exists dbms_lob_substr(clob,integer);
create function 'gbasedbt'.dbms_lob_substr(pclob clob,plen integer)
returns lvarchar
  return dbms_lob_substr(pclob, LEAST(dbms_lob_getlength(pclob),plen,32767),1);
end function;

clob的instr函数

因已有dbms_lob_substr函数，故可以使用instr函数

create function 'gbasedbt'.dbms_lob_instr(pclob clob,pstr lvarchar,pp integer default 1,pn integer default 1)
returns int
  return instr(dbms_lob_substr(pclob), pstr, pp, pn);
end function;

将字符串转为clob

可将小于32767长度的字符串转换为clob类型

-- dbms_lob_new_clob
create function 'gbasedbt'.dbms_lob_new_clob (lvarchar)
returns clob with (not variant)
external name '$GBASEDBTDIR/extend/excompat.1.0/excompat.bld(dbms_lob_new_clob)' language c;