A view is a logical subset of one or more base tables or views where columns are chosen by naming them and rows are chosen through specified conditions relating to column values.
Views are created, for example, so that users who need not see all the data in a single table are shown only the parts of the table that interest them (restriction views). Views can also be created as a combination of a number of columns from several different tables (join views).
Operations on views are actually performed on the underlying base tables. Certain view definitions do not allow data to be changed in the view (read-only views). See Updatable Views for further details.
View names can be up to 128 characters long. Views are defined in terms of a SELECT statement; the result of the SELECT statement forms the contents of the view. There are no restrictions on which select statements that can be used in a view definition.
The example database does not contain any view definitions. Two examples are given below.
Creating a View
Create a restriction view of the BOOK_GUEST table called RECEPTION containing limited information for the hotel reception (reservation number, customer name, check-in date and room number):CREATE VIEW RECEPTION (RESERVATION, FNAME, LNAME, DATE, ROOM) AS SELECT RESERVATION, GUEST_FNAME, GUEST_LNAME, CHECKIN, ROOMNO FROM BOOK_GUEST;
Create a join view listing the billing details for each reservation:CREATE VIEW CHARGE_DESCRIPTION AS SELECT RESERVATION, COST, DESCRIPTION FROM BILL, CHARGES WHERE BILL.CHARGE_CODE = CHARGES.CHARGE_CODE;
If the view definition does not include a list of column names, the columns in the view will be named after the columns listed in the SELECT clause.
Check options can be used in updatable view definitions to limit the data that can be inserted into the view. If a check option is specified, data which does not fulfill the definition of the view cannot be inserted into the view.CREATE VIEW GUEST_VIEW AS SELECT RESERVATION, HOTELCODE, GUEST_FNAME, GUEST_LNAME, CHECKIN, ROOMNO FROM BOOK_GUEST WHERE HOTELCODE = 'STG' OR HOTELCODE = 'WINS' WITH CHECK OPTION;
The check option in the view definition (WITH CHECK OPTION) means that no new rows may be inserted into the view if the value for the HOTELCODE column is not STG or WINS.
If there is an instead of trigger defined for the view, the WITH CHECK OPTION does not have any effect.
Creating Views Based on Other Views
Views can be based on other views. When a view is created based upon another view or views, the original view's limitations are carried over to the new view.CREATE VIEW NEW_VIEW AS SELECT RESERVATION, HOTELCODE, GUEST_FNAME, GUEST_LNAME FROM GUEST_VIEW WHERE RESERVATION > 1385;
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40