PL/SQL-子句中的可选条件-没有动态SQL? [英] PL/SQL - Optional conditions in where-clause - without dynamic sql?

问题描述

我有一个查询，其中并非所有条件都是必需的.这是使用所有条件时的外观示例:

I have a query where not all conditions are necessary. Here's an example of what it looks like when all conditions are used:

select num
from (select distinct q.num
       from cqqv q
       where q.bcode = '1234567' --this is variable
             and q.lb = 'AXCT' --this is variable
             and q.type = 'privt' --this is variable
             and q.edate > sysdate - 30 --this is variable
       order by dbms_random.value()) subq
where rownum <= 10; --this is variable

标记为--this is variable的部分是变化的部分！如果未指定条件，则没有默认值.例如，如果输入为q.type指定"*"(但其他所有内容保持不变)，则查询应匹配type的所有内容，并执行为:

The parts marked as --this is variable are the parts that, well, vary! If a condition is NOT specified, then there is no default value. For example, if the input specifies "*" for q.type (but leaves everything else the same), then the query should match everything for type, and execute as:

select num
from (select distinct q.num
       from cqqv q
       where q.bcode = '1234567' --this is variable
             and q.lb = 'AXCT' --this is variable
             --and q.type = 'privt' --this condition ignored because of "type=*" in input
             and q.edate > sysdate - 30 --this is variable
       order by dbms_random.value()) subq
where rownum <= 10; --this is variable

我知道可以使用动态sql即时构建此查询，但是我想知道这可能会导致哪种性能问题，以及是否有更好的方法来做到这一点.

I know it is possible to use dynamic sql to build this query on the fly, but I am wondering what sort of performance problems this could cause, and if there is a better way to do this.

推荐答案

我解决的解决方案是生成动态SQL查询的解决方案，

The solution I've settled on is one that generates an dynamic SQL query that may look like this:

select num
from (select distinct q.NUM
       from cqqv q 
       where  (q.bcode = :bcode) 
                  and  (1=1 or :lb is null) 
                  and  (1=1 or :type is null) 
                  and  (q.edate> :edate) 
                order by dbms_random.value()) subq 
where rownum <= :numrows

(在此示例中，bcode和edate条件不是可选的，但是lb和type是可选的)

(in this example, the bcode and edate conditions were NOT optional, but the lb and type were)

我认为这是(或非常类似于)Michal Pravda所建议的，并且我们的DBA此处更喜欢此解决方案，而不是上下文变量解决方案.感谢您提供的所有帮助和建议！

I think this is (or is very similar to) what Michal Pravda was suggesting, and our DBA here prefers this solution over the context variable solution. Thanks for all that helped and offered advice!

我们的DBA找到了一个链接，其中详细介绍了此解决方案:

A link our DBA found which details this solution is here:

问汤姆:流行与自然选择

这篇关于PL/SQL-子句中的可选条件-没有动态SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！