PL/SQL ORA-01422:精确提取返回的行数超过请求的行数

问题描述

我不断收到此错误，我不知道出了什么问题.

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:精确提取返回的行数超过请求的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！