oracle - 必须声明 pl/sql PLS-00201 标识符

Question

PL/SQL 新手。我有几个问题，下面是我正在尝试做的一个例子。

CREATE OR REPLACE PROCEDURE "my_procedure" (
   "my_inparam1"              IN     VARCHAR2,
   "my_inparam2"              IN     VARCHAR2,
   "my_output"                OUT SYS_REFCURSOR)
AS
   sql_text   VARCHAR2 (10000);
BEGIN
    sql_text :=
      'select something
       from my_table
       where 1 = 1';

   IF '&my_inparam1' <> 'foo'
   THEN
      sql_text := sql_text || ' and something = 0';
   END IF;

   IF '&my_inparam1' = 'foo' and '&my_inparam2' = 'bar'
   THEN
      sql_text := sql_text || ' and somethingelse = 1';
   ELSIF '&my_inparam1' = 'foo' AND '&my_inparam2' = 'baz'
   THEN
      sql_text := sql_text || ' and somethingelse = 0';
   END IF;

   OPEN my_output FOR sql_text; --ERROR PLS-00201 Identifier 'MY_OUTPUT' must be declared
END;

很明显，我正在尝试返回一个查询结果，可以根据我传入的任何参数进行筛选。我不知道为什么有问题的行会返回错误 - 在较早的迭代中，我能够返回结果，但现在，神秘地，它停止工作了。

1) 有没有更好的方法来解决这个问题？

2) 我是否必须使用“&my_inparam”语法引用输入参数？

3) 如果我确实通过先创建 sql 文本然后打开 ref 游标来解决这个问题，是否有连接字符串的快捷方式，如

sql_text &= ' and another_condition = 1'

?

Answer 1

倒序...不，没有像&=这样的连接简写。您可以改用 concat() 函数，但是 || 方法更常见，也更方便，尤其是当您将两个以上的东西粘在一起时 - 嵌套 concat() 调用并不那么容易理解。我会坚持你正在做的事情。

其次，不，您将 SQL*Plus 替换变量与 PL/SQL 变量混淆了。您对 '&my_inparam1' 的引用应该是 my_inparam1 等；没有符号和引号。

除非出于某种原因你决定让自己过得不好并使用区分大小写的过程和变量名称，所以你必须引用 "my_inparam1"，用双引号引起来，无处不在。

这就是您收到消息 PLS-00201 Identifier 'MY_OUTPUT' must be declared 的原因。您没有引用 my_output，因此默认情况下它正在寻找一个名为 MY_OUTPUT 的不区分大小写的变量，该变量不存在。如果您改为这样做，它会起作用:

OPEN "my_output" FOR sql_text;

除非你有一个非常非常好的理由，否则真的不要那样做。

CREATE OR REPLACE PROCEDURE my_procedure (
   my_inparam1              IN     VARCHAR2,
   my_inparam2              IN     VARCHAR2,
   my_output                OUT SYS_REFCURSOR)
AS
   sql_text   VARCHAR2 (10000);
BEGIN
    sql_text :=
      'select something
       from my_table
       where 1 = 1';

   IF my_inparam1 <> 'foo'
   THEN
      sql_text := sql_text || ' and something = 0';
   END IF;
...
   OPEN my_output FOR sql_text;
END;

有关详细信息，请参阅 naming rules :

Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.

A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.

A nonquoted identifier is not surrounded by any punctuation.

更重要的是:

Note:
Oracle does not recommend using quoted identifiers for database object names. These quoted identifiers are accepted by SQL*Plus, but they may not be valid when using other tools that manage database objects.

您引用的过程名称属于此类；引用的变量名也是如此。它们都是标识符，适用相同的建议。