在 Oracle 中正确使用带日期的绑定变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9020405/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Correct use of bind variables with dates in Oracle?
提问by TrojanName
I'm puzzled about the correct use of bind variables with datesin Oracle. This isn't within the database or when using PL/SQL, but rather when interacting with Oracle across an OCI interface, where the date needs to be passed in as a string using the to_datefunction.
我对在 Oracle 中正确使用带日期的绑定变量感到困惑。这不在数据库内或在使用 PL/SQL 时，而是在通过 OCI 接口与 Oracle 交互时，其中需要使用to_date函数将日期作为字符串传入。
I would have thought the right approach to ensure the proper use of bind variables is to do the following:
我原以为确保正确使用绑定变量的正确方法是执行以下操作：
to_date(:my_date, :my_date_format)
However, I've seen approaches where the date format isn't done using binds, so I'm a little confused.
但是，我见过不使用绑定完成日期格式的方法，所以我有点困惑。
Can anyone confirm this or suggest the best approach?
任何人都可以确认这一点或建议最好的方法吗？
采纳答案by Justin Cave
Is the date format a constant? Or does it change at runtime?
日期格式是常数吗？还是在运行时改变？
Normally, you know what format the string is (at least expected) to be in so the date format would be a constant. If something is a constant, it is not necessary to make it a bind variable, it can just be hard-coded as part of the statement. In this case, it wouldn't matter either way but there are cases where you'd rather the value be hard-coded in the SQL statement because you want to give the optimizer more information (think of a column with highly skewed data where you're always looking for a particular hard-coded value).
通常，您知道字符串的格式（至少是预期的），因此日期格式将是一个常量。如果某事物是常量，则没有必要将其设置为绑定变量，只需将其硬编码为语句的一部分即可。在这种情况下，无论哪种方式都无关紧要，但在某些情况下，您希望将值硬编码到 SQL 语句中，因为您想为优化器提供更多信息（想想您在其中放置的数据高度偏斜的列） '一直在寻找特定的硬编码值）。
On the other hand, if the date format changes at runtime because someone is passing both the string representation of the date and the format the string is in to your procedure, it would make sense for the date format to be a bind variable.
另一方面，如果日期格式在运行时发生更改，因为有人将日期的字符串表示形式和字符串的格式都传递给您的过程，那么将日期格式作为绑定变量是有意义的。
回答by Ben
The answer to your question is it depends...
你的问题的答案是这取决于...
If you're dynamically creating your date_format then you ought to use a bind variable to make yourself SQL-injection safe. If you're not dynamically creating the date-format then it's already hard-coded and there's very little point.
如果您正在动态创建 date_format，那么您应该使用绑定变量来确保 SQL 注入安全。如果您不是动态创建日期格式，那么它已经是硬编码的并且没有什么意义。
select to_date(:my_date,'yyyymmdd') from dual
is safe anyway but:
无论如何都是安全的，但是：
select to_date(:my_date,:my_date_format) from dual
should really be a bind.
真的应该是绑定。
This is all assuming that :my_date is not a column, in which case it cannot be a bind variable at all.
这一切都假设 :my_date 不是列，在这种情况下，它根本不能是绑定变量。
If you're binding :my_datethough you're passing a static date to Oracle and not using a column then can't OCI work this out for you without going to Oracle ( I don't know for sure, never used it ).
如果您在绑定时:my_date将静态日期传递给 Oracle 而未使用列，则 OCI 无法在不使用 Oracle 的情况下为您解决此问题（我不确定，从未使用过它）。
