Records

The PL/SQL could create his own data types that in this language are identified by the keyword RECORD.

A record could be created in three ways: basing it on a table data definition; making it as an indipendent brand new type; and basing it on a cursor type definition.

If we want to fetch data from a table, we could create a record matching the table definition. For instance, let's say that we want to work with the COUNTRIES table in the Oracle test hr schema:

declare
  l_country countries%rowtype; -- 1.
begin
  select * 
  into l_country -- 2.
  from countries 
  where country_id = 'AR';
  
  dbms_output.put_line(l_country.country_name); -- 3.
exception
  when no_data_found then -- 4.
    dbms_output.put_line('No data found');
  when others then
    dbms_output.put_line('Unexpected exception');
end;

1. that's how we define a table based record.
2. we fetch the data through select in our record.
3. that's how we access fields in a record.
4. if SELECT don't find anything, an exception is raised.

No need of duplicating an entire table structure in a record, if that is not necessary. For instance here we create and use a custom record based on the COUNTRIES table, but using only a couple of fields:

declare
  type my_country_rt is record ( -- 1.
    country_id countries.country_id%type,
    country_name countries.country_name%type
  );
  l_country my_country_rt; -- 2.
begin
  select country_id, country_name 
  into l_country
  from countries 
  where country_id = 'AR';

  dbms_output.put_line(l_country.country_name);
exception
  when no_data_found then
    dbms_output.put_line('No data found');
  when others then
    dbms_output.put_line('Unexpected exception');
end;

1. That's how we define a brand new record type.
2. And this is the definition of variable of our new record type.

Given a cursor, we could create a record based on it:

declare
  cursor l_countries_cur is
    select * from countries
    where country_id like 'A%';
  l_country l_countries_cur%rowtype; -- 1.
begin
  open l_countries_cur;
  loop
    fetch l_countries_cur into l_country;
    exit when l_countries_cur%notfound;
    dbms_output.put_line(l_country.country_id || ' ' || l_country.country_name);
  end loop;
  close l_countries_cur;
end;

1. That's the definition of a cursor based record

We have already seen that we could simplify the cursor-record relation using the cursor for loop structure. Here is the previous code rewritten using an implicit cursor based record:

begin
  for l_country in (select * from countries where country_id like 'A%')
  loop
    dbms_output.put_line(l_country.country_id || ' ' || l_country.country_name);
  end loop;
end;

Chapter 11 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein tells a lot more than this about records.