Cursors

[vijuvelu]

Can anyone give me a example on how to build a cursor with the attribute of another cursor,
for eg:
if cursor1 is
select emp_id, emp_name from emp;
I want to build cursor2 which is select dept_no, dept_name from dept where emp_id = cursor1.emp_id.
Thanks!

 

[listener22]

Two (maybe more) interpretations:
1. Find the first emp_id from cursor1 and use that emp_id in cursor2.
Fetch empid into a variable and use that variable in cursor2.
2. Find all the emp_id's from cursor 1 and find all the depts that correspond.
Fetch the emp_id's into an array, or write them to a temp table.
Rewrite cursor 2 to be an in condition, rather than an = condition. Jim

oracle, vb

 

[sem]

You may declare parameter for your second cursor:

cursor cursor2(pid number) is select dept_no, dept_name from dept where emp_id = cursor1.emp_id

and then to open it, passing any value you need, e.g. your 1st cursor field value.

 

[vijuvelu]

Thank you Sem and Jim.

What I did was:
Create cursor2(pid in number) is select dept_no, dept_name
from dept where emp_id = pid;

and then opened it passing the 1st cursor value,
ie, open cursor2(cursor1.emp_id);

It Worked!
-Viju. *:->*