目的：

将数据库的照片导入到文件系统照片的名称以grid主键数据命名照片名称grid.jpg ，并指定照片的位置存放在数据库中的位置

1、创建direcory存放照片位置

create directory BLOBDIR as '/expdp_dir1';

grant read ,write on directory to public;

2、在gr_xx 表添加一列存放照片地址

alter table grxer_xx11 add address varchar2(100);

3、创建存储过程将数据库照片导出

create or replace procedure photo_dump(IDENTITYID in varchar2,filename in varchar2) is

l_file UTL_FILE.FILE_TYPE;

l_buffer RAW(32767);

l_amount BINARY_INTEGER := 32767;

l_pos INTEGER := 1;

l_blob BLOB;

l_blob_len INTEGER;

begin

SELECT photo

INTO l_blob

FROM grxer_xx11 WHERE grid = IDENTITYID and photo is not null;

l_blob_len := DBMS_LOB.GETLENGTH(l_blob);

l_file := UTL_FILE.FOPEN('BLOBDIR',filename,'wb', /*l_blob_len*/32767);

WHILE l_pos < l_blob_len LOOP

DBMS_LOB.READ(l_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);

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line(SQLERRM);

IF UTL_FILE.IS_OPEN(l_file) THEN

UTL_FILE.FCLOSE(l_file);

END IF;

RAISE;

end photo_dump;

4、执行导出照片脚本 并更新照片位置

declare

begin 

for i in (select grid  from grxer_xx11 where photo is not null) loop

photo_dump(i.grid,i.grid||'.jpg');

update  grxer_xx11 set address='/expdp_dir1/'||i.grid||'.jpg' where grid=i.grid;

commit;

end loop;

end;

/

如果图片特别多需要进行分批处理，执行以下方案：

sqlplus bjsx/Tzxz0309 <<EOF

declare

begin 

for i in ( SELECT grid

  FROM ( SELECT ROWNUM AS rowno, t.grid

          FROM gr_xx t where photo is not null

           AND ROWNUM <= 1000000) table_alias

 WHERE table_alias.rowno >= 1) loop

photo_dump1(i.grid,i.grid||'.jpg');

commit;

end loop;

end;

/

EOF

标红色为分页写法。

5、插看运行结果

cd /expdp_dir1/

ll *.jpg

select grid,address from grxer_xx11 where address is not null;