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;

​x
 
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

 
Deparment id is 10Deparment name is AdministrationDeparment id is 20Deparment name is AdministrationDeparment id is 30Deparment name is AdministrationDeparment id is 40Deparment name is AdministrationDeparment id is 50Deparment 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

 
Deparment id is 10Deparment name is AdministrationDeparment id is 20Deparment name is MarketingDeparment id is 30Deparment name is PurchasingDeparment id is 40Deparment name is Human ResourcesDeparment id is 50Deparment 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>

 
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 10Department name is AdministrationDepartment ID is 20Department name is MarketingDepartment ID is 30Department name is PurchasingDepartment ID is 40Department name is Human ResourcesDepartment ID is 50Department name is Shipping​PL/SQL procedure successfully completed.​SQL>​

Advertisement

Answer