我得到这个错误我不知道是什么错了.
“宣布
*
第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语句.
最有可能的是,你想使用一个游标遍历(潜在的)多行数据(我也假设你打算在两个表之间做一个正确的连接,而不是做一个笛卡尔乘积,所以我假设那里是两个表中的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缓冲区的数据.