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

Question

我在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;

我在声明变量 rname时出错，尽管已在记录中正确声明了它。

Answer 1

尝试:

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;

原始代码的问题在于，作为类型的 emp_complex_rec的定义与作为游标循环变量的 emp_complex_rec的定义相冲突。也不需要显式的游标定义-IMO将 SELECT放入 FOR循环更加简单明了。

分享并享受。