cursor

[raji96]

Hi,

I have a cursor which has 78 fields and i loop through each record and based on few condition ,i call another cursor passing in the value of 1 field(emp_id) into the other cursor and fetch 6 feilds from another table into the second cursor.

Now i have two cursors ,one with 78 fields and one with 6 fields and the common field in those tow is the emp_id how do i combine both and put in one cursor or array.

my requirement is:
1.Get all from employee table(78 fields)
2.Loop through each emp_id and if emp_id>10,000 get salary details from new_salary table
else
get salary details from old_salry table.

now i have two cursors one with employee details and one with employye salary details.How do i combine both.

 

[rcurva]

I don't know if I get you right, but if your SQL is straightforward, you could do a simple join and union of the tables like;

select a.emp_id...(78 cols),
b.col1 (5 cols),
null "Salary"
from employee a, new_salary_table b
where a.emp_id = b.emp_id
and b.salary <= 10000
... more criteria
UNION ALL
select a.emp_id...(78 cols),
b.col1 (5 cols),
b.salaryfrom employee a, new_salary_table b
where a.emp_id = b.emp_id
and b.salary > 10000
... more criteria

The cursor within cursor implementation is not that bad, it's up to you to decide which one is faster.

Hope this one helps.

 

[sem]

You may also use a.* instead of writing all 78 columns (see Rcurva's message above).

 

[raji96]

No i cannot join the tables since i depend on the first cursor to pick the table i have to go for the second cursor.Then put them together at the end.



--thanks,snebe

 

[prashjha]

You can use Parameterized Cursor. Make the second cursor parameterized: CURSOR your_cursor(var_emp_id number) is select * from your_table where emp_id = var_emp_id;
Then while opening this cursor pass the emp_id from first cursor.
Reply if you need more details.

 

[carp]

If you have two cursors:

CURSOR first_cursor IS
SELECT 78_columns FROM some_table;
CURSOR second_cursor(p_emp_id NUMBER) IS
SELECT 6_columns FROM another_table
WHERE emp_id = p_emp_id;

BEGIN
FOR i IN first_cursor LOOP
FOR j IN second_cursor(i.emp_id) LOOP
-- NOW YOU HAVE ACCESS TO THE DATA IN BOTH CURSORS
DO_WHATEVERY_YOU_NEED_TO_DO_WITH(i.first_col, i.second_col, j.first_col, etc);
END LOOP;
-- NOW YOU ONLY HAVE ACCESS TO THE DATA IN THE FIRST_CURSOR
END LOOP;
-- AT THIS POINT, YOU'VE PROCESSED ALL OF THE RECORDS IN THE FIRST_CURSOR
END;

Is this what you're trying to find out, or did I miss something?