July 15, 2011, 10:26 a.m.
posted by novinick
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.
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.
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.
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.
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.
13. Get the numbers and the full addresses of the players resident in postcode area 2501.
14. Get the numbers and complete addresses of the players with postcode 1234KK.
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)