BLOB字段操作

置为空或NULL
update blob_test set b_content = null;
update blob_test set b_content = EMPTY_BLOB();

判断内容不为空
select * from blob_test where dbms_lob.getlength(b_content) > 0;
select * from blob_test where dbms_lob.getchunksize(b_content) > 0;

判断为null
select * from blob_test where b_content is null;

判断为空
select * from blob_test where dbms_lob.getlength(b_content) = 0;

根据目前项目的特点，有一个第三方厂家写入的一个表，记录图片信息，原来使用线程导出后，清空blob字段，现在完全改写为数据库自身实现。这样更高效
例子：之后再用job调用存储过程
CREATE OR REPLACE PROCEDURE GET_PIC_BLOB(pk_id number) IS
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
in_front_blob BLOB;
in_back_blob BLOB;
out_front_blob BLOB;
out_back_blob BLOB;
l_blob_len INTEGER;

BEGIN
select t.in_front_pic, t.in_back_pic into in_front_blob,in_back_blob
from car_pic t
where (dbms_lob.getlength(in_front_pic) > 0 and
dbms_lob.getlength(in_back_pic) > 0) and
id = pk_id;
begin
l_blob_len := DBMS_LOB.GETLENGTH(in_front_blob);
l_file := UTL_FILE.FOPEN('TEST1',
pk_id || '_in_front' || '.jpg',
'WB',
32767);
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.READ(in_front_blob, l_amount, l_pos, l_buffer);
UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
UTL_FILE.FCLOSE(l_file);

l_pos :=1;
EXCEPTION

--WHEN NO_DATA_FOUND THEN
--DBMS_OUTPUT.put_line('no data : ' || i_xh);
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(l_file) THEN
UTL_FILE.FCLOSE(l_file);
RAISE;
END IF;
end;

begin
l_blob_len := DBMS_LOB.GETLENGTH(in_back_blob);
l_file := UTL_FILE.FOPEN('TEST1',
pk_id || '_in_back' || '.jpg',
'WB',
32767);
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.READ(in_back_blob, l_amount, l_pos, l_buffer);
UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
UTL_FILE.FCLOSE(l_file);
l_pos := 1;

EXCEPTION

--WHEN NO_DATA_FOUND THEN
--DBMS_OUTPUT.put_line('no data : ' || i_xh);
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(l_file) THEN
UTL_FILE.FCLOSE(l_file);
RAISE;
END IF;
end;

END GET_PIC_BLOB;