This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

Let us begin by making a Table, one of whose Columns is a UDT:

   book_column book_udt,
   serial_number INTEGER);

You can use a UDT wherever the syntax requires <data type>. So far so good. Now let's INSERT a new row into the Table. This won't be so simple.

BEGIN                                  /* compound statement: start */
  DECLARE u book_udt;                  /* temporary variable declaration */
  SET u = book_udt();                  /* constructor function */
  SET u = u.title('The Compleat SQL'); /* mutator function */
  SET u = u.buying_price(10.00);       /* mutator function */
  SET u = u.selling_price(20.00);      /* mutator function */
  INSERT INTO T VALUES (u,1);          /* ordinary-looking SQL statement */
END;                                   /* compound statement: end */

To understand the above compound statement, you'll need to look closely at the declaration and the four function calls that precede the INSERT statement. They are simple things, but the terminology is fancy.

First: "DECLARE u book_udt;" is a declaration of a temporary variable named u. Nothing new about that (if you remember your PSM chapter), but it shows that variables too can be based on UDTs instead of predefined <data type>s.

Second: "u = book_udt();" is a constructor function. The function we're calling is named BOOK_UDT, the same as the UDT. That's not a coincidence. The DBMS's job is to create a constructor function automatically. When the "CREATE TYPE book_udt1" was executed, this SQL statement happened implicitly:

   CREATE FUNCTION book_udt ()
   RETURNS book_udt

In this CREATE FUNCTION statement, the RETURN V, is a value of type book_udt, with all Attribute instance values equal to their default! What does that mean? It means that when we call the book_udt function, the return is a "default value" of the book_udt type. That's useful, so it's good to know that the DBMS makes a constructor function for every UDT.

Third: "u.title('The Compleat SQL');" is a mutator function. The function we're calling is named TITLE, the same as the first Attribute in the UDT. Once again, this is not a coincidence. The DBMS's job is to create a mutator function automatically. When the "CREATE TYPE book_udt1" was executed, this SQL statement also happened implicitly:

  (attribute CHAR(40))
      RETURNS book_udt

This statement defines a "method" (or "method function") because it is a component of a UDT. To invoke the method, we just have to say "<udt instance>.<method name> ( <new value> )" and the new value is a CHAR(40) string, which is compatible with the Attribute's definition. The reason that this method is called a mutator function is that it changes the value in the object instance. So now the title in u is 'The Compleat SQL'.

You might wonder: why all the fuss? If the whole point is to set U.TITLE to a value, why not just:

SET u.title = 'The Compleat SQL';      /* not a good idea! */

Using a SET statement like this would violate an object-oriented principle called "encapsulation", according to which: the only access to Attributes is via their functions (methods) so SET is not legal. If you're used to Windows programming, you'll see the benefits of encapsulation: nothing can access the storage except pre-approved routines which change if the storage method changes.

Fourth and Fifth: "u.buying_price(10.00);" and "u.selling_price(20.00);" are two more mutator functions. When the UDT was created, the DBMS made methods for BUYING_PRICE and FOR SELLING_PRICE too, just as it must do for every Attribute in a UDT.

The bottom line is: constructor functions are the ordinary way to make new instances of UDTs; mutator functions are the ordinary way to change Attributes in UDT instances. Using the constructor and the mutators, we have been able to set up a fully-initialized UDT instance u with the contents we want.

Sixth: "INSERT INTO T VALUES (u,1);" is dènouement. Clearly, it puts a value into Table T. What the value looks like, we have no idea: it's encapsulated. We do know, though, that what we put in was {'The Compleat SQL', 10.00, 20.00}. Insertion phase complete.

Let us now get something back out of the UDT. You've probably guessed already that we aren't going to just say:

SELECT book_column, serial_number

To retrieve from a UDT, we need yet another function the opposite of a mutator to get the Attribute values out of BOOK_COLUMN into a representation that we can read. Such a function is called an observer function and, once again, the DBMS makes observer functions implicitly at the time that a UDT is created one for each Attribute, like this:


Since the observer method exists to help us get values out, we can use them in SELECT statements. To get all the values, we can use this SQL statement:

SELECT book_column.title(),
WHERE  serial_number > 0;

In summary, these are the functions associated with our example UDT BOOK_UDT:

  • One constructor function, named BOOK_UDT.
  • Three mutator functions, named TITLE, BUYING_PRICE, and SELLING_PRICE.
  • Three observer functions, also named TITLE, BUYING_PRICE and SELLING_PRICE. (Actually the above is only for the "default case", we'll worry about options later.) As the examples indicated, constructors and mutators and observers are all we need for simple storage and retrieval operations.

Routine Names and Signatures

One of the strengths of the Object/Relational system is that all the functions have the same name as the Attribute they're associated with, which makes them easy to remember. But at first there's a little confusion too! How do we tell them apart? If you look closely at these three references, you'll see the differences:

  • title the name of an Attribute.
  • title() the name of an observer method.
  • title('The Compleat SQL') the name of a mutator method.

The DBMS distinguishes between names the same way that you do. The first distinction is easy: if it's got parentheses after it, then it must be a routine name. The next distinction is that title() has no arguments, while title('The Compleat SQL') has one argument. That's the difference between an observer and a mutator. Let's express this latter distinction as a general set of rules.

Rule 1: It is legal for two routines to be "in the same name class" that is, two routines with the same name may be in the same Schema.

Rule 2: Routines are distinguishable by their category. The four categories are "procedures", "functions", and two types of "method".

Rule 3: Routines in the same category are distinguishable by the count of parameters, and the declared type of those parameters, in their parameter list.

Because the name alone isn't enough, we have to come up with a different term to describe "what it is that distinguishes routines". The term that is in common use is "signature". The signature of a routine is the routine name plus the routine category plus the parameter-data-type list, and it's what the DBMS uses when it needs to figure out what routine you're really trying to call.


Comments loading...