Databases
StackOverflow2013
Postsdbo
POPLSQL Collections - to create a collection of records or not?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPLSQL Collections - to create a collection of records or not?
    primarykey
    data
    text
    <p>I am new to Oracle PL/SQL and am having some difficulty conceptualizing collections with regard to records.</p> <p>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). </p> <p>For the client table, I plan on creating a cursor and fetching it into a record (and looping), since <strong>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</strong>. If there are 100 clients, there should not be more than 100 matches.</p> <p>This is where I am not sure if I should: </p> <ul> <li>A) Use a collection of records for the person table, or </li> <li>B) Use a collection for LName, a collection for FName, a collection for Soc.</li> </ul> <p>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.</p> <p>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.</p> <p>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...</p> <p>Thank you!</p> <pre><code>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) &gt;= 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; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOMG Ponies
    1. USJulie
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPLSQL Collections - to create a collection of records or not?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIn order to reference `PERSON_TAB.SSN`, you need to update the `person_cur` cursor to expose it. You have match_score/temp_score/etc but you don't use them. My initial impression is that you don't need all the TYPEs you've defined...
      singulars
      1. POPLSQL Collections - to create a collection of records or not?
      1. USOMG Ponies
    2. COI'm not sure what this means - " you need to update the person_cur cursor to expose it." I have defined match_score/temp_score etc. because I think I will need them to perform the comparisons (my goals are explained in the comments section before the inner loop), but again this was the beginnings of my code, and they are just placeholders for now. If there is a way I can do this without defining extra records, I will try.
      singulars
      1. POPLSQL Collections - to create a collection of records or not?
      1. USJulie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload