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
————————————————
版权声明：本文为CSDN博主「袭冷」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/guoxilen/article/details/73470586