Oracle

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 关闭已经打开的LOBDBMS_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 在用户的临时表空间中，建立临时LOBDBMS_LOB.CREATETEMPORARY( lob_loc IN OUT NOCOPY BLOB/DLOB/NCLOB, cache IN BOOLEAN, -- 是否将LOB读取到缓冲区 dur IN PLS_INTEGER:=10 -- 指定何时清除临时LOB（10：会话结束时；12：调用结束时）);

ISTEMPORARY 确定定位符是否为临时LOBDBMS_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 释放在默认临时表空间中的临时LOBDBMS_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————————————————版权声明：本文为CSDN博主「袭冷」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/guoxilen/article/details/73470586