oracle - 在oracle中创建存储过程时，看似执行，但实际上什么也没做。存储过程永远不会被保存。为什么？

Question

我正在尝试使用 TOAD 在 Oracle SQL 中创建存储过程，但它没有执行任何操作。尝试创建它时，没有错误，没有消息，什么也没有。看起来好像已经过去了，但实际上并没有。

查询如下所示:

CREATE OR REPLACE PROCEDURE PottyUseRange (formatty varchar2, start varchar2, end varchar2)
AS
    BEGIN
        SELECT TO_CHAR(TIME_RANGE, formatty) as CURRENT_DATE,
            SUM(CASE WHEN PORTA_POTTY = 'LM' THEN 1 ELSE 0 END) as LM_SEARCH,
            SUM(CASE WHEN PORTA_POTTY = 'AO' THEN 1 ELSE 0 END) as AO_SEARCH,
            SUM(CASE WHEN PORTA_POTTY = 'RO' THEN 1 ELSE 0 END) as RO_SEARCH,
            SUM(CASE WHEN PORTA_POTTY = 'FL' THEN 1 ELSE 0 END) as FL_SEARCH,
            SUM(CASE WHEN PORTA_POTTY IN ('LM', 'AO', 'RO', 'FL') THEN 1 ELSE 0 END) as TOTAL           
        FROM CORE.DATE_TEST
            WHERE to_char(TIME_RANGE, formatty) >= to_char(start, formatty)
            AND to_char(TIME_RANGE, formatty) <= to_char(end, formatty)
        GROUP BY  TO_CHAR(TIME_RANGE, formatty)
        ORDER BY TO_CHAR(TIME_RANGE, formatty)  ASC;

        EXCEPTION WHEN OTHERS THEN
            raise_application_error(-20001,'Proc failed - '||SQLCODE||' -ERROR- '||SQLERRM);
    END;

当我尝试调用它时(我知道这是一个空查询，但它会让我知道它是否存在):

BEGIN
    POTTYUSERANGE();
END;

我收到此错误:PLS-00201:必须声明标识符“POTTYUSERANGE”

当我使用 CALL POTTYUSERANGE(); 调用它时，出现此错误:ORA-06576:不是有效的函数或过程名称

如何正确地为此查询创建存储过程？

更新

我需要将其插入到BULK COLLECTION中并将其返回给用户。它必须受到限制，以免导致过多的内存消耗。这些是相当大的结果集，它们看起来像这样:

+--------------+----+----+----+----+-------+
| CURRENT_DATE | LM | AO | RO | FL | TOTAL |
+--------------+----+----+----+----+-------+
|  1/2/2012    | 01 | 02 | 03 | 04 |  10   |
+--------------+----+----+----+----+-------+
|  1/4/2013    | 02 | 03 | 04 | 05 |  14   |
+--------------+----+----+----+----+-------+

我需要 View 吗？存储过程？我需要什么？

Answer 1

您已将过程声明为:

PottyUseRange (formatty varchar2, start varchar2, end varchar2)

但是你调用它时没有参数:

BEGIN
    POTTYUSERANGE();
END;

没有与您调用的电话相匹配的程序。您需要传递适当数量的参数，这里可以是文字值，因为它们都是 IN 参数，例如:

BEGIN
    POTTYUSERANGE('X', 'Y', 'Z');
END;

当然，虽然有更有意义的值(value)。您还可以传递局部变量而不是常量文字。

但是您说您收到PLS-00201:必须通过一次调用声明标识符“POTTYUSERANGE”，以及ORA-06576:不是有效的函数或过程名称 > 与另一个，这意味着您要么根本没有真正创建它(您输入了代码但没有执行它)，或者您正在两个单独的模式中工作。您尚未在 create 调用中显示架构前缀，因此出于隐私原因您可能会删除该前缀，或者您正在创建和调用单独的 session 。如果您当前的用户不拥有该过程并且没有同义词，您需要在其前面添加所有者前缀 - 从表所有者处猜测:

BEGIN
    CORE.POTTYUSERANGE('X', 'Y', 'Z');
END;

<小时/>

正如 Lalit 所指出的，无论如何，您都会遇到编译错误，因此调用会给出 PLS-00905: object SCHEMA.POTTYUSERANGE is invalid。您可以使用 show errors 或通过查询 user_errors View (或 all_errors 如果您在另一个架构中创建对象，这似乎就是这种情况)，这会告诉你:

PLS-00103: Encountered the symbol "START" when expecting one of the following:

         <an identifier> <a double-quoted delimited-identifier>
         current delete exists prior

开始和结束是保留字。您可以使用更合适的名称(从什么开始？)或通用前缀，例如 p_:

CREATE OR REPLACE PROCEDURE PottyUseRange (p_formatty varchar2, p_start varchar2, p_end varchar2)
AS
    BEGIN
        SELECT TO_CHAR(TIME_RANGE, formatty) as CURRENT_DATE,
            SUM(CASE WHEN PORTA_POTTY = 'LM' THEN 1 ELSE 0 END) as LM_SEARCH,
            SUM(CASE WHEN PORTA_POTTY = 'AO' THEN 1 ELSE 0 END) as AO_SEARCH,
            SUM(CASE WHEN PORTA_POTTY = 'RO' THEN 1 ELSE 0 END) as RO_SEARCH,
            SUM(CASE WHEN PORTA_POTTY = 'FL' THEN 1 ELSE 0 END) as FL_SEARCH,
            SUM(CASE WHEN PORTA_POTTY IN ('LM', 'AO', 'RO', 'FL') THEN 1 ELSE 0 END) as TOTAL           
        FROM CORE.DATE_TEST
            WHERE to_char(TIME_RANGE, p_formatty) >= to_char(p_start, formatty)
            AND to_char(TIME_RANGE, p_formatty) <= to_char(p_end, formatty)
        GROUP BY  TO_CHAR(TIME_RANGE, p_formatty)
        ORDER BY TO_CHAR(TIME_RANGE, p_formatty)  ASC;

        EXCEPTION WHEN OTHERS THEN
            raise_application_error(-20001,'Proc failed - '||SQLCODE||' -ERROR- '||SQLERRM);
    END;
/

但您还需要select into当您使用 PL/SQL 时，例如如果您要在本地使用它们，请声明诸如 l_timerange 等局部变量。但您似乎期望多个值，因此您需要 bulk select into a collection 。但目前尚不清楚这应该实现什么目标。如果您想将这些值传递回调用方，使用引用游标返回结果集可能会更简单；但随后并不清楚您是否真的需要一个过程/函数，或者只是一个简单的 SQL 查询，或者可能是一个 View ...

作为进一步的问题，您将日期作为字符串进行比较，并按这些字符串进行分组/排序:

to_char(TIME_RANGE, formatty) >= to_char(start, formatty)

比较仅适用于某些格式，并且当您传递变量格式时，就会出现问题；即使有效，也可能效率不高。排序也仅适用于某些格式 - 如果排序有意义的话(同样，取决于您对结果执行的操作!)。使用传入的格式将传递的开始/结束字符串转换为日期，然后比较它们:

TIME_RANGE >= to_date(p_start, formatty)

...或者如果可能的话，将日期而不是字符串传递到过程中。

捕获这样的异常也是危险的。您假设调用该过程的任何人都将启用服务器输出并将对错误执行某些操作。除非您能够明智地处理异常，否则您不应该捕获它，当然也不应该像这样压制它。