Named Row Data Type






Named Row Data Type

The third user-defined data type is the named row data type. With it, we can group values logically belonging to each other as one unit. For example, all values belonging to an address are grouped.

Figure. Create the named row data type called ADDRESS and use it in a CREATE TABLE statement.

CREATE   TYPE ADDRESS AS
        (STREET      CHAR(15) NOT NULL,
         HOUSENO     CHAR(4),
         POSTCODE    CHAR(6),
         TOWN        CHAR(10) NOT NULL)

CREATE   TABLE PLAYERS
        (PLAYERNO    INTEGER PRIMARY KEY,
         NAME        CHAR(15),
         :           :
         RESIDENCE   ADDRESS,
         PHONENO     CHAR(13),
         LEAGUENO    CHAR(4))

Explanation Instead of having to define four columns in the CREATE TABLE statement, only one will do: RESIDENCE. That means that in one row in the column RESIDENCE, not one value, but a row with four values, is stored. This row of four values has a name (or, in other words, is named) of ADDRESS, which explains the term named row. The column RESIDENCE is a composite column. For each column belonging to a named row data type, a NOT NULL specification can be included.

Of course, you can use a data type several times in the same CREATE TABLE statement, for example:

CREATE   TABLE PLAYERS
        (PLAYERNO          INTEGER PRIMARY KEY,
         :                 :
         RESIDENCE         ADDRESS,
         MAILING_ADDRESS   ADDRESS,
         HOLIDAY_ADDRESS   ADDRESS,
         PHONENO           CHAR(13),
         LEAGUENO          CHAR(4))

Working with composite columns affects the formulations of SELECT and other statements. We illustrate this with some examples.

9. Get the numbers and complete addresses of the players resident in Stratford.

SELECT   PLAYERNO, RESIDENCE
FROM     PLAYERS
WHERE    RESIDENCE.TOWN = 'Stratford'

The result is:

PLAYERNO  <=============== RESIDENCE ===========>
          STREET          HOUSENO  POSTCODE  TOWN
--------  --------------  -------  --------  ---------
       6  Haseltine Lane  80       1234KK    Stratford
      83  Magdalene Road  16A      1812UP    Stratford
       2  Stoney Road     43       3575NH    Stratford
       7  Edgecombe Way   39       9758VB    Stratford
      57  Edgecombe Way   16       4377CB    Stratford
      39  Eaton Square    78       9629CD    Stratford
     100  Haseltine Lane  80       1234KK    Stratford

Explanation In the SELECT clause, only one column has to be specified instead of four. Obviously, the result consists of five columns. The notation RESIDENCE.TOWN is new. This point notation indicates that only a part of the address is requested.

10. Get the numbers of the players living at the same address as player 6.

SELECT   OTHERS.PLAYERNO
FROM     PLAYERS AS P6, PLAYERS AS OTHERS
WHERE    P6.RESIDENCE = OTHERS.RESIDENCE
AND      P6.PLAYERNO = 6

Explanation: Instead of a join condition on four columns (STREET, HOUSENO, TOWN, and POSTCODE), one simple join condition, in which the composite column is used, is sufficient.

Casting of values is also important with named row data types. We give you an example of a SELECT and an INSERT statement.

11. Get the number and name of the player living at the address 39 Edgecombe Way, Stratford, with postcode 9758VB.

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    RESIDENCE =
         ADDRESS('Edgecombe Way', 39, '9758VB',
                 'Stratford')

Explanation: In this example, we can see clearly how the four values are cast into one ADDRESS value so that they can be compared with the column RESIDENCE.

12. Enter a new player.

INSERT INTO PLAYERS
       (PLAYERNO, NAME, ..., ADDRESS, PHONENO, LEAGUENO)
VALUES (6, 'Parmenter', ...,
        ADDRESS('Haseltine Lane', 80, '1234KK',
                'Stratford'), '070-476537', 8467)

Named row data types are usually defined on base and distinct data types, but they can also be "nested." An example is given next. First, the data type POSTCODE is defined, consisting of two components: a part of four digits and a part of two letters. Next, this new named row data type is used in the definition of the ADDRESS data type.

CREATE   TYPE POSTCODE AS
        (DIGITS     CHAR(4),
         LETTERS    CHAR(2))

CREATE   TYPE ADDRESS AS
        (STREET     CHAR(15) NOT NULL,
         HOUSENO    CHAR(4),
         POSTCODE   POSTCODE,
         TOWN       CHAR(10) NOT NULL)

13. Get the numbers and the full addresses of the players resident in postcode area 2501.

SELECT   PLAYERNO, RESIDENCE
FROM     PLAYERS
WHERE    RESIDENCE.POSTCODE.DIGITS = '2501'

14. Get the numbers and complete addresses of the players with postcode 1234KK.

SELECT   PLAYERNO, RESIDENCE
FROM     PLAYERS
WHERE    RESIDENCE.POSTCODE = POSTCODE('1234', 'KK')

Explanation: In the condition, two values are grouped into one value with a POSTCODE data type. A casting function is used for this.

In addition to the named row data type, some SQL products support the unnamed row data type. This data type also puts values together, but this group does not get a separate name.

CREATE   TABLE PLAYERS
        (PLAYERNO    INTEGER PRIMARY KEY,
         NAME        CHAR(15),
         :           :
         RESIDENCE   ROW (STREET     CHAR(15) NOT NULL,
                          HOUSENO    CHAR(4),
                          POSTCODE   CHAR(6),
                          TOWN       CHAR(10) NOT NULL),
         PHONENO     CHAR(13),
         LEAGUENO    CHAR(4))

Explanation: We can see that the four values are grouped together here. However, no data type is defined explicitly. The effect of an unnamed row data type on SELECT and other statements is the same as that of the named row data type. The difference, however, is that the specification cannot be reused in several places. If there is also a MAILING_ADDRESS column, we must define the four subcolumns once again.

For casting of values, the word ROW is used:

INSERT   INTO PLAYERS
        (PLAYERNO, NAME, ..., ADDRESS, PHONENO, LEAGUENO)
VALUES  (6, 'Parmenter', ...,
         ROW('Haseltine Lane', 80, '1234KK', 'Stratford'),
         '070-476537', 8467)

Exercise 33.2:

What is wrong in the following SELECT statement? (We assume that the situation is the same as in Figure.)

SELECT   RESIDENCE
FROM     PLAYERS
WHERE    RESIDENCE LIKE '12%'

Exercise 33.3:

Create the data type RESULT, consisting of two columns called WON and LOST, and use this new data type at the MATCHES table.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows