最近在客户现场，遇到开发人员说有一个场景在进行数据迁移的时候sql执行不动，大致有点列转行，然后再去关联其他的表，再进行转列的处理，我们先看一下表的数据形式，假设表名字CREDIT_INFO;OB版本是3.2.3，租户是oracle模式。

CREDIT_NO	CUSTOMER_ID
202410101010	AAAAA,CCCC,EEEE
202411101010	BBBBB,DDDD

表中的CUSTOMER_ID是用逗号分开的多个，我们需要CREDIT_INFO中的多个CUSTOMER_ID转换成CUSTOMER_NAME，如下表展示。

CREDIT_NO	CUSTOMER_NAME
202410101010	武松、林冲、李逵
202411101010	宋江、卢俊义

开发人员的实现方式是创建了一个函数，在函数中对字段按照逗号进行拆分，然后关联客户表（CUST_INFO）获取客户姓名,函数的大致实现方式如下：

       create or replace function getsplitname(customer_id_set varchar2,split_str varchar2)
       return varchar2       is
           source_str varchar2(32767);
           return_str varchar2(32767);
           return_temp varchar2(32767);
           temp_str varchar2(32767);
           point_pos number;
       begin
           source_str:=customer_id_set;
           point_pos:=instr(source_str,split_str );
           while source_str>=1 loop
               temp_str := substr(source_str,1,point_pos-1);
               source_str:=substr(source_str,point_pos+1);
               point_pos:=instr(source_str,split_str);
               select customer_name into return_temp  from cust_info where customer_id=temp_str ;
               return_str:=return_str||','return_temp; 
           end loop;
           select customer_name into return_temp  from cust_info where customer_id=source_str;
           return_str:=return_str||','return_temp; 
           return return_str;
       end;

sql代码实现方式如下：

insert /*+ENABLE_PARALLEL_DML PARALLEL(8)*/ into table_target 
select getsplitname(CUSTOMER_ID) from CREDIT_INFO;

我这里对sql语句做了简化，原sql还关联了很多其他的表，CREDIT_INFO表中够用60万条记录，总之这个sql非常慢，因为调用了函数，每一行数据都要通过这个函数进行子查询，于是我尝试对这个进行了改写,不使用函数，还是使用子查询的方式。

改写的sql如下：

select select to_char(wm_concat(customer_name)) from cust_info where  cucstomer_id in(
                select  regexp_substr(ci.customer_id,'[^,]+',1,ROWNUM) id from dual connect by 
                rownum<=length(regexp_replace(ci.customer_id,'[^,]+'))+1) as customer_name 
from credit_info ci;

尝试后效果仍然没有改善，而且这个子查询的查询计划比较奇怪，虽然customer_id是cust_info表的主键，但是查询计划还是全表扫描，只能怀疑ob对这个写法不是特别支持。于是我打算使用最简单的方法：

创建一个存储过程，在存储过程中使用游标，对每一行的客户号进行处理。每一个客户号插入一条记录到临时表，以下是我的临时表

CREATE TABLE CREDIT_MID_USER_TAB(
  creditno        VARCHAR2(40) ,  --源表的管理字段
  USER_ID_SET    VARCHAR2(60),  --客户编号
  order_no  number   --排序字段，因为转换完以后需要与客户号的展示顺序一致);

存储过程脚本如下：

create or replace PROCEDURE CREDIT_USER_MID_FUNis
    iPointNow NUMBER;
    PCREDITNO varchar2(32);
    pInputString VARCHAR2(32767);  
    sSource VARCHAR2(32767);  
    sTemp VARCHAR2(32767);
    sTemp_1 VARCHAR2(32767);
    iCount NUMBER;
 --定义游标，只处理存在多个客户号的情况，区分开是为了提升速度，单个客户的不需要再循环中处理 CURSOR cus_credit_cor is 
         select creditno,USECUSTOMERID from CREDIT_INFO CI where instr(CI.USECUSTOMERID,',')>0 ;                Begin   
    delete from CREDIT_MID_USER_TAB;
    commit;
    iCount:=0;
    open cus_credit_cor;
    LOOP
    fetch cus_credit_cor into pCreditNo,pInputString;
        exit when cus_credit_cor%NOTFOUND;
        iCount:=0;
        sSource := pInputString;
       
        iPointNow :=instr(sSource,',');
        while iPointNow>=1  loop
            sTemp:=substr(sSource,1,iPointNow-1);
            sSource:=substr(sSource,iPointNow+1);
            iPointNow :=instr(sSource,',');
            insert into CREDIT_MID_USER_TAB(creditno,USER_ID_SET,order_no) values(pCreditNo,sTemp,iCount);
            iCount:=iCount+1;
        END LOOP;
        sTemp:=sSource;
        insert into CREDIT_MID_USER_TAB(creditno,USER_ID_SET,order_no) values(pCreditNo,sTemp,iCount);
    END LOOP;
    close cus_credit_cor;   
    commit;
    
    --处理只有单个客户号的情况，
    iCount:=0;
    insert into CREDIT_MID_USER_TAB(creditno,USER_ID_SET)
    select 
    creditno,
    USECUSTOMERID    from CREDIT_INFO CI where  instr(CI.USECUSTOMERID,',')<1  ;
    COMMIT;END;

sql就可以关联临时表进行处理

select LISTAGG(CIT.MFCUSTOMERID,',') WITHIN GROUP(ORDER by CIT.ORDER_NO) NEW_USER_SETfrom CREDIT_INFO Cileft join CREDIT_MID_USER_TAB CITgroup by ci.creditno;

通过临时表的方式实现行转列，然后用通过LISTAGG函数实现了列转行，速度由原来的执行不动，到加上处理临时表的时间总共2分钟就可以执行完成

SQL优化-不一样的行转列