客户的业务涉及大量的图片信息，之前这些图片信息只在数据库里存着路径，图片都是存在文件服务器上，现在要做数据库迁移，需要把这些图片都存放到数据库里。
坑爹的是，图片在数据库中的存放路径SQL*LOADER不能直接加载，需要进行行记录拆分。

11--00024-01-01-0002-003-.jpg;
11--00034-01-01-0003-007-.jpg;
11--00034-01-01-0003-038-.jpg;
... ...

需要将记录拆分为SQL*LOADER可以加载的格式

11--00024-01-01-0002-003-0001.jpg;
11--00024-01-01-0002-003-0002.jpg;
11--00024-01-01-0002-003-0003.jpg;
11--00024-01-01-0002-003-0004.jpg;
11--00024-01-01-0002-003-0005.jpg;
11--00024-01-01-0002-003-0006.jpg;
11--00024-01-01-0002-003-0007.jpg;
11--00034-01-01-0003-007-0001.jpg;
11--00034-01-01-0003-007-0002.jpg;
11--00034-01-01-0003-007-0003.jpg;
11--00034-01-01-0003-038-0001.jpg;
11--00034-01-01-0003-038-0002.jpg;

本人SQL书写能力较差，勉强写出以下SQL实现该需求。

declare
T_OWNERVOL VARCHAR2(25);
T_KEYWORD VARCHAR2(40);
i integer;
j integer;
v_qian varchar2(100);
v_hou varchar2(200);
  cursor cursor_i is
select substr(TZM, instr(TZM, '<') + 1, 4),substr(TZM, instr(TZM, '>') - 4, 4),
substr(TZM, 1, instr(TZM, '<')-1),substr(TZM, instr(TZM, '>')+1, 7),OWNERVOL,
KEYWORD from THAMS.LIBFILE722;
begin
  open cursor_i;
  loop
    fetch cursor_i
      into i,j,v_qian,v_hou,T_OWNERVOL,T_KEYWORD;
    exit when cursor_i%NOTFOUND;
    while (j - i >= 0) loop
insert into image(LOB_ID,DOISSER_NUM,DOC_NUM,PIC_NO,EFILE)
values (722,T_OWNERVOL,T_KEYWORD,I+1,v_qian||lpad(i,4,0)||v_hou);
      i := i + 1;
    end loop;
  end loop;
  close cursor_i;
end;

后来经朋友指导，下面的SQL也可实现该需求

with temp1 as(
select rowid my_rowid,regexp_substr(tzm,'<.+>') my_data,
regexp_replace(tzm,'<.+>','') my_replace
from thams.libfile722)
,temp2 as(
select rownum rn from dual
connect by rownum <=50
)
,temp3 as(
select my_rowid,replace(my_replace,'',
lpad(regexp_substr(my_data,'[0-9]+')+rn-1,4,'0')) as chaifen
from temp1 a,temp2 where
regexp_substr(my_data,'[0-9]+',1,2)-regexp_substr(my_data,'[0-9]+')+1>=rn
order by chaifen
)
select a.*,b.chaifen from thams.libfile722 a,temp3 b
where a.rowid=b.my_rowid

查看image表插入的数据

SQL> SELECT FILE_PATH,IMAGES,EFILE FROM IMAGE;

FILE_PATH字段需要修改

SQL> UPDATE IMAGE SET FILE_PATH=’D:’||EFILE;
SQL> COMMIT;
SQL> SELECT FILE_PATH,IMAGES,EFILE FROM IMAGE;

将此查询结果保存为D:TEST421.csv，并在D:TEST文件夹下建立421.ctl文件，内容如下：

LOAD DATA
INFILE 'd:test421.csv'
INTO TABLE images
append
FIELDS TERMINATED BY ','
(DOISSER_NUM,
DOC_NUM,
EFILE,
file_path,
IMAGES lobfile(file_path) TERMINATED BY EOF)

将图片拷贝到FILE_PATH字段的位置，执行SQL*LOADER加载图片到数据库

sqlldr stream/stream control=d:test421.ctl log=d:test421.log

加载完成查看数据库中的图片信息

SQL> SELECT FILE_PATH,IMAGES,EFILE FROM IMAGE;

本文固定链接: http://www.dbdream.com.cn/2011/10/oracle%e4%b8%80%e8%a1%8c%e6%8b%86%e5%88%86%e5%a4%9a%e8%a1%8c/ | 信春哥，系统稳，闭眼上线不回滚！