写一个对clob按分隔符分割的函数

来源：互联网发布：知天命代指多少岁编辑：程序博客网时间：2024/04/30 09:45

来至 http://blog.chinaunix.net/uid-7655508-id-3620015.html

对lob处理还不太熟悉，看网上有人问就写了个，lob类型处理起来的确很麻烦

SQL> create or replace type varchar2_tt as table
  2    of varchar2 (1000);
  3  /

Type created

SQL>
SQL> create or replace
  2    function in_list( p_string in  clob ,p_delimiter in varchar2 default ',',len in out number)
  3 return varchar2_tt
  4
  5 as
  6
  7       l_data       varchar2_tt := varchar2_tt();
  8       n             number;
  9       l_string clob:=p_string ;
10       l_delimiter clob:=to_clob(p_delimiter);
11       v_str varchar2(32767);
12    begin
13       dbms_lob.append(dest_lob => l_string,src_lob =>l_delimiter)  ;
14       loop
15          n :=  dbms_lob.instr(lob_loc => l_string,pattern => l_delimiter);
16          v_str:= trim(dbms_lob.substr(lob_loc => l_string,amount => n-1,offset => 1));
17       exit when v_str is null;
18          l_data.extend;
19          l_data(l_data.count) :=  v_str ;
20          len := length( l_data(l_data.count))+dbms_lob.getlength(l_delimiter)+len;
21       dbms_lob.erase(lob_loc => l_string,amount => len);
22    end loop;
23    return l_data;
24 end;
25  /

Function created
SQL> declare
  2  lens number default 0;
  3 l_data       varchar2_tt := varchar2_tt();
  4 l_string clob:=to_clob('A,AA,AAA,AAAAAB,BB,BBB,BBBB');
  5  begin
  6 l_data := in_list(p_string => l_string,len => lens);
  7 for i in l_data.first .. l_data.last loop
  8    dbms_output.put_line(l_data(i));
  9 end loop;
10  end;
11  /

A
AA
AAA
AAAAAB
BB
BBB
BBBB

PL/SQL procedure successfully completed

SQL>
SQL>  declare
  2  lens number default 0;
  3 l_data       varchar2_tt := varchar2_tt();
  4 l_string clob:=to_clob('A,AA,AAA,AAAAA@B,BB,BBB,BBBB@');
  5  begin
  6 l_data := in_list(p_string => l_string,p_delimiter =>'@',len => lens);
  7 for i in l_data.first .. l_data.last loop
  8    dbms_output.put_line(l_data(i));
  9 end loop;
10  end;
11
12  /

A,AA,AAA,AAAAA
B,BB,BBB,BBBB

PL/SQL procedure successfully completed

0 0