oracle 绑定变量_您是否正确绑定了Oracle日期？ 我打赌你不是

oracle 绑定变量

Oracle数据库有它的方式。 在会议上SQL演讲中 ，我喜欢将人们与以下Oracle事实混淆：

……答案当然是：

使空字符串与NULL相同不是很可怕吗？ 拜托，Oracle...

遵循前两个的唯一实际合理的幻灯片是此：

但是DATE类型更加微妙

所以您认为VARCHAR2很奇怪？

好吧，我们都知道，Oracle的DATE实际上不是SQL标准中，所有其他数据库中或java.sql.Date 。 Oracle的DATE类型实际上是TIMESTAMP(0) ，即时间戳，秒精度为零。

实际上，大多数遗留数据库实际上正是使用DATE来存储没有小数秒的时间戳，例如：

• 1970-01-01 00:00:00
• 2000-02-20 20:00:20
• 1337-01-01 13:37:00

因此，当您使用Oracle DATE操作时，在Java中使用java.sql.Timestamp类型始终是一个安全的选择。

但是，当您通过JDBC绑定此类变量时，事情可能会变得非常错误， 如此处的堆栈溢出问题所示 。 假设您有一个范围谓词，如下所示：

// execute_at is of type DATE and there's an index
PreparedStatement stmt = connection.prepareStatement(
    "SELECT * " + 
    "FROM my_table " +
    "WHERE execute_at > ? AND execute_at < ?");

现在，自然地，我们希望execute_at上的任何索引是用于从my_table过滤出记录的明智选择，并且当我们绑定java.sql.Date时也会发生这种情况

stmt.setDate(1, start);
stmt.setDate(2, end);

执行计划是最佳的：

-----------------------------------------------------
| Id  | Operation                    | Name         |
-----------------------------------------------------
|   0 | SELECT STATEMENT             |              |
|*  1 |  FILTER                      |              |
|   2 |   TABLE ACCESS BY INDEX ROWID| my_table     |
|*  3 |    INDEX RANGE SCAN          | my_index     |
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:1:1 AND ""EXECUTE_AT""<:2)

但是让我们看看如果我们假设execute_at是带有小时/分钟/秒的DATE ，即Oracle DATE ，会发生什么。 我们将绑定java.sql.Timestamp

stmt.setTimestamp(1, start);
stmt.setTimestamp(2, end);

执行计划突然变得非常糟糕：

-----------------------------------------------------
| Id  | Operation                    | Name         |
-----------------------------------------------------
|   0 | SELECT STATEMENT             |              |
|*  1 |  FILTER                      |              |
|   2 |   TABLE ACCESS BY INDEX ROWID| my_table     |
|*  3 |    INDEX FULL SCAN           | my_index     |
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:1:1 AND 
               INTERNAL_FUNCTION(""EXECUTE_AT"")<:2))

这是什么INTERNAL_FUNCTION（）

INTERNAL_FUNCTION()是Oracle以完全不透明的方式将值静默转换为其他值的方法。 实际上，您甚至不能在该伪函数上放置基于函数的索引来帮助数据库再次选择RANGE SCAN 。 以下是不可能的：

CREATE INDEX oracle_why_oh_why
  ON my_table(INTERNAL_FUNCTION(execute_at));

不。 函数真正的作用是，将execute_at列的较execute_at DATE类型扩展为绑定变量的较精确的TIMESTAMP类型。 以防万一。

为什么？ 因为独家范围边界（>和<），机会是在你的分数秒Timestamp可能导致时间戳是stricly比结合的范围的下限更大，这将包括它，当相同的Timestamp没有小数部分（即Oracle DATE ）将被排除在外。

咄。 但是我们不在乎，我们只是在第一Timestamp将Timestamp用作愚蠢的解决方法！ 既然我们知道了这一点，您可能会认为在显式转换上添加基于函数的索引是可行的，但事实并非如此：

CREATE INDEX nope
  ON my_table(CAST(execute_at AS TIMESTAMP));

也许，如果您神奇地发现了隐式使用的TIMESTAMP(n)类型的精确正确的精度，那么它可以工作，但一切都让人感到摇摇欲坠，而且，我不想在同一列上再建一个索引！

解决方案

用户APC提供的解决方案实际上非常简单（而且很糟糕）。 同样，您可以绑定java.sql.Date ，但这会使您丢失所有的小时/分钟/秒信息。 不，您必须将绑定变量显式转换为数据库中的DATE 。 究竟！

PreparedStatement stmt = connection.prepareStatement(
    "SELECT * " + 
    "FROM my_table " +
    "WHERE execute_at > CAST(? AS DATE) " +
    "AND execute_at < CAST(? AS DATE)");

每次将java.sql.Timestamp变量绑定到Oracle DATE值时（至少在谓词中使用时），都必须这样做。

如何实施？

如果直接使用JDBC，那么注定要失败。 当然，您可以运行AWR报告来查找生产中最差的语句并仅修复那些语句，但是有可能您将无法如此轻松地修复您的语句并如此Swift地部署它们，因此您可能希望正确处理提前。 当然，这是生产。 明天，您的DBA报告中突然弹出另一条语句。

如果您使用的是JPA / Hibernate，则只能希望他们做对了，因为否则您将无法修复这些查询。

如果您使用的是jOOQ 3.5或更高版本 ，则可以利用jOOQ的新自定义类型绑定功能 ，该功能可与Oracle一起使用，并为您透明地呈现CAST(? AS DATE) ，仅在那些确实相关。

其他数据库

如果您认为这是Oracle问题，请三思。 实际上，在绑定变量时，Oracle非常宽容且易于使用。 Oracle可以为您的绑定变量推断出很多类型，因此几乎不需要铸造。 对于其他数据库，情况则不同。 阅读我们有关RDBMS绑定变量强制转换的文章，以获取更多信息。

翻译自: https://www.javacodegeeks.com/2014/12/are-you-binding-your-oracle-dates-correctly-i-bet-you-arent.html

oracle 绑定变量