Skip to Main Content

Loop thru 2 Cursors at the same time?

User_2KMIPAug 19 2022

Objective: Create an SP that's passed a Cursor to a set of Homes. Homes have certain criteria for Pets (cat vs dog, size, demeanor, etc.). For each Home, SELECT the set of related Members who live there, and based on the criteria of the Home, do a search of the Pets table of the nearest Animal Shelter to the Home (with READ-ONLY DBLINKs to each Shelter's DB), and look for compatible Pets. If possible, match and recommend 1 Pet to each Member from each Home, and update the Member record with their matched Pet ID. If enough compatible Pets are not matched, leave the Member's recommended Pet_ID unchanged (blank).
I have the SP, the LOOP through the provided Homes H, the Member_Set (Members WHERE M.Home_ID = H.Home_ID), the DBLINK name looked-up and in a variable, and the complex query for matching the Pets, but each Pet can only be assigned once, so I seem to need a parallel "2 cursor" loop, sort-of like:
FOR My_Mem IN Member_Set AND FOR My_Pet IN Matched_Pet_Set
LOOP
UPDATE Member M SET M.Pet_ID = My_Pet.Pet_ID WHERE M.Mem_ID = My_Mem.Mem_ID ;
END LOOP ;
Problem: How do you loop through 2 unrelated Result Sets (Cursors?) at the same time?

Comments
Post Details
Added on Aug 19 2022
14 comments
82 views