PL- SQL recursive function

Question

I am new on PL SQL and I was trying to create a recursive function on it, but I am way too confused with PL SQL terms.

I have a table structure like :

    FFAM_ACC_ID     FFAM_UPPER_ACC_ID   FFAM_GROUP_FLAG
        1                   0                   1
        2                   1                   1
        3                   1                   2
        4                   2                   1
        5                   4                   2
        6                   1                   2
        7                   6                   2
        8                   0                   1
        9                   8                   2

Now, I would like to create a recursive function. So, if I provide a FFAM_ACC_ID, it should be able to return me the child ids which has FFAM_GROUP_FLAG 2.

FFAM_UPPER_ACC_ID is the parent id and FFAM_GROUP_FLAG determines if the row is a group or not.

So, if I provide 2, it should return nothing because although it has a child row, 4. That row has FFAM_GROUP_FLAG 1. ie. it is group.

If I provide 1, it should return 3, 6, 7. This needs recursion since parent id of 3 is 1 and parent id of 7 is 6.

If I provide 9, it should return 9. Although it does not have child row, it is not a group.

SQL fiddle

Mat · Accepted Answer · 2013-03-04 07:13:31Z

4

You don't actually need a recursive procedure for that, or even a procedure at all: use hierarchical queries instead.

This should do what you want (works on the input you provided at least):

select * from FMS_FC_ACC_MST
where ffam_group_flag = 2
start with ffam_acc_id = <your input>
connect by ffam_group_flag = 2 
       and prior ffam_acc_id = ffam_upper_acc_id;

answered Mar 4, 2013 at 7:13

Mat

208k41 gold badges407 silver badges423 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ben Over a year ago

Aren't hierarchical queries recursive :-)? It's just hidden in the DB engine rather than being laid-out for viewing.

hsuk Over a year ago

Hierarchical queries worked for me, but what if I want to do this creating a recursive function ?

Mat Over a year ago

As @Ben said, this is recursive already, just that you're not doing the recursion "by hand". If you wanted to do it by hand, well, just do it. Do a procedure that selects the "children" given a list of "parent" ids, and call that recursively. Probably a bit tricky, and also not efficient.

Egor Skriptunoff · Accepted Answer · 2013-03-04 07:23:41Z

1

with 
  input as (
     select 9 as FFAM_ACC_ID from dual  -- change to 1, 2
  )

select FFAM_ACC_ID
from FMS_FC_ACC_MST
start with 
   FFAM_UPPER_ACC_ID = (select FFAM_ACC_ID from input)
   and FFAM_GROUP_FLAG = 2
connect by 
   prior FFAM_ACC_ID = FFAM_UPPER_ACC_ID

union all

select FFAM_ACC_ID
from FMS_FC_ACC_MST
natural join input 
where FFAM_GROUP_FLAG = 2

fiddle

answered Mar 4, 2013 at 7:23

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

Collectives™ on Stack Overflow

PL- SQL recursive function

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related