绑定变量不是替换变量；它们不会被您输入的文本所取代，因此您无法使用
我在位置181处看到一个ORA-00905: missing keyword at错误，似乎不知道SQL(Oracle PL/SQL(出了什么问题。
SELECT *
FROM FOO
WHERE LOCATION = :LOCATION
AND SAVED_DATE >= CASE WHEN :BEGIN_D IS NULL THEN SAVED_DATE ELSE TIMESTAMP :BEGIN_D END
AND SAVED_DATE <= CASE WHEN :END_D IS NULL THEN SYSDATE ELSE TIMESTAMP :END_D END
ORDER BY SAVED_DATE;
我的假设是，在这种情况下，用户输入是：
:LOCATION = 'new york'
:BEGIN_D = NULL
:END_D = NULL
则查询被推导为：
SELECT *
FROM FOO
WHERE LOCATION = 'new york'
AND SAVED_DATE >= SAVED_DATE -- This line is ignored
AND SAVED_DATE <= SYSDATE
ORDER BY SAVED_DATE;
然而，我看到了开头提到的错误。
当输入不是NULL(例如：:BEGIN_D = '2015-12-01 00:01:44'(时，我看不到错误。如果:BEGIN_D和:END_D中的一个或两个都是NULL，则返回错误。
TIMESTAMP :BEGIN_D。您只需要使用:BEGIN_D并传入TIMESTAMP数据类型。
SELECT *
FROM FOO
WHERE LOCATION = :LOCATION
AND SAVED_DATE >= CASE WHEN :BEGIN_D IS NULL THEN SAVED_DATE ELSE :BEGIN_D END
AND SAVED_DATE <= CASE WHEN :END_D IS NULL THEN SYSDATE ELSE :END_D END
ORDER BY SAVED_DATE;
您也不需要使用CASE表达式：
SELECT *
FROM FOO
WHERE LOCATION = :LOCATION
AND (:BEGIN_D IS NULL OR SAVED_DATE >= :BEGIN_D)
AND ((:END_D IS NULL AND SAVED_DATE <= SYSDATE) OR SAVED_DATE <= :END_D)
ORDER BY SAVED_DATE;
如果传入字符串值(而不是时间戳(，则使用TO_DATE:
SELECT *
FROM FOO
WHERE LOCATION = :LOCATION
AND (:BEGIN_D IS NULL OR SAVED_DATE >= TO_DATE(:BEGIN_D, 'YYYY-MM-DD HH24:MI:SS'))
AND ((:END_D IS NULL AND SAVED_DATE <= SYSDATE) OR SAVED_DATE <= TO_DATE(:END_D, 'YYYY-MM-DD HH24:MI:SS'))
ORDER BY SAVED_DATE;