oracle dbms_lob trim,Oracle - DBMS_LOB函数和用法

GETLENGTH

返回指定 LOB 数据的长度的函数

DBMS_LOB.GETLENGTH(lob_loc IN BLOB/CLOB/BFILE/NCLOB) RETURN INTEGER;

OPEN

打开LOB对象

DBMS_LOB.OPEN(

lob_loc IN OUT NOCOPY BLOB/CLOB/BFILE,

open_mode IN BINARY_INTEGER -- 只读:DBMS_LOB.LOB_READONLY；读写：DBMS_LOB.LOB_READWRITE

);

READ

从 LOB 数据中读取指定长度数据到缓冲区(变量中)的过程

DBMS_LOB.READ(

lob_loc IN BLOB/CLOB/BFILE, -- LOB 数据

amount IN OUT NOCOPY BINARY_INTEGER), -- IN:要读取的字符数；OUT：实际读取的字符数

offset IN INTEGER, -- 起始位置

buffer OUT RAW/VARCHAR2 -— 存储返回数据的变量

);    例：

DECLARE

var_clob CLOB;

var_buff VARCHAR2(1000);

var_len NUMBER(4);

var_start NUMBER(4);

BEGIN

SELECT RESUME INTO var_clob FROM TLOB WHERE NO = 1;

var_len := DBMS_LOB.GETLENGTH(varc);

var_start := 1;

DBMS_LOB.READ(var_clob, var_len, var_start, var_buff);

DBMS_OUTPUT.PUT_LINE('RETURN: '||var_buff);

END;

WRITE

将指定数量的数据写入LOB的过程。

DBMS_LOB.WRITE(

lob_loc IN OUT NOCOPY BLOB/CLOB, -- 被写入 LOB

amount IN BINARY_INTEGER, -- 写入长度(指写入 LOB 数据)

offset IN INTEGER, -- 写入起始位置(指被写入 LOB)

buffer IN RAW/VARCHAR2 -- 写入 LOB 的数据

);    例：

DECLARE

v_clob CLOB;

v_wstr VARCHAR2(1000);

v_strat NUMBER(4);

v_len NUMBER(4);

BEGIN

v_wstr := 'CLOB';

v_len := LENGTH(VWSTR);

v_strat := 5;

SELECT RESUME INTO v_clob FROM TLOB WHERE NO = 1 FOR UPDATE; --更新 LOB 数据需要 FOR UPDATE 锁定

DBMS_LOB.WRITE(v_clob, v_len, v_strat, v_wstr);

DBMS_OUTPUT.PUT_LINE('改写结果为: ' || v_clob);

COMMIT;

END;

APPEND

将指定的LOB数据追加到指定的LOB数据后的过程。

DBMS_LOB.APPEND(

dest_lob IN OUT NOCOPY BLOB, --追加到的目标LOB

src_lob IN BLOB -- 用来追加的LOB

);    例：

DECLARE

varcl CLOB;

vastr VARCHAR2(1000);

BEGIN

vastr := '，这是大对象列';

SELECT RESUME INTO varcl FROM TLOB WHERE NO = 1 FOR UPDATE;

DBMS_LOB.APPEND(varcl, vastr);

COMMIT;

DBMS_OUTPUT.PUT_LINE('追加结果为: ' || varcl);

END;

WRITEAPPEND

将缓冲区数据写到LOB尾部

DBMS_LOB.WRITEAPPEND(

lob_loc IN OUT NOCOPY BLOB/CLOB/NCLOB,

amount IN BINARY_INTEGER,

buffer IN RAW/VARCHAR2

);

TRIM

截断LOB数据中从第一位置开始指定长度的部分数据的过程

DBMS_LOB.TRIM(

lob_loc IN OUT NOCOPY BLOB/CLOB/NCLOB, --LOB数据

newlen IN INTEGER -- 阶段长度

);    例：

DECLARE

varc CLOB;

len NUMBER(4);

BEGIN

len := 4;

SELECT RESUME INTO varc FROM TLOB WHERE NO = 1 FOR UPDATE;

DBMS_LOB.TRIM(varc, len);

COMMIT;

DBMS_OUTPUT.PUT_LINE('截断结果为: '||varc);

END;

CLOSE

关闭已经打开的LOB

DBMS_LOB.CLOSE(lob_loc IN OUT NOCOPY BLOB/CLOB/BFILE);

SUBSTR

从LOB数据中提取子字符串的函数。

DBMS_LOB.SUBSTR(

lob_loc IN BLOB/CLOB/BFILE, -- 提取的来源

amount IN INTEGER:=32762, -- 提取长度

offset IN INTEGER:=1 -- 开始位置

)RETURN RAW/VARCHAR2; -- 提取到的内容    例：

DECLARE

vclob CLOB;

sustr VARCHAR2(1000);

len NUMBER(4);

start NUMBER(4);

BEGIN

SELECT RESUME INTO vclob FROM TLOB WHERE NO = 1;

len := 4;

start := 1;

sustr := DBMS_LOB.SUBSTR(vclob, len, start);

DBMS_OUTPUT.PUT_LINE('结果为: '||sustr);

END;

INSTR

从LOB数据中查找子字符串位置的函数。

DBMS_LOB.INSTR(

lob_loc IN BLOB/CLOB/NCLOB/BFILE,

pattern IN RAW/VARCHAR2,

offset IN INTERGER:=1,

nth IN INTEGER:=1

)RETURN INTEGER;    例：

DECLARE

vclob CLOB;

instr VARCHAR2(1000);

sustr VARCHAR2(1000);

len NUMBER(4);

BEGIN

SELECT RESUME INTO vclob FROM TLOB WHERE NO = 1;

sustr := '大对象';

len := DBMS_LOB.INSTR(vclob, instr);

DBMS_OUTPUT.PUT_LINE('位置为: ' || len);

sustr := DBMS_LOB.SUBSTR(vclob, LENGTH(instr), len);

DBMS_OUTPUT.PUT_LINE('位置为' || len || '长度为' || LENGTH(instr) || '的子字符串为：' || sustr);

END;

COMPARE

比较二个大对象是否相等。返回数值0为相等，-1为不相等。

DBMS_LOB.COMPARE(

lob_1 IN BLOB/CLOB/BFILE,

lob_2 IN BLOB/CLOB/BFILE,

amount IN INTEGER:=4294967295, --要比较的字符数(CLOB),字节数(BLOB)

offset_1 IN INTEGER:=1, --lob_1 的起始位置

offset_2 IN INTEGER:=1 --lob_2 的起始位置

);    例：

DECLARE

varc1 CLOB;

varc2 CLOB;

varc3 CLOB;

len NUMBER(4);

BEGIN

SELECT RESUME INTO varc1 FROM TLOB WHERE NO = 1;

SELECT RESUME INTO varc2 FROM TLOB WHERE NO = 2;

SELECT RESUME INTO varc3 FROM TLOB WHERE NO = 3;

len := DBMS_LOB.COMPARE(varc1,varc1);

DBMS_OUTPUT.PUT_LINE('比较的结果为: ' || len);

len := DBMS_LOB.COMPARE(varc2,varc3);

DBMS_OUTPUT.PUT_LINE('比较的结果为: ' || len);

END;

ERASE

删除LOB数据中指定位置的部分数据的过程

DBMS_LOB.ERASE(

lob_loc IN OUT NOCOPY BLOB/CLOB/NCLOB,

amount IN OUT NOCOPY INTEGER, --字符/字节数

offset IN INTEGER:=1 —起始位置

);     例：

DECLARE

vclob CLOB;

len NUMBER(4);

start NUMBER(4);

BEGIN

len := 1;

start := 5;

SELECT RESUME INTO vclob FROM TLOB WHERE NO = 1 FOR UPDATE;

DBMS_LOB.ERASE(vclob, len, start);

COMMIT;

DBMS_OUTPUT.PUT_LINE('擦除结果为: '|| clob);

END;

COPY

从指定位置开始将源LOB复制到目标LOB

DBMS_LOB.COPY(

dest_lob IN OUT NOCOPY BLOB/CLOB/NCLOB, -- 目标的LOB

src_lob IN BLOB/CLOB/NCOB, -- 拷贝的来源的LOB

amount IN INTEGER, -- 拷贝的长度

dest_offset IN INTEGER:=1, -- 目标从哪里开始接收

src_offset IN INTEGER:=1 -- 来源从来历开始拷贝

);    例：

DECLARE

vdest_lob CLOB;

vsrc_lob CLOB;

amount NUMBER;

dest_offset NUMBER;

src_offset NUMBER;

BEGIN

SELECT RESUME INTO vdest_lob FROM TLOB WHERE NO = 1 FOR UPDATE;

SELECT RESUME INTO vsrc_lob FROM TLOB WHERE NO = 2 ;

amount := DBMS_LOB.GETLENGTH(vsrc_lob);

dest_offset := DBMS_LOB.GETLENGTH(vdest_lob) + 1;

src_offset := 1;

DBMS_LOB.COPY(vdest_lob, vsrc_lob, amount, dest_offset, src_offset);

DBMS_OUTPUT.PUT_LINE('拷贝结果为: ' || vdest_lob);

END;

CREATETEMPORARY

在用户的临时表空间中，建立临时LOB

DBMS_LOB.CREATETEMPORARY(

lob_loc IN OUT NOCOPY BLOB/DLOB/NCLOB,

cache IN BOOLEAN, -- 是否将LOB读取到缓冲区

dur IN PLS_INTEGER:=10 -- 指定何时清除临时LOB(10：会话结束时；12：调用结束时)

);

ISTEMPORARY

确定定位符是否为临时LOB

DBMS_LOB.ISTEMPORARY(lob_loc IN BLOB/CLOB/NCLOB) RETURN INTEGER;

FILEEXISTS

确定FILE_LOC对应的OS文件是否存在。1：存在；0：不存在

DBMS_LOB.FILEEXISTS(file_loc IN BFILE) RETURN INTEGER;

FILEGETNAME

获取BFILE定位符所对应的目录别名和文件名

DBMS_LOB.FILEGETNAME(

file_loc IN BFILE,

dir_alias OUT VARCHAR2,

filename OUT VARCHAR2

);

FILEISOPEN

确定BFILE对应的OS文件是否打开

DBMS_LOB.FILEISOPEN(file_loc IN BFILE) RETURN INTEGER;

FREETEMPORARY

释放在默认临时表空间中的临时LOB

DBMS_LOB.FREETEMPORARY(lob_loc IN OUT NOCOPY BLOB/CLOB/NCLOB);

FILEOPEN

打开文件

DBMS_LOB.FILEOPEN(

file_loc IN OUT NOCOPY BFILE,

open_mode IN BINARY_INTEGER:FILE_READONLY

);

LOADBLOBFROMFILE

将BFILE数据装载到BLOB中，并且在装载后取得最新的偏移位置

DBMS_LOB.LOADBLOBFROMFILE(

dest_loc IN OUT NOCOPY BLOB,

src_bfile IN BFILE,

amount IN INTEGER,

dest_offset IN OUT INTEGER,

src_offset IN OUT INTEGER

);    例：

DECLARE

piece_bfile BFILE := BFILENAME('BFILE_DATA','test.jpg'); --创建一个BFILE指针

photo_blob BLOB;

BEGIN

SELECT bifle_content INTO photo_blob FROM bfile_demo WHERE bfile_id = 101;

DBMS_LOB.OPEN(photo_blob, DBMS_LOB.LOB_READWRITE);

DBMS_LOB.OPEN(piece_bfile);

DBMS_LOB.LOADBLOBFROMFILE(photo_blob, piece_bfile, DBMS_LOB.LOBMAXSIZE, 1, 1);

DBMS_LOB.CLOSE(photo_blob);

DBMS_LOB.CLOSE(piece_bfile);

END;

FILECLOSE

关闭打开的BFILE定位符所指向的OS文件

DBMS_LOB.FILECLOSE(FILE_LOC IN OUT NOCOPY BFILE);

FILECLOSEALL

关闭当前会话已经打开的所有BFILE文件

DBMS_LOB.FILECLOSEALL();

参考

http://blog.csdn.net/cunxiyuan108/article/details/16873733

http://www.linuxidc.com/Linux/2011-12/49911.htm

http://www.th7.cn/db/Oracle/201406/58454.shtml