Printing organization hierarchy in PLSQL for beginners (with step by step code execution walkthrough)

 

Understanding PL/SQL Code to Print Organization Hierarchy

Introduction

In this blog post, we will learn how to create and execute a PL/SQL function to print the organization hierarchy in an employee table. This example uses a loop to traverse the hierarchy from a given employee up to the CEO. We will break down each step of the code, explain its purpose, and show the values of variables at each iteration.

1. Setting Up the Employee Table

Let's start by creating an example employee table with three columns: employee_id, employee_name, and manager_id. We'll insert five employees into this table.

CREATE TABLE employee (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(50),
    manager_id NUMBER
);

INSERT INTO employee (employee_id, employee_name, manager_id) VALUES (1, 'Alice', NULL);
INSERT INTO employee (employee_id, employee_name, manager_id) VALUES (2, 'Bob', 1);
INSERT INTO employee (employee_id, employee_name, manager_id) VALUES (3, 'Carol', 1);
INSERT INTO employee (employee_id, employee_name, manager_id) VALUES (4, 'David', 2);
INSERT INTO employee (employee_id, employee_name, manager_id) VALUES (5, 'Eve', 2);

Employee Table  

        

            

                
EmployeeID
                
EmployeeName
                
ManagerID
            
        
        

            

                
1
                
Alice
                
NULL
            
            

                
2
                
Bob
                
1
            
            

                
3
                
Carol
                
1
            
            

                
4
                
David
                
2
            
            

                
5
                
Eve
                
2

2. Creating the PL/SQL Function

We will create a PL/SQL function print_hierarchy that takes an employee_id as input and prints the hierarchy from that employee up to the CEO.

CREATE OR REPLACE FUNCTION print_hierarchy(p_employee_id IN NUMBER) RETURN VARCHAR2 IS
    v_employee_id NUMBER := p_employee_id;
    v_employee_name VARCHAR2(50);
    v_manager_id NUMBER;
    v_hierarchy VARCHAR2(4000);
BEGIN
    LOOP
        -- Fetch the employee name and manager ID
        SELECT employee_name, manager_id
        INTO v_employee_name, v_manager_id
        FROM employee
        WHERE employee_id = v_employee_id;

        -- Concatenate the employee name to the hierarchy string
        v_hierarchy := v_employee_name || ' -> ' || v_hierarchy;

        -- If the manager ID is NULL, we've reached the CEO
        EXIT WHEN v_manager_id IS NULL;

        -- Update the employee ID to the manager ID for the next iteration
        v_employee_id := v_manager_id;
    END LOOP;

    -- Remove the trailing " -> "
    RETURN RTRIM(v_hierarchy, ' -> ');
END;

3. Example Execution and Explanation

Let's execute the function with an example employee_id and explain each step.

-- Example execution for employee ID 4 (David)
DECLARE
    result VARCHAR2(4000);
BEGIN
    result := print_hierarchy(4);
    DBMS_OUTPUT.PUT_LINE(result);
END;

Step-by-Step Execution

1. Initialization

• p_employee_id = 4 (input)
• v_employee_id = 4
• v_employee_name = NULL
• v_manager_id = NULL
• v_hierarchy = NULL

2. First Iteration

• Query: Fetch employee details where employee_id = 4
  • v_employee_name = 'David'
  • v_manager_id = 2
• Update Hierarchy: v_hierarchy = 'David -> '
• Update Employee ID: v_employee_id = 2

3. Second Iteration

• Query: Fetch employee details where employee_id = 2
  • v_employee_name = 'Bob'
  • v_manager_id = 1
• Update Hierarchy: v_hierarchy = 'Bob -> David -> '
• Update Employee ID: v_employee_id = 1

4. Third Iteration

• Query: Fetch employee details where employee_id = 1
  • v_employee_name = 'Alice'
  • v_manager_id = NULL
• Update Hierarchy: v_hierarchy = 'Alice -> Bob -> David -> '
• Exit Loop: v_manager_id = NULL

5. Finalization

• Remove Trailing Arrow: v_hierarchy = 'Alice -> Bob -> David'
• Return Result: 'Alice -> Bob -> David'

Final Output

The final output will be:

Alice -> Bob -> David

Summary

This blog post has demonstrated how to create a PL/SQL function to print the organization hierarchy. By using a loop and traversing the hierarchy from a given employee up to the CEO, we've learned how to manage and understand the variable values at each iteration. Feel free to use and modify this example to fit your specific needs.

Support Our Efforts and Earn Together 🚀

Visit https://parucodes.github.io/ today and start your journey to becoming a fast, accurate, and confident touch typist.

If you find our website useful and want to support us, consider joining the exciting world of Bitcoin mining on your mobile phone. Follow this link: Mine PI Bitcoin and use my username prarthanadp as your invitation code. With the referral code prarthanadp, you'll receive a special referral bonus.

Thank you for your support! Let's grow and earn together! 🌟