Recursion in PL/SQL

Originally Posted by gvi

Hi,

I am not sure if recursion is the solution. But here is my issue. This has to be done on Oracle 9i and only in the procedure and no front end involved.

I have a teacher and he has a class he is teaching. I want to know all the students who are taking that class only glitch is the students themselves might be taking some class and I want the students of the class the students are teaching.

i hope i am clear and not confusing you people

i have 2 relations

teacher-class table which has teacherid, classid
class-student table which has classid, studentid

in this say if teacher John teaches math and students A, B,C are enrolled in math.
in turn A might teach science , B might teach history. in the science class which A teaches there might be students E,F,G

So i want all the users if I pass John as teh parameter I should get
A,B,C,E,F,G...But I should not get John again if he is enrolled in A's science class

I am at loss now as to which way to go.
Can you please help.

I have modified my original requirement into a more readable example . pls let me know how to approach this issue. i am going nuts

thanks
J

Originally Posted by amitpatel66

You have your requirement in place. Start writing a procedure which would give you desired information.
Let us know in case of any difficulty writing a procedure.
You also know that the input parameter to the procedure would be <teachers name>
1. your first step would be to get all the students under a particular teacher.
2. Your second step would be to loop through all the students and get the list of their students.
3. Now put all the students together and return the distinct values.

Do POST back what you have tried to achieve this?
We will help you in case of any issues!!

Originally Posted by amitpatel66

You have your requirement in place. Start writing a procedure which would give you desired information.
Let us know in case of any difficulty writing a procedure.
You also know that the input parameter to the procedure would be <teachers name>
1. your first step would be to get all the students under a particular teacher.
2. Your second step would be to loop through all the students and get the list of their students.
3. Now put all the students together and return the distinct values.

Do POST back what you have tried to achieve this?
We will help you in case of any issues!!

Originally Posted by gvi

Hi Amitpatel

thanks for replying . here is my procedure pls. go through it and say if its looking okay.


create procedure findstudents(teacheridin varchar2) is


stud_id_First varchar2(150);
stud_id_Cursor varchar2(150);
stud_id_insert varchar2(150);
cursor c1
is
Select userid into stud_id_First from studentclasstable
where classid in (Select classid from teacherclass where
teacherid = teacheridin);


begin
open c1;

loop

fetch c1 into stud_id_cursor;
insert into temptable values(stud_id_cursor);
if teacheridin <> stud_id_cursor then
--exit when c1%notfound;
select userid into stud_id_insert from
studentclasstable
where classid in (select classid from teacherclass
where teacherid = stud_id_cursor);
insert into temptable values(stud_id_insert);
end if;
end loop;
close c1;
end;


thanks
J


Thanks