PLSQL 导出blob为图像

1.PLSQL界面建SQL Window

2.编写查询语句

查询相应表获取id,blob与存图名称，需根据自己表更改相应名称。

select rownum id,t_blob,t_name from tableName;

3.批量存图代码

create or replace directory MY_DIR as 'E:/Images';

declare
  l_file     UTL_FILE.FILE_TYPE;
  l_buffer   RAW(32767);
  l_amount   BINARY_INTEGER := 32767;
  l_pos      INTEGER := 1;
  l_blob_len INTEGER;
  cursor cur is select rownum id,t_blob,t_name  from tableName;
  rec number(5) := 1;

BEGIN
  DBMS_OUTPUT.ENABLE(1000000000);
  for rec in cur loop
     DBMS_OUTPUT.put_line('rec: ' || rec.id);
     l_blob_len := DBMS_LOB.GETLENGTH(rec.t_blob);
     l_file     := UTL_FILE.FOPEN('MY_DIR', rec.id||'_'||rec.t_name || '.jpg', 'WB', 32767);
     --注：重新初始化位置
     l_pos:= 1;
     WHILE l_pos < l_blob_len LOOP
        --注：重新初始化大小
        l_amount := 32767;
        DBMS_LOB.READ(rec.t_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); 
  end loop;
END;