在包含空搜索的where子句中使用函数

Question

我目前在Java中有一个准备好的语句，它在我的查询的WHERE子句中使用以下SQL语句，但是我想重新写入一个函数来限制用户参数传递给它并可能使其更有效。

(
    (USER_PARAM2 IS NULL AND 
    (COLUMN_NAME = nvl(USER_PARAM1, COLUMN_NAME) OR 
     (nvl(USER_PARAM1, COLUMN_NAME) IS NULL) 
    ) 
) 
    OR 
    (USER_PARAM2 IS NOT NULL AND COLUMN_NAME IS NULL) 
) 

USER_PARAM1和USER_PARAM2被用户传递到准备好的语句中。 USER_PARAM1表示应用程序用户想要搜索此特定COLUMN_NAME的内容。如果用户不包含此参数，则它将默认为NULL。 USER_PARAM2是我允许用户在此COLUMN_NAME上仅搜索NULL值的方式。此外，我有一些服务器逻辑，如果用户传入，则将USER_PARAM2设置为“true”;如果未由用户指定，则为NULL。

预期的行为是，如果声明USER_PARAM2，则只返回NULL的COLUMN_NAME值。如果未声明USER_PARAM2并声明了USER_PARAM1，则只返回COLUMN_NAME = USER_PARAM1。如果没有声明用户参数，则返回所有行。

任何人都可以帮我解决这个问题吗？ 在此先感谢...

编辑： 只是为了澄清这是我当前查询的外观（没有其他WHERE子句中陈述..）

SELECT * 
FROM TABLE_NAME 
WHERE (
    (USER_PARAM2 IS NULL AND 
    (COLUMN_NAME = nvl(USER_PARAM1, COLUMN_NAME) OR 
     (nvl(USER_PARAM1, COLUMN_NAME) IS NULL) 
    ) 
) 
    OR 
    (USER_PARAM2 IS NOT NULL AND COLUMN_NAME IS NULL) 
) 

...这是我愿意喜欢去......

SELECT * 
FROM TABLE_NAME 
WHERE customSearchFunction(USER_PARAM1, USER_PARAM2, COLUMN_NAME) 

编辑＃2： 好了，另一个同事帮我这...

CREATE OR REPLACE function searchNumber (pVal IN NUMBER, onlySearchForNull IN CHAR, column_value IN NUMBER) 
    RETURN NUMBER 
IS 
BEGIN 
    IF onlySearchForNull IS NULL THEN 
    IF pVal IS NULL THEN 
     RETURN 1; 
    ELSE 
     IF pVal = column_value THEN 
     RETURN 1; 
     ELSE 
     RETURN 0; 
     END IF; 
    END IF; 
    ELSE 
    IF column_value IS NULL THEN 
     RETURN 1; 
    ELSE 
     RETURN 0; 
    END IF; 
    END IF; 
END; 

...这似乎在我的初步试验工作..

SELECT * 
FROM TABLE_NAME 
WHERE 1=searchNumber(USER_PARAM1, USER_PARAM2, COLUMN_NAME); 

...我与它唯一的问题是 1）可能的性能问题VS复杂的SQL语句，我开始用。 2）我将不得不为每种数据类型创建类似的函数。 但是，后者对我来说不是什么问题。

编辑＃3 2012.02.01

所以我们结束了我在下面选择的解决方案去，在使用功能为基础的方法，其中代码/查询清洁胜过性能。我们发现基于函数的方法比使用纯SQL要差6倍左右。

感谢大家为伟大的输入大家！

编辑＃4 2012.02.14

所以回过头来看，我注意到，在@为@ danihp的溶液的澄清度应用虚拟表的概念艾伦的解决方案给出了清晰和性能方面非常不错的整体解决方案。这里是我现在有的

WITH params AS (SELECT user_param1 AS param, user_param2 AS param_nullsOnly FROM DUAL) 
SELECT * 
FROM table_name, params p 
WHERE (nvl(p.param_nullsOnly, p.param) IS NULL     --1) 
     OR p.param_nullsOnly IS NOT NULL AND column_name IS NULL --2) 
     OR p.param IS NOT NULL AND column_name = p.param   --3) 
     ) 
-- 1) Test if all rows should be returned 
-- 2) Test if only NULL values should be returned 
-- 3) Test if param equals the column value 

再次感谢您的建议和意见！

Answer 1

有一个简单的方法来传递您的参数只有一次，并根据需要引用它们多次，使用通用表表达式：

WITH params AS (SELECT user_param1 AS up1, user_param2 AS up2 FROM DUAL) 
SELECT * 
FROM table_name, params p 
WHERE ((p.up2 IS NULL 
     AND (column_name = NVL(p.up1, column_name) 
       OR (NVL(p.up1, column_name) IS NULL))) 
     OR (p.up2 IS NOT NULL AND column_name IS NULL)) 

实际上，您正在创建一个虚拟表，其中列是您的参数，并且只填充一行。

方便的是，这也确保您的所有参数都收集在同一个地方，并且可以以任意顺序指定（与自然出现在查询中的顺序相反）。

这对于基于功能的方法有一些很大的优点。首先，这不会阻止使用索引（正如@Bob Jarvis指出的那样）。其次，这将查询的逻辑保留在查询中，而不是隐藏在函数中。

Answer 2

我不知道如果我的方法有更多的表现，但它有最好的可读性：

发送2个a​​dditionals参数查询，可以重新编写类似的查询：

where 
(P_ALL_RESULTS is not null 
    OR 
    P_ONLY_NULLS is not null AND COLUMN_NAME IS NULL 
    OR 
    P_USE_P1 is not null AND COLUMN_NAME = USER_PARAM1 
) 

免责声明：以前回答OP的问题澄清