Creating user-defined data types and view IN SQL

14.1.2.1 Creating user-defined data types
Step 1 is to create a user-defined data type to represent the data from these two tables as an
object. We'll start with the PERSON_IDENTIFIER table. Here's the DDL to create a
corresponding user-defined data type:
create type PERSON_IDENTIFIER_typ as object (
id varchar2(30),
id_type varchar2(30) )
Notice that we don't have the person_id in the type definition. That's because the person_id
will be implicit, because the identifiers are stored in the form of a nested table within the enclosing
person object. Next, we need to create a nested table type definition to transform the
PERSON_IDENTIFIER table into a collection for the person object. Here's the DDL to do that:
create type PERSON_IDENTIFIER_tab as
table of PERSON_IDENTIFIER_typ
Now that we have a collection type for the PERSON_IDENTIFIER table, we can define the
person type:
create type PERSON_typ as object (
person_id number,
last_name varchar2(30),
first_name varchar2(30),
middle_name varchar2(30),
birth_date date,
mothers_maiden_name varchar2(30),
identifiers person_identifier_tab,
map member function get_map return varchar2,
member function get_age return number,
member function get_age_on( aid_date in date ) return number,
static function get_id return number );
/
create type body PERSON_typ as
map member function get_map return varchar2 is
begin
return rpad( last_name, 30 )||
rpad( first_name, 30 )||
rpad( middle_name, 30 )||
rpad( mothers_maiden_name,30 )||
to_char( birth_date, 'YYYYMMDDHH24MISS' );
end get_map;
member function get_age return number is
begin
return trunc( months_between( SYSDATE, birth_date ) / 12 );
end get_age;
member function get_age_on( aid_date in date ) return number is
begin
return trunc( months_between( aid_date, birth_date ) / 12 );
end get_age_on;
static function get_id return number is
n_person_id number := 0;
begin
select person_seq.nextval into n_person_id from dual;
return n_person_id;
end get_id;
end;
/
We've added one static and three member methods to person_typ. I'll use these in the coming
chapters to demonstrate how to call a database object's methods.
14.1.2.2 Selecting a reference value
Now that we have defined types for the PERSON and PERSON_IDENTIFIER tables, we need to
decide which value to use for an object reference. An object reference acts as a unique identifier
for an object, just as a primary key acts as a unique identifier for a row in a relational table. Since
we're creating object views, and the column person_id is common to both tables, we'll use it for
the reference value.
If we were creating an object table, as we will do later in this chapter, we could choose between
using a unique value in the attribute of a user-defined data type or a reference. A reference is a
database-generated global unique identifier (GUID). My preference, even with object tables, is to
use an attribute as a primary key instead of a GUID, because you can create foreign key
constraints between object tables with a primary key.
14.1.2.3 Creating an object view
Now that we have all the necessary types defined and have selected a reference value, we can
move on to step 3, which is to create a view to extract the data from our two relational tables and
cast it to a person type object. Here's the object view:
create or replace view person_ov of
person_typ with object identifier( person_id ) as
select person_id,
last_name,
first_name,
middle_name,
birth_date,
mothers_maiden_name,
cast(
multiset (
select i.id,
i.id_type
from person_identifier i
where i.person_id = p.person_id ) as
person_identifier_tab ) as
identifiers
from person p
In this object view, we select data from the PERSON table and use the CAST and MULTISET
keywords to transform the related values in the PERSON_IDENTIFER table into a
person_identifier_tab object. The MULTISET keyword is used because the result of the
subquery has multiple rows. The CAST keyword takes the values and creates a
person_identifier_typ object for each row, which in turn becomes elements of the
person_identifier_tab object. The result of a query against the person_ov object view is a
person_typ object for each PERSON row in the database. Each person_typ object includes
any related person_identifiers.