PL/SQL — Avoiding TOO_MANY_ROWS Errors in Batch Jobs

-- Anonymous PL/SQL block — runs immediately, not stored
DECLARE
    -- Variable declarations
    v_employee_name  VARCHAR2(50);   -- String up to 50 chars
    v_salary         NUMBER(10, 2);  -- Number with 2 decimal places
    v_department_id  NUMBER := 10;   -- Initialised to 10

BEGIN
    -- Query into variables using SELECT INTO
    SELECT first_name || ' ' || last_name, salary
    INTO   v_employee_name, v_salary
    FROM   employees
    WHERE  department_id = v_department_id
    AND    ROWNUM = 1;

    -- Output using DBMS_OUTPUT (enable with SET SERVEROUTPUT ON)
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('Salary: $' || TO_CHAR(v_salary, '999,999.99'));

EXCEPTION
    -- Handle the case where no rows are found
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found in department ' || v_department_id);
END;
/  -- The slash executes the block in SQL*Plus and SQLDeveloper

DECLARE
    -- %TYPE anchors variable type to the actual column definition
    v_salary    employees.salary%TYPE;
    v_grade     VARCHAR2(10);
    v_counter   NUMBER;

BEGIN
    SELECT salary INTO v_salary
    FROM employees WHERE employee_id = 100;

    -- IF / ELSIF / ELSE
    IF v_salary > 20000 THEN
        v_grade := 'Executive';
    ELSIF v_salary > 10000 THEN
        v_grade := 'Senior';
    ELSIF v_salary > 5000 THEN
        v_grade := 'Mid-level';
    ELSE
        v_grade := 'Junior';
    END IF;

    DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);

    -- FOR loop (most common) — implicit counter, no DECLARE needed
    FOR v_counter IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_counter);
    END LOOP;

END;
/

DECLARE
    v_employee_id employees.employee_id%TYPE := 9999;
    v_name        employees.first_name%TYPE;
BEGIN
    SELECT first_name INTO v_name
    FROM employees WHERE employee_id = v_employee_id;

    DBMS_OUTPUT.PUT_LINE('Found: ' || v_name);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id || ' not found.');
        -- Optionally log or re-raise
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple employees with same ID — data error.');
        -- Log for investigation
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        RAISE;  -- Re-raise after logging; don't hide it
END;
/

DECLARE
    -- Explicit cursor declaration
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 10;

    v_emp_rec emp_cursor%ROWTYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp_rec;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_rec.employee_id || ': ' || v_emp_rec.first_name);
    END LOOP;
    CLOSE emp_cursor;
END;
/

-- Simpler: Implicit cursor FOR loop (recommended)
BEGIN
    FOR emp_rec IN (SELECT employee_id, first_name FROM employees WHERE department_id = 10) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ': ' || emp_rec.first_name);
    END LOOP;
END;
/

-- Stored procedure: updates salary and returns the new value via OUT parameter
CREATE OR REPLACE PROCEDURE raise_salary(
    p_employee_id IN employees.employee_id%TYPE,
    p_percent     IN NUMBER,
    p_new_salary  OUT employees.salary%TYPE
) IS
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_percent/100)
    WHERE employee_id = p_employee_id
    RETURNING salary INTO p_new_salary;

    COMMIT;
END raise_salary;
/

-- Stored function: returns a single value, callable from SQL
CREATE OR REPLACE FUNCTION get_annual_salary(
    p_employee_id employees.employee_id%TYPE
) RETURN NUMBER
DETERMINISTIC   -- Indicates same input always returns same output
IS
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_salary
    FROM employees WHERE employee_id = p_employee_id;

    RETURN v_salary * 12;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END get_annual_salary;
/

-- Calling the function from SQL
SELECT employee_id, get_annual_salary(employee_id) AS annual_salary
FROM employees
WHERE department_id = 10;