BLOB字段操作

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;