Displaying and Updating Abstract Data Types with SQL Oracle Tips by Burleson Consulting

Displaying abstract data types with SQL

But there is to abstract data types than the ability to define them within a relational table.  Another useful feature of these data types is to be able to reference then from within an SQL query.  Since data types are normally comprised of sub-parts, the SQL must be extended to allow for the entire data type to be referenced as a single unit, while the SQL will automatically format all of the sub-components.  For example, the following SQL would display full_address data without the need to select each sub-type:

SELECT DISTINCT full_address FROM CUSTOMER;

This SQL would produce a listing like this:

STREET_ADDRESS               CITY_NAME STATE_ABBR            ZIP_CODE 

123 first street                           Minot               ND                              77363
44 west avenue                         Albuquerque    NM                              87112
8337 glenwood drive                Fairport            NY                              14450
3 wedgewood avenue               Denver             CO                              63533

Note that we would also have to alter our SQL if we wanted to select a component of full_address.  This is generally done by specifying the sub-component by inserting a "dot" between the higher-level data type and the data item that we wish to display:

 SELECT full_address.street_address
 WHERE
 full_address.zip_code LIKE '144%';

This would produce the following listing:

STREET_ADDRESS

8337 glenwood drive

Now that we understand how ANSI standard SQL has been extended for selecting rows that contain abstract data types, let's move on to look at how abstract data types are updated with SQL.

Updating abstract data types with SQL

Since user-defined data types contain sub-entities, special extensions will need to be added to relational SQL to allow for these aggregate data types to be updated.  This would involve partitioning the SQL UPDATE statement to allow for the sub-types to be specified.  For example, the SQL to update a customer address might look like this:

UPDATE CUSTOMER
      (full_address
         (
         VALUES (
                        '444 North avenue',
                        'West Lake'
                        'NJ'
                        83733
                        )
          )
      );
 

As we see from this example, the update statement is referencing only the full_address data type, but since it consists of sub-types, we must specify each of the sub-types separately in the update statement.

Nesting of abstract data types

Now lets take this concept one step further and consider now  abstract data types can be nested within other data types.  Remember, the primary reason for the introduction of user-defined data types is the ability to reuse these components in a consistent fashion across the entire database domain.  Since data types are created to encapsulate other data types we should be able to "nest" or embed user-defined data types within other user-defined data types.  For example, we could create a data type that would encapsulate  all of the data in a table, and then use this data type in a table definition:

 CREATE TYPE    customer_stuff (
    full_name           customer_name,
    home_address     customer_address
    business_address customer_address);

With the customer_stuff type defined, table definition becomes simple:

 CREATE TABLE CUSTOMER (customer_data           customer_stuff);

By using this type of user-defined data typing we are essentially duplicating the object-oriented concept of encapsulation.  That is, we are placing groups of related data types into a container that is completely self-contained and has the full authority of the innate relational data types such as int and char.

Displaying nested user-defined data types would be performed in the same fashion as the earlier sample queries with the exception that the target data types would require several "dots" to delimit the levels of nesting within the data structure.  For example, the following query will display the street_address for a customer.

Select customer_stuff.customer_name.zip_code
from customer
where customer_stuff.customer_name.zip_code like '144%';

Here we see that a reference to zip_code must be prefaced with customer_name since it participates in this data types.  Further, the customer_name data type is nested within the customer_stuff data types.  Hence, the proper SQL reference to zip_code is expressed as

customer_stuff.customer_name.zip_code

Now, let's move on to take a look at how data type usage is managed.

 

---