将逗号分割的clob字段转化为varchar，并将字符串拆分为多行

SELECT '远大' 企业名称,
       sgpro.project_name 工程名称,
       pur.product_type 产品类别,
       pur.product_name 产品名称,
       pur.purchase_num 涉及数量,
       pur.product_regdate 材料进场时间,
       sgpro.build_licence 施工许可证号,
       sgpro.contractor_name 总包单位名称
  FROM t_purchase_info pur, t_allproject_info sgpro
 WHERE pur.project_id = sgpro.id
   AND pur.id in (select distinct regexp_substr(ids, '[^,]+', 1, level) project_id
                    from (select dbms_lob.substr(project_ids) ids
                            from g_enterprise_score
                           where class in (3, 4)
                             and score_type = 5
                             AND bad_no = 1
                             AND enterprise_id in
                                 (select distinct id
                                    from bemms.g_enterprise_info
                                   where name like '%远大%')
                             and project_ids is not null)
                  connect by level <=
                             length(dbms_lob.substr(ids)) -
                             length(replace(dbms_lob.substr(ids), ',', '')) + 1
                  )

其中，将clob转化为varchar，利用dbms_lob.substr，例：

 

select dbms_lob.substr(project_ids)
  from g_enterprise_score
 where class in (3, 4)
   and score_type = 5
   AND bad_no = 1
   AND enterprise_id in (select distinct id
                           from bemms.g_enterprise_info
                          where name like '%远大%')
   and project_ids is not null ;

将一行字符串（逗号分割）转化多行，利用regexp_substr() connect by level，例：

 

 

select distinct regexp_substr(ids, '[^,]+', 1, level) project_id
  from (select dbms_lob.substr(project_ids) ids
          from g_enterprise_score
         where class in (3, 4)
           and score_type = 5
           AND bad_no = 1
           AND enterprise_id in (select distinct id
                                   from bemms.g_enterprise_info
                                  where name like '%远大%')
           and project_ids is not null)
connect by level <= length(dbms_lob.substr(ids)) -
           length(replace(dbms_lob.substr(ids), ',', '')) + 1

 

 

以上SQL执行效率特别低，我们欢欢大神说过： 越是高级的函数越有可能影响效率，还是最基础的函数好，效率杠杠的。

 

 

其中尽量少用in，用exists，或者instr，经过优化后的SQL如下：

 

SELECT '北京XXX股份有限公司' 企业名称,
       sgpro.project_name 工程名称,
       pur.product_type 产品类别,
       pur.product_name 产品名称,
       pur.purchase_num 涉及数量,
       pur.product_regdate 材料进场时间,
       sgpro.build_licence 施工许可证号,
       sgpro.contractor_name 总包单位名称
  FROM t_purchase_info pur, t_allproject_info sgpro
 WHERE pur.project_id = sgpro.id
   AND instr((select ',' || wm_concat(dbms_lob.substr(g.project_ids)) || ',' ids
               from g_enterprise_score g
              where exists (select id
                       from bemms.g_enterprise_info e
                      where g.enterprise_id = e.id
                        and name like '%北京%')
                and (g.class = 3 or g.class = 4)
                and g.score_type = 5
                AND g.bad_no = 1
                and project_ids is not null),
             ',' || pur.id || ',') > 0