O'Reilly logo

SQL and Relational Theory, 2nd Edition by C.J. Date

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required