oracle pl/sql 必须声明 PLS-00201 标识符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21762321/
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
pl/sql PLS-00201 identifier must be declared
提问by earachefl
Newbie to PL/SQL. I have several questions, so here's an example of what I'm trying to do.
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;
So obviously I'm trying to return a query result, optionally filtered by whatever parameters I pass in. I'm at a loss as to why the offending line returns an error - in an earlier iteration, I was able to return results, but now, mysteriously, it's stopped working.
所以很明显我正在尝试返回一个查询结果，可以选择通过我传入的任何参数进行过滤。我不知道为什么违规行返回错误 - 在早期的迭代中，我能够返回结果，但是现在，神秘地，它停止工作了。
1) Is there a better way to approach this?
1）有没有更好的方法来解决这个问题？
2) Do I have to reference the input params with the '&my_inparam' syntax?
2) 我是否必须使用 '&my_inparam' 语法引用输入参数？
3) If I do approach this by creating the sql text first and then opening the ref cursor, is there a shortcut for concatening the strings, like
3）如果我通过首先创建sql文本然后打开引用光标来解决这个问题，是否有连接字符串的快捷方式，比如
sql_text &= ' and another_condition = 1'
?
?
回答by Alex Poole
In reverse order... no, there is no shorthand for concatenation like &=. You could use the concat()function instead, but the ||method is more common, and more convenient especially if you're sticking more than two things together - nested concat()calls aren't as easy to follow. I'd stick with what you're doing.
以相反的顺序......不，像&=. 您可以改用该concat()函数，但该||方法更常见，也更方便，尤其是当您将两个以上的东西粘在一起时 - 嵌套concat()调用并不容易遵循。我会坚持你在做什么。
Secondly, no, you're confusing SQL*Plus substitution variables with PL/SQL variables. Your references to '&my_inparam1'should be my_inparam1, etc; no ampersand and no quotes.
其次，不，您将 SQL*Plus 替换变量与 PL/SQL 变量混淆了。您对'&my_inparam1'should 的引用是my_inparam1等；没有＆符号和引号。
Except for some reason you've decided to make life difficult for yourself and use case-sentisive procedure and variable names, so you have to refer to "my_inparam1", in double quotes, everywhere.
除非出于某种原因，您决定让自己过得很艰难并使用区分大小写的过程和变量名称，因此您必须"my_inparam1"在任何地方用双引号引用。
That's why you're getting the message PLS-00201 Identifier 'MY_OUTPUT' must be declared. You didn't quote my_outputso by default it's looking for a case-insensitive variable called MY_OUTPUT, which does not exist. It would work if you did this instead:
这就是您收到消息的原因PLS-00201 Identifier 'MY_OUTPUT' must be declared。您没有引用，my_output因此默认情况下它正在寻找一个MY_OUTPUT不存在的名为 的不区分大小写的变量。如果你这样做，它会起作用：
OPEN "my_output" FOR sql_text;
Unless you have a really really good reason, really don't do that.
除非你有非常好的理由，否则真的不要那样做。
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;
For more information, refer to the 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.
每个数据库对象都有一个名称。在 SQL 语句中，您用带引号的标识符或不带引号的标识符表示对象的名称。
带引号的标识符以双引号 (") 开头和结尾。如果使用带引号的标识符命名架构对象，则在引用该对象时必须使用双引号。
未加引号的标识符没有被任何标点符号包围。
And more importantly:
更重要的是：
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.
注意：
Oracle 不建议对数据库对象名称使用带引号的标识符。SQL*Plus 接受这些带引号的标识符，但在使用其他管理数据库对象的工具时，它们可能无效。
You quoted procedure name falls into this category; so do the quoted variable names. They're all identifiers and the same advice applies.
您引用的程序名称属于此类；引用的变量名也是如此。它们都是标识符，适用相同的建议。
