Oracle PL/SQL 中基于参数选择查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4197771/
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
Picking query based on parameter in Oracle PL/SQL
提问by clifgriffin
Ok, say I have a query:
好的，假设我有一个查询：
SELECT * FROM TABLE_AWESOME WHERE YEAR = :AMAZINGYEAR;
Which works very nicely. But say I want to be able to return either just those results or all results based on a drop down. (e.g., the drop down would have 2008, 2009, ALL YEARS)
这很好用。但是说我希望能够仅返回这些结果或基于下拉列表的所有结果。（例如，下拉列表将包含 2008、2009、ALL YEARS）
I decided to tackle said problem with PL/SQL with the following format:
我决定用以下格式用 PL/SQL 解决上述问题：
DECLARE
the_year VARCHAR(20) := &AMAZINGYEAR;
BEGIN
IF the_year = 'ALL' THEN
SELECT * FROM TABLE_AWESOME;
ELSE
SELECT * FROM TABLE_AWESOME WHERE YEAR = the_year;
END IF;
END;
Unfortunately, this fails. I get errors like "an INTO clause is expected in this SELECT statement".
不幸的是，这失败了。我收到诸如“此 SELECT 语句中需要 INTO 子句”之类的错误。
I'm completely new to PL/SQL so I think I'm just expecting too much of it. I have looked over the documentation but haven't found any reason why this wouldn't work the way I have it. The query I'm actually using is much much more complicated than this but I want to keep this simple so I'll get answer quickly.
我对 PL/SQL 完全陌生，所以我想我只是期望太多。我查看了文档，但没有找到任何原因，为什么这不会像我拥有的那样工作。我实际使用的查询比这复杂得多，但我想保持这个简单，这样我会很快得到答案。
Thanks in advance :)
提前致谢 ：）
回答by Stephanie Page
There is a real danger in the queries offered by Jim and Alex.
吉姆和亚历克斯提出的问题确实存在危险。
Assumption, you have 20 years of data in there, so a query on YEAR = return 5% of the blocks. I say blocks and not rows because I assume the data is being added on that date so the clustering factor is high.
假设您有 20 年的数据，因此对 YEAR 的查询 = 返回 5% 的块。我说块而不是行，因为我假设数据是在那个日期添加的，所以聚类因子很高。
If you want 1 year, you want the optimizer to use an index on year to find those 5% of rows.
如果您想要 1 年，您希望优化器使用年的索引来查找那 5% 的行。
If you want all years, you want the optimizer to use a full table scan to get every row.
如果您想要所有年份，您希望优化器使用全表扫描来获取每一行。
Are we good so far?
到目前为止我们还好吗？
Once you put this into production, the first time Oracle loads the query it peaks at the bind variable and formulates a plan based on that.
一旦将其投入生产，Oracle 第一次加载查询时，它会在绑定变量处达到峰值，并基于此制定计划。
SO let's say the first load is 'All'.
所以假设第一个负载是“全部”。
Great, the plan is a Full table scan (FTS) and that plan is cached and you get all the rows back in 5 minutes. No big deal.
太好了，该计划是全表扫描 (FTS)，并且该计划已缓存，您可以在 5 分钟内恢复所有行。没什么大不了。
The next run you say 1999. But the plan is cached and so it uses a FTS to get just 5% of the rows and it takes 5 minutes. "Hmmm... the user says, that was many fewer rows and the same time." But that's fine... it's just a 5 minute report... life is a little slow when it doesn't have to be but no one is yelling.
下一次运行您说 1999 年。但该计划已缓存，因此它使用 FTS 仅获取 5% 的行，并且需要 5 分钟。“嗯……用户说，行数和时间都少了很多。” 但这很好……这只是一个 5 分钟的报告……当它不必如此但没有人大喊大叫时，生活就会有点慢。
That night the batch jobs blow that query out of the cache and in the morning the first user asks for 2001. Oracle checks the cache, not there, peeks at the variable, 2001. Ah, the best plan for that is an index scan. and THAT plan is cached. The results come back in 10 seconds and blows the user away. The next person, who is normally first, does the morning "ALL" report and the query never returns.
那天晚上，批处理作业从缓存中删除该查询，早上第一个用户要求 2001。Oracle 检查缓存，不在那里，查看变量 2001。啊，最好的计划是索引扫描。并且该计划已缓存。结果在 10 秒内返回，让用户大吃一惊。下一个人，通常是第一个，做早上的“所有”报告，查询永远不会返回。
WHY?
为什么？
Because it's getting every single row by looking through the index.... horrible nested loops. The 5 minute report is now at 30 and counting.
因为它通过查看索引来获取每一行......可怕的嵌套循环。5 分钟的报告现在是 30 和计数。
Your original post has the best answer. Two queries, that way both will ALWAYS get the best plan, bind variable peeking won't kill you.
你原来的帖子有最好的答案。两个查询，这样两者都将始终获得最佳计划，绑定变量偷看不会杀死您。
The problem you're having is just a fundamental Oracle issue. You run a query from a tool and get the results back INTO the tool. If you put a select statement into a pl/sql block you have to do something with it. You have to load it into a cursor, or array, or variable. It's nothing to do with you being wrong and them being right... it's just a lack of pl/sql skills.
您遇到的问题只是一个基本的 Oracle 问题。您从工具运行查询并将结果返回到工具中。如果将 select 语句放入 pl/sql 块中，则必须对其进行处理。您必须将其加载到游标、数组或变量中。这与你错而他们是对无关……只是缺乏 pl/sql 技能。
回答by Jim Davis
You could do it with one query, something like:
您可以通过一个查询来完成，例如：
SELECT * FROM TABLE_AWESOME WHERE (? = 'ALL' OR YEAR = ?)
and pass it the argument twice.
并将参数传递给它两次。
回答by Alex Poole
In PL/SQL you have to SELECT ... INTOsomething, which you need to be able to return to the client; that could be a ref cursor as tanging demonstrates. This can complicate the client.
在 PL/SQL 中，你必须做SELECT ... INTO一些事情，你需要能够返回给客户端；正如 tanging 所展示的那样，这可能是一个引用光标。这会使客户端复杂化。
You can do this in SQL instead with something like:
您可以在 SQL 中执行此操作，而不是使用以下内容：
SELECT * FROM TABLE_AWESOME WHERE :AMAZING_YEAR = 'ALL' OR YEAR = :AMAZINGYEAR;
... although you may need to take care about indexes; I'd look at the execution plan with both argument types to check it isn't doing something unexpected.
...虽然您可能需要注意索引；我会查看带有两种参数类型的执行计划，以检查它没有做一些意外的事情。
回答by Harrison
Not sure about using a SqlDataSource, but you can definately do this via the system.data.oracle or the oracle clients.
不确定是否使用 SqlDataSource，但您肯定可以通过 system.data.oracle 或 oracle 客户端执行此操作。
You would do this via an anonymous block in asp.net
您可以通过 asp.net 中的匿名块执行此操作
VAR SYS1 REFCURSOR;
VAR SYS2 REFCURSOR;
DECLARE
FUNCTION CURSORCHOICE(ITEM IN VARCHAR2) RETURN SYS_REFCURSOR IS
L_REFCUR SYS_REFCURSOR;
returnNum VARCHAR2(50);
BEGIN
IF upper(item) = 'ALL' THEN
OPEN L_REFCUR FOR
SELECT level FROM DUAL
CONNECT BY LEVEL < 15 ;
ELSE
OPEN L_REFCUR FOR
SELECT 'NONE' FROM DUAL ;
END IF;
RETURN L_REFCUR;
END ;
BEGIN
:SYS1 := CURSORCHOICE('ALL');
:SYS2 := CURSORCHOICE('NOT ALL');
end ;
/
PRINT :SYS1 ;
PRINT :SYS2 ;
whereas you would simply create an output param (of type refcursor) -- instead of the var sys# refcursors) and pretty much just amend the above code.
而您只需创建一个输出参数（类型为 refcursor）——而不是 var sys# refcursors）并且几乎只是修改上面的代码。
I answered a similar question about getting an anonymous block refcuror here How to return a RefCursor from Oracle function?
我在这里回答了一个关于获取匿名块 refcuror 的类似问题 如何从 Oracle 函数返回 RefCursor？
回答by Will Marcouiller
This kind of parameter shall be processed from within your code so that your OracleCommandobject only executes either queries.
此类参数应在您的代码中进行处理，以便您的OracleCommand对象仅执行任一查询。
using (var connection = new OracleConnection(connString)) {
connection.Open();
string sql = "select * from table_awesome";
sql = string.Concat(sql, theYear.Equals(@"ALL") ? string.Empty : " where year = :pYear")
using (var command = connection.CreateCommand()) {
command.CommancText = sql;
command.CommandType = CommandType.Text;
var parameter = command.CreateParameter();
parameter.Name = @":yearParam";
parameter.Direction = ParameterDirection.Input;
parameter.Value = theYear;
var reader = command.ExecuteQuery();
if (!reader.HasRows) return;
while (reader.Read()) {
// Extract your data from the OracleDataReader instance here.
}
}
}
