Oracle PL/SQL 中基于参数选择查询

Question

好吧，假设我有一个疑问：

SELECT * FROM TABLE_AWESOME WHERE YEAR = :AMAZINGYEAR;

效果非常好。但是假设我希望能够仅返回这些结果或基于下拉列表的所有结果。 （例如，下拉列表将包含 2008 年、2009 年、所有年份）

我决定使用以下格式使用 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;

不幸的是，这失败了。我收到诸如“此 SELECT 语句中需要 INTO 子句”之类的错误。

我对 PL/SQL 完全陌生，所以我认为我对它的期望太高了。我已经查看了文档，但没有找到任何原因为什么这不能按照我的方式工作。我实际使用的查询比这复杂得多，但我想保持简单，这样我就能很快得到答案。

提前致谢 ：）

Answer 1

吉姆和亚历克斯提出的问题确实存在危险。

假设，其中有 20 年的数据，因此对 YEAR 的查询 = 返回 5% 的块。我说的是块而不是行，因为我假设数据是在该日期添加的，因此聚类因子很高。

如果您想要 1 年，您希望优化器使用年份索引来查找那 5% 的行。

如果您想要所有年份，您希望优化器使用全表扫描来获取每一行。

到目前为止我们还好吗？

一旦将其投入生产，Oracle 第一次加载查询时，它会在绑定变量处达到峰值，并根据该变量制定计划。

所以假设第一个负载是“全部”。

太棒了，该计划是全表扫描 (FTS)，并且该计划已缓存，您可以在 5 分钟内恢复所有行。没什么大不了的。

您说的下一次运行是 1999 年。但是该计划已缓存，因此它使用 FTS 只获取 5% 的行，并且需要 5 分钟。 “嗯......用户说，行数少得多，而且时间相同。”但没关系...这只是一个 5 分钟的报告...生活本来就有点慢，但没有人大喊大叫。

那天晚上，批处理作业将该查询从缓存中删除，早上第一个用户请求 2001。Oracle 检查缓存（不在缓存中），查看变量 2001。啊，最好的计划是索引扫描。并且该计划已被缓存。结果在 10 秒内返回，让用户大吃一惊。下一个人（通常是第一个）做了早上的“ALL”报告，并且查询永远不会返回。

为什么？

因为它通过查看索引来获取每一行......可怕的嵌套循环。 5 分钟的报告现已进行到 30 分，并且仍在计时。

您的原始帖子有最佳答案。两个查询，这样两者总是会得到最好的计划，绑定变量窥视不会杀死你。

您遇到的问题只是一个基本的 Oracle 问题。您从工具运行查询并将结果返回到工具中。如果将 select 语句放入 pl/sql 块中，则必须对其执行某些操作。您必须将其加载到游标、数组或变量中。这与你错而他们正确无关……这只是缺乏 pl/sql 技能。

Answer 2

您可以通过一个查询来完成此操作，例如：

SELECT * FROM TABLE_AWESOME WHERE (? = 'ALL' OR YEAR = ?)

并将参数传递给它两次。

Answer 3

在 PL/SQL 中，您必须SELECT ... INTO某些内容，您需要能够将其返回给客户端；正如 taning 所示，这可能是一个参考光标。这可能会让客户变得复杂。

您可以在 SQL 中使用以下内容来执行此操作：

SELECT * FROM TABLE_AWESOME WHERE :AMAZING_YEAR = 'ALL' OR YEAR = :AMAZINGYEAR;

... 尽管您可能需要注意索引；我会查看两种参数类型的执行计划，以检查它没有执行意外的操作。

Answer 4

不确定是否使用 SqlDataSource，但您肯定可以通过 system.data.oracle 或 oracle 客户端来执行此操作。

您可以通过 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 ;

而您只需创建一个输出参数（类型为 refcursor）——而不是 var sys# refcursors），并且几乎只需修改上面的代码即可。

我在这里回答了有关获取匿名块引用的类似问题
如何从 Oracle 函数返回 RefCursor？

Answer 5

此类参数应在您的代码中进行处理，以便您的 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.
        }
    }
}