The first example is a view derived from the titles table. Suppose you are interested only in books priced higher than $15 and for which an advance of more than $5000 was paid. This straightforward select statement finds the rows that qualify:
select * from titles where price > $15 and advance > $5000
Now, suppose you have a lot of retrieval and update operations to do on this collection of data. You can, of course, combine the conditions shown in the previous query with any command that you issue. However, for convenience, you can create a view that displays only the records of interest:
create view hiprice as select * from titles where price > $15 and advance > $5000
When Adaptive Server receives this command, it does not actually execute the select statement that follows the keyword as. Instead, it stores the select statement, which is the definition of the view hiprice, in the system table syscomments. Entries are also made in sysobjects and in syscolumns for each column included in the view.
Now, when you display or operate on hiprice, Adaptive Server combines your statement with the stored definition of hiprice. For example, you can change all the prices in hiprice just as you can change any other table:
update hiprice set price = price * 2
Adaptive Server finds the view definition in the system tables and converts the update command into the statement:
update titles set price = price * 2 where price > $15 and advance > $5000
In other words, Adaptive Server knows from the view definition that the data to be updated is in titles. It also knows that it should increase the prices only in the rows that meet the conditions on the price and advance columns given in the view definition and those in the update statement.
Having issued the update to hiprice, you can see its effect either in the view or in the titles table. Conversely, if you had created the view and then issued the second update statement, which operates directly on the base table, the changed prices would also be visible through the view.
Updating a view’s underlying table in such a way that different rows qualify for the view affects the view. For example, suppose you increase the price of the book You Can Combat Computer Stress to $25.95. Since this book now meets the qualifying conditions in the view definition statement, it is considered part of the view.
However, if you alter the structure of a view’s underlying table by adding columns, the new columns do not appear in a view that is defined with a select * clause unless you drop and redefine the view. This is because the asterisk in the original view definition considers only the original columns.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
