PLSQL looping through hierarchy

Question

I am trying to figure out how loop through a hierarchy, I don't know how to put in PLSQL. What I am trying to achieve: I want to know what department is 10 steps above me in a hierarchy:

Say I have a table with a department and a parent department. I want to perform this kind of operation:

select my_department from table_departments as v_department
FOR counter in 1...9
LOOP
v_department:=
(SELECT parent_department
FROM table_department_hierarchy
WHERE child_department=v_department)
END LOOP as top_department;

I can't figure out the correct syntax, is there a brave soul out there who can help me?

Answer 1

Please, check the following example. Not tested, but believe :)

DECLARE
G_EMPLOYEE_ID NUMBER:=1880;
FUNCTION GET_MANAGER(V_EMPLOYEE_ID NUMBER) RETURN NUMBER IS 
V_MANAGER_ID NUMBER;
BEGIN
  SELECT ID_MANAGER INTO V_MANAGER_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMPLOYEE_ID;
  RETURN V_MANAGER_ID;
EXCEPTION
  WHEN OTHERS THEN
  RETURN NULL;
END;
BEGIN
LOOP 
DBMS_OUTPUT.PUT_LINE('EMPLOYEE:' || G_EMPLOYEE_ID);
G_EMPLOYEE_ID := GET_MANAGER(G_EMPLOYEE_ID);
DBMS_OUTPUT.PUT_LINE('MANAGER:' || G_EMPLOYEE_ID);
EXIT WHEN G_EMPLOYEE_ID IS NULL;
END LOOP;
END;

Another great option (primary) is CONNECT BY, START WITH

Answer 2

This is a large pl/sql procedure that i wrote a long while ago that was meant to traverse a employee/boss reporting tree all the way to the top (CEO). This version was specific to Peoplesoft but it as long as your reading something that has a parent/child relationship in a record it will work on anything.... I have other more dynamic versions of this but this maybe the simplest to decipher. I removed some fluff stuff that you won't care about. Also this particular solution delivers to a table for many different reasons because reporting tools can consume it...

Also it determines levels dynamically so you don't have to know how many levels there are as you would with a connect by solution.

Hope it helps:

CREATE OR REPLACE PROCEDURE DW."SPW_T_RESOURCE_HIERARCHY" (iCommit   In Integer Default 1000,
                                                     pdBegin   In Date Default trunc(Sysdate) - 3,
                                                     pdEnd     In Date Default trunc(Sysdate) + 1,
                                                     vTruncate In Varchar2 Default 'Y' ) Is

   ------------------------------------------------------
   --  DECLARATIONS
   ------------------------------------------------------

   Cursor curDataSource Is

   ---**********************************----
   ---****BEGIN CUSTOMIZE THIS BLOCK****----
   ---**********************************----
      Select
                Eh.empl_id         F_DESCENDANT_ID
               ,Eh.Super_Empl_Id   F_IMMEDIATE_ANCESTOR_ID
        From
               Employee_Header EH
       Where
               EH.SUPER_EMPL_ID IS NOT NULL OR EH.TERM_DATE IS NULL;

   ---**********************************----
   ---****END CUSTOMIZE THIS BLOCK******----
   ---**********************************----

   dNow              Date := Sysdate;
   iTotalRows        Integer := 0;
   iTotalErrors      Integer := 0;

   ---**********************************----
   ---****BEGIN CUSTOMIZE THIS BLOCK****----
   ---**********************************----
   vDescendentID     Varchar2(20);
   iDescendentLevel  Integer := 1;
   iAncestorLevel    Integer := 0;
   vAncestorID       Varchar2(20);
   vTmpAncestorID    Varchar2(20);
   vTmpEmployeeID    Varchar2(20);

   ---**********************************----
   ---****END CUSTOMIZE THIS BLOCK******----
   ---**********************************----

   ------------------------------------------------------
   -- END DECLARATIONS
   ------------------------------------------------------

   ------------------------------------------------------
   -- BEGIN MAIN
   ------------------------------------------------------
Begin
   -- Loop over source records
   For recDataSource In curDataSource
   Loop
      iDescendentLevel := 1;
      vAncestorID := recDataSource.f_Immediate_Ancestor_Id;

      -- Start Transaction
      Begin
         while (Trim(vAncestorID) is not null)
         loop
             Begin
                          -- Fetch Next Ancestor
                 Select EH.SUPER_EMPL_ID
                   Into vTmpAncestorID
                   From
                        EMPLOYEE_HEADER EH
                  Where
                        EH.EMPL_ID = vAncestorID;
             Exception
             When Others Then
                vTmpAncestorID := null;
             End;

              If NVL(vTmpAncestorID,'-XYZ-') = NVL(vAncestorID,'-123-') Then
                 vTmpAncestorID := null;
              End If;

              vAncestorID := vTmpAncestorID;
              iDescendentLevel := iDescendentLevel + 1;
         end loop;

         -- Insert Resource Base
         Insert Into T_RESOURCE_HIERARCHY
            (
                T_RESOURCE_HIERARCHY.F_HIERARCHY_NAME,
                T_RESOURCE_HIERARCHY.F_DESCENDANT_LEVEL,
                T_RESOURCE_HIERARCHY.F_DESCENDANT_ID,
                T_RESOURCE_HIERARCHY.F_ANCESTOR_LEVEL,
                T_RESOURCE_HIERARCHY.F_ANCESTOR_ID
            )
         Values
            (
                'Physical Org Chart',
                iDescendentLevel,
                recDataSource.f_Descendant_Id,
                To_Number(Decode(iDescendentLevel,1,2,iDescendentLevel) - 1),
                NVL(recDataSource.f_Immediate_Ancestor_Id,'ROOT')
            );


      -- Insert MySelf Into Resource Base as well for full hierarchy research
         Insert Into T_RESOURCE_HIERARCHY
            (
                T_RESOURCE_HIERARCHY.F_HIERARCHY_NAME,
                T_RESOURCE_HIERARCHY.F_DESCENDANT_LEVEL,
                T_RESOURCE_HIERARCHY.F_DESCENDANT_ID,
                T_RESOURCE_HIERARCHY.F_ANCESTOR_LEVEL,
                T_RESOURCE_HIERARCHY.F_ANCESTOR_ID
            )
         Values
            (
                'Physical Org Chart',
                iDescendentLevel,
                recDataSource.f_Descendant_Id,
                iDescendentLevel,
                NVL(recDataSource.f_Descendant_Id,'ROOT')
            );

      -- Now Its Time To Climb The Tree
      -- For This Employee
         vAncestorID := recDataSource.f_Immediate_Ancestor_Id;
         iAncestorLevel := iDescendentLevel-1;
         vTmpAncestorID := null;

         -- Loop over parents
         while (Trim(vAncestorID) is not null)
         loop

             Begin
                 -- Fetch Next Ancestor
                 Select EH.SUPER_EMPL_ID
                   Into vTmpAncestorID
                   From
                        EMPLOYEE_HEADER EH
                  Where
                        EH.EMPL_ID = vAncestorID;
             Exception
             When Others Then
                vTmpAncestorID := null;
             End;

             If NVL(vTmpAncestorID,'-XYZ-') = '-XYZ-' Then
                 vTmpAncestorID := null;
             End If;

              vAncestorID := vTmpAncestorID;
              iAncestorLevel := iAncestorLevel - 1;

             If vAncestorID is not null Then
                 -- Insert Resource Base
                 Insert Into T_RESOURCE_HIERARCHY
                    (
                        T_RESOURCE_HIERARCHY.F_HIERARCHY_NAME,
                        T_RESOURCE_HIERARCHY.F_DESCENDANT_LEVEL,
                        T_RESOURCE_HIERARCHY.F_DESCENDANT_ID,
                        T_RESOURCE_HIERARCHY.F_ANCESTOR_LEVEL,
                        T_RESOURCE_HIERARCHY.F_ANCESTOR_ID
                    )
                 Values
                    (
                       'Physical Org Chart',
                        iDescendentLevel,
                        recDataSource.f_Descendant_Id,
                        iAncestorLevel,
                        vAncestorID

                    );
             End If;
         end loop;


         -- TRANSACTION EXCEPTION HANDLING
      Exception
         When Others Then

      End;

      -- ASSIGN HOW MANY RECORDS PROCESSED
      iTotalRows := curDataSource%Rowcount;

      -- CONDITIONAL/INCREMENTAL TRANSACTION COMMIT
      If Mod(iTotalRows, iCommit) = 0
      Then
         Commit;
      End If;

   End Loop;


   -- FINAL COMMIT AND MD UPDATE
   Commit;


   -- MAIN EXCEPTION HANDLING
Exception
   When Others Then
      Begin
         iExceptionCode    := Sqlcode;
         vExceptionMessage := Sqlerrm;
         Raise_application_error(Sqlcode, Sqlerrm);
      End;

   ------------------------------------------------------
   -- END MAIN
   ------------------------------------------------------
End SPW_T_RESOURCE_HIERARCHY;
/

Answer 3

Your method with corrected PL/SQL syntax would be something like:

begin
  select my_department into v_department from table_departments;
  FOR counter in 1...9
  LOOP
    SELECT parent_department
    INTO v_department
    FROM table_department_hierarchy
    WHERE child_department=v_department;
  END LOOP:
END;

However you could perhaps get it all in one statement something like this:

SELECT parent_department
INTO v_department
FROM
( SELECT parent_department, level as lvl
  FROM table_department_hierarchy
  CONNECT BY child_department = PRIOR parent_department
  START WITH child_department = v_department
)
WHERE lvl = 9;

See Oracle docs on hierarchical queries