Creates a view.

Syntax

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ]
  AS query

Description

You can use the CREATE VIEW command to define a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.

CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, the name is replaced.

If a schema name is specified (for example, CREATE VIEW myschema.myview...), the view is created in the specified schema. Otherwise, it is created in the current schema. The view name must be different from the name of any other view, table, sequence, or index in the same schema.

Parameters

Parameter Description
name The name of a view to be created. The name can be schema-qualified.
column_name An optional list of columns names in the view. If not specified, the column names are deduced from the query.
query A query (a SELECT statement), which provides the columns and rows of the view.
Note For more information about valid queries, see the SELECT topic.

Notes

Views are read-only. The system does not allow the insert, update, or delete operations on views. You can obtain the effect of an updatable view by creating rules that convert the insert operations on the view into appropriate operations on other tables.

Access to tables referenced in the view is determined by permissions of the view owner. However, the functions that are called in the view are treated the same as those called from the query by using the view. Therefore, the user of a view must have permissions to call all functions that are used by the view.

Examples

Create a view that consists of all employees in department 30:

CREATE VIEW dept_30 AS SELECT * FROM emp WHERE deptno = 30;