oracle 必须声明标识符吗？PL/SQL 错误

Identifier must be declared? PL/SQL error

提问by LoveMeow

This is the program I wrote:

这是我写的程序：

set serveroutput on;
declare
  b empl.name1%type;
  r varchar;   --can i change this to r empl.designation%type; ?
begin
  r:=&designation;                      --getting input for the designation
  dbms_output.put_line('hello');            --random output to check for errors
  select name1 into b from empl where designation=r;   --i want all the names from the table
  dbms_output.put_line('name'||b);                   --employee where designation is as entered
  dbms_output.put_line(' closed');                 --by user,should i loop this statement?
end;

When I enter designation as 'a' (which is entered in the table already) I get an error identifier 'a' is not declared. What does that mean? Does the select statement take one row at a time? So if I loop it will I get all the rows? Or should i use a cursor? Why does SQL Developer not accept %rowtype?

当我输入名称为“a”（已在表中输入）时，出现错误 identifier 'a' is not declared。这意味着什么？select 语句是否一次占用一行？所以如果我循环它会得到所有的行吗？还是我应该使用游标？为什么 SQL Developer 不接受%rowtype？

I changed my program to this:

我把我的程序改成这样：

set serveroutput on;
declare
  cursor cempl is select name1,designation from empl;
  b empl.name1%type;
  des empl.designation%type;
  r empl.designation%type;
begin
  r:='meow';
  dbms_output.put_line('hello');
  open cempl;
  if cempl%ISOPEN then
    loop
      fetch cempl into b,des;
      if des=r then
        dbms_output.put_line('name'||b);
      end if;
      exit when cempl%notfound;
    end loop;
    close cempl;
    dbms_output.put_line(' closed');
  end if;
end;

Whenever I get an input like r:=&rand imagine I enter 'a' it says identifier 'a' must be declared, but its a value in the table! Why should it be declared, but if its given in the program like above it doesn't give an error. Instead it repeats the last row twice!

每当我收到类似的输入r:=&r并想象我输入“a”时，它表示必须声明标识符“a”，但它是表中的一个值！为什么要声明它，但如果它在上面的程序中给出，它不会给出错误。相反，它重复最后一行两次！

回答by Luke Woodward

There are a few questions to answer here:

这里有几个问题需要回答：

• 'Identifier not found' errors: &designationis a SQL*Plus substitution variable. When you enter a value for &designation, SQL*Plus replaces &designationwith the text of what you entered. So, if you enter the vaue a, the line

  r:=&designation;
  

  becomes

  r:=a;
  

  The error arises because Oracle doesn't know of anything called a. You haven't declared a variable called a, and there isn't a procedure or function or anything else it could find with the name a. If you want the end result to be r:='a';, you would need to write r:='&designation';

• SELECT ... INTO ...only works if the query returns exactly one row. If no rows are returned, you will get a no data founderror, and if more than one row is returned, you will get a too many rowserror. You should only use SELECT ... INTO ...if you know there will only be one result. If there may be more than one result, you should use a cursor.

• 'Why does SQL Developer not accept %rowtype'? It should do - could you come up with an example that causes a problem for you?

• In your second example, you're getting the last row repeated because you're not exiting the loop immediately after the cursor fails to find any more rows. You should move the exit whenline to immediately below the fetchline.

• “未找到标识符”错误： &designation是 SQL*Plus 替换变量。当您为 输入值时&designation，SQL*Plus 将替换&designation为您输入的文本。因此，如果您输入 vaue a，则该行

  r:=&designation;
  

  变成

  r:=a;
  

  出现错误是因为 Oracle 不知道任何名为a. 您尚未声明名为 的变量a，并且没有过程或函数或任何其他可以找到名称为 的变量a。如果您希望最终结果是r:='a';，则需要编写r:='&designation';

• SELECT ... INTO ...仅当查询仅返回一行时才有效。如果没有返回任何行，则会no data found报错，如果返回多行，则会too many rows报错。仅SELECT ... INTO ...当您知道只有一个结果时才应使用。如果可能有多个结果，则应使用游标。

• '为什么 SQL Developer 不接受%rowtype'？它应该这样做 - 你能想出一个给你带来问题的例子吗？

• 在您的第二个示例中，您将重复最后一行，因为您没有在游标找不到更多行后立即退出循环。您应该将该exit when线移动到该线的正下方fetch。

回答by A.B.Cade

Looked into you first script:

看着你的第一个脚本：

set serveroutput on;
declare
  b empl.name1%type;
  r empl.designation%type;   -- made a change here 
begin
  r:='&designation';                      --made a change here
  dbms_output.put_line('hello');            
  select name1 into b from empl where designation=r;   
  dbms_output.put_line('name'||b);                  
  dbms_output.put_line(' closed');                 
end;

1. if you declare r as varchar2 then you need to set the length, ie- varchar2(32)
2. When you assign a value to r it needs to include the ', so you can either put it in the script (as in the code above or you can assign to designationa value containiing the 'such as 'a'

1. 如果你将 r 声明为 varchar2 那么你需要设置长度，即- varchar2(32)
2. 当您为 r 分配值时，它需要包含'，因此您可以将其放入脚本（如上面的代码中，或者您可以分配给designation包含'诸如'a'

I tried the code above from sql*plus and it works

我从 sql*plus 尝试了上面的代码并且它有效

---

---

What happens is that after you put afor &designation(without the ') your code looks like this:

发生的情况是，在您a输入&designation（没有'）之后，您的代码如下所示：

r:=a;

since there is no identifier ait fires the exception

因为没有标识符a它会触发异常