PL/SQL ORA-01422:精确获取返回的行数超过了请求的行数 [英] PL/SQL ORA-01422: exact fetch returns more than requested number of rows
问题描述
我不断收到此错误,我不知道出了什么问题.
I get keep getting this error I can't figure out what is wrong.
声明
*
第1行出现错误:
ORA-01422:精确获取返回的行数超过了请求的行数
ORA-06512:在第11行
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 11
这是我的代码.
DECLARE
rec_ENAME EMPLOYEE.ENAME%TYPE;
rec_JOB EMPLOYEE.DESIGNATION%TYPE;
rec_SAL EMPLOYEE.SALARY%TYPE;
rec_DEP DEPARTMENT.DEPT_NAME%TYPE;
BEGIN
SELECT EMPLOYEE.EMPID, EMPLOYEE.ENAME, EMPLOYEE.DESIGNATION, EMPLOYEE.SALARY, DEPARTMENT.DEPT_NAME
INTO rec_EMPID, rec_ENAME, rec_JOB, rec_SAL, rec_DEP
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.SALARY > 3000;
DBMS_OUTPUT.PUT_LINE ('Employee Nnumber: ' || rec_EMPID);
DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------');
DBMS_OUTPUT.PUT_LINE ('Employee Name: ' || rec_ENAME);
DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------');
DBMS_OUTPUT.PUT_LINE ('Employee Designation: ' || rec_JOB);
DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------');
DBMS_OUTPUT.PUT_LINE ('Employee Salary: ' || rec_SAL);
DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------');
DBMS_OUTPUT.PUT_LINE ('Employee Department: ' || rec_DEP);
END;
/
推荐答案
如果SELECT INTO
语句返回的内容不是1行,则将引发错误.如果返回0行,您将得到no_data_found
异常.如果返回的行多于1,则会出现too_many_rows
异常.除非您知道总是有1名员工的薪水高于3000,否则您不希望在这里使用SELECT INTO
语句.
A SELECT INTO
statement will throw an error if it returns anything other than 1 row. If it returns 0 rows, you'll get a no_data_found
exception. If it returns more than 1 row, you'll get a too_many_rows
exception. Unless you know that there will always be exactly 1 employee with a salary greater than 3000, you do not want a SELECT INTO
statement here.
最有可能的是,您想使用游标迭代(潜在地)遍历多行数据(我还假设您打算在两个表之间进行适当的联接,而不是做笛卡尔积,所以我假设两个表中都有一个departmentID
列)
Most likely, you want to use a cursor to iterate over (potentially) multiple rows of data (I'm also assuming that you intended to do a proper join between the two tables rather than doing a Cartesian product so I'm assuming that there is a departmentID
column in both tables)
BEGIN
FOR rec IN (SELECT EMPLOYEE.EMPID,
EMPLOYEE.ENAME,
EMPLOYEE.DESIGNATION,
EMPLOYEE.SALARY,
DEPARTMENT.DEPT_NAME
FROM EMPLOYEE,
DEPARTMENT
WHERE employee.departmentID = department.departmentID
AND EMPLOYEE.SALARY > 3000)
LOOP
DBMS_OUTPUT.PUT_LINE ('Employee Nnumber: ' || rec.EMPID);
DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------');
DBMS_OUTPUT.PUT_LINE ('Employee Name: ' || rec.ENAME);
DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------');
DBMS_OUTPUT.PUT_LINE ('Employee Designation: ' || rec.DESIGNATION);
DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------');
DBMS_OUTPUT.PUT_LINE ('Employee Salary: ' || rec.SALARY);
DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------');
DBMS_OUTPUT.PUT_LINE ('Employee Department: ' || rec.DEPT_NAME);
END LOOP;
END;
我假设您也正在学习PL/SQL.在实际代码中,您永远不会像这样使用dbms_output
,并且不会依赖任何人看到您写入到dbms_output
缓冲区的数据.
I'm assuming that you are just learning PL/SQL as well. In real code, you'd never use dbms_output
like this and would not depend on anyone seeing data that you write to the dbms_output
buffer.
这篇关于PL/SQL ORA-01422:精确获取返回的行数超过了请求的行数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!