Oracle PL \ SQL空输入参数WHERE条件 [英] Oracle PL\SQL Null Input Parameter WHERE condition

问题描述

截至目前，我正在使用IF ELSE来处理这种情况

As of now I am using IF ELSE to handle this condition

IF INPUT_PARAM IS NOT NULL

    SELECT ... FROM SOMETABLE WHERE COLUMN = INPUT_PARAM
ELSE
    SELECT ... FROM SOMETABLE

在没有IF ELSE循环的情况下，是否有更好的方法可以在单个查询中完成此操作.随着查询变得复杂，将会有更多类似这样的输入参数，并且所需的IF ELSE数量也会过多.

Is there any better way to do this in a single query without IF ELSE loops. As the query gets complex there will be more input parameters like this and the amount of IF ELSE required would be too much.

推荐答案

一种方法是使用

 WHERE column = nvl(var, column)

这里有两个陷阱:

1. 如果该列可为空，则此子句将过滤空值，而在您的问题中，第二种情况将不过滤空值.您可以修改此子句以将空值考虑在内，但它变得丑陋:

1. if the column is nullable, this clause will filter null values whereas in your question you would not filter the null values in the second case. You could modify this clause to take nulls into account but it turns ugly:

    WHERE nvl(column, impossible_value) = nvl(var, impossible_value)

当然，如果以某种方式插入了impossible_value，则会遇到其他类型的(有趣)问题.

Of course if somehow the impossible_value is ever inserted you will run into some other kind of (fun) problems.

这就是为什么当有很多参数(例如，几个搜索字段以大格式显示)时，我喜欢使用动态SQL的原因:

This is why when there are lots of parameters (several search fields in a big form for example), I like to use dynamic SQL:

DECLARE
   l_query VARCHAR2(32767) := 'SELECT ... JOIN ... WHERE 1 = 1';
BEGIN
   IF param1 IS NOT NULL THEN
      l_query := l_query || ' AND column1 = :p1';
   ELSE 
      l_query := l_query || ' AND :p1 IS NULL';
   END IF;
   /* repeat for each parameter */
   ...
   /* open the cursor dynamically */
   OPEN your_ref_cursor FOR l_query USING param1 /*,param2...*/; 
END;

您也可以使用EXECUTE IMMEDIATE l_query INTO l_result USING param1;

这篇关于Oracle PL \ SQL空输入参数WHERE条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！