SQL and Relational Theory, 2nd Edition

ROW AND TABLE TYPES IN SQL

Here repeated from the section SCALAR vs. NONSCALAR TYPES is an example of a tuple variable definition:

     VAR STV TUPLE { STATUS INTEGER , SNO CHAR , CITY CHAR , SNAME CHAR } ;

The expression TUPLE {...} here is, as you’ll recall, an invocation of the TUPLE type generator. SQL has a corresponding ROW type generator (though it calls it a type constructor). Here’s an SQL analog of the foregoing Tutorial D example:

     DECLARE SRV /* SQL row variable */
             ROW ( SNO    VARCHAR(5) ,
                   SNAME  VARCHAR(25) ,
                   STATUS INTEGER ,
                   CITY   VARCHAR(20) ) ;

Unlike tuples, however, rows in SQL have a left to right ordering to their components;^[36] in the case at hand, there are actually 24 (= 4 * 3 * 2 * 1) different row types all consisting of the same four components (!).

SQL also supports row assignment. Recall this Tutorial D tuple assignment:

     STV := TUPLE FROM ( S WHERE SNO = 'S1' ) ;

Here’s an SQL row assignment analog:

     SET SRV = ( S WHERE SNO = 'S1' ) ;

The expression on the right side here is a row subquery—i.e., it’s a table expression, syntactically speaking, but it’s one that’s acting as a row expression. That’s why there’s no explicit counterpart to Tutorial D’s TUPLE FROM (see the discussion of subqueries and coercion in the section “SQL Type Checking and Coercion” a couple of pages back).

Row assignments are also involved, in effect, in SQL UPDATE statements (see Chapter 3).

Turning to tables: Interestingly, SQL doesn’t really have a TABLE type generator (or type constructor, as ...