PL/SQL FOR LOOP IMPLICIT CURSOR

Question

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;

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

Comments

[Yinda Yin]

Yes, that looks better. And he has to do his INTO departments before running this code, correct?

[David Aldridge]

No INTO at all -- you reference the values returned by the cursor as cursor_name.column_name

[qasim.hasnain]

my question still remain if it is not correct to use INTO then why is it not giving any error....and i am sorry the cursor is explicit and i can store the values using variable i of FOR LOOP.....

[David Aldridge]

I don't know why it is not raising an error. However explicit cursors are generally discouraged in situation where implicit cursors can also be used, as they require more code and are therefore more prone to error and more difficult to support, and are slower in many cases. So, just use the correct implicit cursor syntax and your code will work.