oracle PL/SQL - where-clause 中的可选条件 - 没有动态 sql？

PL/SQL - Optional conditions in where-clause - without dynamic sql?

提问by FrustratedWithFormsDesigner

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

The parts marked as --this is variableare 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:

标记为--this is variable的部分是不同的部分！如果未指定条件，则没有默认值。例如，如果输入为 q.type 指定“*”（但保留其他所有内容相同），则查询应匹配所有类型的内容，并执行为：

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

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 即时构建此查询，但我想知道这会导致什么样的性能问题，以及是否有更好的方法来做到这一点。

采纳答案by FrustratedWithFormsDesigner

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

我已经确定的解决方案是生成一个动态 SQL 查询，它可能如下所示：

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

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

（在本例中，bcode 和 edate 条件不是可选的，但 lb 和 type 是可选的）

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!

我认为这就是（或非常类似于）Michal Pravda 的建议，我们的 DBA 更喜欢这个解决方案而不是上下文变量解决方案。感谢所有帮助和提供建议的人！

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

我们的 DBA 找到的链接详细介绍了此解决方案：

Ask Tom: On Popularity and Natural Selection

问汤姆：关于流行和自然选择

回答by Tony Andrews

While you could do this...

虽然你可以这样做......

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

... the performance using dynamic SQL will usually be better, as it will generate a more targeted query plan. In the above query, Oracle cannot tell whether to use an index on bcode or lb or type or edate, and will probably perform a full table scan every time.

... 使用动态 SQL 的性能通常会更好，因为它会生成更有针对性的查询计划。在上面的查询中，Oracle 无法判断是否使用 bcode 或 lb 或 type 或 edate 上的索引，并且每次都可能执行全表扫描。

Of course, you mustuse bind variables in your dynamic query, not concatenate the literal values into the string, otherwise performance (and scalability, and security) will be very bad.

当然，您必须在动态查询中使用绑定变量，而不是将文字值连接到字符串中，否则性能（以及可扩展性和安全性）将非常糟糕。

To be clear, the dynamic version I have in mind would work like this:

需要明确的是，我想到的动态版本的工作方式如下：

declare
    rc sys_refcursor;
    q long;
begin
    q := 'select num
    from (select distinct q.num
           from cqqv q
           where 1=1';

    if p_bcode is not null then
        q := q || 'and q.bcode = :bcode';
    else
        q := q || 'and (1=1 or :bcode is null)';
    end if;

    if p_lb is not null then
        q := q || 'and q.lb = :lb';
    else
        q := q || 'and (1=1 or :lb is null)';
    end if;

    if p_type is not null then
        q := q || 'and q.type = :type';
    else
        q := q || 'and (1=1 or :type is null)';
    end if;

    if p_edate is not null then
        q := q || 'and q.edate = :edate';
    else
        q := q || 'and (1=1 or :edate is null)';
    end if;

    q := q || ' order by dbms_random.value()) subq
    where rownum <= :numrows';

    open rc for q using p_bcode, p_lb, p_type, p_edate, p_numrows;
    return rc;
end;

This means that the result query willbe "sargable" (a new word to me I must admit!) since the resulting query run will be (for example):

这意味着结果查询将是“sargable”（对我来说是一个新词，我必须承认！）因为结果查询运行将是（例如）：

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

However, I accept that this could require up to 16 hard parses in this example. The "and :bv is null" clauses are required when using native dynamic SQL, but could be avoided by using DBMS_SQL.

但是，我接受在此示例中这可能需要多达 16 次硬解析。使用本机动态 SQL 时需要“and :bv is null”子句，但可以通过使用 DBMS_SQL 避免。

Note: the use of (1=1 or :bindvar is null)when the bind variable is null was suggested in a comment by Michal Pravda, as it allows the optimizer to eliminate the clause.

注意：(1=1 or :bindvar is null)Michal Pravda 在评论中建议使用when the bind variable is null ，因为它允许优化器消除子句。

回答by OMG Ponies

While I agree with Tony that performance of using dynamic SQL is better, context variables is a better approach than using bind variables.

虽然我同意 Tony 的观点，即使用动态 SQL 的性能更好，但上下文变量是比使用绑定变量更好的方法。

Using IN_VARIABLE IS NULL OR table.fieldx = IN_VARIABLEis not ideal for handling optional values. Each time a query is submitted, Oracle first checks in its shared pool to see if the statement has been submitted before. If it has, the execution plan for the query is retrieved and the SQL is executed. If the statement can not be found in the shared pool, Oracle has to go through the process of parsing the statement, working out various execution paths and coming up with the optimal access plan (AKA “best path”) before it can be executed. This process is known as a “hard parse”, and can take longer than the query itself. Read more about the hard/soft parse in Oracle here, and AskTom here.

使用IN_VARIABLE IS NULL OR table.fieldx = IN_VARIABLE对于处理可选值并不理想。每次提交查询时，Oracle 首先检查其共享池以查看该语句之前是否已提交。如果有，则检索查询的执行计划并执行 SQL。如果在共享池中找不到该语句，则 Oracle 必须经过解析该语句、制定各种执行路径并提出最佳访问计划（也称为“最佳路径”）的过程，然后才能执行该语句。这个过程被称为“硬解析”，可能比查询本身花费更长的时间。了解更多有关甲骨文硬/软分析这里，和AskTom这里。

In short - this:

简而言之 - 这：

and (:bcode is null or q.bcode = :bcode)

...will execute the same, dynamic or otherwise. There's no benefit to using bind variables in dynamic SQL for optional parameters. The setup still destroys SARGability...

...将执行相同的，动态的或其他方式。在动态 SQL 中为可选参数使用绑定变量没有任何好处。该设置仍然会破坏 SARGability...

Context parameters are a feature that was introduced in Oracle 9i. They are tied to a package, and can be used to set attribute values (only for users with EXECUTE permission on the package, and you'll have to grant CREATE CONTEXT to the schema). Context variables can be used to tailor dynamic SQL so it includes only what is necessary for the query based on the filter/search criteria. In comparison, Bind variables (also supported in dynamic SQL) require that a value is specified which can result in IN_VARIABLE IS NULL OR table.fieldx = IN_VARIABLEtests in the search query. In practice, a separate context variable should be used for each procedure or function to eliminate the risk of value contamination.

上下文参数是Oracle 9i中引入的一个特性。它们与包相关联，可用于设置属性值（仅适用于对包具有 EXECUTE 权限的用户，您必须将 CREATE CONTEXT 授予架构）。上下文变量可用于定制动态 SQL，因此它仅包含基于过滤器/搜索条件的查询所需的内容。相比之下，绑定变量（在动态 SQL 中也支持）需要指定一个值，该值可以IN_VARIABLE IS NULL OR table.fieldx = IN_VARIABLE在搜索查询中进行测试。在实践中，应为每个过程或功能使用单独的上下文变量，以消除值污染的风险。

Here's your query using context variables:

这是您使用上下文变量的查询：

L_CURSOR SYS_REFCURSOR;
L_QUERY  VARCHAR2(5000) DEFAULT 'SELECT num
                                   FROM (SELECT DISTINCT q.num
                                           FROM CQQV q
                                          WHERE 1 = 1 ';
BEGIN

    IF IN_BCODE IS NOT NULL THEN
      DBMS_SESSION.SET_CONTEXT('THE_CTX',
                               'BCODE',
                               IN_BCODE);
      L_QUERY := L_QUERY || ' AND q.bcode = SYS_CONTEXT(''THE_CTX'', ''BCODE'') ';
    END IF;

    IF IN_LB IS NOT NULL THEN
      DBMS_SESSION.SET_CONTEXT('THE_CTX',
                               'LB',
                               IN_LB);
      L_QUERY := L_QUERY || ' AND q.lb = SYS_CONTEXT(''THE_CTX'', ''LB'') ';
    END IF;

    IF IN_TYPE IS NOT NULL THEN
      DBMS_SESSION.SET_CONTEXT('THE_CTX',
                               'TYPE',
                               IN_TYPE);
      L_QUERY := L_QUERY || ' AND q.type = SYS_CONTEXT(''THE_CTX'', ''TYPE'') ';
    END IF;

    IF IN_EDATE IS NOT NULL THEN
      DBMS_SESSION.SET_CONTEXT('THE_CTX',
                               'EDATE',
                               IN_EDATE);
      L_QUERY := L_QUERY || ' AND q.edate = SYS_CONTEXT(''THE_CTX'', ''EDATE'') - 30 ';
    END IF;

    L_QUERY := L_QUERY || ' ORDER BY dbms_random.value()) subq
           WHERE rownum <= :numrows ';

    FOR I IN 0 .. (TRUNC(LENGTH(L_QUERY) / 255)) LOOP
      DBMS_OUTPUT.PUT_LINE(SUBSTR(L_QUERY, I * 255 + 1, 255));
    END LOOP;

    OPEN L_CURSOR FOR L_QUERY USING IN_ROWNUM;
    RETURN L_CURSOR;

END;

The example still uses a bind variable for the rownum, becausethe value is notoptional.

该示例仍然为 rownum 使用绑定变量，因为该值不是可选的。

DBMS_SESSION.SET_CONTEXT('THE_CTX', 'LB', IN_LB);

The SET_CONTEXT parameters are as follows:

SET_CONTEXT 参数如下：

1. The context variable name. There's no instance creation involved
2. The variable within the context variable. A context variable is like a session variable, assuming familiarity with web applications & session objects.
3. The value for the variable defined in parameter #2.

1. 上下文变量名称。不涉及实例创建
2. 上下文变量中的变量。假设熟悉 Web 应用程序和会话对象，上下文变量就像会话变量。
3. 参数#2 中定义的变量的值。

Bind vs Context

绑定与上下文

Bind variables means Oracle expects a variable reference to populate - it's an ORA error otherwise. For example:

绑定变量意味着 Oracle 需要填充变量引用 - 否则是 ORA 错误。例如：

... L_QUERY USING IN_EXAMPLE_VALUE

...expects that there is a single bind variable reference to be populated. If IN_EXAMPLE_VALUEis null, there hasto be :variablein the query. IE: AND :variable IS NULL

...期望有一个要填充的绑定变量引用。如果IN_EXAMPLE_VALUE为空，也有是:variable在查询中。IE：AND :variable IS NULL

Using a context variable means not having to include the extraneous/redundant logic, checking if a value is null.

使用上下文变量意味着不必包含无关/冗余的逻辑，检查值是否为空。

IMPORTANT: Bind variables are processed in order of occurrence (known as ordinal), NOTby name. You'll notice there's no datatype declaration in the USINGclause. Ordinals aren't ideal - if you change them in the query without updating the USINGclause, it will break the query until it's fixed.

重要提示：绑定变量按出现顺序（称为序数）处理，而不是按名称处理。您会注意到USING子句中没有数据类型声明。序数并不理想 - 如果您在查询中更改它们而不更新USING子句，它将破坏查询，直到它被修复。

回答by Pedro Mendes

I would just do this

我会这样做

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 = nvl(<variable-type>, q.type)  --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

One only has to guarantee that the variable-type is null when the q.TYPE filtering is to be ignored.

当要忽略 q.TYPE 过滤时，只需保证 variable-type 为 null。

回答by user6104564

where ( columnA = passedValue or passedValue = -1 )

其中（ columnA = passValue 或passedValue = -1 ）

when passed value into sql is -1, columnA can be anything..

当传递给 sql 的值为 -1 时，columnA 可以是任何东西..