活动专区

活动公告

上车！GBase 8a开发者联盟发车了 技术福利、人脉资源全都有！

认证培训专区

认证培训资料

GBase 8s GDCA 认证培训课前准备

GBase 8a

其他

物化视图报错：references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

01-GBase 8s 事务型数据库 安装与卸载

GBase 8s

适配迁移

[GBase 8s 教程]GBase 8s 常用函数、表达式

### 获取clob/blob的长度  
同名函数，参数输入不同  
```sql
-- 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。   
```sql
-- 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的部分字符串  
```sql
-- 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  
```sql
-- 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  
```sql
-- 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函数  
```sql
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类型  
```sql
-- 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;
```

GBase 8s内置dbms_lob函数说明

GBase 8s内置dbms_lob函数说明

评论

热门帖子