Views | Oracle Database 10g SQL (Osborne ORACLE Press Series)

A view is basically a predefined query on one or more tables (known as base tables). Retrieving information from a view is done in the same manner as retrieving from a table: you simply include the view in the FROM clause of a SELECT statement. With some views you can also perform DML operations on the base tables.

Note	Views don t store data, they only access rows in the base tables.

You ve already seen some examples of retrieving information from views when you selected rows from the data dictionary, which is accessed through views. For example, user_tables, user_sequences, and user_indexes are all views.

Views offer you several benefits, such as:

Only allow a user to retrieve data through a view. This allows you to hide the underlying base tables on which a view is built from an end user .
Write complex queries as a view. This allows you to hide complexity from an end user.
Only allow a view to access certain rows in the base tables. This allows you to implement another layer of security and allows you to hide rows from an end user.

In this section, you ll learn how to:

Create and use a view
Get details of a view from the data dictionary
Modify a view
Drop a view

Creating and Using a View

You create a view using CREATE VIEW , which has the following simplified syntax:

 CREATE [OR REPLACE] VIEW [{FORCE  NOFORCE}] VIEW  view_name  [(  alias_name  [,  alias_name  ...])] AS  subquery  [WITH {CHECK OPTION  READ ONLY} CONSTRAINT  constraint_name  ];

where

OR REPLACE specifies the view is to replace an existing view if present.
FORCE specifies the view is to be created even if the base tables don t exist.
NOFORCE specifies the view is not to be created if the base tables don t exist; NOFORCE is the default.
view_name specifies the name of the view.
alias_name specifies the name of an alias for an expression in the subquery. There must be the same number of aliases as there are expressions in the subquery.
subquery specifies the subquery that retrieves from the base tables. If you ve supplied aliases, you can use those aliases in the list after the SELECT clause.
WITH CHECK OPTION specifies that only the rows that would be retrieved by the subquery can be inserted, updated, or deleted. By default, rows are not checked that they are retrievable by the subquery before they are inserted, updated, or deleted.
constraint_name specifies the name of the WITH CHECK OPTION or READ ONLY constraint.
WITH READ ONLY specifies that rows may only read from the base tables.

There are two basic types of views:

Simple views, which contain a subquery that retrieves from one base table
Complex views, which contain a subquery that:
- Retrieves from multiple base tables
- Groups rows using a GROUP BY or DISTINCT clause
- Contains a function call

You ll learn how to create and use these types of views in the following sections.

Creating and Using Simple Views

Simple views access one base table. The following example creates a view named cheap_products_view whose subquery only retrieves products where the price is less than $15:

 CREATE VIEW cheap_products_view AS SELECT * FROM products WHERE price < 15;

The next example creates a view named employees_view whose subquery retrieves all the columns from the employees table except salary:

 CREATE VIEW employees_view AS SELECT employee_id, manager_id, first_name, last_name, title FROM employees;

Performing a SELECT on a View

Once you ve created a view, you can use it to access the base table. The following example performs a SELECT on cheap_products_view:

  SELECT product_id, name, price   FROM cheap_products_view;  PRODUCT_ID NAME                                PRICE ---------- ------------------------------ ----------          4 Tank War                            13.95          6 2412: The Return                    14.95          7 Space Force 9                       13.49          8 From Another Planet                 12.99          9 Classical Music                     10.99         11 Creative Yell                       14.99         12 My Front Line                       13.49

The next example retrieves from employees_view:

  SELECT *   FROM employees_view;  EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME  TITLE ----------- ---------- ---------- ---------- -------------           1            James      Smith      CEO           2          1 Ron        Johnson    Sales Manager           3          2 Fred       Hobbs      Salesperson           4          2 Susan      Jones      Salesperson

Performing an INSERT Using a View

You can also perform DML operations using cheap_products_view. The following example performs an INSERT into cheap_products_view and then retrieves the row:

  INSERT INTO cheap_products_view (   product_id, product_type_id, name, price   ) VALUES (   13, 1, 'Westerrn Front', 13.50   );  1 row created.  SELECT *   FROM cheap_products_view   WHERE product_id = 13;  PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- ------------------------------ DESCRIPTION                                             PRICE -------------------------------------------------- ----------         13               1 Westerrn Front                                                          13.5

Note	You can only perform DML operations with simple views. Complex views don t support DML.

Because cheap_products_view didn t use WITH CHECK OPTION , you can insert, update, and delete rows that aren t retrievable by the subquery. The following example inserts a row using cheap_products_view whose price is $16.50, which is greater than $15 and therefore not retrievable by the subquery:

  INSERT INTO cheap_products_view (   product_id, product_type_id, name, price   ) VALUES (   14, 1, 'Eastern Front', 16.50   );  1 row created.  SELECT *   FROM cheap_products_view   WHERE product_id = 14;  no rows selected

The view employees_view contains a subquery that selects every column from employees except salary. When you perform an INSERT using employees_view, the salary column in the employees base table will be set to null; for example:

  INSERT INTO employees_view (   employee_id, manager_id, first_name, last_name, title   ) VALUES (   5, 1, 'Jeff', 'Jones', 'CTO'   );  1 row created.  SELECT *   FROM employees   WHERE employee_id = 5;  EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME  TITLE            SALARY ----------- ---------- ---------- ---------- ------------- ---------           5          1 Jeff       Jones      CTO

Notice salary is null in the employees table for the new row.

Creating a View with a CHECK OPTION Constraint

You can specify that DML operations on a view must satisfy the subquery by adding a CHECK OPTION constraint to the view. For example, the following CREATE VIEW statement creates a view named cheap_products_view2 that has a CHECK OPTION constraint:

 CREATE VIEW cheap_products_view2 AS SELECT * FROM products WHERE price < 15 WITH CHECK OPTION CONSTRAINT cheap_products_view2_price;

The next example attempts to insert a row using cheap_products_view2 with a price of $19.50. Notice the database returns an error because the row isn t retrievable by the view:

  INSERT INTO cheap_products_view2 (   product_id, product_type_id, name, price   ) VALUES (   15, 1, 'Southern Front', 19.50   );  INSERT INTO cheap_products_view2 (             * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation

Creating a View with a READ ONLY Constraint

You can make a view read only by adding a READ ONLY constraint to the view. For example, the following CREATE VIEW statement creates a view named cheap_products_view3 that has a READ ONLY constraint:

 CREATE VIEW cheap_products_view3 AS SELECT * FROM products WHERE price < 15 WITH READ ONLY CONSTRAINT cheap_products_view3_read_only;

The following example attempts to insert a row using cheap_products_view3. Notice the database returns an error because the view is read only and doesn t allow DML operations:

  INSERT INTO cheap_products_view3 (   product_id, product_type_id, name, price   ) VALUES (   16, 1, 'Northen Front', 19.50   );  product_id, product_type_id, name, price   * ERROR at line 2: ORA-01733: virtual column not allowed here

Getting Information on View Definitions

You get information on view definitions using the DESCRIBE command and the data dictionary. The following example uses the DESCRIBE command on cheap_products_view3:

  DESCRIBE cheap_products_view3  Name                                      Null?    Type ----------------------------------------- -------- ------------ PRODUCT_ID                                NOT NULL NUMBER(38) PRODUCT_TYPE_ID                                    NUMBER(38) NAME                                      NOT NULL VARCHAR2(30) DESCRIPTION                                        VARCHAR2(50) PRICE                                              NUMBER(5,2)

You can get information about your views from user_views. Table 10-9 describes some of the columns in user_views.

Table 10-9: Some Columns in user_views
Column	Type	Description
view_name	VARCHAR2(30)	Name of the view.
text_length	NUMBER	Number of characters in the view's subquery.
text	LONG	Text of the view's subquery.

Note	You can get information on all the indexes you have access to using all_views.

The following example retrieves the view_name, text_length, and text from user_views:

  SELECT view_name, text_length, text   FROM user_views;  VIEW_NAME                      TEXT_LENGTH ------------------------------ ----------- TEXT ------------------------------------------------------------------ CHEAP_PRODUCTS_VIEW                     97 SELECT "PRODUCT_ID","PRODUCT_TYPE_ID","NAME","DESCRIPTION","PRICE" FROM products CHEAP_PRODUCTS_VIEW2                   116 SELECT "PRODUCT_ID","PRODUCT_TYPE_ID","NAME","DESCRIPTION","PRICE" FROM products CHEAP_PRODUCTS_VIEW3                   112 SELECT "PRODUCT_ID","PRODUCT_TYPE_ID","NAME","DESCRIPTION","PRICE" FROM products EMPLOYEES_VIEW                          75 SELECT employee_id, manager_id, first_name, last_name, title FROM employees

Getting Information on View Constraints

Earlier you saw you can add CHECK OPTION and READ ONLY constraints to a view. The view cheap_products_view2 contained a CHECK OPTION constraint to ensure the price was less than $15. The view cheap_products_view3 contained a READ ONLY constraint to prevent modifications to the rows in the base table.

You get information on view constraints from user_constraints; for example:

  SELECT   constraint_name, constraint_type, status, deferrable, deferred   FROM user_constraints   WHERE table_name IN ('CHEAP_PRODUCTS_VIEW2', 'CHEAP_PRODUCTS_VIEW3');  CONSTRAINT_NAME                C STATUS   DEFERRABLE     DEFERRED ------------------------------ - -------- -------------- --------- CHEAP_PRODUCTS_VIEW2_PRICE     V ENABLED  NOT DEFERRABLE IMMEDIATE CHEAP_PRODUCTS_VIEW3_READ_ONLY O ENABLED  NOT DEFERRABLE IMMEDIATE

The constraint_type for CHEAP_PRODUCTS_VIEW2_PRICE is V , which from Table 10-3 shown earlier corresponds to a CHECK OPTION constraint. The constraint_type for CHEAP_PRODUCTS_VIEW3_READ_ONLY is O , which corresponds to a READ ONLY constraint.

Creating and Using Complex Views

Complex views contain subqueries that:

Retrieve from multiple base tables
Group rows using a GROUP BY or DISTINCT clause
Contain a function call

The following example creates a view named products_and_types_view whose subquery performs a full outer join on the products and product_types tables using the SQL/92 syntax:

 CREATE VIEW products_and_types_view AS SELECT p.name product_name, pt.name product_type_name, p.price FROM products p FULL OUTER JOIN product_types pt USING (product_type_id);

The following example queries products_and_types_view:

  SELECT *   FROM products_and_types_view;  PRODUCT_NAME                   PRODUCT_TY      PRICE ------------------------------ ---------- ---------- Eastern Front                  Book             16.5 Westerrn Front                 Book             13.5 Chemistry                      Book               30 Modern Science                 Book            19.95 2412: The Return               Video           14.95 Z Files                        Video           49.99 Tank War                       Video           13.95 Supernova                      Video           25.99 From Another Planet            DVD             12.99 Space Force 9                  DVD             13.49 Creative Yell                  CD              14.99 Pop 3                          CD              15.99 Classical Music                CD              10.99 My Front Line                                  13.49                                Magazine

The next example creates a view named employee_salary_grades_view whose subquery uses an inner join to retrieve the salary grades for the employees, which is determined using the BETWEEN operator:

 CREATE VIEW employee_salary_grades_view AS SELECT e.first_name, e.last_name, e.title, e.salary,  sg.salary_grade_id FROM employees e INNER JOIN salary_grades sg ON e.salary BETWEEN sg.low_salary AND sg.high_salary;

The following example queries products_average_view:

  SELECT *   FROM employee_salary_grades_view;  FIRST_NAME LAST_NAME  TITLE                    SALARY SALARY_GRADE_ID ---------- ---------- -------------------- ---------- --------------- Fred       Hobbs      Salesperson              150000               1 Susan      Jones      Salesperson              500000               2 Ron        Johnson    Sales Manager            600000               3 James      Smith      CEO                      800000               4

The next example creates a view named product_average_view whose subquery uses

A WHERE clause to filter the rows from the products table to those whose price is less than $15
A GROUP BY clause to group the remaining rows by the product_type_id column

A HAVING clause to filter the row groups to those whose average price is greater than $13

 CREATE VIEW product_average_view AS SELECT product_type_id, AVG(price) average_price FROM products WHERE price < 15 GROUP BY product_type_id HAVING AVG(price) > 13;

The following example queries product_average_view:

  SELECT *   FROM product_average_view;  PRODUCT_TYPE_ID AVERAGE_PRICE --------------- -------------               1          13.5               2         14.45               3         13.24                         13.49

Modifying a View

You can completely replace a view using CREATE OR REPLACE ; you can alter the constraints on a view using ALTER VIEW. The following example uses CREATE OR REPLACE to replace product_average_view:

 CREATE OR REPLACE VIEW product_average_view AS SELECT product_type_id, AVG(price) average_price FROM products WHERE price < 12 GROUP BY product_type_id HAVING AVG(price) > 11;

The next example uses ALTER VIEW to drop the cheap_products_view2_price constraint from cheap_products_view2:

 ALTER VIEW cheap_products_view2 DROP CONSTRAINT cheap_products_view2_price;

Dropping a View

You drop a view using DROP VIEW. The following example drops cheap_products_view2:

 DROP VIEW cheap_products_view2;

Note	Go ahead and rerun store_schema.sql to recreate the store tables so that your queries match mine in the rest of this book.