16. dbms_lob
16.1. 插件dbms_lob简介
dbms_lob是KingbaseES的一个扩展插件,它定义了一套对于CLOB/BLOB大对象的操作。
16.2. 插件dbms_lob的加载方式
在使用 dbms_lob 之前,我们需要将它添加到 kingbase.conf 文件的 shared_preload_libraries 中,并重启数据库。
此插件需要在kdb_raw插件加载之后才可以成功创建。
shared_preload_libraries = 'dbms_lob' # (change requires restart) create extension dbms_lob;
不需要使用时drop extension dbms_lob即可。
16.3. 插件dbms_lob的参数配置
无需配置任何参数
16.4. 插件dbms_lob的使用方法
16.4.1. dbms_lob.createtemporary
dbms_lob.createtemporary创建一个空的CLOB或者BLOB。
语法
createtemporary(lob_loc IN OUT CLOB, cache IN BOOLEAN, dur IN integer DEFAULT 1); createtemporary(lob_loc IN OUT BLOB, cache IN BOOLEAN, dur IN integer DEFAULT 1);
参数
lob_loc
目前该参数是OUT
cache
所有的临时大对象都在内存中
dur
目前都是SESSION级别的
返回值
无返回值。
注解
无
示例
\set SQLTERM / create or replace function test0010() return CLOB AS declare myclob CLOB ; begin dbms_lob.createtemporary( myclob, true, dbms_lob.SESSION); return myclob; end; / \set SQLTERM ; select test0010(); test0010 ---------- (1 row)
16.4.2. dbms_lob.istemporary
dbms_lob.istemporary该函数判断一个CLOB/BLOB对象是否是临时的
语法
istemporary(lob_loc IN CLOB) 返回bool类型; istemporary(lob_loc IN BLOB) 返回bool类型;
参数
lob_loc
该参数是一个传入的CLOB/BLOB大对象。
返回值
如果传入参数是NULL 则返回NULL 如果入参大对象是临时的 则返回TRUE 否则 返回FALSE。
注解
无
示例
\set SQLTERM / create or replace function test0020() return BOOL AS declare myclob CLOB ; declare bval BOOL; begin dbms_lob.createtemporary( myclob, true, dbms_lob.SESSION); bval = dbms_lob.istemporary( myclob ); return bval; end; / \set SQLTERM ; select test0020(); test0020 ---------- f (1 row)
16.4.3. dbms_lob.freetemporary
dbms_lob.freetemporary释放临时的CLOB/BLOB大对象。
语法
freetemporary(lob_loc IN CLOB); freetemporary(lob_loc IN BLOB);
参数
lob_loc
该参数是一个传入的CLOB/BLOB大对象。
返回值
无返回值。
注解
无
示例
-- dbms_lob.substr \set SQLTERM / CREATE OR REPLACE FUNCTION test0050() RETURNS VARCHAR AS DECLARE V1 CLOB; DECLARE V3 VARCHAR; DECLARE i integer; BEGIN dbms_lob.createtemporary(V1,true); dbms_lob.append(V1,'hello world'::CLOB); V3= dbms_lob.substr(V1); dbms_lob.freetemporary(V1); return V3; END; / \set SQLTERM ; select * from test0050(); test0050 ------------- hello world (1 row)
16.4.4. dbms_lob.append
dbms_lob.append该函数给出两个大对象 dest_lob和src_lob 把src_lob追加到dest_lob。
语法
append( dest_lob IN OUT CLOB, src_lob IN CLOB ); append( dest_lob IN OUT NOCOPY BLOB, src_lob IN BLOB );
参数
dest_lob
该参数是一个传入的CLOB/BLOB大对象 要把另外一个大对象src_lob追加到其后。
src_lob
该参数是一个传入的CLOB/BLOB大对象 要把该对象追加到dest_lob。
返回值
无返回值。
注解
无
示例
\set SQLTERM / create or replace function test0060() return CLOB as DECLARE myclob CLOB; begin dbms_lob.createtemporary( myclob, true); dbms_lob.append(myclob,'hello'::CLOB ); return myclob; end; / \set SQLTERM ; select test0060(); test0060 ----------
16.4.5. dbms_lob.substr
dbms_lob.substr该函数从一个CLOB对象中提取一个子串用子串构建并返回一个新的VARCHAR对象。
语法
dbms_lob.substr(lob_loc CLOB, amount INT DEFAULT 32767 , offsets INT DEFAULT 1 ) RETURNS VARCHAR;
参数
lob_loc
该参数是一个传入的CLOB大对象 要从这个CLOB对象中提取子串。
amount
从lob_loc提取多少个字符 默认值是32767。
offsets
从dest_lob的第几个字符开始提取子串 1表示第一个字符 默认值是1。
返回值
返回提取出来的子串。
注解
无
示例
\set SQLTERM / CREATE OR REPLACE FUNCTION test0051() RETURNS integer AS DECLARE V1 CLOB := 'hello 人大金仓database'; DECLARE V3 VARCHAR; BEGIN V3= dbms_lob.substr(V1,0, 7); return dbms_lob.getlength(V3); END; / \set SQLTERM ; select * from test0051(); test0051 ---------- 0 (1 row)
16.4.6. dbms_lob.writeappend
dbms_lob.writeappend该函数把VARCHAR或RAW类型 buffer中一定数量的字符串或字节串追加到CLOB或BLOB对象后。
语法
writeappend(lob_loc IN OUT CLOB, amount IN INTEGER, buffer IN VARCHAR); writeappend(lob_loc IN OUT BLOB, amount IN INTEGER, buffer IN RAW);
参数
lob_loc
该参数是一个传入的CLOB或BLOB大对象 要向这个CLOB或BLOB对象追加字符串或字节串。
amount
向lob_loc追加多少个字符或字节。
offsets
把buffer中一定数量的字符串或字节串追加到lob_loc中。
返回值
无返回值。
注解
无
示例
\set SQLTERM / CREATE OR REPLACE FUNCTION test0070() RETURNS CLOB AS DECLARE V1 CLOB := 'hello'; DECLARE V2 VARCHAR := 'world'; BEGIN dbms_lob.writeappend(V1,2,V2); return V1; END; / \set SQLTERM ; select test0070(); test0070 ---------- hellowo (1 row)
16.4.7. dbms_lob.instr
dbms_lob.instr给定两个CLOB对象 以及各自的起始字符位置 比较一定数量的字符串片断。
语法
instr(lob_loc CLOB, pattern VARCHAR, offsets INT default 1, nth INT default 1) return integer;
参数
lob_loc
该参数是一个传入的CLOB大对象 要在这个CLOB中搜索模式。
pattern
要在CLOB lob_loc中搜索该模式。
offset
从CLOB lob_loc中的第几个字符开始搜索 1表示从第1个字符开始搜索。
nth
在CLOB lob_loc中搜索第nth个匹配 1表示第1个。
返回值
返回整数 表示第nth个匹配的字符位置 如果nth匹配没有找到 则返回0。
注解
无
示例
\set SQLTERM / CREATE OR REPLACE FUNCTION test0080() RETURNS INT AS DECLARE V1 CLOB := 'hello world world'; DECLARE V2 VARCHAR := 'world'; DECLARE V3 INT :=1 ; BEGIN V3 = dbms_lob.instr(V1,V2,1,3); return V3; END; / \set SQLTERM ; select * from test0080(); test0080 ---------- 0 (1 row)
16.4.8. dbms_lob.compare
dbms_lob.compare给定两个CLOB对象以及各自的起始字符位置,比较一定数量的字符串片断。
语法
compare(lob_loc1 CLOB, lob_loc2 CLOB, amount INT, offset1 INT default 1, offset2 INT default 1) return integer;
参数
lob_loc
传入的两个CLOB大对象。
amount
比较这么多数量的字符。
offset
分别是lob_loc1和lob_loc2的起始字符位置 1表示从第1个字符开始比较。
返回值
返回整数0 表示相等 非0表示不相等。
注解
无
示例
\set SQLTERM / CREATE OR REPLACE FUNCTION test0090() RETURNS INT AS DECLARE V1 CLOB := '你好'; DECLARE V2 CLOB := '你好吗'; DECLARE V3 INT :=1 ; BEGIN V3=dbms_lob.compare(V2,V1,1,1,1); return V3; END; / \set SQLTERM ; select * from test0090(); test0090 ---------- 1 (1 row)
16.4.9. dbms_lob.getlength
dbms_lob.getlength给定的CLOB或BLOB对象,返回其中包含的字符长度或字节长度。
语法
getlength(lob_loc CLOB) return int4; getlength(lob_loc BLOB) return int4;
参数
lob_loc
传入的CLOB或BLOB大对象。
返回值
返回整数 表示CLOB中字符长度或BLOB中的字节长度。
注解
无
示例
\set SQLTERM / CREATE OR REPLACE FUNCTION test0100() RETURNS INT AS DECLARE V1 CLOB := 'abcd 1234'; DECLARE V3 INT :=1 ; BEGIN select dbms_lob.getlength(V1) into V3; return V3; END; / \set SQLTERM ; select * from test0100(); test0100 ---------- 9 (1 row)
16.4.10. dbms_lob.trim
dbms_lob.trim截取CLOB或BLOB对象指定长度的子串。
语法
trim(lob_loc IN OUT CLOB, newlen INT); trim(lob_loc IN OUT BLOB, newlen INT);
参数
lob_loc
传入的CLOB或BLOB大对象。
newlen
截取后长度。
返回值
如果lob_loc为NULL,抛出异常。
-
如果newlen小于1或大于MAXLOBSIZE(1073741824),抛出异常。
-
否则lob_loc返回截取后的CLOB或BLOB对象。
注解
无
示例
\set SQLTERM / CREATE OR REPLACE FUNCTION test0110() RETURNS INT AS DECLARE V1 CLOB := 'abcd'; DECLARE V3 INT :=1 ; BEGIN select dbms_lob.trim(V1,V3); return V1; END; / \set SQLTERM ; select * from test0110(); test0110 ---------- a (1 row)
16.4.11. dbms_lob.erase
dbms_lob.erase擦除CLOB或BLOB对象对象中指定的部分子串。CLOB擦除部分用空格填充,BLOB擦除部分用0填充。如果指定长度超出了CLOB或BLOB对象结尾,则实际擦除截止到CLOB或BLOB对象结尾,实际擦除长度通过amount参数返回。
语法
erase(lob_loc IN OUT CLOB, amount IN OUT INT, start_offset IN INT default 1); erase(lob_loc IN OUT BLOB, amount IN OUT INT, start_offset IN INT default 1);
参数
lob_loc 传入的CLOB或BLOB大对象。
amount 输入要擦除的子串长度。
start_offset 要擦除的起始位置,默认值为1。开头位置为1。
返回值
如果lob_loc, amout, start_offset任一输入为NULL,抛出异常。
如果amount小于1或大于MAXLOBSIZE(1073741824),抛出异常。
如果start_offset小于1或大于MAXLOBSIZE(1073741824),抛出异常。
否则lob_loc返回擦除后的BLOB对象,amount返回实际擦除的字节长度。
注解
无
示例
\set SQLTERM / CREATE OR REPLACE FUNCTION test0120() RETURNS INT AS DECLARE V1 CLOB := 'abcd'; DECLARE V2 INT :=1 ; DECLARE V3 INT :=2 ; BEGIN select dbms_lob.erase(V1,V2,V3); return V1; END; / \set SQLTERM ; select * from test0120(); test0120 ---------- a cd (1 row)
16.4.12. dbms_lob.copy
dbms_lob.copy拷贝源CLOB或BLOB对象中指定的部分子串,到目标CLOB或BLOB对象的指定位置,替换目标对象起始位置和长度指定的子串部分。如果目标对象指定拷贝的起始位置超出其原始长度,则在目标对象之后的起始位置附加源对象子串。原目标对象结尾和附加子串起始位置之间CLOB用空格填充,BLOB用0填充。
语法
copy(dest_lob IN OUT CLOB, src_lob IN CLOB, amount IN INT, dest_offset IN INT default 1, src_offset IN INT default 1); copy(dest_lob IN OUT BLOB, src_lob IN BLOB, amount IN INT, dest_offset IN INT default 1, src_offset IN INT default 1);
参数
dest_lob 目标CLOB或BLOB大对象。
src_lob 源CLOB或BLOB大对象。
amount 要拷贝的字符或字节长度。
dest_offset 目标对象要开始拷贝替换的起始位置,默认值为1。开头位置以1开始计数。
src_offset 源对象要开始拷贝读取的起始位置,默认值为1。开头位置以1开始计数。
返回值
如果dest_lob, src_lob amout, dest_offset, src_offset任一输入为NULL,抛出异常。
如果amount小于1或大于MAXLOBSIZE(1073741824),抛出异常。
如果dest_offset小于1或大于MAXLOBSIZE(1073741824),抛出异常。
如果src_offset小于1或大于MAXLOBSIZE(1073741824),抛出异常。
否则dest_lob返回复制后的CLOB或BLOB对象。
注解
无
示例
\set SQLTERM / CREATE OR REPLACE FUNCTION test0130() RETURNS INT AS DECLARE V1 CLOB := '一二三四'; DECLARE V2 CLOB := 'abcd'; DECLARE V3 INT :=1 ; DECLARE V4 INT :=2 ; DECLARE V5 INT :=1 ; BEGIN select dbms_lob.copy(V2,V1,V3,V5,V4); return V2; END; / \set SQLTERM ; select * from test0130(); test0130 ---------- 一二cd (1 row)
16.5. dbms_lob.read
dbms_lob.read读取CLOB或BLOB对象中指定部分到缓冲区对象。
语法
read(lob_loc IN CLOB, amount IN OUT INT, start_offset IN INT, buffer OUT VARCHAR2); read(lob_loc IN BLOB, amount IN OUT INT, start_offset IN INT, buffer OUT RAW);
参数
lob_loc 传入的CLOB或BLOB对象。
amount 输入要读取的字节长度。
start_offset 要读取的起始位置。开头位置以1开始计数。
返回值
如果lob_loc,amout, start_offset 任一输入为NULL,抛出异常。
如果amount小于1或大于32767,抛出异常。
如果start_offset小于1或大于MAXLOBSIZE(1073741824),抛出异常。
否则buffer返回读取的BLOB子串;amount返回实际读取的子串长度,如果start_offset超出原对象长度,则amount返回0。
注解
无
示例
\set SQLTERM / CREATE OR REPLACE FUNCTION test0140() RETURNS INT AS DECLARE V2 CLOB := 'abcd'; DECLARE V3 INT :=1 ; DECLARE V5 INT :=1 ; DECLARE buffer VARCHAR2; BEGIN select dbms_lob.copy(V2,V3,V5,buffer); return buffer; END; / \set SQLTERM ; select * from test0140(); test0140 ---------- a (1 row)
16.6. 插件dbms_lob卸载方法
不需要插件时执行drop extension dbms_lob即可。
16.7. 插件dbms_lob升级方法
dbms_lob扩展插件通常随KingbaseES安装包一并升级。通常情况下用户无法单独升级插件。