Creating and Using a View : Create View « View « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. View
3. Create 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

1. OR REPLACE specifies the view is to replace an existing view if present.
2. FORCE specifies the view is to be created even if the base tables don't exist.
3. NOFORCE specifies the view is not to be created if the base tables don't exist; NOFORCE is the default.
4. alias_name specifies the name of an alias for an expression in the subquery.
5. There must be the same number of aliases as there are expressions in the subquery.
6. subquery specifies the subquery that retrieves from the base tables.
7. If you've supplied aliases, you can use those aliases in the list after the SELECT clause.
8. WITH CHECK OPTION specifies that only the rows that would be retrieved by the subquery can be inserted, updated, or deleted.
9. By default, rows are not checked that they are retrievable by the subquery before they are inserted, updated, or deleted.
10. constraint_name specifies the name of the WITH CHECK OPTION or READ ONLY constraint.
11. 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:

1. Retrieves from multiple base tables
2. Groups rows using a GROUP BY or DISTINCT clause
3. Contains a function call

Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813