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

来源：互联网发布：欧特克软件下载编辑：程序博客网时间：2024/05/18 00:42

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

0 0