Oracle 在 where 中使用变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3265027/
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
Oracle using variable in where like
提问by Travis
I'm trying to run SQL in a PL/SQL procedure. If I were to say:
我正在尝试在 PL/SQL 过程中运行 SQL。如果我要说：
SELECT * FROM myTable WHERE FNAME like 'Joe%' AND dept = 'xyz';
this runs instantly.
这立即运行。
if I try putting 'Joe' in a variable it takes forever to run, about 3 minutes. Here is some syntax I found for using like with variables.
如果我尝试将 'Joe' 放入一个变量中，它需要永远运行，大约 3 分钟。这是我发现的一些语法，用于将 like 与变量一起使用。
PROCEDURE myProcedure(firstName IN VARCHAR,
cEmployees OUT cursor_type)
IS
BEGIN
OPEN cEmployees FOR
SELECT * FROM myTable WHERE FNAME like firstName || '%' AND dept = 'xyz';
END myProcedure;
what am I doing wrong? thanks.
我究竟做错了什么？谢谢。
回答by shahkalpesh
I haven't worked on Oracle for some time.
However, you could avoid this
我有一段时间没有在 Oracle 上工作了。
但是，您可以避免这种情况
SELECT * FROM myTable WHERE FNAME like firstName || '%'
Instead, set the firstNamevariable before the above statement.
e.g. firstName = firstName || '%'(pardon the syntax)
and then SELECT * FROM myTable WHERE FNAME like firstName
相反，firstName在上述语句之前设置变量。
例如firstName = firstName || '%'（请原谅语法）
然后SELECT * FROM myTable WHERE FNAME like firstName
回答by Jeffrey Kemp
(sorry, this was too long for a comment so I'm adding it as an "answer")
（抱歉，评论太长了，所以我将其添加为“答案”）
Travis, I suspect you haven't got it quite right yet. You've changed the query and it "fixed it" (i.e. ran in less than 3 minutes), but you don't know why. Later on you may find that the new procedure will suffer from the same problem again.
特拉维斯，我怀疑你还没有完全正确。您已经更改了查询并“修复了它”（即在不到 3 分钟的时间内运行），但您不知道为什么。稍后您可能会发现新程序会再次遇到同样的问题。
The reason is that when the query is reparsed (e.g. when you make a small change to it, like adding parentheses), Oracle generates a new plan for the query. When it generates the query, it is probably using bind variable peeking to see what you are searching on. When it parses it with the value 'Joe', it creates the best plan for that particular value, and it runs quickly. If, however, the query gets re-parsed later on (which it likely will from time to time as the query gets aged out of the shared pool), a different value might be presented (e.g. 'Tom') - and Oracle will optimize the query for that value, which might very well be quite a different plan. Then, all of a sudden, the query on "Joe" runs much slower.
原因是当重新解析查询时（例如，当您对其进行小的更改时，例如添加括号），Oracle 会为该查询生成一个新计划。当它生成查询时，它可能正在使用绑定变量窥视来查看您正在搜索的内容。当它使用值 'Joe' 解析它时，它会为该特定值创建最佳计划，并且运行速度很快。但是，如果查询稍后被重新解析（随着查询从共享池中老化，它可能会不时地重新解析），可能会显示一个不同的值（例如“Tom”）——并且 Oracle 将优化对该值的查询，这很可能是一个完全不同的计划。然后，突然之间，对“Joe”的查询运行速度要慢得多。
Bottom line: if you don't know why it became faster, you don't know if the improvement is permanent or temporary.
底线：如果你不知道为什么它变得更快，你就不知道改进是永久性的还是暂时的。
Disclaimer: the above is general advice only - if you specify your database version, and provide the explain plan output for the two queries, you may get more specific advice.
免责声明：以上只是一般建议 - 如果您指定数据库版本，并提供两个查询的解释计划输出，您可能会得到更具体的建议。
