title: confused on how to properly use %rowtype for many tables
body: <p>I'm attempting to create a derived table of country data from several other tables.  Those tables look something like this:</p>
  
  <pre><code>Countries 
  ID | Name
  
  Country_demographics
  ID | date | Population | urban_pop | birth_rate
  
  country_financials
  ID | date | GDP | GDP_per_capita
  </code></pre>
  
  <p>Now, I'm trying to make a new table with</p>
  
  <pre><code>New_Table
  ID | Name | date | population | urban_pop | birth_rate | gdp | gdp_per_capita
  </code></pre>
  
  <p>I have a stored procedure that currently looks something like this:</p>
  
  <pre><code>CREATE OR REPLEACE PROCEDURE SP_COUNTRY (
  chunkSize IN INT
  ) AS
  
      --create tables to hold IDs and stats
          TYPE idTable IS TABLE OF COUNTRIES.ID%TYPE;
          TYPE dateTable IS TABLE OF COUNTRY_DEMOGRAPHICS.EVALUATION_DATE%TYPE;
          TYPE totPopTable IS TABLE OF COUNTRY_DEMOGRAPHICS.POPULATION_TOTAL_COUNT%TYPE;
          TYPE urbanPopTable IS TABLE OF COUNTRY_DEMOGRAPHICS.POPULATION_URBAN_COUNT%TYPE;
          --constructors
          ids idTable;
          dates dateTable;
          totpop totPopTable;
          urbanpop urbanPopTable;
  
  
      --cursors
      CURSOR countryCur IS
        SELECT c.ID,cd.EVALUATION_DATE,cd.POPULATION_TOTAL_COUNT,cd.POPULATION_URBAN_COUNT
        FROM COUNTRIES c,COUNTRY_DEMOGRAPHICS cd
        WHERE c.id=cd.COUNTRY_ID
        ORDER BY ID,EVALUATION_DATE;
  
      BEGIN
        dbms_output.enable(999999);
        --open cursor
        OPEN countryCur;
        LOOP
          --fetch and bulk collect
          FETCH countryCur BULK COLLECT INTO ids,dates,totpop,urbanpop
          LIMIT chunkSize;
          --loop over collections
          FOR j in ids.FIRST..ids.LAST
          LOOP
            --populate record
            country.COUNTRY_ID := ids(j);
            country.EVALUATION_DATE := dates(j);
            country.POPULATION_TOTAL_COUNT := totpop(j);
            country.POPULATION_URBAN_COUNT := urbanpop(j);
            --update/insert table with record (much confusion here on how to update/insert and check if already exists in derived table..)
            UPDATE NEW_TABLE SET ROW = country WHERE COUNTRY_ID = ids(j);
            dbms_output.put_line('id: ' || country.COUNTRY_ID || '  date: ' || country.EVALUATION_DATE);
            dbms_output.put_line('     pop: ' || country.POPULATION_TOTAL_COUNT || '  urban: ' || country.POPULATION_URBAN_COUNT);
          END LOOP;
        END LOOP;
        --close cursor
        CLOSE countryCur;
      END;
  </code></pre>
  
  <p>As you can see, I'm using a different table type for each piece of data.  I then plan on making a loop and then just inserting/updating in my new_table.  I think there must be a better way to do this with %rowtype, or maybe creating a record and inserting the record?  I'm not sure</p>