如何在 WHERE 子句中使用 IF…ELSE 條件？

我試圖在我的 WHERE 子句中使用 IF…ELSE 構造來選擇性地將條件應用於我的SELECT.

這應該工作嗎？

CREATE OR REPLACE package body If_Else_Pack IS

   PROCEDURE Moving
   (
       obj_A       IN varchar2,
       obj_B       IN varchar2,
       obj_C       IN varchar2,
       obj_D       IN varchar2,
       cur_Result  OUT T_CURSOR
   ) IS
   BEGIN
       OPEN cur_Result FOR
       SELECT 
           w.assetid,
           w.vehiclenumber,
           w.LatLong,
           w.CurrentSpeed,
           w.timeOfMovement,
           w.CurrentPlace,
           w.curTime,
           w.motion,
           w.fuelR,
           w.VehicleStart
       FROM waypoints1 w
       WHERE 
           IF ((obj_D= '0' OR obj_D IS NULL) AND (obj_C= '0' OR  obj_C IS NULL)) THEN
               WHERE w.customer_id =obj_A
               AND w.delegate_user_id = obj_B;
           ELSE IF ((obj_D= '0 'OR obj_D IS NULL) AND (obj_C<> '0' OR obj_C IS NOT NULL)) THEN
               WHERE w.customer_id = obj_A
               AND w.category_id = obj_C
               AND w.delegate_user_id = obj_B;
           ELSE IF ((obj_D<> '0' OR obj_Dis NOT NULL) AND(obj_C= '0' OR obj_C IS NULL)) THEN
               WHERE w.customer_id = obj_A
               AND w.fleet_id = obj_D
               AND w.delegate_user_id = obj_B;
   END MOVING;
END IF_ELSE_PACK;

我認為這個問題有點誤導並導致人們思考不正確。看起來這 3 個條件不會重疊，所以您需要做的就是OR將 3 個語句放在一起：

create or replace package body If_Else_Pack is

 Procedure Moving(obj_A IN varchar2,
                  obj_B  IN varchar2,
                  obj_C IN varchar2,
                  obj_D    IN varchar2,
                  cur_Result     OUT T_CURSOR) is

  begin

       open cur_Result for
       select w.assetid,
                w.vehiclenumber,
                w.LatLong,
                w.CurrentSpeed,
                w.timeOfMovement,
                w.CurrentPlace,
                w.curTime,
                w.motion,
                w.fuelR,
                w.VehicleStart
       from waypoints1 w
       where 
       (((obj_D= '0' or obj_D is null) and (obj_C= '0' oR  obj_C is null)) 
                and w.customer_id =obj_A
                and w.delegate_user_id = obj_B)
       or
       (((obj_D= '0 'or obj_D is null) and (obj_C<> '0' or obj_C is not null)) 
          and w.customer_id = obj_A 
          and w.category_id = obj_C 
          and w.delegate_user_id = obj_B)
       or
       (((obj_D<> '0' or obj_D is not null) and (obj_C= '0' or obj_C is null))
          and w.customer_id = obj_A
          and w.fleet_id = obj_D
          and w.delegate_user_id = obj_B);
END MOVING;
END IF_ELSE_PACK;

---

WHERE條件可以進一步簡化為：

       WHERE 
             w.customer_id = obj_A
         AND 
             w.delegate_user_id = obj_B
         AND 
             ( (obj_D = '0' or obj_D IS NULL) AND (obj_C= '0' OR obj_C IS NULL) 
            OR 
               (obj_D = '0' or obj_D IS NULL) AND (w.category_id = obj_C) 
            OR 
               (w.fleet_id = obj_D) AND (obj_C= '0' OR obj_C IS NULL) 
             ) ;

您應該查找CASE 構造。Adrian 的文章很有幫助。Oracle 文件在這裡。

對於你想要做的那種查詢，我不禁認為數據庫模式中有一些遺漏。當然你可以在 obj_C 和 obj_D 上添加一些約束，這樣就不允許 null 了？這將簡化事情。

引用自：https://dba.stackexchange.com/questions/24251