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.

No comments:

Post a Comment