我得到这个错误我不知道是什么错了.
“宣布
*
第1行出错
ORA-01422：精确提取返回超过请求的行数
ORA-06512：在第11行“
这是我的代码
DECLARE
rec_EMPID EMPLOYEE.EMPID%TYPE;
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语句.
原文链接：https://www.f2er.com/oracle/205475.html最有可能的是,你想使用一个游标遍历(潜在的)多行数据(我也假设你打算在两个表之间做一个正确的连接,而不是做一个笛卡尔乘积,所以我假设那里是两个表中的departmentID列)
BEGIN
FOR rec IN (SELECT EMPLOYEE.EMPID,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缓冲区的数据.
