时间戳

首页 > 解决方案 > 消除 PL/SQL 函数中的重复代码片段

sql - 消除 PL/SQL 函数中的重复代码片段

问题描述

我需要关于优化我的功能的帮助/建议。如何避免重复这段代码：

    select 1 from order_attr oa
    where oa.order_id = o.order_id and ea.order_attr_name = 'administrationId'

下面是我的 PL/SQL 函数（这是一个示例，在实际代码中，该片段重复了很多次）：

   begin
   select count (distinct o.order_id)
   into :result
   from orders o
   where o.ext_order_id = :ExtOrderId and o.service_id not like 'tech#%' and (:Include = 1 and exists
   (
      select 1 from order_attr oa
      where oa.order_id = o.order_id and ea.order_attr_name = 'administrationId'  
   )
   or :Include = 0 and not exists
   (
      select 1 from order_attr oa
      where oa.order_id = o.order_id and ea.order_attr_name = 'administrationId' 
   ));
   exception
   when others then
      :result := 0;
   end;

标签： sqloracleplsql

解决方案

左连接将在这里为您提供帮助

 from orders o
 left join order_attr oa 
   on oa.order_id = o.order_id and ea.order_attr_name = 'administrationId'  
where o.ext_order_id = :ExtOrderId and o.service_id not like 'tech#%' 
  and (:Include = 1 and oa.order_id is not null -- exists
   or :Include = 0 and oa.order_is is null; -- not exists

我没有要测试的数据，但我希望你能明白

推荐阅读