Views

SELECT statements, as we've seen particularly in the context of their use as subqueries can be seen as returning tables. The SQL CREATE VIEW command allows us essentially to "freeze" this table as a view. This view can then be used almost as though it were a full-fledged table.

It is important to note that a view does not capture the results of the query but rather captures the SELECT statement that created it. Changes to the underlying tables will, therefore, be reflected in the view.

Creating and Querying Views

To create a view, we use the following basic format:

 CREATE VIEW AS subquery 

The subquery can be simple or arbitrarily complex. It can be used to create a virtual table that contains a subset of data in the actual table, for example; this can be important for security. If we have a table of employee information that includes salary and other sensitive information but we want to make names, offices, and telephone numbers available to all other employees, we can create a view that includes just this information.

Let's take the fruit table as an example and create a view that includes just a few select columns: the fruit name, the quantity, and the price. This is the SELECT statement that gives us that result:

 SQL> SELECT FRUIT, QUANTITY, PRICE FROM FRUITS; FRUIT        QUANTITY      PRICE ---------- ---------- ---------- Apple              12         .5 Apple              12         .5 Mango              10        1.5 Mangosteen          5          2 Durian              2         15 Orange             10          1 6 rows selected. 

We create a view by using this as the subquery:

 SQL> CREATE VIEW FRUIT_VIEW AS   2  SELECT FRUIT, QUANTITY, PRICE FROM FRUITS; View created. 

Now we can use the view name in place of a table name in a query:

 SQL> SELECT * FROM FRUIT_VIEW; FRUIT        QUANTITY      PRICE ---------- ---------- ---------- Apple              12         .5 Apple              12         .5 Mango              10        1.5 Mangosteen          5          2 Durian              2         15 Orange             10          1 6 rows selected. 

The view is depicted in Figure 4-2.

Figure 4-2. Creating a view.

Another important use arises because of normalization. Information that is commonly used together is often found in separate tables. Rather than denormalizing the data so that it's easy to use, we can use a view to save us the trouble of having to enter complex joins.

We saw this example of a join above:

 SQL> SELECT C.OWNER, C.MODEL, A.MAKE   2    FROM ANTIQUE_CARS A, CAR_CLUB C   3    WHERE A.MODEL=C.MODEL; OWNER        MODEL        MAKE ------------ ------------ ------------ Lou          Bearcat      Stutz Bob          Terraplane   Hudson 

We can make this into a view:

 SQL> CREATE VIEW ANTIQUE_CAR_CLUB_VIEW AS   2     SELECT C.OWNER, C.MODEL, A.MAKE   3        FROM ANTIQUE_CARS A, CAR_CLUB C   4        WHERE A.MODEL=C.MODEL; View created. 

Now we can query this view as though it were a table too:

 SQL> SELECT * FROM ANTIQUE_CAR_CLUB_VIEW; OWNER        MODEL        MAKE ------------ ------------ ------------ Lou          Bearcat      Stutz Bob          Terraplane   Hudson 

Updating Views

Views are not necessarily limited to use in queries. In fact, standard SQL specifies that we should be able to update the underlying tables through a view. Unfortunately, this is not an easy thing to implement and Oracle places some restrictions on using a view to insert, update, or delete rows.

In general, views are updateable only if it is possible to map updates to the underlying table or tables in a straightforward, unambiguous way. This isn't possible if the view contains aggregate functions or GROUP BY clauses, for example. In the case of joins, this is possible only if the join meets certain specific requirements in particular, one of the join columns must have a unique index. The ANTIQUE_CAR_CLUB_VIEW does not meet this requirement. Even though it seems that it should be unambiguously possible to make this change, Oracle balks:

 SQL> UPDATE ANTIQUE_CAR_CLUB_VIEW   2    SET OWNER = 'Ted' WHERE MAKE = 'Hudson';   SET OWNER = 'Ted' WHERE MAKE = 'Hudson'       * ERROR at line 2: ORA-01779: cannot modify a column which maps to a non key-preserved table 

Our first view example, using the FRUITS table, isn't disqualified by Oracle's restrictions, however. We can insert a row:

 SQL> INSERT INTO FRUIT_VIEW VALUES('Tangerine', 10, 1.25); 1 row created. 

and see that it is added to the underlying table:

 SQL> SELECT * FROM FRUITS; FRUIT      COLOR        QUANTITY      PRICE PICKED ---------- ---------- ---------- ---------- --------- Apple      Green              12         .5 12-SEP-02 Apple      Red                12         .5 15-SEP-02 Mango      Yellow             10        1.5 22-SEP-02 Mangosteen Purple              5          2 25-SEP-02 Durian                         2         15 Orange     Orange             10          1 28-AUG-02 Tangerine                     10       1.25 7 rows selected. 

Obviously, because our view contains only the FRUIT, QUANTITY, and PRICE columns, it's not possible to insert values for the COLOR and PICKED columns using this view.

Let's delete the row we just added:

 SQL> DELETE FROM FRUIT_VIEW   2    WHERE FRUIT='Tangerine'; 1 row deleted. 

Once again, let's take a look at the underlying table to see that it really got updated:

 SQL> SELECT * FROM FRUITS; FRUIT      COLOR        QUANTITY      PRICE PICKED ---------- ---------- ---------- ---------- --------- Apple      Green              12         .5 12-SEP-02 Apple      Red                12         .5 15-SEP-02 Mango      Yellow             10        1.5 22-SEP-02 Mangosteen Purple              5          2 25-SEP-02 Durian                         2         15 Orange     Orange             10          1 28-AUG-02 6 rows selected. 

To get rid of a view, we use a DROP statement:

 SQL> DROP VIEW FRUIT_VIEW; View dropped. 

Naturally, this only gets rid of the view and has no effect on the underlying table!

Views are an important tool for making a database easier to use. As we saw, queries, subqueries, and joins can be powerful but complicated, hard to use, and hard to debug. (And it can also be hard to prove that the results are actually what you intend.) Views are one way of hiding this difficulty and complexity.