How to Loop department names

Oracle SQL Developer

I want to Loop department id and department names starting from dep_id = 10 and till 50th department. Departments are increasing by 10, so, there are 5 departments from 10 to 50.

Here’s my code

DECLARE     
    dep_name VARCHAR(15);
    dep_id NUMBER;
BEGIN
    SELECT department_name, department_id INTO dep_name, dep_id FROM Departments
    WHERE DEPARTMENT_ID = 10;
    
    LOOP

    IF dep_id < 51 THEN
        DBMS_OUTPUT.PUT_LINE('Deparment id is ' || dep_id);
        
        dep_id := dep_id + 10;
        
        DBMS_OUTPUT.PUT_LINE('Deparment name is ' || dep_name);
    ELSE
        EXIT WHEN dep_id > 51;
    END IF;
        
    END LOOP;
    END;

And here is the output,

Deparment id is 10
Deparment name is Administration
Deparment id is 20
Deparment name is Administration
Deparment id is 30
Deparment name is Administration
Deparment id is 40
Deparment name is Administration
Deparment id is 50
Deparment name is Administration

But as you can see the Administration row is repeating itself. The output should be like this

Deparment id is 10
Deparment name is Administration
Deparment id is 20
Deparment name is Marketing
Deparment id is 30
Deparment name is Purchasing
Deparment id is 40
Deparment name is Human Resources
Deparment id is 50
Deparment name is Shipping

What am I supposed to do about this?

Thanks!

Answer

How about a simple cursor FOR loop?

SQL> set serveroutput on;
SQL> begin
  2    for cur_d in (select department_id, department_name
  3                  from departments
  4                  where department_id between 10 and 50
  5                 )
  6    loop
  7      dbms_output.put_line('Department ID is '   || cur_d.department_id);
  8      dbms_output.put_line('Department name is ' || cur_d.department_name);
  9    end loop;
 10  end;
 11  /
Department ID is 10
Department name is Administration
Department ID is 20
Department name is Marketing
Department ID is 30
Department name is Purchasing
Department ID is 40
Department name is Human Resources
Department ID is 50
Department name is Shipping

PL/SQL procedure successfully completed.

SQL>