oracle PLS-00302：必须声明组件 - 无法解析

PLS-00302: component must be declared- Unable to resolve

提问by Vandee

I tried the below program in SQL developer and facing some issues:

我在 SQL developer 中尝试了以下程序并面临一些问题：

declare
  cursor emp_complex_cur is
    select e.fname,d.dlocation
      from employee e, dept_location d
     where e.dno=d.dnumber;
  emp_max_id integer;
  type emp_complex_rec is record(rname employee.fname%type,rlocation dept_location.dlocation%type);
begin
  open emp_complex_cur;
  for emp_complex_rec in emp_complex_cur loop
    fetch emp_complex_cur into emp_complex_rec;
    dbms_output.put_line('The employee id is: '||emp_complex_rec.rname||' and the employee''s location is '||emp_complex_rec.rlocation);
  close emp_complex_cur;
end;

I am getting the error to declare the variable rnamethough it has been properly declared in the record.

rname尽管已在记录中正确声明了该变量，但我收到了声明该变量的错误。

回答by Bob Jarvis - Reinstate Monica

Try:

尝试：

begin
  for emp_complex_rec in (select e.fname,
                                 d.dlocation
                            from employee e
                            INNER JOIN dept_location d
                              ON (e.dno = d.dnumber))
  loop
    dbms_output.put_line('The employee id is: ' ||
                         emp_complex_rec.rname ||
                         ' and the employee''s location is ' ||
                         emp_complex_rec.rlocation);
  end loop;
end;

The problem with the original code was that the definition of emp_complex_recas a type was colliding with the definition of emp_complex_recas a cursor loop variable. The explicit cursor definition isn't needed either - IMO putting the SELECTin the FORloop is easier and clearer.

原始代码的问题是emp_complex_rec作为类型的定义与emp_complex_rec作为游标循环变量的定义相冲突。不需要显式游标定义或者- IMO把SELECT中FOR环更容易，更清晰。

Share and enjoy.

分享和享受。

回答by Allan

You seem to be confusing a few different techniques.

您似乎混淆了几种不同的技术。

---

---

Option #1 - Explicit cursor fetched manually

选项 #1 - 手动获取显式游标

declare
  cursor emp_complex_cur is
    select e.fname,d.dlocation
      from employee e, dept_location d
     where e.dno=d.dnumber;
  emp_max_id integer;
  type emp_complex_typ is record(rname employee.fname%type,rlocation dept_location.dlocation%type);
  --A variable must be declared of the type you've created
  emp_complex_rec emp_complex_typ;
begin
  open emp_complex_cur;
  --Initial fetch is needed
  fetch emp_complex_cur into r_complex_rec;
  while emp_complex_cur%found loop
    fetch emp_complex_cur into r_complex_rec;
  end loop;
  close emp_complex_cur;
  dbms_output.put_line('The employee id is: '||emp_complex_rec.rname||' and the employee''s location is '||emp_complex_rec.rlocation);
end;

---

---

Option #2 - Explicit cursor fetched using forloop

选项 #2 - 使用for循环获取显式游标

declare
  cursor emp_complex_cur is
    select e.fname,d.dlocation
      from employee e, dept_location d
     where e.dno=d.dnumber;
begin
  --no "open" required
  for emp_complex_rec in emp_complex_cur loop
    --no "fetch" required
    null;
  end loop;
  --no "close" required
  dbms_output.put_line('The employee id is: '||emp_complex_rec.rname||' and the employee''s location is '||emp_complex_rec.rlocation);
end;

---

---

Option #3 - Implicit cursor fetched using forloop

选项 #3 - 使用for循环获取隐式游标

See the answer provided by @BobJarvis

请参阅@BobJarvis 提供的答案

---

---

Option #4 - Explicit cursor fetched only once

选项 #4 - 显式游标仅获取一次

For this kind of scenario where you're only retrieving a single record, I prefer to use an explicit cursor and a single fetch. It doesn't matter if your query is returning 1 row or 100, since you're only acting on one. Why iterate through all of the rows you're going to ignore?

对于这种您只检索单个记录的场景，我更喜欢使用显式游标和单个提取。无论您的查询返回 1 行还是 100 行都没有关系，因为您只对其中之一进行操作。为什么要遍历您要忽略的所有行？

declare
  cursor emp_complex_cur is
    select e.fname,d.dlocation
      from employee e, dept_location d
     where e.dno=d.dnumber;
  emp_complex_rec emp_complex_cur%rowtype;
begin
  open emp_complex_cur;
  fetch emp_complex_cur into r_complex_rec;
  close emp_complex_cur;
  dbms_output.put_line('The employee id is: '||emp_complex_rec.fname||' and the employee''s location is '||emp_complex_rec.dlocation);
end;

回答by Elias Medeiros

The column's names in your cursor are different from what you're calling in output line, and I guess that for loop you're using is messing your code. Actually, it could be more simple:

光标中列的名称与您在输出行中调用的名称不同，我猜您使用的 for 循环正在弄乱您的代码。其实，还可以更简单：

declare
  cursor emp_complex_cur is
    select e.fname,d.dlocation
      from employee e, dept_location d
     where e.dno=d.dnumber;
  emp_max_id integer;
begin
  for emp_complex_rec in emp_complex_cur loop
    dbms_output.put_line('The employee id is: '||emp_complex_rec.fname||' and the employee''s location is '||emp_complex_rec.dlocation);
  end loop;
end;