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.

There are several other SQL statements and expression which are useful, but not vital, when working with Object/Relational SQL. All of these statements are simply analogs of statements we have already seen; only the type of Schema Object is different. We therefore limit ourselves to noting their existence here, in the hope that you'll find the details to be intuitive.

ALTER TYPE Statement

The ALTER TYPE statement lets you change the definition of a UDT. The required syntax for the ALTER TYPE statement is as follows.

ALTER TYPE <UDT name> <alter type action>

   <alter type action> ::=
   ADD ATTRIBUTE <Attribute definition> |
   DROP ATTRIBUTE <Attribute name>

CREATE METHOD Statement

The CREATE METHOD statement lets you make a new method it's actually a special form of the CREATE FUNCTION statement. The required syntax for the CREATE METHOD statement is as follows.

CREATE [ INSTANCE | STATIC ] METHOD <routine name>
(SQL parameter declaration list)
RETURNS <data type>
FOR <UDT name>
[ SPECIFIC <specific name> ]
<routine body>

(See our chapter on procedures for a definition of <routine body>.)

A method is a function which is associated with a UDT. Methods and functions can look quite different, even when they're the same thing. Consider these two examples:

CREATE FUNCTION f
(book_udt)
RETURNS FLOAT

CREATE METHOD f
()
RETURNS FLOAT
FOR book_udt

These examples shorn of unnecessary detail illustrate a confusing fact: the function and the method are the same thing! When you want to list a method's parameters, you should "augment" the parameter list by adding one parameter, called the "self" parameter, in ordinal position 1.

CREATE TRANSFORM Statement

The CREATE TRANSFORM statement lets you make a method that will be used in casting for host languages. The required syntax for the CREATE TRANSFORM statement is as follows.

CREATE {TRANSFORM | TRANSFORMS} FOR <UDT name>
<group name>
(<transform element> [ {,<transform element>} ... ])

   <transform element> ::=
   TO SQL WITH <specific routine designator> |
   FROM SQL WITH <specific routine designator>

A transform is an SQL-invoked function that is automatically invoked when you transfer UDT values to and from a host language program. It identifies one or two functions each identified by a <group name> (the name is either an <identifier> of the <keyword> DEFAULT).

The transform's TO SQL function casts a predefined <data type> value to a UDT value and gets invoked whenever a UDT value is passed to the DBMS by a host language program or external routine. The transform's FROM SQL function casts a UDT value to a predefined <data type> value and gets invoked whenever a UDT value is passed from the DBMS to a host language program or external routine.

DROP CAST Statement

The DROP CAST statement lets you destroy a user-defined cast. The required syntax for the DROP CAST statement is as follows.

DROP CAST (<source type> AS <target type> {CASCADE | RESTRICT}

DROP ORDERING Statement

The DROP ORDERING statement lets you destroy an ordering for a UDT. The required syntax for the DROP ORDERING statement is as follows.

DROP ORDERING FOR <UDT name> {CASCADE | RESTRICT}

DROP TRANSFORM Statement

The DROP TRANSFORM statement lets you destroy a transform. The required syntax for the DROP TRANSFORM statement is as follows.

DROP TRANSFORM {ALL | <group name>} FOR <UDT name> {CASCADE | RESTRICT}

DROP TYPE Statement

The DROP TYPE statement lets you destroy a UDT. The required syntax for the DROP TYPE statement is as follows.

DROP TYPE <UDT name> {CASCADE | RESTRICT}

NEW Statement

The NEW statement lets you invoke a method on a newly-constructed value of a structured type. The required syntax for the NEW statement is as follows.

NEW <method invocation>

TREAT Statement

The TREAT statement lets you modify the declared type of a structured type expression to a type of one of its supertypes. The required syntax for the TREAT statement is as follows.

TREAT (<subtype expression> AS <target UDT>)

DEREF Function

The DEREF function lets you obtain the data value referenced by a <reference type>. The required syntax is as follows (for details, see Chapter 12 "Reference Types").

<reference resolution> ::=
DEREF (reference argument)

SPECIFICTYPE Function

The SPECIFICTYPE function returns an SQL_TEXT character string containing the fully-qulified name of the UDT to which a given value belongs. The required syntax is as follows.

<specifictype function> ::=
SPECIFICTYPE (UDT value expression)

Dereference Operation

The <dereference operation> allows you to access a Column of the row identified by a REF value. The required syntax is as follows (for details, see Chapter 12 "Reference Types").

<dereference operation> ::=
reference_argument -> <Attribute name>

TYPE Predicate

The TYPE Predicate returns TRUE if a given value is within the set of values defined by a UDT (or by a list of UDTs which are all in the same subtype family). The required syntax is as follows.

<type predicate> ::=
<UDT value expresion> IS [ NOT ] OF
([ ONLY ] <UDT-name-1> [ {,<UDT-name-2>} ... ])

Comments

Comments loading...