PL/SQL过程-如果为null则忽略参数

Question

我有一个过程，它接受三个参数，然后根据这些参数查询一个表。现在其中一个可以是null，如果是的话，我希望在选择的WHERE子句中忽略它。

create or replace PROCEDURE Procedure1 
(
   COUNTRY IN VARCHAR2, MAKE IN VARCHAR2, SERIAL IN number
) AS

BEGIN

DECLARE
  CURSOR c1 IS  select v.ID from vehicle v   
                where v.country = COUNTRY AND 
                      v.make = MAKE AND 
                      ((SERIAL IS NOT NULL AND v.serial = SERIAL) OR 1);


BEGIN 

  FOR e_rec IN c1 LOOP

    DBMS_OUTPUT.PUT_LINE(e_rec.id);

  END LOOP;

END;
END Procedure1;

我试过这样的方法但不起作用。

Answer 1

您可以使用以下条件

WHERE v.country = COUNTRY AND 
                      v.make = MAKE AND 
                     (SERIAL IS NULL or v.serial = SERIAL)

这也能起作用

WHERE v.country = COUNTRY AND 
                  v.make = MAKE AND 
((SERIAL IS NOT NULL AND v.Serial = SERIAL) OR SERIAL IS NULL)

Answer 2

试试这个--这和米勒建议的使用NVL功能一样。

WHERE v.country = nvl(COUNTRY,v.country) 
AND v.make = nvl(MAKE,v.make)   
AND v.serial = nvl(SERIAL,v.serial)