1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to create procedure to return hierarchical tree

Discussion in 'SQL PL/SQL' started by mukulverma2408, Jul 18, 2015.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hi,

    I have a table as given below :
    Code (Text):

    emp_name    emp_id    emp_manager
    john          1                2
    sean          2             NULL
    paul           3             2
    kent           4              2
    peter          5              3
    fiona          6              3
     
    I want to create a stored procedure where i will pass the emp_id as in input parameter and it will return emp_id of all the emp that exceed input id in hierarchy.

    Basically i want to return manager of manager

    Example passing 5 will return 3,2.
    passing 3 will return 2
     
    Last edited: Jul 18, 2015
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    You can generate the data using a CONNECT BY...PRIOR query. If you need the results to be used in a subprogram, then put the CBP query into the subprogram as a cursor FOR LOOP. The query itself follows:

    Code (Text):

    SELECT emp_id
    FROM   temp_emp
    WHERE  LEVEL > 1
    START WITH emp_id = 5
    CONNECT BY PRIOR emp_manager = emp_id;

        EMP_ID
    ----------
             3
             2
     
    mukulverma2408 likes this.
  3. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    works perfectly :)