如何在 Oracle (PL/SQL) 中创建动态 where 子句

但丁·K

我有以下查询:

SELECT *
FROM FOO
WHERE LOCATION = :LOCATION
AND MY_DATE >= TIMESTAMP :BEGIN AND MY_DATE <= TIMESTAMP :END -- option 1 if :BEGIN & :END is not NULL
AND MY_DATE >= TIMESTAMP :BEGIN AND MY_DATE <= sysdate        -- option 2 if :BEGIN is not NULL & :END is NULL
AND MY_DATE <= TIMESTAMP :END                                 -- option 3 if :BEGIN is NULL & :END is not NULL
AND MY_DATE <= sysdate                                        -- option 4 if both :BEGIN & :END is NULL
ORDER BY MY_DATE;

所以这里:LOCATION是由用户在代码级别使用OCI8. 例如:

require 'oci8'
cursor = conn.parse(query)
cursor.bind_param(':LOCATION', 'Chicago', String)

我只希望从 1 到 4 的选项之一成为最终查询的一部分。例如,如果选项 3 为真(:BEGINis NULL& :ENDis not NULL),那么最终查询将是:

SELECT *
FROM FOO
WHERE LOCATION = :LOCATION
AND MY_DATE <= TIMESTAMP :END                                 -- option 3 if :BEGIN is not NULL & :END is NULL
ORDER BY MY_DATE;

用户提供:END日期的地方

require 'oci8'
cursor = conn.parse(query)
cursor.bind_param(':LOCATION', 'Chicago', String)
cursor.bind_param(':START', NULL)
cursor.bind_param(':END', '2001-01-22 12:01:00', String)

并会导致:

SELECT *
FROM FOO
WHERE LOCATION = 'chicago'
AND MY_DATE <= TIMESTAMP '2001-01-22 12:01:00'
ORDER BY MY_DATE;

如何编写查询以允许此逻辑?

关注

一个选项是使用CASE

SELECT *
  FROM FOO
 WHERE LOCATION = :LOCATION
   AND MY_DATE >= CASE WHEN :BEGIN IS NOT NULL THEN :BEGIN
                       ELSE MY_DATE
                  END
   AND MY_DATE <= CASE WHEN :END IS NOT NULL THEN :END
                       ELSE SYSDATE
                  END
 ORDER BY MY_DATE;

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章