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

score 4 · Accepted Answer · answered Mar 04 '13 at 07:13

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 04 '13 at 07:13

Mat

202,337
40
393
406

1

Aren't hierarchical queries recursive :-)? It's just hidden in the DB engine rather than being laid-out for viewing. – Ben Mar 04 '13 at 08:44
Hierarchical queries worked for me, but what if I want to do this creating a recursive function ? – hsuk Mar 04 '13 at 08:54
1

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. – Mat Mar 04 '13 at 09:20

score 1 · Answer 2 · answered Mar 04 '13 at 07:23

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

PL- SQL recursive function

2 Answers2