PL/SQL FOR LOOP IMPLICIT CURSOR

database-cursor, for-loop, oracle, plsql

Solution

You don't use INTO with an implicit cursor:

    DECLARE
        emp_dept_id employees.department_id%TYPE;
        emp_emp_id employees.employee_id%TYPE;
        emp_last_name employees.last_name%TYPE;
        v_count number DEFAULT 0;
    BEGIN
        FOR i IN (SELECT DISTINCT department_id, department_name
            FROM departments)
        LOOP
            --v_COUNT := v_COUNT + 1;
            DBMS_OUTPUT.PUT_LINE('HELLO'||i.department_id||' '||i.department_name);
            FOR j IN (SELECT employee_id, last_name
                INTO emp_emp_id, emp_last_name
                FROM employees)
                --WHERE department_id=i.department_id)
            LOOP
                DBMS_OUTPUT.PUT_LINE(emp_emp_id||' '||emp_last_name);
                v_COUNT := v_COUNT + 1;
            END LOOP;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(v_COUNT);
    END;
    /

Problem

There are 2 tables `EMPLOYEES` and `DEPARTMENTS` with `department_id` as primary key for `DEPARTMENTS` and foreign key on `EMPLOYEES`. I want to print all the employee names that belong to a particular department. I know it can be easily achieved by JOINS or `EXPLICIT` cursors. I thought why not try with FOR loop and a `IMPLICIT` cursors. My question is if it is syntactically correct to write `INTO` like this. If so why is not assigning any values? ``` DECLARE emp_dept_id employees.department_id%TYPE; emp_emp_id employees.employee_id%TYPE; emp_last_name employees.last_name%TYPE; dept_dept_id departments.department_id%TYPE; dept_dept_name departments.department_name%TYPE; v_count number DEFAULT 0; BEGIN FOR i IN (SELECT DISTINCT department_id, department_name INTO dept_dept_id, dept_dept_name FROM departments) LOOP --v_COUNT := v_COUNT + 1; DBMS_OUTPUT.PUT_LINE('HELLO'||dept_dept_id||' '||dept_dept_name); FOR j IN (SELECT employee_id, last_name INTO emp_emp_id, emp_last_name FROM employees) --WHERE department_id=dept_dept_id) LOOP DBMS_OUTPUT.PUT_LINE(emp_emp_id||' '||emp_last_name); v_COUNT := v_COUNT + 1; END LOOP; END LOOP; DBMS_OUTPUT.PUT_LINE(v_COUNT); END; ```

Original source