我不断收到此错误,我无法弄清楚出了什么问题。
DECLARE
*
第 1 行错误:
ORA-01422: 精确提取返回的行数多于请求的行数
ORA-06512: 在第 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;
/
A SELECT INTO
如果语句返回 1 行以外的任何内容,则会引发错误。如果它返回 0 行,您将得到no_data_found
例外。如果它返回超过 1 行,您将得到一个too_many_rows
例外。除非您知道总会有 1 名员工的薪水大于 3000,否则您不希望SELECT INTO
在此声明。
最有可能的是,您想使用游标来迭代(可能)多行数据(我还假设您打算在两个表之间进行适当的联接,而不是进行笛卡尔积,所以我假设有是一个departmentID
两个表中的列)
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
buffer.
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)