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.
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;
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.