create or replace procedure sp11(eno number)is
ename1 emp.ename%type;
dname1 dept.dname%type;
ejob emp.job%type;
esal emp.sal%type;
emgr emp.mgr%type;
begin
select e1.ename,d1.dname,e1.job,e1.sal,e2.ename into ename1, dname1,ejob, esal ,emgr from emp e1,dept d1,emp e2 where e1.mgr = e2.empno and e1.deptno = d1.deptno;
dbms_output.put_line('姓名:'||ename1||' 员工所属领导名:'||dname1||' 职业:'||ejob||' 工资:'||esal||' 所在的部门名:'||emgr);
exception
when too_mamy_rows then
dbms_output.put_line('返回的行数超过一行!');
Warning: Procedure created with compilation errors
end;
/
Warning: Procedure created with compilation errors
SQL> show errors;
Errors for PROCEDURE SCOTT.SP11:
LINE/COL ERROR
-------- --------------------------------------------
11/10 PLS-00201: 必须声明标识符 'TOO_MAMY_ROWS'
0/0 PL/SQL: Compilation unit analysis terminated
ename1 emp.ename%type;
dname1 dept.dname%type;
ejob emp.job%type;
esal emp.sal%type;
emgr emp.mgr%type;
begin
select e1.ename,d1.dname,e1.job,e1.sal,e2.ename into ename1, dname1,ejob, esal ,emgr from emp e1,dept d1,emp e2 where e1.mgr = e2.empno and e1.deptno = d1.deptno;
dbms_output.put_line('姓名:'||ename1||' 员工所属领导名:'||dname1||' 职业:'||ejob||' 工资:'||esal||' 所在的部门名:'||emgr);
exception
when too_mamy_rows then
dbms_output.put_line('返回的行数超过一行!');
Warning: Procedure created with compilation errors
end;
/
Warning: Procedure created with compilation errors
SQL> show errors;
Errors for PROCEDURE SCOTT.SP11:
LINE/COL ERROR
-------- --------------------------------------------
11/10 PLS-00201: 必须声明标识符 'TOO_MAMY_ROWS'
0/0 PL/SQL: Compilation unit analysis terminated
too_mamy_rows
改为
too_many_rows
create or replace procedure sp11(eno number) is
ename1 emp.ename%type;
dname1 dept.dname%type;
ejob emp.job%type;
esal emp.sal%type;
emgr emp.mgr%type;
begin
select e1.ename, d1.dname, e1.job, e1.sal, e2.ename
into ename1, dname1, ejob, esal, emgr
from emp e1, dept d1, emp e2
where e1.mgr = e2.empno
and e1.deptno = d1.deptno;
dbms_output.put_line('姓名:' || ename1 || ' 员工所属领导名:' || dname1 || ' 职业:' || ejob ||
' 工资:' || esal || ' 所在的部门名:' || emgr);
exception
when too_many_rows then --too_mamy_rows改为too_many_rows
dbms_output.put_line('返回的行数超过一行!');
end;
2 ename1 emp.ename%type;
3 dname1 dept.dname%type;
4 ejob emp.job%type;
5 esal emp.sal%type;
6 emgr emp.mgr%type;
7 begin
8 select e1.ename,d1.dname,e1.job,e1.sal,e2.ename into ename1, dname1,ejob, esal ,emgr from emp e1,dept d1,emp e2 where e1.mgr = e2.empno and e1.deptno = d1.deptno;
9 dbms_output.put_line('姓名:'||ename1||' 员工所属领导名:'||dname1||' 职业:'||ejob||' 工资:'||esal||' 所在的部门名:'||emgr);
10 -- exception
11 --when too_many_rows then
12 -- dbms_output.put_line('返回的行数超过一行!');
13 end;
14 /
Procedure created
SQL> exec sp11(7788);
begin sp11(7788); end;
ORA-01422: 实际返回的行数超出请求的行数
ORA-06512: 在 "SCOTT.SP11", line 8
ORA-06512: 在 line 2
如果让其显示该如何解决