CODEWITHSUNDEEP
sqloracle-databaseplsql
Nathan Klero
Nathan Klero

Reputation: 59

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.

See Departments table here

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!

Upvotes: 0

Views: 1807

Answers (4)

Belayer
Belayer

Reputation: 14934

It seems the major requirement is to print department id and name. That does indeed require a plsql loop; and a cursor to hold the data after the select. Further, you should NOT relay on a specific interval between department id. Instead let the query and the cursor holding result set control the entire process.

declare      
    cursor c_department_list is 
            select department_name, department_id 
              from departments
             where department_id <= 50 ;
             
    l_dept_name  departments.department_name%type;
    l_dept_id    departments.department_id%type;
    
begin
    open c_department_list; 
    loop
        fetch c_department_list 
         into l_dept_name, l_dept_id;
        exit when  c_department_list%notfound. 
        
        dbms_output.put_line(' Deparment id is ' || l_dep_id ||
                             '. Deparment name is ' || l_dep_name);    
    end loop;

    close c_department_list; 
end;

References: Cursors, Loop
The above uses a technique referred to as explicit cursor. There is another preferred (perhaps easier) technique referred to as implicit cursor. I will leave that to your research.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143103

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>

Upvotes: 1

user15164443
user15164443

Reputation:

If the loop is mandatory

DECLARE     
     dep_name VARCHAR(15);
     dep_id NUMBER;
BEGIN
dep_id:=10;
LOOP
IF dep_id < 51 THEN
    DBMS_OUTPUT.PUT_LINE('Deparment id is ' || dep_id);
  
    SELECT department_name INTO dep_name FROM Departments
    WHERE DEPARTMENT_ID = dep_id;

    DBMS_OUTPUT.PUT_LINE('Deparment name is ' || dep_name);
    dep_id := dep_id + 10;


ELSE
    EXIT WHEN dep_id > 51;
END IF;
    
END LOOP;
END;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

You could avoid the loop .. using a simply (correct) query

 SELECT department_name, department_id 
 FROM Departments 
 WHERE DEPARTMENT_ID between 10 AND 50;

(your code is wrong, you are only selecting the id = 10 not the values from 10 to 50 )

Upvotes: 0

Related Questions