oracle将一行拆分为多行

佴朗宁

　　客户的业务涉及大量的图片信息，之前这些图片信息只在数据库里存着路径，图片都是存在文件服务器上，现在要做数据库迁移，需要把这些图片都存放到数据库里。
坑爹的是，图片在数据库中的存放路径SQL*LOADER不能直接加载，需要进行行记录拆分。
\01\01--0002\04-01-01-0002-003-<0001-0007>.jpg;
\01\01--0003\04-01-01-0003-007-<0001-0003>.jpg;
\01\01--0003\04-01-01-0003-038-<0001-0002>.jpg;
... ...
需要将记录拆分为SQL*LOADER可以加载的格式
\01\01--0002\04-01-01-0002-003-0001.jpg;
\01\01--0002\04-01-01-0002-003-0002.jpg;
\01\01--0002\04-01-01-0002-003-0003.jpg;
\01\01--0002\04-01-01-0002-003-0004.jpg;
\01\01--0002\04-01-01-0002-003-0005.jpg;
\01\01--0002\04-01-01-0002-003-0006.jpg;
\01\01--0002\04-01-01-0002-003-0007.jpg;
\01\01--0003\04-01-01-0003-007-0001.jpg;
\01\01--0003\04-01-01-0003-007-0002.jpg;
\01\01--0003\04-01-01-0003-007-0003.jpg;
\01\01--0003\04-01-01-0003-038-0001.jpg;
\01\01--0003\04-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>') my_replace
from thams.libfile722)
,temp2 as(
select rownum rn from dual
connect by rownum <=50
)
,temp3 as(
select my_rowid,replace(my_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:\TEST\421.csv，并在D:\TEST文件夹下建立421.ctl文件，内容如下：
LOAD DATA
INFILE 'd:\test\421.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:\test\421.ctl log=d:\test\421.log
加载完成查看数据库中的图片信息
SQL> SELECT FILE_PATH,IMAGES,EFILE FROM IMAGE;

　　任务完成。