Oracle函数where子句不考虑所有筛选

Question

首先，我通常尝试在一些虚拟数据中复制我的问题，以便在这里更容易地提出问题，但这在当时是不可能的。所以请原谅我。

所以。我的问题如下。我有一个Oracle数据库，在它上我有七个表，我想在这些表上执行一个select，做左连接。所以。这是原始脚本

select turn_on_off.ID as Event_ID,controllers.ID as Ctrl_ID,Ctrl,SubCtrl,Turn_OFF,Turn_ON,DiscountPCT,Reason,Observation,Comments
from
sagi_un
left join
sagi_industrial
on
sagi_un.ID = sagi_industrial.UN_ID
left join
sagi_plant
on
sagi_industrial.ID = sagi_plant.INDUSTRIAL_ID
left join
sagi_area
on
sagi_plant.ID = sagi_area.PLANT_ID
left join
sagi_area_ctrl_map
on
sagi_area.ID = sagi_area_ctrl_map.AREA_ID
left join
controllers
on
controllers.ID = sagi_area_ctrl_map.CTRL_ID
left join
turn_on_off
on
sagi_area_ctrl_map.CTRL_ID = turn_on_off.CTRL_ID
left join
justification
on
turn_on_off.ID = justification.Event_ID
left join
reasons
on
reasons.ID = justification.REASON_ID
where
Turn_OFF > TO_DATE('10-07-2022','DD-MM-YYYY') and
sagi_un.UN = 'Q 2 RS' and
sagi_industrial.Industrial = 'Olefinas' and
sagi_plant.plant = 'OLE-2' and
sagi_area.area = 'Area Quente'

在查询结束时，我有一个where子句，其中设置了对数据的一些筛选。这个很好用。为了使我们在访问这些数据时更容易，我创建了一个管道函数，它接收过滤参数并返回查询结果。这是生成函数的代码。

create or replace FUNCTION                  "GET_OFF_INTERVALS2" 
(
  UN IN VARCHAR2 
, INDUSTRIAL IN VARCHAR2 
, PLANTA IN VARCHAR2 
, AREA IN VARCHAR2 
, MAX_DATE IN DATE 
) RETURN OFF_INTERVAL_TABLE PIPELINED IS
type ref0 is ref cursor;
cur0 ref0;
out_rec OFF_INTERVAL
:= OFF_INTERVAL(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
BEGIN
open cur0 for
select turn_on_off.ID as Event_ID,controllers.ID as Ctrl_ID,Ctrl,SubCtrl,Turn_OFF,Turn_ON,DiscountPCT,Reason,Observation,Comments
from
sagi_un
left join
sagi_industrial
on
sagi_un.ID = sagi_industrial.UN_ID
left join
sagi_plant
on
sagi_industrial.ID = sagi_plant.INDUSTRIAL_ID
left join
sagi_area
on
sagi_plant.ID = sagi_area.PLANT_ID
left join
sagi_area_ctrl_map
on
sagi_area.ID = sagi_area_ctrl_map.AREA_ID
left join
controllers
on
controllers.ID = sagi_area_ctrl_map.CTRL_ID
left join
turn_on_off
on
sagi_area_ctrl_map.CTRL_ID = turn_on_off.CTRL_ID
left join
justification
on
turn_on_off.ID = justification.Event_ID
left join
reasons
on
reasons.ID = justification.REASON_ID
where
Turn_OFF > max_date and
sagi_un.UN = UN and
sagi_industrial.Industrial = INDUSTRIAL and
sagi_plant.plant = PLANTA and
sagi_area.area = AREA;

  loop
    fetch cur0 into 
      out_rec.event_id,
      out_rec.ctlr_id,
      out_rec.ctrl,
      out_rec.subctrl,
      out_rec.turn_on,
      out_rec.turn_off,
      out_rec.discount_pct,
      out_rec.reason,
      out_rec.observation,
      out_rec.comments;
    exit when cur0%NOTFOUND;
    pipe row(out_rec);
  end loop;
close cur0;
  RETURN;
END GET_OFF_INTERVALS2;

我把这个函数叫做这样。

select * from TABLE(GET_OFF_INTERVALS2('Q 2 RS','Olefinas','OLE-2','Area Quente',TO_DATE('10-07-2022','DD-MM-YYYY')));

您可以看到过滤参数是相同的。

问题是，函数似乎没有考虑面积参数。在我正在测试的数据中，有两个区域(Area Quente和Area Fria)，但是管道函数返回的是两个区域的数据，而不是我请求的区域。就像有人评论了这句话。我认为我在这里做了一些非常愚蠢的事情，但我看不出是什么。如果有人能看看它，指出我的错误，我会非常感激。谢谢!

Answer 1

筛选条件将LEFT [OUTER] JOIN转换为INNER JOIN，因为WHERE条件要求有匹配的行，并且不能匹配NULL行。

select turn_on_off.ID as Event_ID,
       controllers.ID as Ctrl_ID,
       Ctrl,
       SubCtrl,
       Turn_OFF,
       Turn_ON,
       DiscountPCT,
       Reason,
       Observation,
       Comments
from   sagi_un
       INNER JOIN sagi_industrial
       on sagi_un.ID = sagi_industrial.UN_ID
       INNER JOIN sagi_plant
       on sagi_industrial.ID = sagi_plant.INDUSTRIAL_ID
       INNER JOIN sagi_area
       on sagi_plant.ID = sagi_area.PLANT_ID
       INNER JOIN sagi_area_ctrl_map
       on sagi_area.ID = sagi_area_ctrl_map.AREA_ID
       INNER JOIN turn_on_off
       on sagi_area_ctrl_map.CTRL_ID = turn_on_off.CTRL_ID
       left join controllers
       on controllers.ID = sagi_area_ctrl_map.CTRL_ID
       left join justification
       on turn_on_off.ID = justification.Event_ID
       left join reasons
       on reasons.ID = justification.REASON_ID
where  Turn_OFF > TO_DATE('10-07-2022','DD-MM-YYYY')
and    sagi_un.UN                 = 'Q 2 RS'
and    sagi_industrial.Industrial = 'Olefinas'
and    sagi_plant.plant           = 'OLE-2'
and    sagi_area.area             = 'Area Quente'

如果您想要LEFT OUTER JOIN，那么需要在JOIN的ON条件中包含过滤器。

select turn_on_off.ID as Event_ID,
       controllers.ID as Ctrl_ID,
       Ctrl,
       SubCtrl,
       Turn_OFF,
       Turn_ON,
       DiscountPCT,
       Reason,
       Observation,
       Comments
from   sagi_un
       LEFT OUTER JOIN sagi_industrial
       on (   sagi_un.ID = sagi_industrial.UN_ID
          AND sagi_industrial.Industrial = 'Olefinas')
       LEFT OUTER JOIN sagi_plant
       on (   sagi_industrial.ID = sagi_plant.INDUSTRIAL_ID
          AND sagi_plant.plant = 'OLE-2' )
       LEFT OUTER JOIN sagi_area
       on (   sagi_plant.ID = sagi_area.PLANT_ID
          AND sagi_area.area = 'Area Quente' )
       LEFT OUTER JOIN sagi_area_ctrl_map
       on sagi_area.ID = sagi_area_ctrl_map.AREA_ID
       LEFT OUTER JOIN controllers
       on controllers.ID = sagi_area_ctrl_map.CTRL_ID
       LEFT OUTER JOIN turn_on_off
       on ( sagi_area_ctrl_map.CTRL_ID = turn_on_off.CTRL_ID
          AND Turn_OFF > TO_DATE('10-07-2022','DD-MM-YYYY') )
       LEFT OUTER JOIN justification
       on turn_on_off.ID = justification.Event_ID
       LEFT OUTER JOIN reasons
       on reasons.ID = justification.REASON_ID
where  sagi_un.UN = 'Q 2 RS'

至于您的函数，从不将PL/SQL变量命名为与SQL列相同的名称(如果这样做，则在外部PL/SQL作用域之前考虑本地SQL作用域，以便在比较中有效地使用WHERE sagi_un.UN = sagi_un.UN而不是WHERE sagi_un.UN = plsql_UN)：

CREATE FUNCTION GET_OFF_INTERVALS2
(
  v_UN         IN sagi_un.UN%TYPE
, v_INDUSTRIAL IN sagi_industrial.Industrial%TYPE
, v_PLANT      IN sagi_plant.plant%TYPE
, v_AREA       IN sagi_area.area%TYPE
, v_MAX_DATE   IN turn_on_off.Turn_OFF%TYPE
) RETURN OFF_INTERVAL_TABLE PIPELINED
IS
  cur0 REF CURSOR;
  out_rec OFF_INTERVAL
:= OFF_INTERVAL(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
BEGIN
  open cur0 for
  select turn_on_off.ID as Event_ID,
         controllers.ID as Ctrl_ID,
         Ctrl,
         SubCtrl,
         Turn_OFF,
         Turn_ON,
         DiscountPCT,
         Reason,
         Observation,
         Comments
  from   sagi_un
         LEFT OUTER JOIN sagi_industrial
         on (   sagi_un.ID = sagi_industrial.UN_ID
            AND sagi_industrial.Industrial = v_industrial)
         LEFT OUTER JOIN sagi_plant
         on (   sagi_industrial.ID = sagi_plant.INDUSTRIAL_ID
            AND sagi_plant.plant = v_plant )
         LEFT OUTER JOIN sagi_area
         on (   sagi_plant.ID = sagi_area.PLANT_ID
            AND sagi_area.area = v_area )
         LEFT OUTER JOIN sagi_area_ctrl_map
         on sagi_area.ID = sagi_area_ctrl_map.AREA_ID
         LEFT OUTER JOIN controllers
         on controllers.ID = sagi_area_ctrl_map.CTRL_ID
         LEFT OUTER JOIN turn_on_off
         on ( sagi_area_ctrl_map.CTRL_ID = turn_on_off.CTRL_ID
            AND Turn_OFF > v_max_date )
         LEFT OUTER JOIN justification
         on turn_on_off.ID = justification.Event_ID
         LEFT OUTER JOIN reasons
         on reasons.ID = justification.REASON_ID
  where  sagi_un.UN = v_un;

  loop
    fetch cur0 into 
      out_rec.event_id,
      out_rec.ctlr_id,
      out_rec.ctrl,
      out_rec.subctrl,
      out_rec.turn_on,
      out_rec.turn_off,
      out_rec.discount_pct,
      out_rec.reason,
      out_rec.observation,
      out_rec.comments;
    exit when cur0%NOTFOUND;
    pipe row(out_rec);
  end loop;
  close cur0;
EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    close cur0;
END GET_OFF_INTERVALS2;
/

注意:您还应该捕获NO_DATE_NEEDED异常，以便如果函数在读取所有行之前停止调用，则可以关闭游标。