CODEWITHSUNDEEP
plsqloracle10gplsqldeveloper
hsuk
hsuk

Reputation: 6860

PL- SQL recursive function

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

Upvotes: 2

Views: 3937

Answers (2)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23747

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

Upvotes: 1

Mat
Mat

Reputation: 206727

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;

Upvotes: 4

Related Questions