confused on how to properly use %rowtype for many tables

Question

I'm attempting to create a derived table of country data from several other tables. Those tables look something like this:

Countries 
ID | Name

Country_demographics
ID | date | Population | urban_pop | birth_rate

country_financials
ID | date | GDP | GDP_per_capita

Now, I'm trying to make a new table with

New_Table
ID | Name | date | population | urban_pop | birth_rate | gdp | gdp_per_capita

I have a stored procedure that currently looks something like this:

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;

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

Answer 1

Unless I'm missing something by simplifying this, and assuming cd.date and cf.date are equal, this should work:

INSERT INTO NEW_TABLE (ID, Name, date, population, urban_pop, birth_rate, gdp, gdp_per_capita)
values
(select c.id, c.name, cd.date, 
        cd.population, cd.urban_pop, cd.birthrate,
        cf.gdp, cf.gdp_per_capita)
from Countries c, country_demographics cd, country_financials cf 
where c.id = cd.id
and   cd.id = cf.id);

Edit: Use the MERGE statement to update or insert depending on if the primary key exists:

MERGE INTO NEW_TABLE nt
    USING ( select c.id, c.name, cd.date, 
                   cd.population, cd.urban_pop, cd.birthrate,
                   cf.gdp, cf.gdp_per_capita
            from Countries c, country_demographics cd, country_financials cf 
            where c.id = cd.id
            and   cd.id = cf.id ) a
    ON (nt.id = a.id )
    WHEN MATCHED THEN 
      UPDATE SET nt.Name           = a.Name,
                 nt.date           = a.date,
                 nt.population     = a.population,
                 nt.urban_pop      = a.urban_pop,
                 nt.birth_rate     = a.birth_rate,
                 nt.gdp            = a.gdp,
                 nt.gdp_per_capita = a.gdp_per_capita
    WHEN NOT MATCHED THEN 
      INSERT (ID, Name, date, population, urban_pop, birth_rate, gdp, gdp_per_capita)
      VALUES (a.id, a.Name, a.date, a.population, a.urban_pop, a.birth_rate, a.gdp, a.gdp_per_capita);