Looping Department Names in Oracle SQL Developer

Introduction

In this article, we will explore how to loop through department names in Oracle SQL Developer. The problem presented involves looping through a range of department IDs and displaying the corresponding department names. We will use a combination of SQL and PL/SQL to achieve this.

Problem Statement

Given a table Departments with columns department_id and department_name, we want to loop through department IDs from 10 to 50, inclusive, and display the corresponding department names. The issue arises when the loop includes duplicate department IDs, as it currently does in the provided example.

Solution Using SQL

One possible approach is to use a single query with an aggregate function to limit the number of unique department IDs. However, this method may not be suitable for all use cases, as we need to display both the department ID and name. In this case, we will utilize a cursor-based solution using PL/SQL.

Solution Using Cursor-Based Loop

The recommended approach involves using a cursor FOR loop in PL/SQL. Here’s an example of how you can achieve this:

-- Enable server-side output
SET SERVEROUTPUT ON;

-- Declare variables and type definitions
DECLARE
    -- Define the cursor to fetch department IDs and names
    CURSOR cur_departments IS SELECT department_id, department_name FROM Departments WHERE department_id BETWEEN 10 AND 50;
    
    -- Initialize the department ID variable
    dep_id NUMBER;
    
    -- Initialize a temporary variable for storing department names
    temp_dep_name VARCHAR2(20);
    
BEGIN
    -- Open the cursor to fetch data
    OPEN cur_departments;

    -- Loop through each row in the result set
    LOOP
        -- Fetch the next row from the cursor
        FETCH cur_departments INTO dep_id, temp_dep_name;
        
        -- Check if there are more rows available
        EXIT WHEN cur_departments%NOTFOUND;
        
        -- Display department ID and name
        DBMS_OUTPUT.PUT_LINE('Department ID is ' || TO_CHAR(dep_id));
        DBMS_OUTPUT.PUT_LINE('Department name is ' || temp_dep_name);
    END LOOP;

    -- Close the cursor to free up system resources
    CLOSE cur_departments;
END;

Explanation

The provided code snippet demonstrates how to create a cursor FOR loop in Oracle SQL Developer. Here’s a step-by-step explanation of the solution:

1. Enable server-side output: The SET SERVEROUTPUT ON statement enables the display of PL/SQL variables, which is useful for debugging purposes.
2. Declare variables and type definitions: Define the necessary variables to store department IDs (dep_id) and names (temp_dep_name). Additionally, declare a cursor (cur_departments) to fetch data from the Departments table.
3. Open the cursor: Use the OPEN statement to begin fetching rows from the Departments table where the department_id is between 10 and 50 (inclusive).
4. Loop through each row: Utilize a LOOP statement to iterate over each row in the result set returned by the cursor.
5. Fetch the next row: Use the FETCH statement with the cursor’s INTO clause to retrieve the next available row from the query results. The retrieved values are then assigned to variables dep_id and temp_dep_name.
6. Check for data availability: Add a check using the %NOTFOUND pseudorecord to verify whether there are still rows available in the result set.
7. Display department information: Inside the loop, print out the department ID (TO_CHAR(dep_id)) and name (temp_dep_name) using DBMS_OUTPUT.
8. Close the cursor: Once all data has been processed, use the CLOSE statement to release system resources allocated by the cursor.

Alternative Solution Using SQL

While not as efficient as the PL/SQL-based solution, you can achieve similar results with an alternative approach using a single query:

SELECT department_id,
       department_name
FROM   Departments
WHERE  department_id BETWEEN 10 AND 50;

This approach directly retrieves data from the Departments table using a subquery. However, keep in mind that this method may not be suitable for large datasets due to potential performance constraints.

Conclusion

In conclusion, looping through department names in Oracle SQL Developer can be achieved efficiently using a cursor-based PL/SQL solution. The provided code snippet demonstrates how to create a cursor FOR loop and fetch data from the Departments table while avoiding duplicate values. By utilizing this technique, you can easily process your database data and extract relevant information as needed.

Last modified on 2025-04-03