oracle中猜分字符串成多个字段

 有一个数据表，其中的一个字段比较长，是由多个属性组成，例如：

SQL >   select  code_string,项目名称,code_value  from  code_standard;

CODE_STRING          项目名称                       CODE_VALUE
-- ------------------ ------------------------------ --------------------------------------------------------------------------------
210403006011          螺母                           物料大分类码段,标准件,203|标准件分类码段,紧固件,204|紧固件分类码段,螺母,215|标准代号码段,GB/T6178-1986,530|规格码段,M20,1371|材料（强度等级）码段,8,1718|表面处理码段,氧化,506

第三个字段非常的长，希望猜分成 这样：

210403006011　螺母　标准件　紧固件　螺母　GB/T6178-1986　M20　8　氧化

１，创建一个 split　函数

create   or   replace  type type_split  as   table   of   varchar2 ( 50 );   -- 创建一个　　type　　，如果为了使split函数具有通用性，请将其size 设大些。


-- 创建function
create   or   replace   function  split
(
   p_list  varchar2 ,
   p_sep  varchar2  : =   ' , '
)   return  type_split pipelined
  is
   l_idx  pls_integer;
   v_list   varchar2 ( 50 ) : =  p_list;
begin
   loop
      l_idx : =  instr(v_list,p_sep);
       if  l_idx  >   0   then
           pipe  row(substr(v_list, 1 ,l_idx - 1 ));
          v_list : =  substr(v_list,l_idx + length(p_sep));
       else
           pipe  row(v_list);
           exit ;
       end   if ;
    end  loop;
    return ;
end  split;

２，创建主函数

-- --创建类型
create   or   replace  type m_type  is  object(a  varchar2 ( 50 ),b  varchar2 ( 50 ),c  varchar2 ( 50 ),d  varchar2 ( 50 ),e  varchar2 ( 50 ),f  varchar2 ( 50 ),g  varchar2 ( 50 ),h  varchar2 ( 50 ),k varchar2(50))


create   or   replace  type m_table  is   table    of  m_type



-- ---创建function
create   or   replace   function  getCodeData  return  m_table
     as
      cursor  ca  is   select  code_string  as  item_code,项目名称  as  item_name,code_value  from  code_standard;
     rs ca % rowtype;   
     rs2 m_table: = m_table();   
     
     type cursor_type  is  ref  cursor ;
     type record_type  is  record(
        a  varchar2 ( 50 )       
     );
     
     c1 cursor_type;
     r_c1 record_type;
     
     c2 cursor_type;
     r_c2 record_type;
      
     TYPE  type_arrry  IS   TABLE   OF   VARCHAR2 ( 50 );
     myArray type_arrry: = type_arrry();
      
     mysql  varchar2 ( 500 );
     
     i  integer : = 0 ;
     m  integer ;
     n  integer ;
 
      begin
     
         n: = 1 ;
         m: = 1 ;
         myArray.extend( 10 );
          open  ca;
         loop
             fetch  ca  into  rs;
             exit   when  ca % notfound;
            
            rs2.extend( 1 );

            myArray( 1 ): = rs.item_code;
            myArray( 2 ): = rs.item_name;
            i: = 3 ;
            mysql: = ' select * from table(split( ''' ||  rs.code_value  || ''' , '' | '' )) ' ;
             open  c1  for  mysql;
            loop
               fetch  c1  into  r_c1;
               exit   when  c1 % notfound;      
              mysql: = ' select * from table(split( '''   ||  r_c1.a  ||   ''' , '' , '' )) ' ;
              n: = 1 ;
               open  c2  for  mysql;
              loop
                  fetch  c2  into  r_c2;
                  exit   when  c2 % notfound;
                    if  n = 2   then
                        -- myArray.extend(1);
                       myArray(i): = r_c2.a;
                    end   if ;
                   n: = n + 1 ;
               end  loop; 
               close  c2;   
              i: = i + 1 ;    
             end  loop;
             close  c1;  
            rs2(m): = m_type(myArray( 1 ),myArray( 2 ),myArray( 3 ),myArray( 4 ),myArray( 5 ),myArray( 6 ),myArray( 7 ),myArray( 8 ),myArray(9));
            m: = m + 1 ;         
          end  loop;
          close  ca;
         
          return  rs2;
    end ;

３，测试

SQL >   select   *   from   table (getcodedata)  where  rownum < 10 ;

A                                                  B                                                  C                                                  D                                                  E                                                  F                                                  G                                                  H                                                  K
-- ------------------------------------------------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
210403006011                                        螺母                                               标准件                                             紧固件                                             螺母                                               GB / T6178 - 1986                                       M20                                                 8                                                   氧化
210401001022                                        螺母                                               标准件                                             紧固件                                             螺母                                               GB / T6181 - 1986                                       M8                                                  04                                                  不经处理
210504004012                                        垫圈                                               标准件                                             紧固件                                             垫圈                                               GB / T853 - 1988                                         8                                                   Q215                                               不经处理
210113026011                                        螺栓                                               标准件                                             紧固件                                             螺栓                                               GB / T37 - 1988                                         M10 * 40                                               8.8                                                 氧化
210113002013                                        螺栓                                               标准件                                             紧固件                                             螺栓                                               GB / T37 - 1988                                         M12 * 45                                               8.8                                                 镀铬
210113017011                                        螺栓                                               标准件                                             紧固件                                             螺栓                                               GB / T37 - 1988                                         M6 * 30                                                8.8                                                 氧化
210113005011                                        螺栓                                               标准件                                             紧固件                                             螺栓                                               GB / T37 - 1988                                         M8 * 65                                                8.8                                                 氧化
210111009012                                        螺栓                                               标准件                                             紧固件                                             螺栓                                               GB / T5780 - 2000                                       M20 * 110                                              4.8                                                 不经处理
210107053012                                        螺栓                                               标准件                                             紧固件                                             螺栓                                               GB / T5781 - 2000                                       M10 * 25                                               4.8                                                 不经处理

9  rows selected

SQL >