CODEWITHSUNDEEP
sqloracle-databaseplsql
user2853262
user2853262

Reputation: 43

PL/SQL: Calling a procedure recursively

I may be way off with this, but I'm trying to write a procedure that inserts all the ancestors of a given child into a new table. Here's what I have:

create or replace procedure ancestors(childIn IN varchar2)
is
begin
insert into AncestorTable(Parent)
select Parent from Family
where Child=childIn;
ancestors(Parent);
end;
/

I know the ancestors(Parent)is way off but I was wondering if there's an easy way to do this without going into cursors. If I need a cursor, could I get some direction on how to do that? Thanks for any insight.

Upvotes: 0

Views: 828

Answers (1)

Diver
Diver

Reputation: 1608

Try a hierarchical query.

Something like

select Parent 
  from Family
 start with Child=childIn
connect by prior Parent = Child;

Here's an sqlFiddle

This way you can make a single insert and you dont need recursion.

insert into AncestorTable(Parent)
(select Parent 
   from Family
 start with Child=childIn
  connect by prior Parent = Child);

Upvotes: 2

Related Questions