If you expect a single value from multiple rows, use aggregation functions like MAX, MIN, SUM, or AVG.
MAX
MIN
SUM
AVG
BEGIN SELECT MAX(salary) INTO v_max_salary FROM employees WHERE department_id = 10; END;
If you need to handle multiple rows, use a CURSOR to fetch each row individually.
DECLARE CURSOR emp_cursor IS SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10; v_emp_id employees.employee_id%TYPE; v_first_name employees.first_name%TYPE; v_last_name employees.last_name%TYPE; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO v_emp_id, v_first_name, v_last_name; EXIT WHEN emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp_id || ' ' || v_first_name || ' ' || v_last_name); END LOOP; CLOSE emp_cursor; END;
Ensure your query is specific enough to return only one row by adding more conditions to your WHERE clause.
BEGIN SELECT employee_id, first_name, last_name INTO v_emp_id, v_first_name, v_last_name FROM employees WHERE department_id = 10 AND ROWNUM = 1; END;
SELECT INTO
EXCEPTION
Handle the situation where multiple rows are returned using the TOO_MANY_ROWS exception.
TOO_MANY_ROWS
BEGIN SELECT employee_id, first_name, last_name INTO v_emp_id, v_first_name, v_last_name FROM employees WHERE department_id = 10; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Query returned more than one row.'); END;
If you want to fetch the highest salary from a department:
BEGIN SELECT MAX(salary) INTO v_max_salary FROM employees WHERE department_id = 20; END;
If you need to process all employees in a department:
DECLARE CURSOR emp_cursor IS SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 20; v_emp_id employees.employee_id%TYPE; v_first_name employees.first_name%TYPE; v_last_name employees.last_name%TYPE; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO v_emp_id, v_first_name, v_last_name; EXIT WHEN emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp_id || ' ' || v_first_name || ' ' || v_last_name); END LOOP; CLOSE emp_cursor; END;
If you want to ensure only one employee is returned:
BEGIN SELECT employee_id, first_name, last_name INTO v_emp_id, v_first_name, v_last_name FROM employees WHERE department_id = 20 AND ROWNUM = 1; END;
Handling cases where the query might return multiple rows:
BEGIN SELECT employee_id, first_name, last_name INTO v_emp_id, v_first_name, v_last_name FROM employees WHERE department_id = 20; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Query returned more than one row.'); END;