PLSQL Collections - to create a collection of records or not?

Question

Hi, I am new to Oracle PL/SQL and am having some difficulty conceptualizing collections with regard to records.

I have the following problem: I want to compare various fields of a record from the client table to various fields of a record from the person table. e.g., LName, FName, Soc. (unfortunately, there is no identifier to easily link the two).

For the client table, I plan on creating a cursor and fetching it into a record (and looping), since for each record of the client table, I want to look through all of the person records and see if there is a best match. If there are 100 clients, there should not be more than 100 matches.

This is where I am not sure if I should:

• A) Use a collection of records for the person table, or
• B) Use a collection for LName, a collection for FName, a collection for Soc.

If I use A, how do I reference a specific column within the record? This is how I was proceeding with the code, but I am getting a little lost.

If I use B, is there a way to ensure that I am comparing columns with the same record? i.e., If I compare the client record for John Doe 111-222-3333 I want to make sure that if I get a match that it is from a single record, and not record 10 FName = John, record 200 LNAME = Doe, record 5000 Soc = 111-222-3333.

Answers with Oracle PL/SQL syntax are greatly appreciated, as I am still learning and will be too easily confused by other languages... Below are the beginnings of my code...

Thank you!

DECLARE
   CURSOR client_cur IS
  SELECT id_client, nm_client_last, nm_client_first, nbr_client_ssn,
         cd_client_gender, dt_client_birth
    FROM client
   WHERE yr_service_fiscal BETWEEN 2007 AND 2009
ORDER BY nm_client_last,
         nm_client_first,
         nbr_client_ssn,
         cd_client_gender,
         dt_client_birth;

  CURSOR person_cur IS
  SELECT id_person, nm_person_last, nm_person_first, nbr_person_id_number,
         cd_person_sex, dt_person_birth
    FROM person
   WHERE EXTRACT (YEAR FROM dt_last_update) >= 2007
     AND nm_person_full != 'Employee,Conversion'
ORDER BY nm_person_last,
         nm_person_first,
         nbr_person_id_number,
         cd_person_sex,
         dt_person_birth;

-- Record for client and person data
client_rec client_cur%ROWTYPE;
person_rec person_cur%ROWTYPE;    

-- Record for client_match and person_match
client_match_rec client_cur%ROWTYPE;
person_match_rec person_cur%ROWTYPE;

-- For person data collection- create "table of records" (index-by table type collection)
TYPE person_t IS TABLE OF person_rec%ROWTYPE INDEX BY BINARY_INTEGER;
person_tab person_t;

-- For best client and person matches collections- create "table of records" (index-by table type collection) 
TYPE client_best_matches_t IS TABLE OF client_match_rec%ROWTYPE INDEX BY BINARY_INTEGER;
client_matches_tab client_best_matches_t;

TYPE person_best_matches_t IS TABLE OF person_match_rec%ROWTYPE INDEX BY BINARY_INTEGER;
person_matches_tab person_best_matches_t;


-- Variables
v_match_score number DEFAULT 0
v_temp_score number DEFAULT 0
v_match_threshold number DEFAULT 0 

BEGIN

-- Populate the person collection by processing the person cursor rows into the person records
OPEN person_cur;
LOOP
    FETCH person_cur INTO person_rec;
    EXIT WHEN person_cur%NOTFOUND;
    person_tab (person_cur%ROWCOUNT) := person_rec;
    END LOOP;

-- Process the client cursor rows into the client records 
OPEN client_cur;
LOOP
    FETCH client_cur INTO client_rec;
    EXIT WHEN client_cur%NOTFOUND;

/*
Inner loop compares one record in client to each record in person collection
Save match score in v_temp_score
Compare v_temp_score to v_match_score to see if this is the best match yet
If so, save records in best_match_client and best_match_person and save match score in v_match_score
*/
    IF person_tab IS NOT NULL
    THEN
    i := person_tab.FIRST;
    WHILE (i IS NOT NULL)
    LOOP
     (case when client_cur.nbr_client_ssn = person_tab.--HOW TO REFERENCE PERSON_TAB.SSN?
     then )

     i:= person_tab.NEXT (i);
     END LOOP;
    END IF;

 /*
If a match exists, add it to the collection for match results
Initialize the records and variables
*/

-- End outer loop

END LOOP;

END;

Answer 1

The best practice here would be to use a single SQL statement if it is practicable to do so. Never do in PL/SQL what you can do in SQL.

In any case, avoid explicit cursors if you can do so and use the implicit syntax instead:

For x in (select table_name from user_tables)
Loop
   other_variable = x.table_name;
   etc. .....
End Loop;

Answer 2

You asked:

person_tab.--HOW TO REFERENCE PERSON_TAB.SSN?

Like this:

person_tab(i).ssn