CREATE VIEW statement
Derby Reference Manual
49
customerID INT REFERENCES customer( customerID ),
totalPrice typeSchema.price
);
Although UDTs have no natural order, you can use generated columns to provide useful
sort orders:
ALTER TABLE order
ADD COLUMN normalizedValue DECIMAL( 31, 5 ) GENERATED ALWAYS AS
( convert( 'EUR', TIMESTAMP('2005-01-01 09:00:00'), totalPrice ) );
CREATE INDEX normalizedOrderPrice ON order( normalizedValue );
You can use factory functions to construct UDTs. For example:
INSERT INTO order( customerID, totalPrice )
VALUES ( 12345,
makePrice( 'USD',
CAST( 9.99 AS DECIMAL( 31, 5 ) ),
TIMESTAMP('2009-10-16 14:24:43') ) );
Once a UDT column has been populated, you can use it in other INSERT and UPDATE
statements. For example:
INSERT INTO backOrder SELECT * from order;
UPDATE order SET totalPrice = ( SELECT todaysDiscount FROM discount );
UPDATE order SET totalPrice = adjustForInflation( totalPrice );
Using functions, you can access fields inside UDTs in a SELECT statement:
SELECT getCurrencyCode( totalPrice ) from order;
You can use JDBC API setObject() and getObject() methods to store and retrieve values
of UDTs. For example:
PreparedStatement ps = conn.prepareStatement( "SELECT * from order" );
ResultSet rs = ps.executeQuery();
while( rs.next() )
{
int orderID = rs.getInt( 1 );
int customerID = rs.getInt( 2 );
Price totalPrice = (Price) rs.getObject( 3 );
...
}
CREATE VIEW statement
Views are virtual tables formed by a query. A view is a dictionary object that you can use
until you drop it. Views are not updatable.
If a qualified view name is specified, the schema name cannot begin with SYS.
A view operates with the privileges of the owner of the view. See "Using SQL standard
authorization" and "Privileges on views, triggers, and constraints" in the Derby
Developer's Guide for details.
The view owner automatically gains the SELECT privilege on the view. The SELECT
privilege cannot be revoked from the view owner. The
gains the SELECT privilege on the view and is able to grant this privilege to other users.
The SELECT privilege cannot be revoked from the database owner.
The view owner can only grant the SELECT privilege to other users if the view owner
also owns the underlying objects.
If the underlying objects that the view references are not owned by the view owner, the
view owner must be granted the appropriate privileges. For example, if the authorization
