oracle where条件中的不等于,oracle – PL / SQL – where子句中的可选条件 – 没有动态sql？...

虽然你可以这样做

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

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

当然,您必须在动态查询中使用绑定变量,而不是将字面值连接到字符串中,否则性能(和可伸缩性和安全性)将会非常糟糕.

要清楚,我想到的动态版本会像这样工作：

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;

这意味着结果查询将是“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

但是,我接受这个例子中最多可能需要16个硬解析.使用本机动态SQL时,“和：bv为空”子句是必需的,但可以通过使用DBMS_SQL来避免.

注意：由Michal Pravda在注释中建议使用(1 = 1或：bindvar为null),因为它允许优化器消除该子句.